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  ...

Tablas dinámicas y VBA