Ediciones ENI Editorial | líder en informática
Tablas dinámicas y VBA
Introducción
El objetivo de este capítulo no es el de convertirle en desarrollador VBA para Excel, pero si necesita automatizar tareas para personas que no dominan Excel, las macros y procedimientos VBA pueden serle de gran utilidad.
Dispone de dos métodos para crear macros:
-
Guardar primero la macro para editarla después.
-
Escribir directamente la macro en Visual Basic.
El primer método deja a Excel la tarea de escribir el texto de la macro, lo que evita los errores de sintaxis y evita que el usuario tenga que dominar el lenguaje Visual Basic.
El segundo método exige que el usuario domine el lenguaje Visual Basic.
En primer lugar, vamos a poner en práctica el primer método.
Abra el libro Incidentes.xlsx.
Cada fila de la hoja Incidentes registra una avería acaecida en una cadena de producción.
Se ha creado un rango dinámico (BaseDatos) que se ha utilizado como fuente de la tabla dinámica presentada en la hoja TD.
Para familiarizarse con la sintaxis VBA relativa a las tablas dinámicas, vamos a crear dos macros a fin de modificar la disposición de nuestra tabla dinámica dejando que Excel grabe nuestras acciones.
-
La primera macro va a mostrar el coste total de la mano de obra por línea de fabricación.
-
La segunda va a mostrar el coste total de la mano de obra por tipo de avería.
Crear una tabla dinámica con la ayuda de una macro
Active la pestaña Programador
Si no ha trabajado nunca con la pestaña Programador, actívela:
Haga clic derecho en la cinta y haga clic en la opción Personalizar la cinta de opciones.
En la categoría Personalizar cinta de opciones, en la lista Pestañas principales, active la casilla Programador y haga clic en Aceptar.
A la derecha de la pestaña Vista, se ha añadido la pestaña Programador.
Grabar las macros
Durante la grabación de una macro, Excel transcribe todas nuestras acciones al lenguaje VBA. Si comete algún error durante la grabación, este error se grabará y se reproducirá en cada ejecución de la macro.
Tenga cuidado en la fase de grabación, vaya paso a paso.
Para que la macro pueda grabar la ubicación del cursor en la hoja correcta, en primer lugar haga clic en una celda de la hoja INCIDENTES.
En la pestaña Programador, grupo Código, haga clic en el botón Grabar macro.
Introduzca el nombre de la macro en el cuadro de diálogo y haga clic en Aceptar.
El atajo de teclado le permitirá más tarde ejecutar rápidamente la macro. La descripción permite explicar qué hace la macro.
A partir de ahora, Excel graba todas sus acciones. Fíjese en la esquina inferior izquierda de su pantalla en el botón...
Modificar las macros
Con objeto de que nuestra tabla resulte más agradable a la vista, vamos a añadir una imagen que se va a modificar en función de la estadística que se muestre. Para ello, vamos a usar las imágenes ImgElec.png e ImgMug.png.
En primer lugar, inserte un rectángulo debajo de la tabla (pestaña Insertar - Ilustraciones - Formas - Rectángulo).
Cámbiele el nombre: pestaña Inicio - grupo Edición - Buscar y seleccionar - Panel de selección.
En el panel Selección, reemplace el nombre del rectángulo por MarcoFoto.
Acceda al editor de VisualBasic para editar los dos procedimientos.
El listing de las macros se detalla a continuación:
Sub ManoObra_LínProd ()
' ManoObra_LínProd Macro
Dim RutaImagen As String
' Definición de la ruta de la imagen
RutaImagen = "D:\2019-Excel TD\Cap-10\ImgMug.png"
'Modificación de la imagen
ActiveSheet.Shapes.Range(Array("MarcoFoto")).Select
With Selection.ShapeRange.Fill
.Visible = msoTrue
.UserPicture RutaImagen
End With
' Cálculo del total de mano de obra por línea...Complementos
Si tiene que crear otras macros, utilice siempre para empezar el modo de grabación, lo que le permitirá obtener fácilmente la sintaxis Visual Basic de las diferentes funcionalidades de las tablas dinámicas. Así podrá obtener y modificar el código para filtrar, agrupar, modificar los formatos…
A continuación, le mostramos algunos ejemplos de código.
No mostrar la lista de campos
ActiveWorkbook.ShowPivotTableFieldList = False Dar formato a los valores de la tabla en euros
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields( _
"Suma de IMPORTE")
.NumberFormat = "#.##0,00 €"
End With Actualizar una tabla dinámica
ActiveSheet.PivotTables("Tabla dinámica1").PivotCache.Refresh Actualizar todas las tablas dinámicas
ActiveWorkbook.RefreshAll Eliminar los totales de filas
ActiveSheet.PivotTables("Tabla dinámica1").ColumnGrand = False Eliminar los totales de columnas
ActiveSheet.PivotTables("Tabla dinámica1").RowGrand = False Mostrar el total de columnas y ocultar el total de filas
With ActiveSheet.PivotTables("Tabla dinámica1")
.ColumnGrand = False ...