Estas son mis notas del programa de mi aparición en el Episodio 33 de Call for Help en TechTV Canada.
Grabadora de macros VBA
Cada copia de Excel vendida desde 1995 incluye el increíblemente poderoso Visual Basic para Aplicaciones (VBA) que se esconde detrás de las celdas. Si tiene Excel, tiene VBA.
VBA le permite automatizar cualquier cosa que pueda hacer en Excel, además de hacer más cosas que generalmente no son posibles en Excel.
Por suerte para nosotros, Microsoft incluyó una grabadora de macros con Excel. Esto le permite escribir código VBA sin ser un programador. Desafortunadamente, la grabadora de macros tiene fallas. No producirá código que funcione siempre. La grabadora de macros lo acercará, pero en muchos casos es necesario corregir un poco el código para que funcione de manera confiable. En otros casos, debe saber cuándo utilizar la grabación relativa para que la macro funcione.
Tracy Syrstad y yo escribimos VBA y Macros Microsoft Excel 2016 para ayudarlo a comprender las limitaciones del código grabado y enseñarle cómo corregir el código grabado en algo que funcione en todo momento.
En el programa, demostré el proceso de grabación de una macro que funcionará perfectamente si sabe cómo usar el botón Referencia relativa. Alguien me había dado una hoja de cálculo de Excel con cientos de nombres y direcciones. Querían etiquetas postales. Si alguna vez ha utilizado la función de combinación de correspondencia de Microsoft Word, sabe que necesita cada campo en una nueva columna en la hoja de trabajo. En cambio, esta hoja de trabajo en particular tenía los datos en la columna A.
El proceso para arreglar una sola etiqueta es bastante tedioso.
- Comience con el puntero de celda en el nombre en la columna A.
- Presione la flecha hacia abajo para moverse a la dirección
- Ctrl + x para cortar
- Flecha arriba, flecha derecha para pasar a la columna B junto al nombre
- Ctrl + v para pegar
- Flecha hacia abajo dos veces, flecha hacia la izquierda una vez para moverse a la ciudad
- Ctrl + x para cortar
- Flecha arriba dos veces, flecha derecha tres veces
- Ctrl + v para pegar
- Flecha izquierda dos veces, flecha abajo una vez para pasar a la fila ahora en blanco.
- Mantenga presionada la tecla Mayús mientras presiona la flecha hacia abajo dos veces
- Alt + edr para eliminar las filas en blanco
- Flecha hacia arriba y flecha hacia abajo para seleccionar solo el nombre nuevamente.
Aquí está la animación que muestra estos pasos:
Configuración de Excel para permitir macros
Aunque cada copia de Excel contiene VBA, de forma predeterminada, Microsoft desactiva la capacidad de ejecutar macros. Debe realizar un ajuste único para permitir que se ejecuten las macros. Abre Excel. En el menú, seleccione Herramientas> Macro> Seguridad. Si su nivel de seguridad de macros está establecido actualmente en alto, cámbielo a Medio.
Preparación para grabar la macro
Piense en los pasos necesarios para lograr la tarea. Desea asegurarse de comenzar con el puntero de celda en un nombre y terminar con él en el siguiente nombre. Esto le permitirá ejecutar repetidamente la macro muchas veces. Cuando tenga los pasos listos, encienda la grabadora de macros. En el menú, seleccione Herramientas> Macro> Grabar nueva macro.
Si va a ejecutar esta macro durante muchos días, debe darle un nombre útil. Si es una macro de una sola vez para automatizar una tarea de una sola vez, entonces dejar el nombre de la macro como Macro1 probablemente esté bien. El campo importante aquí es el campo de la tecla de método abreviado. Si está creando una macro para un uso único, asigne la macro a una tecla de método abreviado como Ctrl + a; es bastante fácil presionar Ctrl + a repetidamente ya que las teclas están cerca una de la otra. Si va a crear una macro que utilizará todos los días, entonces desea asignar la macro a una tecla que aún no sea una combinación importante de teclas de método abreviado. Ctrl + j o Ctrl + k son buenas opciones.
Las macros de uso único se deben almacenar en ThisWorkbook. Si necesita utilizar la macro repetidamente en muchos libros de trabajo diferentes, puede almacenar la macro en el Libro de macros personal.
Ahora se le presenta la barra de herramientas más pequeña de todo Excel; la barra de herramientas Detener grabación. Tiene solo dos íconos y se ve así:
Si esta barra de herramientas se interpone en su camino, no presione la X para cerrarla. En su lugar, tome la barra azul y arrastre la barra de herramientas a una nueva ubicación. El acto de mover la barra de herramientas no se registra en la macro. Si cierra accidentalmente la barra de herramientas, recupérela con Ver> Barras de herramientas> Detener grabación. Sin embargo, esta acción quedará registrada.
El primer botón de la barra de herramientas es el botón "Detener grabación". Esto se explica por sí mismo. Cuando haya terminado de grabar la macro, presione la barra de herramientas para detener la grabación.
El botón más importante (y rara vez entendido) es el botón "Referencia relativa".
En nuestro ejemplo actual, debe presionar el botón Referencia relativa antes de comenzar. Si graba una macro con Referencias relativas activadas, Excel registrará pasos como este:
- Mover una celda hacia abajo
- Cortar la celda actual
- Mover una celda hacia arriba
- Mover una celda a la derecha
- Pegar
- etc.
Si, en cambio, registrara la macro sin referencias relativas, la macro registraría estos pasos:
- Mover a la celda A4
- Cortar celda A4
- Mover a la celda A3
- Mover a la celda B3
- Pegar en la celda B3
- etc.
Esto sería terriblemente incorrecto: necesitamos que la macro funcione en celdas que están a 1 y 2 celdas del punto de partida de la macro. A medida que ejecuta la macro una y otra vez, el punto de inicio será la fila 4, la fila 5, la fila 6, etc. Al grabar la macro sin las referencias relativas activadas, la macro siempre se ejecutará en la fila 3 como punto de partida.
Sigue estos pasos:
- Elija el botón de referencia relativa en la barra de herramientas Detener grabación
- El puntero de celda ya debería estar en un nombre en la columna A.
- Presione la flecha hacia abajo para moverse a la dirección
- Ctrl + x para cortar
- Flecha arriba, flecha derecha para pasar a la columna B junto al nombre
- Ctrl + v para pegar
- Flecha hacia abajo dos veces, flecha hacia la izquierda una vez para moverse a la ciudad
- Ctrl + x para cortar
- Flecha arriba dos veces, flecha derecha tres veces
- Ctrl + v para pegar
- Flecha izquierda dos veces, flecha abajo una vez para pasar a la fila ahora en blanco.
- Mantenga presionada la tecla Mayús mientras presiona la flecha hacia abajo dos veces
- Alt + edr para eliminar las filas en blanco
- Flecha hacia arriba y flecha hacia abajo para seleccionar el siguiente nombre en la lista.
- Pulsa la barra de herramientas Detener grabación
- Guardar el libro de trabajo
- Pruebe la macro presionando la tecla de método abreviado asignada anteriormente. Debería arreglar perfectamente el siguiente nombre en la lista.
Una vez que vea que la macro funciona como lo desea, puede mantener presionada la tecla Ctrl + a para corregir rápidamente algunos nombres por segundo. La lista completa de 500 nombres se puede arreglar en uno o dos minutos.
Sugerencia adicional: no en el programa
Puede envolver fácilmente la macro en un ciclo simple para que corrija los 500 nombres sin tener que presionar Ctrl + varios cientos de veces.
Presione la tecla Alt + F11 para abrir el editor de macros.
Si no ve el panel Proyecto - VBAProject, presione Ctrl + r.
Haga clic con el botón derecho en Module1 y elija Ver código. Verá un código que se ve así:
Ahora, no tienes que entender qué hace este código, pero sabes que queremos ejecutar todo en esa macro una vez por cada nombre que tengamos. Si sabe que hay 462 nombres, puede agregar 2 líneas para ejecutar el código 462 veces. En la parte superior de la macro, inserte una nueva línea con las palabras " For i = 1 to 462". En la parte inferior de la macro, inserte una línea que diga " Next i". Esto le dice a VBA que ejecute el código dentro de 462 veces.
Conclusión
Después de esta simple macro, mostré un ejemplo en el programa de una macro muy compleja. Esta macro creó tablas dinámicas y gráficos para 46 departamentos de una empresa. Este informe solía tomar 40 horas al mes. La macro de VBA ahora se ejecuta y crea los 46 informes en menos de 2 minutos.
El libro VBA y Macros Microsoft Excel 2016 lo guiará por la curva de aprendizaje para que pueda pasar de grabar macros simples como esta a ejecutar informes muy complejos que pueden ahorrarle cientos de horas al año. Por supuesto, si no desea aprender VBA, contrate a un consultor de Excel para diseñar un informe para usted.
