24 ejemplos útiles de macros de Excel para principiantes de VBA (listos para usar)

El uso de macros de Excel puede acelerar su trabajo y ahorrar mucho tiempo.

Una forma de obtener el código VBA es grabar una macro y obtener el código que genera.Sin embargo, el código de la grabadora de macros suele estar lleno de código que realmente no se necesita.La grabadora de macros también tiene algunas limitaciones.

Por lo tanto, vale la pena tener un conjunto útil de macros de VBA que pueda guardar en su bolsillo trasero y usar cuando lo necesite.

Si bien puede tomar algún tiempo codificar una macro VBA de Excel inicialmente, una vez que haya terminado, puede usarla como referencia y usarla la próxima vez que la necesite.

En este enorme artículo, enumeraré algunos ejemplos útiles de macros de Excel que a menudo necesito y guardo en mi bóveda privada.

Continuaré actualizando este tutorial con más ejemplos de macros.Por favor, deja un comentario si crees que algo debería estar en la lista.

Puede marcar esta página para referencia futura.

Ahora, antes de entrar en los ejemplos de macros y darle el código VBA, primero permítame mostrarle cómo usar el código de ejemplo.

Usando el código del ejemplo de macro de Excel

Estos son los pasos a seguir para utilizar el código de cualquiera de los ejemplos:

• Abra el libro de trabajo en el que desea utilizar la macro.
• Mantenga presionada la tecla ALT y presione F11.Esto se abriráeditor de VB.
• Haga clic derecho en cualquier objeto en el Explorador de proyectos.
• Vaya a Insertar -> Módulos.
• Copie y pegue el código en la ventana de código del módulo.

Si el ejemplo dice que necesita pegar el código en la ventana de código de la hoja de trabajo, haga doble clic en el objeto de la hoja de trabajo y copie y pegue el código en la ventana de código.

Después de insertar el código en el libro de trabajo, debe guardarlo con la extensión .XLSM o .XLS.

Cómo ejecutar una macro

Después de copiar el código en el editor de VB, los pasos para ejecutar la macro son los siguientes:

• Ve a la pestaña de desarrollador.
• Haga clic en Macro.

• En el cuadro de diálogo Macros, seleccione la macro para ejecutar.
• Haga clic en el botón Ejecutar.

Si no puede encontrar la pestaña de desarrollador en la cinta, lea este tutorial para saber cómo obtenerla.

Si el código se pega en la ventana de código de la hoja de trabajo, no necesita preocuparse por ejecutar el código.Se ejecuta automáticamente cuando se produce la acción especificada.

Ahora, veamos ejemplos de macros útiles que pueden ayudarlo a automatizar su trabajo y ahorrar tiempo.

Nota: encontrará muchos apóstrofes (') seguidos de una línea o dos.Estos son comentarios que se ignoran cuando se ejecuta el código y se colocan como comentarios propios/del lector.

Si encuentra algún error en el artículo o código, por favor hágamelo saber.

Ejemplo de macro de Excel

En este artículo se describen los siguientes ejemplos de macros:

Mostrar todas las hojas de trabajo a la vez

Si está trabajando en un libro de trabajo con varias hojas ocultas, debe mostrar estas hojas una por una.Esto puede llevar algún tiempo si hay muchas hojas ocultas.

Aquí está el código para mostrar todas las hojas en el libro de trabajo.

"Este código mostrará todas las hojas en el libro de trabajo Sub UnhideAllWoksheets() Dim ws As Worksheet For Each Ws In ActiveWorkbook. Worksheets ws. Visible = xlSheetVisible Next ws End Sub

El código anterior usa un bucle VBA (Para cada uno) para iterar a través de cada hoja de trabajo en el libro de trabajo.Luego cambia la propiedad visible de la hoja a visible.

Aquí hay un tutorial detallado sobre cómo mostrar una hoja de trabajo en Excel usando varios métodos.

Ocultar todas las hojas de trabajo excepto la hoja de trabajo activa

Puede usar este código de macro si está trabajando en un informe o panel y desea ocultar todas las hojas excepto la hoja que tiene el informe/panel.

'Esta macro ocultará toda la hoja de trabajo excepto la hoja activa Sub HideAllExceptActiveSheet() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden Next ws End Sub

Ordenar la hoja de trabajo alfabéticamente usando VBA

Este código de macro puede resultar útil si tiene un libro de trabajo con muchas hojas y desea ordenarlas alfabéticamente.Esto puede suceder si tiene el nombre de la hoja como el año, el nombre del empleado o el nombre del producto.

'Este código ordenará las hojas de trabajo alfabéticamente Sub SortSheetsTabName() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount Si Hojas(j).Nombre < Hojas(i).Nombre Entonces Hojas(j).Mover antes:=Hojas(i) Finalizar Si Siguiente j Siguiente i Aplicación.Actualización de pantalla = True End Sub

Proteja todas las hojas de trabajo a la vez

Si tiene muchas hojas en su libro de trabajo y desea proteger todas las hojas, puede usar este código de macro.

Le permite especificar una contraseña en el código.Necesitará esta contraseña para desproteger la hoja.

'Este código protegerá todas las hojas de una sola vez Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'reemplace Test123 con la contraseña que desee For Each ws In Worksheets ws.Protect password:=password Next ws Finalizar sub

Desproteger todas las hojas de trabajo a la vez

Si protegió algunas o todas sus hojas de trabajo, puede desprotegerlas con solo una ligera modificación del código utilizado para proteger las hojas de trabajo.

'Este código protegerá todas las hojas de una vez Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'reemplace Test123 con la contraseña que desee For Each ws In Worksheets ws.Unprotect password:=password Next ws Finalizar sub

Tenga en cuenta que la contraseña debe ser la misma contraseña utilizada para bloquear la hoja.Si no, verá un error.

Mostrar todas las filas y columnas

Este código de macro mostrará todas las filas y columnas ocultas.

Esto puede ser útil si está tomando el archivo de otra persona y quiere asegurarse de que no haya filas/columnas ocultas.

'Este código mostrará todas las filas y columnas en la hoja de trabajo Sub UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub

Descombinar todas las celdas combinadas

Combinar celdas en una sola es una práctica común.Mientras funciona, no podrá ordenar los datos cuando se combinen las celdas.

Si está utilizando una hoja de trabajo con celdas combinadas, use el código a continuación para deshacer la combinación de todas las celdas a la vez.

'Este código separará todas las celdas combinadas Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

Tenga en cuenta que recomiendo usar la opción "Centrar en la selección" en lugar de la opción "Combinar y centrar".

Guardar libro de trabajo con marca de tiempo en el nombre

Hay muchas ocasiones en las que puede necesitar crear una versión de trabajo.Estos son excelentes para proyectos a largo plazo que tratan con archivos a lo largo del tiempo.

Una buena práctica es guardar el archivo con una marca de tiempo.

El uso de una marca de tiempo le permitirá volver a un archivo para ver qué cambios se realizaron o qué datos se usaron.

Aquí está el código que guarda automáticamente el libro de trabajo en la carpeta especificada y agrega una marca de tiempo al guardar.

'Este código guardará el archivo con una marca de tiempo en su nombre Sub SaveWorkbookWithTimeStamp() Dim timestamp As String timestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" y marca de tiempo End Sub

Debe especificar la ubicación de la carpeta y el nombre del archivo.

En el código anterior, "C:UsersUsernameDesktop es la ubicación de la carpeta que uso.Debe especificar la ubicación de la carpeta donde desea guardar el archivo.Además, utilicé el nombre genérico "WorkbookName" como prefijo del nombre de archivo.Puede especificar contenido relacionado con su proyecto o empresa.

Guarde cada hoja de trabajo como un PDF separado

Si usa datos de diferentes años, departamentos o productos, es posible que deba guardar diferentes hojas de trabajo como archivos PDF.

Si bien esto puede ser un proceso lento si se hace manualmente, VBA acelera las cosas.

Aquí hay un código VBA que guarda cada hoja de trabajo como un PDF separado.

'Este código guardará cada hoja de trabajo como PDF Sub SaveWorkshetAsPDF() Dim ws As Worksheet For Each ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub

En el código anterior, especifiqué la dirección de la ubicación de la carpeta donde quiero guardar el PDF.Además, cada PDF tendrá el mismo nombre que la hoja.Deberá modificar la ubicación de esta carpeta (a menos que su nombre también sea Sumit y lo haya guardado en una carpeta de prueba en su escritorio).

Tenga en cuenta que este código solo funciona para hojas de trabajo (no para hojas de gráficos).

Guarde cada hoja de trabajo como un PDF separado

Aquí está el código para guardar todo el libro de trabajo como PDF en la carpeta especificada.

'Este código guardará todo el libro de trabajo como PDF Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub

Debe cambiar la ubicación de la carpeta para usar este código.

Convertir todas las fórmulas a valores

Use este código cuando tenga una hoja de trabajo con muchas fórmulas y desee convertir esas fórmulas en valores.

'Este código convertirá todas las fórmulas en valores Sub ConvertToValues() With ActiveSheet.UsedRange .Value = .Value End With End Sub

Este código reconoce automáticamente la celda utilizada y la convierte en un valor.

Proteger/bloquear celdas con fórmulas

Cuando tiene muchos cálculos y no quiere eliminarlos o cambiarlos accidentalmente, es posible que desee bloquear las celdas con fórmulas.

Aquí está el código que bloqueará todas las celdas con fórmulas y dejará todas las demás celdas desbloqueadas.

'Este código de macro bloqueará todas las celdas con fórmulas Sub LockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True End With End Sub

Tutorial relacionado: Cómo bloquear celdas en Excel.

Proteger todas las hojas de un libro

Utilice el siguiente código para proteger todas las hojas de un libro de trabajo a la vez.

'Este código protegerá todas las hojas en el libro de trabajo Sub ProtectAllSheets() Dim ws As Worksheet For Each ws In Worksheets ws. Protect Next ws End Sub

Este código pasará por todas las hojas una por una y las protegerá.

Si desea desproteger todas las hojas, use ws.Unprotect en lugar de ws.Protect en su código.

Insertar una fila después de cada fila de la selección

Use este código cuando desee insertar una fila en blanco después de cada fila en el rango seleccionado.

'Este código insertará una fila después de cada fila en la selección Sub InsertAlternateRows() Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow. Inserte ActiveCell.Offset(2, 0).Seleccione Siguiente i End Sub

Nuevamente, puede modificar este código para insertar una columna en blanco después de cada columna en el rango seleccionado.

Inserte automáticamente la fecha y la marca de tiempo en las celdas adyacentes

Las marcas de tiempo son lo que usa cuando desea realizar un seguimiento de la actividad.

Por ejemplo, es posible que desee realizar un seguimiento de actividades como cuándo se incurrió en un gasto en particular, cuándo se creó una factura de venta, cuándo se ingresaron datos en una celda, cuándo se actualizó por última vez un informe, etc.

Use este código para insertar una marca de fecha y hora en celdas adyacentes al escribir o editar contenido existente.

'Este código insertará una marca de tiempo en la celda adyacente Private Sub Worksheet_Change (ByVal Target As Range) On Error GoTo Handler If Target.Column = 1 And Target.Value <> "" Then Application.EnableEvents = False Target.Offset(0, 1) = Formato (Ahora (), "dd-mm-yyyy hh: mm: ss") Aplicación.EnableEvents = True End If Handler: End Sub

Tenga en cuenta que debe insertar este código en la ventana de código de la hoja de trabajo (no en la ventana de código del módulo como lo hemos hecho en otros ejemplos de macros de Excel hasta ahora).Para hacer esto, en el editor de VB, haga doble clic en el nombre de la hoja para la que desea esta funcionalidad.Luego copie y pegue este código en la ventana de código de esa hoja.

Además, este código funciona cuando la entrada de datos se realiza en la columna A (tenga en cuenta que el código tiene una línea Target.Column = 1).Puede cambiarlo en consecuencia.

Resaltar filas alternativas en la selección

Resaltar líneas alternas puede mejorar en gran medida la legibilidad de sus datos.Esto puede ser útil cuando necesite imprimirlo y examinar los datos.

Aquí hay un código que resaltará inmediatamente filas alternativas en la selección.

'Este código resaltaría filas alternativas en la selección Sub HighlightAlternateRows() Dim Myrow As Range Dim Myrow As Range Set Myrange = Selección para cada Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End Si el siguiente final de Myrow Sub

Tenga en cuenta que especifiqué el color como vbCyan en el código.También puede especificar otros colores (por ejemplo, vbRed, vbGreen, vbBlue).

Resaltar celdas mal escritas

Excel no tiene el corrector ortográfico en Word o PowerPoint.Si bien puede ejecutar el corrector ortográfico presionando F7, no hay una señal visual para los errores ortográficos.

Use este código para resaltar instantáneamente todas las celdas mal escritas.

'Este código resaltará las celdas que tienen palabras mal escritas Sub HighlightMisspelledCells() Dim cl As Range For Each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) Then cl.Interior.Color = vbRed End If Next cl End Sub

Tenga en cuenta que las celdas resaltadas son las celdas con texto que Excel cree que está mal escrito.En muchos casos, también destaca nombres o términos de marca que no comprende.

Actualizar todas las tablas dinámicas en un libro de trabajo

Si tiene varias tablas dinámicas en el libro de trabajo, puede usar este código para actualizarlas todas a la vez.

'Este código actualizará toda la tabla dinámica en el libro de trabajo Sub RefreshAllPivotTables() Dim PT As PivotTable For Each PT In ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub

Puede leer más sobre la actualización de tablas dinámicas aquí.

Cambiar el caso de las letras de las celdas seleccionadas a mayúsculas

Si bien Excel tiene fórmulas para cambiar el caso de las letras del texto, le permite hacerlo en otro conjunto de celdas.

Use este código para cambiar instantáneamente el uso de mayúsculas y minúsculas en el texto seleccionado.

'Este código cambiará la selección a Mayúsculas Sub ChangeCase() Dim Rng As Range For Each Rng In Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase(Rng.Value) End If Next Rng End Sub

Tenga en cuenta que en este caso uso UCase para configurar el texto en mayúsculas.Puede usar LCase para minúsculas.

Resalta todas las celdas con comentarios

Use el siguiente código para resaltar todas las celdas con comentarios en ellas.

'Este código resaltará las celdas que tienen comentarios' Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

En este caso, uso vbBlue para darle a la celda un color azul.Puede cambiarlo a otro color si es necesario.

Resaltar celdas en blanco con VBA

Si bien puede usar el formato condicional o usar el cuadro de diálogo Ir a especial para resaltar las celdas en blanco, si tiene que hacer esto con frecuencia, es mejor usar una macro.

Una vez creada, puede tener esta macro en la barra de herramientas de acceso rápido o guardarla en su libro de macros personal.

Aquí está el código de la macro VBA:

'Este código resaltará todas las celdas en blanco en el conjunto de datos Sub HighlightBlankCells() Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub

En este código, especifiqué celdas en blanco resaltadas en rojo.Puedes elegir otros colores como azul, amarillo, cian, etc.

Cómo ordenar los datos por una sola columna

Puede usar el siguiente código para ordenar los datos por una columna específica.

SubclasificaciónDataHeader() 
Rango ("Rango de datos"). Clave de clasificación 1: = Rango ("A1"), Orden 1: = xlAscendente, Encabezado: = xlSí 
End Sub

Tenga en cuenta que creé un rango con nombre llamado "DataRange" y lo usé en lugar de la referencia de celda.

También hay tres parámetros clave utilizados aquí:

• Key1: esta es la clave por la que desea ordenar el conjunto de datos.En el código de ejemplo anterior, los datos se ordenarán según los valores de la columna A.
• Orden1: aquí debe especificar si desea ordenar los datos en orden ascendente o descendente.
• Encabezado: aquí debe especificar si sus datos tienen un encabezado o no.

Lea más sobre cómo ordenar datos en Excel usando VBA.

Cómo ordenar datos por varias columnas

Suponga que tiene un conjunto de datos que se ve así:

Aquí está el código para ordenar los datos en función de varias columnas:

Sub SortMultipleColumns() Con ActiveSheet.Sort .SortFields.Add Key:=Rango("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1 :C13") .Header = xlSí .Aplicar End With End Sub

Tenga en cuenta que aquí he especificado ordenar primero en la columna A y luego en la columna B.

La salida se verá así:

Cómo obtener solo la parte numérica de una cadena en Excel

Si solo desea extraer la parte numérica o la parte de texto de la cadena, puede crear una función personalizada en VBA.

Luego puede usar esta función de VBA en su hoja de trabajo (al igual que una función normal de Excel) y extraerá solo el número o la parte de texto de la cadena.

Como se muestra abajo:

Aquí está el código VBA que creará una función que extrae la parte numérica de una cadena:

'Este código de VBA creará una función para obtener la parte numérica de una cadena. ) Entonces Resultado = Result & Mid(CellRef, i, 1) Siguiente i GetNumeric = Resultado Función final

Debe colocar el código en un módulo y luego puede usar la función =GetNumeric en la hoja de trabajo.

Esta función tomará solo un parámetro, la referencia de celda de la celda de la que se obtendrá la parte numérica.

Del mismo modo, la siguiente función solo obtendrá la parte de texto de una cadena en Excel:

'Este código VBA creará una función para obtener la parte de texto de una cadena. 1))) Entonces Resultado = Result & Mid(CellRef, i, 1) Next i GetText = Resultado Función final

Entonces, estos son algunos códigos de macro de Excel útiles que puede usar en su trabajo diario para automatizar tareas y aumentar su productividad.