El uso de macros de Excel puede acelerar el trabajo y ahorrarle mucho tiempo.
Una forma de obtener el código VBA es grabar la macro y tomar el código que genera. Sin embargo, ese código de la grabadora de macros a menudo está lleno de código que realmente no es necesario. Además, la grabadora de macros tiene algunas limitaciones.
Por lo tanto, vale la pena tener una colección de códigos de macro VBA útiles que pueda tener en su bolsillo trasero y usarlos cuando sea necesario.
Si bien la escritura de un código de macro de Excel VBA puede llevar algo de tiempo inicialmente, una vez que esté hecho, puede mantenerlo disponible como referencia y usarlo cuando lo necesite a continuación.
En este artículo masivo, voy a enumerar algunos ejemplos útiles de macros de Excel que necesito a menudo y que guardo en mi bóveda privada.
Seguiré actualizando este tutorial con más ejemplos de macros. Si cree que algo debería estar en la lista, simplemente deje un comentario.
Puede marcar esta página para consultarla en el futuro.
Ahora, antes de entrar en el Ejemplo de macro y darle el código VBA, permítame mostrarle primero cómo usar estos códigos de ejemplo.
Uso del código de ejemplos de macros de Excel
Estos son los pasos que debe seguir para usar 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 abre el VB Editor.
- Haga clic con el botón derecho en cualquiera de los objetos en el explorador de proyectos.
- Vaya a Insertar -> Módulo.
- Copie y pegue el código en la ventana Código del módulo.
En caso de que el ejemplo diga 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.
Una vez que haya insertado el código en un libro de trabajo, debe guardarlo con una extensión .XLSM o .XLS.
Cómo ejecutar la macro
Una vez que haya copiado el código en el VB Editor, estos son los pasos para ejecutar la macro:
- Vaya a la pestaña Desarrollador.
- Haga clic en Macros.
- En el cuadro de diálogo Macro, seleccione la macro que desea ejecutar.
- Haga clic en el botón Ejecutar.
En caso de que no pueda encontrar la pestaña de desarrollador en la cinta, lea este tutorial para aprender cómo obtenerla.
Tutorial relacionado: diferentes formas de ejecutar una macro en Excel.
En caso de que el código se pegue en la ventana de código de la hoja de trabajo, no necesita preocuparse por ejecutar el código. Se ejecutará automáticamente cuando se produzca la acción especificada.
Ahora, veamos los útiles ejemplos de macros que pueden ayudarlo a automatizar el trabajo y ahorrar tiempo.
Nota: encontrará muchas instancias de un apóstrofe (‘) seguido de una línea o dos. Estos son comentarios que se ignoran mientras se ejecuta el código y se colocan como notas para el propio lector.
En caso de que encuentre algún error en el artículo o en el código, sea genial y hágamelo saber.
Ejemplos de macros de Excel
En este artículo se tratan los siguientes ejemplos de macros:
Mostrar todas las hojas de trabajo de una sola vez
Si está trabajando en un libro de trabajo que tiene varias hojas ocultas, debe mostrar estas hojas una por una. Esto podría llevar algún tiempo en caso de que haya muchas hojas ocultas.
Aquí está el código que mostrará todas las hojas de trabajo 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 revisar cada hoja de trabajo en el libro de trabajo. Luego cambia la propiedad visible de la hoja de trabajo a visible.
Aquí hay un tutorial detallado sobre cómo usar varios métodos para mostrar hojas en Excel.
Ocultar todas las hojas de trabajo excepto la hoja activa
Si está trabajando en un informe o panel y desea ocultar toda la hoja de trabajo excepto la que tiene el informe / panel, puede usar este código de macro.
'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 hojas de trabajo alfabéticamente usando VBA
Si tiene un libro de trabajo con muchas hojas de trabajo y desea ordenarlas alfabéticamente, este código de macro puede resultar muy útil. Este podría ser el caso si tiene nombres de hojas como años o nombres de empleados o nombres de productos.
'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 If Sheets (j) .Name <Sheets (i) .Name Then Sheets (j) .Move before: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub
Proteja todas las hojas de trabajo de una sola vez
Si tiene muchas hojas de trabajo en un libro y desea proteger todas las hojas, puede usar este código de macro.
Le permite especificar la contraseña dentro del código. Necesitará esta contraseña para desproteger la hoja de trabajo.
'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 Para cada ws en las hojas de trabajo ws.Protect contraseña: = contraseña Siguiente ws End Sub
Desproteger todas las hojas de trabajo de una sola vez
Si tiene algunas o todas las hojas de trabajo protegidas, puede usar una pequeña modificación del código utilizado para proteger las hojas para desprotegerlo.
'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 Para cada ws en las hojas de trabajo ws.Unprotect contraseña: = contraseña Siguiente ws End Sub
Tenga en cuenta que la contraseña debe ser la misma que se utilizó para bloquear las hojas de trabajo. Si no es así, verá un error.
Mostrar todas las filas y columnas
Este código de macro mostrará todas las filas y columnas ocultas.
Esto podría ser realmente útil si obtiene un archivo de otra persona y desea 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
Separar todas las celdas fusionadas
Es una práctica común fusionar celdas para convertirlas en una. Mientras hace el trabajo, cuando las celdas se combinan, no podrá ordenar los datos.
En caso de que esté trabajando con una hoja de trabajo con celdas combinadas, use el siguiente código para separar todas las celdas combinadas de una sola vez.
'Este código separará todas las celdas fusionadas Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub
Tenga en cuenta que en lugar de Fusionar y Centrar, recomiendo utilizar la opción Centrar en toda la selección.
Guardar libro de trabajo con marca de tiempo en su nombre
Es posible que necesite mucho tiempo para crear versiones de su trabajo. Estos son muy útiles en proyectos largos en los que trabaja con un archivo a lo largo del tiempo.
Una buena práctica es guardar el archivo con marcas de tiempo.
El uso de marcas de tiempo le permitirá volver a un determinado archivo para ver qué cambios se realizaron o qué datos se utilizaron.
Aquí está el código que guardará automáticamente el libro de trabajo en la carpeta especificada y agregará una marca de tiempo cada vez que se guarde.
'Este código guardará el archivo con una marca de tiempo en su nombre Sub SaveWorkbookWithTimeStamp () Dim timestamp As String timestamp = Formato (Fecha, "dd-mm-aaaa") & "_" & Formato (Hora, "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 he usado. Debe especificar la ubicación de la carpeta donde desea guardar el archivo. Además, he utilizado un nombre genérico "WorkbookName" como prefijo del nombre de archivo. Puede especificar algo relacionado con su proyecto o empresa.
Guarde cada hoja de trabajo como un PDF separado
Si trabaja con datos de diferentes años, divisiones o productos, es posible que tenga que guardar diferentes hojas de trabajo como archivos PDF.
Si bien podría ser un proceso lento si se realiza manualmente, VBA realmente puede acelerarlo.
Aquí hay un código VBA que guardará cada hoja de trabajo como un PDF separado.
'Este código guardará cada hoja de trabajo como un 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, he especificado la dirección de la ubicación de la carpeta en la que quiero guardar los PDF. Además, cada PDF recibirá el mismo nombre que el de la hoja de trabajo. Tendrá que modificar la ubicación de esta carpeta (a menos que su nombre también sea Sumit y lo esté guardando en una carpeta de prueba en el escritorio).
Tenga en cuenta que este código solo funciona para hojas de trabajo (y no para hojas de gráficos).
Guarde cada hoja de trabajo como un PDF separado
Aquí está el código que guardará todo su 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
Deberá cambiar la ubicación de la carpeta para usar este código.
Convertir todas las fórmulas en valores
Utilice este código cuando tenga una hoja de trabajo que contenga muchas fórmulas y desee convertir estas 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 identifica automáticamente las celdas que se utilizan y las convierte en valores.
Proteger / Bloquear celdas con fórmulas
Es posible que desee bloquear celdas con fórmulas cuando tenga muchos cálculos y no desee eliminarlo o cambiarlo accidentalmente.
Aquí está el código que bloqueará todas las celdas que tienen fórmulas, mientras que todas las demás celdas no están bloqueadas.
'Este código 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 trabajo en el libro de trabajo
Utilice el siguiente código para proteger todas las hojas de trabajo de un libro de una sola 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 revisará todas las hojas de trabajo una por una y lo protegerá.
En caso de que desee desproteger todas las hojas de trabajo, use ws.Unprotect en lugar de ws.Protect en el código.
Insertar una fila después de cada otra fila en la selección
Utilice 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. Insertar ActiveCell.Offset (2, 0) .Seleccione Siguiente i Fin Sub
Del mismo modo, 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 la celda adyacente
Una marca de tiempo es algo que usa cuando desea realizar un seguimiento de las actividades.
Por ejemplo, es posible que desee realizar un seguimiento de actividades como cuándo se incurrió en un gasto particular, a qué hora se creó la factura de venta, cuándo se realizó la entrada de datos en una celda, cuándo se actualizó por última vez el informe, etc.
Utilice este código para insertar una marca de fecha y hora en la celda adyacente cuando se realiza una entrada o se editan los contenidos existentes.
'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 (Now (), "dd-mm-aaaa hh: mm: ss") Application.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 (y 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 VB Editor, haga doble clic en el nombre de la hoja en 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 la línea Target.Column = 1). Puede cambiar esto en consecuencia.
Resaltar filas alternativas en la selección
Resaltar filas alternativas puede aumentar enormemente la legibilidad de sus datos. Esto puede resultar útil cuando necesite realizar una copia impresa y revisar los datos.
Aquí hay un código que resaltará instantáneamente filas alternativas en la selección.
'Este código resaltaría filas alternativas en la selección Sub HighlightAlternateRows () Atenuar Myrange como rango Atenuar Myrow como rango Establecer Myrange = Selección para cada Myrow en Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End Si el próximo Myrow End Sub
Tenga en cuenta que he especificado el color como vbCyan en el código. También puede especificar otros colores (como vbRed, vbGreen, vbBlue).
Resaltar celdas con palabras mal escritas
Excel no tiene un corrector ortográfico como en Word o PowerPoint. Si bien puede ejecutar el corrector ortográfico presionando la tecla F7, no hay una señal visual cuando hay un error ortográfico.
Use este código para resaltar instantáneamente todas las celdas que tienen un error de ortografía.
'Este código resaltará las celdas que tienen palabras mal escritas. cl End Sub
Tenga en cuenta que las celdas que están resaltadas son aquellas que tienen texto que Excel considera como un error ortográfico. En muchos casos, también destacaría nombres o términos de marca que no comprende.
Actualizar todas las tablas dinámicas del libro de trabajo
Si tiene más de una tabla dinámica en el libro de trabajo, puede usar este código para actualizar todas estas tablas dinámicas 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 la letra de las celdas seleccionadas a mayúsculas
Si bien Excel tiene las fórmulas para cambiar las mayúsculas y minúsculas del texto, te obliga a hacerlo en otro conjunto de celdas.
Utilice este código para cambiar instantáneamente las mayúsculas y minúsculas del texto en el texto seleccionado.
'Este código cambiará la Selección a Mayúsculas Sub ChangeCase () Dim Rng As Range For Cada 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, he usado UCase para hacer que el texto sea superior. Puede utilizar LCase para minúsculas.
Resaltar todas las celdas con comentarios
Use el siguiente código para resaltar todas las celdas que tienen comentarios.
'Este código resaltará las celdas que tienen comentarios` Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments) .Interior.Color = vbBlue End Sub
En este caso, he usado vbBlue para dar un color azul a las celdas. Puede cambiar esto a otros colores si lo desea.
Resalte celdas en blanco con VBA
Si bien puede resaltar la celda en blanco con formato condicional o usando el cuadro de diálogo Ir a especial, si tiene que hacerlo con bastante 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 trabajo de macros personal.
Aquí está el código de macro de 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, he especificado las celdas en blanco que se resaltarán en color rojo. Puedes elegir otros colores como azul, amarillo, cian, etc.
Cómo ordenar datos por columna única
Puede utilizar el siguiente código para ordenar los datos por la columna especificada.
Sub SortDataHeader () Range ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlYes End Sub
Tenga en cuenta que he creado un rango con nombre con el nombre "DataRange" y lo he usado en lugar de las referencias de celda.
También hay tres parámetros clave que se utilizan aquí:
- Key1: este es el lugar en el 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.
- Orden: aquí debe especificar si desea ordenar los datos en orden ascendente o descendente.
- Encabezado: aquí debe especificar si sus datos tienen encabezados 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 como se muestra a continuación:
A continuación se muestra el código que ordenará los datos en función de varias columnas:
Sub SortMultipleColumns () With ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 ") .Header = xlYes .Apply End With End Sub
Tenga en cuenta que aquí he especificado ordenar primero según la columna A y luego según la columna B.
La salida sería algo como se muestra a continuación:
Cómo obtener solo la parte numérica de una cadena en Excel
Si desea extraer solo la parte numérica o solo la parte de texto de una cadena, puede crear una función personalizada en VBA.
Luego puede usar esta función de VBA en la hoja de trabajo (al igual que las funciones normales de Excel) y extraerá solo la parte numérica o de texto de la cadena.
Algo como se muestra a continuación:
A continuación se muestra el código VBA que creará una función para extraer una parte numérica de una cadena:
'Este código de VBA creará una función para obtener la parte numérica de una cadena Función GetNumeric (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1) ) Luego Result = Result & Mid (CellRef, i, 1) Siguiente i GetNumeric = Result End Function
Necesita 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 argumento, que es la referencia de celda de la celda de la que desea obtener la parte numérica.
De manera similar, a continuación se muestra la función que 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 Función GetText (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Luego Result = Result & Mid (CellRef, i, 1) Siguiente i GetText = Result End Function
Estos son algunos de los códigos macro útiles de Excel que puede utilizar en su trabajo diario para automatizar tareas y ser mucho más productivo.
