Tutorial de macros de Excel: cómo grabar y crear sus propias macros de Excel

Las hojas de cálculo son infinitamente flexibles, especialmente en Excel, una de las aplicaciones de hojas de cálculo más poderosas. Sin embargo, la mayoría de la gente usa solo un pequeño porcentaje de sus aparentemente innumerables posibilidades. Sin embargo, no se necesitan años de capacitación para aprovechar el poder de las hojas de cálculo y la magia de automatización de las macros de Excel.

Probablemente ya uses funciones como =sum(A1:A5), los simples fragmentos de texto que suman, promedian y calculan sus valores. Son los que hacen de las hojas de cálculo una herramienta poderosa para procesar números y texto. Las macros son el siguiente paso: son herramientas que automatizan tareas simples y lo ayudan a hacer más en menos tiempo. A continuación, le mostramos cómo desbloquear esa nueva parte de su conjunto de habilidades de Excel creando sus propias macros en Excel.

---

---

¿Qué son las macros de Excel?

Las macros son códigos que automatizan el trabajo en un programa; le permiten agregar sus propias características y mejoras pequeñas para ayudarlo a lograr exactamente lo que necesita hacer, rápidamente con solo un clic de un botón. En una herramienta de hoja de cálculo como Excel, las macros pueden ser especialmente poderosas. Escondidas detrás de la interfaz de usuario normal, son más poderosas que las funciones estándar que ingresa en una celda (p. Ej.=IF(A2<100,100,A2)).

Estas macros hacen que Excel funcione para usted. Reemplazan las acciones que realiza manualmente, desde dar formato a celdas, copiar valores y calcular totales. Entonces, con unos pocos clics, puede reemplazar rápidamente las tareas repetitivas.

Para hacer estas macros, simplemente puede registrar sus acciones en Excel para guardarlas como pasos repetibles o puede usar Visual Basic para Aplicaciones (VBA), un lenguaje de programación simple que está integrado en Microsoft Office. Le mostraremos cómo usar ambos a continuación, y compartiremos ejemplos de macros de Excel para ayudarlo a comenzar.

¿Por qué utilizar macros de Excel?

Aprender a automatizar Excel es una de las formas más fáciles de acelerar su trabajo, especialmente porque Excel se utiliza en muchos procesos de trabajo. Supongamos que cada semana exporta datos analíticos de su sistema de gestión de contenido (CMS) para crear un informe sobre su sitio. El único problema es que esas exportaciones de datos no siempre están en un formato compatible con Excel. Son desordenados y, a menudo, incluyen muchos más datos de los que requiere su informe. Esto significa que debe limpiar filas vacías, copiar y pegar datos en el lugar correcto y crear sus propios gráficos para visualizar los datos y hacerlos fáciles de imprimir. Todos estos pasos pueden llevarle horas completarlos.

Si solo hubiera una manera de presionar un botón y dejar que Excel lo haga por usted en un instante … Bueno, ¿puede adivinar lo que voy a decir a continuación?

¡Hay!

Todo lo que necesita es un poco de tiempo para configurar una macro, y luego ese código puede hacer el trabajo por usted automáticamente cada vez. Ni siquiera es tan difícil como parece.

Cómo construir su primera macro de Excel

Ya conoce Excel y está familiarizado con su cuadrícula de celdas donde ingresa su texto y funciones. Sin embargo, para crear macros de Excel, necesitará una herramienta adicional integrada en Excel: el Editor de Visual Basic.

Conoce al editor de VBA

Excel tiene una herramienta incorporada para escribir macros llamada Editor de Visual Basic, o VBA Editor para abreviar. Para abrir eso, abra una hoja de cálculo y use el atajo Alt + F11 (para Mac: Fn + Shift + F11).

La nueva ventana que aparece se llama VBA Editor. Es donde editarás y almacenarás todas tus macros. Su diseño puede verse un poco diferente al de esta captura de pantalla, pero puede mover las ventanas en el orden que desee. Solo asegúrese de mantener abierto el panel del Explorador de proyectos para que pueda editar fácilmente sus macros.

Sus macros estarán formadas por «Módulos» o archivos con su código VBA. Agregará un nuevo módulo o abrirá uno existente en el Editor de VBA, luego escriba el código que desee. Para insertar un módulo, haga clic en «Insertar» y luego haga clic en «Módulo». Luego verá el espacio en blanco para escribir su código a la derecha.

Cómo grabar una macro de Excel

Hay dos formas de crear una macro: codificarla o grabarla. El enfoque principal de este artículo es el primero, pero grabar una macro es tan simple y útil que también vale la pena explorarlo. Grabar una macro es una buena forma de conocer los conceptos básicos de VBA. Más adelante, sirve como almacenamiento útil para el código que no necesita memorizar.

Cuando graba una macro, le dice a Excel que inicie la grabación. Luego, realiza las tareas que desea traducir al código VBA. Cuando haya terminado, dígale a Excel que detenga la grabación y podrá usar esta nueva macro para repetir las acciones que acaba de realizar una y otra vez.

Esto tiene limitaciones, por lo que no puede automatizar todas las tareas o convertirse en un experto en automatización con solo grabar. A veces, aún necesitará escribir o editar el código manualmente. Pero sigue siendo una forma práctica de empezar. He aquí cómo: 1. Vaya a la pestaña «Ver» de la cinta y haga clic en la pequeña flecha debajo del botón «Macros». 2. Luego haga clic en «Grabar macro» 3. Escriba el nombre de su macro y haga clic en «Aceptar» para iniciar la grabación. 4. Realice las acciones en su hoja de cálculo que desea convertir en una macro. 5. Cuando haya terminado, vaya a la pestaña «Ver», haga clic en la pequeña flecha debajo del botón «Grabar macro» nuevamente y seleccione «Detener grabación».

Ahora, usa el atajo Alt + F11 (para Mac: Fn + Shift + F11) para abrir el Editor de VBA y haga doble clic en «Módulo 1» en el Explorador de proyectos.

¡Este es tu primer código! Increíble, ¿verdad? Es posible que no lo haya escrito usted mismo, pero aún así se genera a partir de sus acciones.

El tuyo probablemente se vea diferente al mío. ¿Puedes adivinar lo que hace mi código?

• Sub Makebold es solo el texto Sub seguido del nombre que ingresé cuando comencé a grabar.

• La línea verde en realidad no hace nada; es un comentario en el que puede agregar una explicación de lo que hace la macro.

• Selection.Font.Bold = True hace que los valores en las celdas seleccionadas negrita.

• End sub simplemente le dice a Excel que la macro se detiene aquí.

Ahora, ¿qué pasará si cambio el True parte de la tercera línea para False? La macro utilizaría entonces cualquier formato en negrita de la selección en lugar de hacerlo en negrita.

Así es como grabas una macro simple. Pero el verdadero poder de las macros viene cuando puede escribirlas usted mismo, así que comencemos a aprender a escribir código VBA simple.

Cómo codificar sus propias macros de Excel

Las macros son solo fragmentos de código en Excel que cumplen sus deseos. Una vez que escriba el código en el Editor de VBA, puede ejecutarlo y dejar que el código haga su magia en su hoja de cálculo. Pero lo que es aún mejor es construir su macro en su hoja de cálculo, y la mejor herramienta para eso son los botones.

Entonces, primero, antes de comenzar a codificar, agreguemos un botón para ejecutar nuestra macro.

Agregue un botón para ejecutar su macro

Puede usar varios objetos de Excel como botones para ejecutar macros, pero prefiero usar una forma de la pestaña «Insertar». Cuando haya insertado su forma, haga clic derecho y seleccione «Asignar macro …» Luego seleccione la macro que desea ejecutar cuando se haga clic en la forma, tal vez la que acaba de hacer con una grabación y guárdela haciendo clic en «Aceptar».

Ahora, cuando hace clic en la forma que acabamos de convertir en un botón, Excel ejecutará la macro sin tener que abrir el código cada vez.

Hay otra cosa a tener en cuenta antes de comenzar: guardar su hoja de cálculo con Macros. De forma predeterminada, los archivos de hoja de cálculo de Excel con un .xlsx la extensión no puede incluir macros. En su lugar, cuando guarde su hoja de cálculo, seleccione el «Libro de trabajo habilitado para macros de Excel (*.xlsm) «y agregue su nombre de archivo como de costumbre.

Continúe y haga eso para guardar su hoja de cálculo antes de comenzar a codificar.

---

¡Ahora, comencemos con la codificación real!

Copiar y pegar es la forma más sencilla de mover datos, pero sigue siendo tedioso. ¿Y si su hoja de cálculo pudiera hacer eso por usted? Con una macro, podría. Veamos cómo codificar una macro que copiará datos y los moverá en una hoja de cálculo.

Abra el archivo del proyecto que descargó anteriormente y asegúrese de que la hoja «Copiar, cortar y pegar» esté seleccionada. Esta es una base de datos de empleados de muestra con los nombres, departamentos y salarios de algunos empleados.

Intentemos copiar todos los datos de las columnas A a C en D a F usando VBA. Primero, veamos el código que necesitamos:

Copiar celdas con VBA

Copiar en VBA es bastante fácil. Simplemente inserte este código en el Editor de VBA: Range("Insert range here").Copy. He aquí algunos ejemplos:

¿Recuerdas cuando grabaste una macro antes? La macro tenía Sub Nameofmacro() y End sub en la línea superior e inferior del código. Estas líneas siempre deben incluirse. Excel también lo hace fácil: cuando escribe «Sub» seguido del nombre de la macro al principio del código, el End sub se inserta automáticamente en la línea inferior.

Pegar celdas con VBA

El pegado se puede hacer de diferentes formas dependiendo de lo que desee pegar. El 99% del tiempo, necesitará una de estas dos líneas de código:

• Range("The cell/area where you want to paste").Pastespecial ← pega normalmente (fórmulas y formato)

• Range("The cell/area where you want to paste").Pastespecial xlPasteValues ← solo pega valores

Células de corte con VBA

Si desea reubicar sus datos en lugar de copiarlos, debe cortarlos. Cortar es bastante fácil y sigue exactamente la misma lógica que copiar.

Aquí está el código: Range("Insert range here").Cut

Al cortar, no puede usar el comando ‘PasteSpecial’. Eso significa que no puede pegar solo valores o solo formatear. Por lo tanto, necesita estas líneas para pegar sus celdas con VBA: Range («Insertar donde desea pegar»). Seleccione ActiveSheet.Paste

Por ejemplo, aquí está el código que necesitaría para cortar el rango A:C y pégalo en D1:

• Range("A:C").Cut

• Range("D1").Select

• ActiveSheet.Paste

---

Copiar, cortar y pegar son acciones simples que se pueden realizar manualmente sin sudar. Pero cuando copia y pega las mismas celdas varias veces al día, un botón que lo haga por usted puede ahorrar mucho tiempo. Además, puede combinar copiar y pegar en VBA con algún otro código interesante para hacer aún más en su hoja de cálculo automáticamente.

Agregar bucles a VBA

Acabo de mostrarle cómo realizar una acción simple (copiar y pegar) y adjuntarla a un botón, para que pueda hacerlo con un clic del mouse. Esa es solo una acción automatizada. Sin embargo, cuando tiene el código para repetirse, puede realizar tareas de automatización más largas y complejas en segundos.

Eche un vistazo a la hoja «Bucles» en el archivo del proyecto. Son los mismos datos que en la hoja anterior, pero cada tercera fila de datos ahora se mueve una columna a la derecha. Este tipo de estructura de datos defectuosa no es inusual cuando se exportan datos de programas más antiguos.

Esto puede llevar mucho tiempo arreglarlo manualmente, especialmente si la hoja de cálculo incluye miles de filas en lugar de los pequeños datos de muestra en este archivo de proyecto.

Hagamos un bucle que lo solucione por ti. Ingrese este código en un módulo, luego mire las explicaciones debajo de la imagen:

1. Esta línea asegura que el ciclo comience en la celda superior izquierda de la hoja y no estropee accidentalmente los datos al comenzar en otro lugar.

2. La For i = 1 To 500 línea significa que el número de veces que se ha ejecutado el bucle (representado por i) es un número creciente que comienza con 1 y termina con 500. Esto significa que el ciclo se ejecutará 500 veces. El número de veces que debe ejecutarse el bucle depende de las acciones que desee que realice. Use su buen sentido aquí. 500 veces es demasiado para nuestro conjunto de datos de muestra, pero encajaría perfectamente si la base de datos tuviera 1500 filas de datos.

3. Esta línea reconoce la celda activa y le dice a Excel que mueva 3 filas hacia abajo y seleccione esa celda, que luego se convierte en la nueva celda activa. Si fuera cada cuarta fila la que se perdió en nuestros datos, en lugar de cada tercera, podríamos simplemente reemplazar el 3 con un 4 en esta línea.

4. Esta línea le dice a Excel qué hacer con esta celda recién seleccionada. En este caso, queremos eliminar la celda de tal manera que las celdas a la derecha de la celda se muevan hacia la izquierda. Eso se consigue con esta línea. Si quisiéramos hacer algo más con las filas fuera de lugar, este es el lugar para hacerlo. Si quisiéramos eliminar cada tercera fila por completo, entonces la línea debería haber sido: Selection.Entirerow.delete.

5. Esta línea le dice a Excel que no hay más acciones dentro del ciclo. En este caso, 2 y 5 son el marco del bucle y 3 y 4 son las acciones dentro del bucle.

Cuando ejecutamos esta macro, dará como resultado un conjunto de datos ordenado sin filas fuera de lugar.

Agregar lógica a VBA

La lógica es lo que da vida a un fragmento de código al convertirlo en algo más que una máquina que puede realizar acciones simples y repetirse. La lógica es lo que hace que una hoja de Excel sea casi humana: le permite tomar decisiones inteligentes por sí misma. ¡Usemos eso para automatizar las cosas!

Esta sección trata sobre declaraciones IF que habilita la lógica «si-esto-entonces-aquello», al igual que la función IF en Excel.

Digamos que la exportación desde nuestro sitio web CMS fue incluso más errónea de lo esperado. Cada tercera fila todavía está fuera de lugar, pero ahora, algunas de las filas fuera de lugar se colocan 2 columnas a la derecha en lugar de 1 columna a la derecha. Eche un vistazo a la hoja «instrucción IF» en el archivo del proyecto para ver cómo se ve.

¿Cómo tenemos esto en cuenta en nuestra macro? ¡Agregamos una instrucción IF al ciclo!

Formulemos lo que queremos que haga Excel:

Comenzamos en la celda A1. Luego bajamos tres filas (a la celda A4, A7, A10, etc.) hasta que no haya más datos. Cada vez que bajamos tres filas, comprobamos esta fila para ver si los datos se han extraviado en 1 o 2 columnas. Luego, mueva los datos de la fila 1 o 2 columnas a la izquierda.

Ahora, traduzcamos esto al código VBA. Comenzaremos con un ciclo simple, como antes:

Lo único que necesitamos ahora es escribir lo que debería suceder dentro del ciclo. Esta es la parte de «ir tres filas hacia abajo» que desarrollamos en la sección sobre bucles. Ahora estamos agregando una declaración IF que verifica cuánto se extraviaron los datos y los corrige en consecuencia.

Este es el código final para copiar en el editor de su módulo, y cada paso se explica a continuación:

1. Esta es la primera parte de la declaración IF. Dice que la celda a la derecha de la celda activa (o Activecell.Offset(0,1) en el código VBA) está en blanco (representado por = "") hacer algo. Este algo es exactamente la misma acción que hicimos cuando creamos el ciclo en primer lugar: eliminar la celda activa y mover la fila activa una celda a la izquierda (logrado con el Selection.Delete Shift:=xlToLeft código). Esta vez, lo hacemos dos veces en lugar de una, porque hay dos celdas en blanco en el lado izquierdo de la fila.

2. Si lo anterior no es cierto y la celda a la derecha de la celda activa no está en blanco, entonces la celda activa está en blanco. Por lo tanto, solo necesitamos eliminar la celda activa y mover la fila activa una celda hacia la izquierda una vez.

La sentencia IF siempre debe terminar con un End If para decirle a Excel que ha terminado de ejecutarse. Después de la instrucción IF, el ciclo puede ejecutarse una y otra vez, repitiendo la instrucción IF cada vez

¡Felicitaciones, acaba de crear una macro que puede limpiar datos desordenados! Vea la animación a continuación para verlo en acción (si aún no lo ha probado usted mismo).

Automatizar Excel sin macros

Las macros de Excel solo tienen un problema: están vinculadas a su computadora y no pueden ejecutarse en la aplicación web de Excel o en su dispositivo móvil. Y son mejores para trabajar con datos que ya están en su hoja de cálculo, lo que dificulta la obtención de nuevos datos de sus otras aplicaciones en su hoja de cálculo.

La herramienta de integración de aplicaciones Zapier puede ayudar. Conecta la edición Office 365 for Business de Excel con cientos de otras aplicaciones (Stripe, Salesforce, Slack y más) para que pueda registrar datos en su hoja de cálculo automáticamente o iniciar tareas en otras aplicaciones directamente desde Excel.

Así es como funciona. Supongamos que desea guardar las entradas del formulario de Typeform en una hoja de cálculo de Excel. Simplemente cree una cuenta de Zapier y haga clic en el botón en la esquina superior derecha. Luego, seleccione en el selector de aplicaciones y configúrelo para que observe su formulario en busca de nuevas entradas.

Pruebe su Zap, luego haga clic para agregar otro paso a su Zap. Esta vez seleccionaremos la aplicación y elegiremos nuestra hoja de cálculo. También puede actualizar una fila o buscar en su hoja de cálculo una fila específica si lo desea.

Ahora, elija su hoja de cálculo y la hoja de trabajo, luego haga clic en el icono + a la derecha de cada fila de la hoja de cálculo para seleccionar el campo de formulario correcto para guardar en esa fila de la hoja de cálculo. Guarde y pruebe su integración de Zapier, luego enciéndala. Luego, cada vez que se complete su formulario de Typeform, Zapier guardará esos datos en su hoja de cálculo de Excel.

A continuación, se muestran algunas formas excelentes de comenzar a automatizar Excel con Zapier en una pocos clics, o cree sus propias integraciones de Excel para conectar sus hojas de cálculo a sus aplicaciones favoritas.

Administre los datos de su hoja de cálculo

Guardar entradas de formulario en una hoja de cálculo de Excel

Registrar datos en una hoja de cálculo de Excel

Trabaje desde su hoja de cálculo

---

¡Construya sus propias macros!

Ahora ha aprendido algunas de las herramientas de VBA más esenciales para crear una macro para limpiar datos y automatizar su trabajo. Juegue con los trucos y herramientas que acaba de aprender, porque son los fundamentos de la automatización en VBA. Recuerda usar la grabadora de macros (y Google) cuando sientas que estás sobre tu cabeza.

Para obtener más información, aquí hay algunos recursos adicionales que lo ayudarán a aprovechar al máximo las macros de Excel: