Crear gráficos e informes de tabla dinámica con VBA en Excel 2010

Artículo
07/01/2015

Resumen: obtenga información acerca de cómo usar Visual Basic para Aplicaciones (VBA) en Microsoft Excel 2010 con el fin de crear y manipular gráficos e informes de tabla dinámica. El uso de código para crear informes y gráficos de tabla dinámica puede ayudarle a realizar estas tareas varias veces y con mayor eficacia.

Última modificación: domingo, 05 de junio de 2011

Hace referencia a: Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA

En este artículo
Información general sobre el uso de VBA con gráficos e informes de tabla dinámica
Crear un informe de tabla dinámica mediante VBA
Crear un gráfico mediante VBA
Realizar cambios en un gráfico incrustado
Crear un gráfico basado en un informe de tabla dinámica
Conclusión
Recursos adicionales

Se aplica a: Microsoft Excel 2010

Publicado: junio de 2011

Proporcionado por: Icono de los miembros de la comunidad Dr. Gerard M. Verschuuren, Mr. Excel (en inglés) | Mark Roberts, Microsoft Corporation

Contenido

Información general sobre el uso de VBA con gráficos e informes de tabla dinámica
Crear un informe de tabla dinámica mediante VBA
Crear un gráfico mediante VBA
Realizar cambios en un gráfico incrustado
Crear un gráfico basado en un informe de tabla dinámica
Conclusión
Recursos adicionales

Descargue el libro de ejemplo sobre la creación de gráficos e informes de tabla dinámica con VBA (PivotTablesAndCharts.xlsm) (en inglés)

Información general sobre el uso de VBA con gráficos e informes de tabla dinámica

Si lee este artículo y descarga el libro de ejemplo, podrá aprender a crear gráficos e informes de tabla dinámica mediante código de Visual Basic para Aplicaciones (VBA). Para ejecutar el código descrito en este artículo con datos de ejemplo, descargue el libro de ejemplo PivotTablesAndCharts (en inglés).

Crear un informe de tabla dinámica mediante VBA

El informe de tabla dinámica creado por la macro del libro de ejemplo CreatePivot del PivotTablesAndCharts se muestra en la figura 1.

Figura 1. Informe de tabla dinámica

Informe de tabla dinámica

Este informe de tabla dinámica se basa en la tabla de datos de la hoja de cálculo Employees del libro de ejemplo.

Tal como se muestra en las líneas de código siguiente, la macro CreatePivot activa la hoja de cálculo y luego usa el método PivotTableWizard para iniciar el proceso de creación del informe de tabla dinámica.

ActiveWorkbook.Sheets("Employees").Select
Range("A1").Select
Set objTable = Sheet1.PivotTableWizard

Nota

Si presiona F8 en el editor de código para recorrer código de la macro CreatePivot línea por línea, verá que la línea de código que llama al método PivotTableWizard agrega una nueva hoja de cálculo al libro y luego crea el nuevo informe de tabla dinámica en esa hoja.

El método PivotTableWizard devuelve un objeto de Tabla dinámica, que tiene asociada una colección de Campos dinámicos. La macro CreatePivot continúa agregando objetos de Campo dinámico a la colección de Campos dinámicos para especificar los siguientes campos de informe de tabla dinámica.

Un campo de fila llamado DEPT
Un campo de columna llamado LOCATION
Un campo de datos basado en el campo SALARIO que usa la función SUM

Set objField = objTable.PivotFields("DEPT")
objField.Orientation = xlRowField

Set objField = objTable.PivotFields("LOCATION")
objField.Orientation = xlColumnField

Set objField = objTable.PivotFields("SALARY")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "$ #,##0"

Nota

Aunque en este ejemplo solo se usan un campo de fila y un campo de columna, puede agregar otros campos a la colección de campos dinámicos para crear informes de tabla dinámica que tengan varios niveles en cada dimensión.

La macro CreatePivot también agrega un campo de página para habilitar que el campo GENDER filtre el informe, tal como se muestra en las líneas de código siguientes.

Set objField = objTable.PivotFields("GENDER")
objField.Orientation = xlPageField

A continuación, la macro CreatePivot muestra el nuevo informe de tabla dinámica en vista previa de impresión.

ActiveSheet.PrintPreview

Tal como se muestra en las líneas de código siguientes, una vez el usuario ha cerrado la vista previa de impresión, la macro CreatePivot le pregunta si desea eliminar el nuevo informe de tabla dinámica. La línea que establece la propiedad DisplayAlerts en False suprime la advertencia integrada de Microsoft Excel que indica "Pueden existir datos en las hojas seleccionadas para eliminar".

Application.DisplayAlerts = False
If MsgBox("Delete the PivotTable?", vbYesNo) = vbYes Then
    ActiveSheet.Delete
End If
Application.DisplayAlerts = True

En el listado siguiente se muestra todo el código para la macro CreatePivot.

Sub CreatePivot()
    ' Creates a PivotTable report from the table on Sheet1
    ' by using the PivotTableWizard method with the PivotFields
    ' method to specify the fields in the PivotTable.
    Dim objTable As PivotTable, objField As PivotField
    
    ' Select the sheet and first cell of the table that contains the data.
    ActiveWorkbook.Sheets("Employees").Select
    Range("A1").Select
    
    ' Create the PivotTable object based on the Employee data on Sheet1.
    Set objTable = Sheet1.PivotTableWizard
    
    ' Specify row and column fields.
    Set objField = objTable.PivotFields("DEPT")
    objField.Orientation = xlRowField
    Set objField = objTable.PivotFields("LOCATION")
    objField.Orientation = xlColumnField
    
    ' Specify a data field with its summary
    ' function and format.
    Set objField = objTable.PivotFields("SALARY")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "$ #,##0"
    
    ' Specify a page field.
    Set objField = objTable.PivotFields("GENDER")
    objField.Orientation = xlPageField
    
    ' Preview the new PivotTable report.
    ActiveSheet.PrintPreview
    
    ' Prompt the user whether to delete the PivotTable.
    Application.DisplayAlerts = False
    If MsgBox("Delete the PivotTable?", vbYesNo) = vbYes Then
        ActiveSheet.Delete
    End If
    Application.DisplayAlerts = True
 End Sub

Crear un gráfico mediante VBA

El gráfico creado por la macro CreateChart del libro de ejemplo se muestra en la figura 2.

Figura 2. Gráfico de columnas 3D

Gráfico de columnas 3D

Este gráfico se basa en los datos de la hoja de cálculo Table del libro de ejemplo.

Las primeras líneas de la macro CreateChart seleccionan la hoja de cálculo Table y luego le piden al usuario que seleccione el rango de datos para crear el gráfico. Si se establece el parámetro Default del método InputBox en Selection.Address y el parámetro Type en 8, se devolverá un objeto Rango con su propiedad Address definida como la referencia del rango seleccionado por el usuario.

ActiveWorkbook.Sheets("Table").Select

Set objSelection = _
    Application.InputBox(Prompt:="Select the columns and rows to chart", _
    Default:=Selection.Address, _
    Type:=8)

Las líneas de código siguientes comprueban si se ha seleccionado más de una celda y, si no es el caso, le piden al usuario que realice una selección apropiada.

If objSelection.Cells.Count = 1 Then
    MsgBox "You must select at least one row or column for the chart range."
    Exit Sub
End If

Las líneas de código siguientes hacen el trabajo de creación del gráfico. El método Add de la colección Charts crea una hoja de gráfico nueva y luego devuelve un objeto Gráfico que se asigna a la variable objChart. A continuación, la instrucción With usa esa variable para especificar el aspecto del gráfico, empezando con el uso del método SetSourceData para especificar el rango de datos seleccionado por el usuario. Tenga en cuenta que la línea .Legend.Delete se usa para eliminar la leyenda porque duplica la información mostrada en el tercer eje de un gráfico de columnas 3D.

Set objChart = Charts.Add
With objChart
    .SetSourceData objSelection
    .ChartType = xl3DColumn
    .Location xlLocationAsNewSheet
    .Legend.Delete
    .PlotBy = xlColumns
End With

Nota

En este ejemplo se crea un gráfico bastante simple. Las propiedades y los métodos del objeto Gráfico son bastante amplios. Lea el tema Miembros de gráfico para obtener información acerca de los diferentes métodos y propiedades que se pueden usar para especificar el aspecto de un gráfico.

Las líneas de código restantes de la macro CreateChart proporcionan algunos ejemplos en los que se pide al usuario que especifique propiedades adicionales que afecten al aspecto del gráfico, así como preguntar al usuario si desea eliminar el gráfico. En el listado siguiente se muestra todo el código de la macro CreateChart.

Sub CreateChart()
    ' Create a new chart sheet from the table on Sheet2
    ' by using the Add method of the Charts collection.
    Dim objSelection As Range, objChart As Chart
    
    ' Select the sheet that contains the data.
    ActiveWorkbook.Sheets("Table").Select
    
    ' Prompt the user to select the range to chart
    ' and set the Range object to the specified range.
    Set objSelection = _
        Application.InputBox(Prompt:="Select the columns and rows to chart", _
        Default:=Selection.Address, _
        Type:=8)
    
    ' Verify whether a selection was made.
    If objSelection.Cells.Count = 1 Then
        MsgBox "You must select at least one row or column for the chart range."
        Exit Sub
    End If
    
    ' Create a new chart sheet and specify its source data
    ' and appearance.
    Set objChart = Charts.Add
    With objChart
        .ChartType = xl3DColumn
        .SetSourceData objSelect
        .Location xlLocationAsNewSheet
        .Legend.Delete
        .PlotBy = xlColumns
    End With
    
    ' Ask the user whether to plot by rows instead.
    If MsgBox("Or plot by rows?", vbYesNo) = vbYes Then
        objChart.PlotBy = xlRows
    End If
    
    ' Prompt the user for a title.
    objChart.HasTitle = True
    objChart.ChartTitle.Text = InputBox("Title?")
    
    ' Ask the user whether to delete the chart.
    Application.DisplayAlerts = False
    If MsgBox("Delete chart?", vbYesNo) = vbYes Then
        ActiveSheet.Delete
    End If
    Application.DisplayAlerts = True
End Sub

Realizar cambios en un gráfico incrustado

La macro DynamicChart del libro de ejemplo muestra cómo cambiar el aspecto de un gráfico incrustado basado en la selección del usuario de filas y columnas enteras. El aspecto del gráfico después de ejecutar la macro y de seleccionar las columnas de datos North y East se muestra en la figura 3.

Figura 3. Gráfico dinámico

Gráfico de barra dinámica

Para tener acceso a un gráfico dinámico y trabajar con él, debe usar la colección ChartObjects del objeto Hoja de cálculo en lugar de la colección Charts del objeto Libro que se usó en el ejemplo de la sección anterior de este artículo. Las primeras líneas de código de la macro DynamicChart muestran cómo hacerlo activando la hoja de cálculo que contiene el gráfico incrustado y luego definiendo una variable de objeto Gráfico como el primer elemento de la colección ChartObjects de esa hoja.

ActiveWorkbook.Sheets("Table+Chart").Activate
Set objChart = ActiveSheet.ChartObjects(1).Chart

Las líneas de código siguientes le piden al usuario que seleccione filas o columnas para crear un gráfico y, a continuación, asignan esa selección a una variable de objeto Rango con la misma técnica que se mostró en la sección Crear un gráfico mediante VBA de este artículo.

Según la selección del usuario, el código determina si se usa la primera fila o la primera columna para las categorías del gráfico. Para ello, se compara el número de filas seleccionadas con el número de columnas seleccionadas. Si el número de filas es mayor, se seleccionarán los valores de la primera columna como categorías. Si el número de columnas es mayor, se seleccionarán como categorías los valores de la primera fila.

r = objSelection.Rows.Count
c = objSelection.Columns.Count
If r > c Then
    Set objCategories = Range(Cells(1, 1), Cells(r, 1))
Else
    Set objCategories = Range(Cells(1, 1), Cells(1, c))
End If

Las últimas líneas de código de la macro DynamicChart usan el método Union para crear un solo rango a partir de la selección del usuario y la fila o columna que el código determina para su uso en las categorías. Por último, el código transmite ese rango al método SetSourceData para actualizar la visualización del gráfico.

Set objSrcData = Union(objCategories, objSelection)
objChart.SetSourceData objSrcData

En el ejemplo de código siguiente se muestra todo el listado de la macro DynamicChart.

Sub DynamicChart()
    ' Adjusts the embedded chart on Sheet3.
    Dim objChart As Chart, objChObject As ChartObject
    Dim objSelection As Range, objSrcData As Range, objCategories As Range
    Dim r As Long, c As Long
    
    ' Activate the sheet that contains the chart.
    ActiveWorkbook.Sheets("Table+Chart").Activate
    
    ' Access the chart from the ChartObject collection
    ' of the active sheet.
    Set objChart = ActiveSheet.ChartObjects(1).Chart
    
    ' Prompt user to select the rows or columns to chart
    ' and set the Range object to the specified range.
    Set objSelection = _
        Application.InputBox(Prompt:="Select entire rows or columns to chart", _
        Default:=Selection.Address, _
        Type:=8)
    
    ' Determine whether the user selected rows or columns,
    ' and then use either the first row or first column
    ' as the range for categories.
    r = objSelection.Rows.Count
    c = objSelection.Columns.Count
    If r > c Then
        Set objCategories = Range(Cells(1, 1), Cells(r, 1))
    Else
        Set objCategories = Range(Cells(1, 1), Cells(1, c))
    End If
    
    ' Create a single range from the union of
    ' categories and selected data, and then
    ' update the chart.
    Set objSrcData = Union(objCategories, objSelection)
    objChart.SetSourceData objSrcData
End Sub

Crear un gráfico basado en un informe de tabla dinámica

También puede crear gráficos basados en un informe de tabla dinámica tal como lo demuestra la macro CreateChartForPivot en el libro de ejemplo. La macro inicia su proceso llamando a la macro CreatePivot descrita en la sección Crear un informe de tabla dinámica mediante VBA de este artículo.

Uno de los principales aspectos interesantes es la línea de código que obtiene acceso al informe de tabla dinámica recién creado a partir de la colección PivotTables de la nueva hoja de cálculo.

Set objPivot = ActiveSheet.PivotTables(1)

Otro aspecto interesante es la línea de código que usa la propiedad PivotTable para crear un rango que incluya todo el informe de tabla dinámica excluyendo los campos de página.

Set objPivRange = objPivot.TableRange1

Las últimas líneas de código de la macro CreateChartForPivot usan este rango para especificar los datos de origen y luego especificar el aspecto del gráfico. Tenga en cuenta que, de nuevo, se elimina la leyenda porque duplica la información del tercer eje de un gráfico de columnas 3D.

With objChart
    .SetSourceData objPivRange
    .ChartType = xl3DColumn
    .Legend.Delete
End With

En el ejemplo de código siguiente se muestra todo el listado de la macro CreateChartForPivot.

Sub CreateChartForPivot()
    ' Creates a chart based on a PivotTable report.
    Dim objPivot As PivotTable, objPivotRange As Range, objChart As Chart
    
    ' Call the CreatePivot macro to create a new PivotTable report.
    CreatePivot
   
    ' Determine whether the user deleted the PivotTable report,
    ' and if so, exit the macro.
    If ActiveSheet.PivotTables.Count = 0 Then Exit Sub
    
    ' Access the new PivotTable from the sheet's PivotTables collection.
    Set objPivot = ActiveSheet.PivotTables(1)
    
    ' Add a new chart sheet.
    Set objChart = Charts.Add
    
    ' Create a Range object that contains
    ' all of the PivotTable data, except the page fields.
    Set objPivotRange = objPivot.TableRange1
    
    ' Specify the PivotTable data as the chart's source data.
    With objChart
        .SetSourceData objPivotRange
        .ChartType = xl3DColumn
        .Legend.Delete
    End With
End Sub

Conclusión

En este artículo y en el libro de ejemplo PivotTablesAndCharts se ofrecen ejemplos de creación de gráficos e informes de tabla dinámica mediante código de VBA en Excel.