Utilizar 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 suele estar lleno de código que no es realmente necesario. Además, la grabadora de macros tiene algunas limitaciones.
Así que vale la pena tener una colección de códigos de macros VBA útiles que pueda tener en su bolsillo trasero y utilizarlo cuando lo necesite.
Aunque escribir un código de macro VBA de Excel puede llevar algo de tiempo inicialmente, una vez hecho, puede tenerlo disponible como referencia y utilizarlo 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 mantengo escondidos en mi bóveda privada.
Seguiré actualizando este tutorial con más ejemplos de macros. Si crees que algo debería estar en la lista, sólo tienes que dejar un comentario.
Puedes marcar esta página para futuras referencias.
Ahora, antes de entrar en el ejemplo de macro y darte el código VBA, déjame primero mostrarte cómo usar estos códigos de ejemplo.
Utilizando el código de los ejemplos de macros de Excel
Aquí tienes los pasos que debes seguir para utilizar el código de cualquiera de los ejemplos:
- Abre el Libro de Trabajo en el que quieres utilizar la macro.
- Mantén la tecla ALT y pulsa F11. Esto abre el Editor VB.
- Haga clic con el botón derecho del ratón en cualquiera de los objetos del explorador del proyecto.
- Vaya a Insertar -> Módulo.
- Copie y pegue el código en la ventana de código del módulo.
En caso de que el ejemplo diga que hay que 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 Editor VB, estos son los pasos para ejecutar la macro:
- Vaya a la pestaña Desarrollador.
- Haga clic en Macros.
- En el cuadro de diálogo de macros, selecciona la macro que quieres ejecutar.
- Haz clic en el botón Ejecutar.
En caso de que no encuentres la pestaña de desarrollador en la cinta de opciones, lee este tutorial para aprender a conseguirla.
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 hay que preocuparse por ejecutar el código. Se ejecutará automáticamente cuando se produzca la acción especificada.
Ahora, vamos a entrar en los útiles ejemplos de macros que pueden ayudarle a automatizar el trabajo y ahorrar tiempo.
Nota: encontrará muchos casos de un apóstrofe (‘) seguido de una o dos líneas. Son comentarios que se ignoran al ejecutar el código y se colocan como notas para uno mismo/lector.
En caso de que encuentres algún error en el artículo o en el código, por favor, sé impresionante y házmelo saber.
Ejemplos de macros de Excel
Los siguientes ejemplos de macros están cubiertos en este artículo:
Desocultar todas las hojas de trabajo de una sola vez
Si está trabajando en un libro de trabajo que tiene múltiples hojas ocultas, necesita desocultar 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 desocultará todas las hojas de trabajo del libro.
'This code will unhide all sheets in the workbookSub UnhideAllWoksheets()Dim ws As WorksheetFor Each ws In ActiveWorkbook.Worksheetsws.Visible = xlSheetVisibleNext wsEnd Sub
El código anterior utiliza un bucle VBA (For Each) para recorrer cada una de las hojas de trabajo del libro. A continuación, cambia la propiedad visible de la hoja de trabajo a visible.
Aquí hay un tutorial detallado sobre cómo utilizar varios métodos para desocultar hojas en Excel.
Ocultar todas las hojas de trabajo excepto la hoja activa
Si estás trabajando en un informe o tablero de control y quieres ocultar todas las hojas de trabajo excepto la que tiene el informe/ tablero, puedes utilizar este código de macro.
'This macro will hide all the worksheet except the active sheetSub HideAllExceptActiveSheet()Dim ws As WorksheetFor Each ws In ThisWorkbook.WorksheetsIf ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHiddenNext wsEnd Sub
Ordenar hojas de trabajo alfabéticamente usando VBA
Si tienes un libro de trabajo con muchas hojas de trabajo y quieres ordenarlas alfabéticamente, este código de macro puede ser muy útil. Este podría ser el caso si tiene nombres de hojas como años o nombres de empleados o nombres de productos.
'This code will sort the worksheets alphabeticallySub SortSheetsTabName()Application.ScreenUpdating = FalseDim ShCount As Integer, i As Integer, j As IntegerShCount = Sheets.CountFor i = 1 To ShCount - 1For j = i + 1 To ShCountIf Sheets(j).Name < Sheets(i).Name ThenSheets(j).Move before:=Sheets(i)End IfNext jNext iApplication.ScreenUpdating = TrueEnd Sub
Proteger todas las hojas de trabajo de una sola vez
Si tiene muchas hojas de trabajo en un libro y quiere proteger todas las hojas, puede utilizar 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.
'This code will protect all the sheets at one goSub ProtectAllSheets()Dim ws As WorksheetDim password As Stringpassword = "Test123" 'replace Test123 with the password you wantFor Each ws In Worksheets ws.Protect password:=passwordNext wsEnd Sub
Desproteger todas las hojas de trabajo de una sola vez
Si tiene algunas o todas las hojas de trabajo protegidas, puede simplemente utilizar una ligera modificación del código utilizado para proteger las hojas para desprotegerlas.
'This code will protect all the sheets at one goSub ProtectAllSheets()Dim ws As WorksheetDim password As Stringpassword = "Test123" 'replace Test123 with the password you wantFor Each ws In Worksheetsws.Unprotect password:=passwordNext wsEnd Sub
Tenga en cuenta que la contraseña tiene que ser la misma que se ha utilizado para bloquear las hojas de trabajo. Si no lo es, verá un error.
Desocultar todas las filas y columnas
Este código de macro desocultará todas las filas y columnas ocultas.
Esto podría ser realmente útil si obtienes un archivo de otra persona y quieres estar seguro de que no hay filas/columnas ocultas.
'This code will unhide all the rows and columns in the WorksheetSub UnhideRowsColumns()Columns.EntireColumn.Hidden = FalseRows.EntireRow.Hidden = FalseEnd Sub
Desfusionar todas las celdas fusionadas
Es una práctica común fusionar celdas para que sea una sola. Si bien hace el trabajo, cuando las celdas están fusionadas no podrás ordenar los datos.
En caso de que estés trabajando con una hoja de trabajo con celdas fusionadas, utiliza el siguiente código para deshacer la fusión de todas las celdas fusionadas de una sola vez.
'This code will unmerge all the merged cellsSub UnmergeAllCells()ActiveSheet.Cells.UnMergeEnd Sub
Tenga en cuenta que en lugar de Combinar y Centrar, le recomiendo que utilice la opción Centrar a través de la selección.
Guardar libro de trabajo con marca de tiempo en su nombre
Muchas veces, puede necesitar crear versiones de su trabajo. Estas son bastante útiles en proyectos largos en los que se trabaja con un archivo a lo largo del tiempo.
Una buena práctica es guardar el archivo con marcas de tiempo.
Usar marcas de tiempo le permitirá volver a un determinado archivo para ver qué cambios se hicieron o qué datos se utilizaron.
Aquí tienes el código que guardará automáticamente el libro de trabajo en la carpeta especificada y añadirá una marca de tiempo cada vez que se guarde.
'This code will Save the File With a Timestamp in its nameSub SaveWorkbookWithTimeStamp()Dim timestamp As Stringtimestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss")ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & timestampEnd Sub
Tienes que 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 utilizado. Tienes que especificar la ubicación de la carpeta donde quieres guardar el archivo. Además, he utilizado un nombre genérico «WorkbookName» como prefijo del nombre del archivo. Puede especificar algo relacionado con su proyecto o empresa.
Guardar cada hoja de trabajo como un PDF independiente
Si trabaja con datos de diferentes años o divisiones o productos, puede tener la necesidad de guardar diferentes hojas de trabajo como archivos PDF.
Aunque podría ser un proceso que consume tiempo si se hace manualmente, VBA puede realmente acelerarlo.
Aquí hay un código VBA que guardará cada hoja de trabajo como un PDF separado.
'This code will save each worsheet as a separate PDFSub SaveWorkshetAsPDF()Dim ws As WorksheetFor Each ws In Worksheetsws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf"Next wsEnd 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 tendrá el mismo nombre que el de la hoja de trabajo. Tendrás que modificar esta ubicación de la carpeta (a no ser que también te llames Sumit y la estés guardando en una carpeta de prueba en el escritorio).
Nota que este código sólo funciona para las hojas de trabajo (y no para las hojas de gráfico).
Guardar cada hoja de trabajo como un PDF independiente
Aquí tienes el código que guardará todo tu libro de trabajo como un PDF en la carpeta especificada.
'This code will save the entire workbook as PDFSub SaveWorkshetAsPDF()ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf"End Sub
Tendrás que cambiar la ubicación de la carpeta para utilizar este código.
Convertir todas las fórmulas en valores
Usa este código cuando tengas una hoja de trabajo que contenga muchas fórmulas y quieras convertir estas fórmulas en valores.
'This code will convert all formulas into valuesSub ConvertToValues()With ActiveSheet.UsedRange.Value = .ValueEnd WithEnd Sub
Este código identifica automáticamente las celdas se usan y las convierte en valores.
Proteger/Bloquear Celdas con Fórmulas
Es posible que quieras bloquear las celdas con fórmulas cuando tengas muchos cálculos y no quieras borrarlo o cambiarlo accidentalmente.
Aquí tienes el código que bloqueará todas las celdas que tengan fórmulas, mientras que el resto de celdas no estarán bloqueadas.
'This macro code will lock all the cells with formulasSub LockCellsWithFormulas()With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=TrueEnd WithEnd Sub
Tutorial relacionado: Cómo bloquear celdas en Excel.
Proteger todas las hojas de trabajo del libro
Utiliza el siguiente código para proteger todas las hojas de trabajo de un libro de una sola vez.
'This code will protect all sheets in the workbookSub ProtectAllSheets()Dim ws As WorksheetFor Each ws In Worksheetsws.ProtectNext wsEnd Sub
Este código recorrerá todas las hojas de trabajo una por una y las protegerá.
En caso de que quiera desproteger todas las hojas de trabajo, utilice ws.Unprotect en lugar de ws.Protect en el código.
Insertar una fila después de cada fila en la selección
Utilice este código cuando quiera insertar una fila en blanco después de cada fila en el rango seleccionado.
'This code will insert a row after every row in the selectionSub InsertAlternateRows()Dim rng As RangeDim CountRow As IntegerDim i As IntegerSet rng = SelectionCountRow = rng.EntireRow.CountFor i = 1 To CountRowActiveCell.EntireRow.InsertActiveCell.Offset(2, 0).SelectNext iEnd Sub
De forma similar, puede modificar este código para insertar una columna en blanco después de cada columna en el rango seleccionado.
Insertar automáticamente la fecha & Sello de tiempo en la celda adyacente
Un sello de tiempo es algo que se utiliza cuando se desea realizar un seguimiento de las actividades.
Por ejemplo, es posible que desee realizar un seguimiento de las actividades como cuándo se incurrió en un gasto determinado, a qué hora se creó la factura de venta, cuándo se realizó la entrada de datos en una celda, cuándo se actualizó el informe por última vez, etc.
Utilice este código para insertar una marca de fecha y hora en la celda adyacente cuando se realice una entrada o se edite el contenido existente.
'This code will insert a timestamp in the adjacent cellPrivate Sub Worksheet_Change(ByVal Target As Range)On Error GoTo HandlerIf Target.Column = 1 And Target.Value <> "" ThenApplication.EnableEvents = FalseTarget.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss")Application.EnableEvents = TrueEnd IfHandler:End Sub
Note que necesita 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 hemos hecho en otros ejemplos de macro de Excel hasta ahora). Para ello, en el Editor VB, haz doble clic en el nombre de la hoja en la que quieres esta funcionalidad. A continuación, copie y pegue este código en la ventana de código de esa hoja.
Además, este código está hecho para que funcione cuando la entrada de datos se realiza en la Columna A (observe que el código tiene la línea Objetivo.Columna = 1). Usted puede cambiar esto en consecuencia.
Resaltar filas alternas en la selección
Resaltar filas alternas puede aumentar la legibilidad de sus datos tremendamente. Esto puede ser útil cuando necesitas tomar una impresión y revisar los datos.
Aquí hay un código que resaltará instantáneamente las filas alternas en la selección.
'This code would highlight alternate rows in the selectionSub HighlightAlternateRows()Dim Myrange As RangeDim Myrow As RangeSet Myrange = SelectionFor Each Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End IfNext MyrowEnd Sub
Nota que he especificado el color como vbCyan en el código. Puedes especificar otros colores también (como vbRed, vbGreen, vbBlue).
Resaltar las celdas con palabras mal escritas
Excel no tiene un corrector ortográfico como tiene en Word o PowerPoint. Aunque puedes ejecutar el corrector ortográfico pulsando la tecla F7, no hay ninguna señal visual cuando hay un error ortográfico.
Usa este código para resaltar instantáneamente todas las celdas que tengan un error ortográfico.
'This code will highlight the cells that have misspelled wordsSub HighlightMisspelledCells()Dim cl As RangeFor Each cl In ActiveSheet.UsedRangeIf Not Application.CheckSpelling(word:=cl.Text) Thencl.Interior.Color = vbRedEnd IfNext clEnd Sub
Nota que las celdas que se resaltan son las que tienen texto que Excel considera como un error ortográfico. En muchos casos, también resaltaría nombres o términos de marca que no entiende.
Refrescar todas las tablas dinámicas del libro de trabajo
Si tienes más de una tabla dinámica en el libro de trabajo, puedes usar este código para refrescar todas estas tablas dinámicas a la vez.
'This code will refresh all the Pivot Table in the WorkbookSub RefreshAllPivotTables()Dim PT As PivotTableFor Each PT In ActiveSheet.PivotTablesPT.RefreshTableNext PTEnd Sub
Puedes leer más sobre cómo refrescar las tablas dinámicas aquí.
Cambiar las mayúsculas de las celdas seleccionadas a mayúsculas
Aunque Excel tiene las fórmulas para cambiar las letras del texto, te obliga a hacerlo en otro conjunto de celdas.
Usa este código para cambiar instantáneamente las letras del texto en el texto seleccionado.
'This code will change the Selection to Upper CaseSub ChangeCase()Dim Rng As RangeFor Each Rng In Selection.CellsIf Rng.HasFormula = False ThenRng.Value = UCase(Rng.Value)End IfNext RngEnd Sub
Nota que en este caso, he usado UCase para hacer que el texto sea mayúscula. Puedes usar LCase para las minúsculas.
Resaltar todas las celdas con comentarios
Usa el siguiente código para resaltar todas las celdas que tienen comentarios.
'This code will highlight cells that have comments`Sub HighlightCellsWithComments()ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlueEnd Sub
En este caso, he usado vbBlue para dar un color azul a las celdas. Puedes cambiarlo por otros colores si lo deseas.
Resaltar celdas en blanco con VBA
Aunque puedes resaltar celdas en blanco con formato condicional o utilizando el cuadro de diálogo Ir a especial, si tienes que hacerlo con bastante frecuencia, es mejor utilizar 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 la macro VBA:
'This code will highlight all the blank cells in the datasetSub HighlightBlankCells()Dim Dataset as RangeSet Dataset = SelectionDataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRedEnd Sub
En este código, he especificado que las celdas en blanco se resalten en el color rojo. Puede elegir otros colores como azul, amarillo, cian, etc.
Cómo ordenar los datos por una sola columna
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:=xlYesEnd Sub
Nota que he creado un rango con el nombre de ‘DataRange’ y lo he utilizado en lugar de las referencias de celdas.
También hay tres parámetros clave que se utilizan aquí:
- Clave1 – Es la que se quiere ordenar el conjunto de datos. En el código de ejemplo anterior, los datos se ordenarán en base a los valores de la columna A.
- Orden- Aquí necesita especificar si quiere ordenar los datos en orden ascendente o descendente.
- Cabecera – Aquí necesita especificar si sus datos tienen cabeceras o no.
Lea más sobre cómo ordenar datos en Excel usando VBA.
Cómo ordenar datos por múltiples columnas
Suponga que tiene un conjunto de datos como el que se muestra a continuación:
A continuación se muestra el código que ordenará los datos en base a múltiples 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 .ApplyEnd WithEnd Sub
Nótese que aquí he especificado ordenar primero en base a la columna A y luego en base a la columna B.
La salida sería algo como lo que se muestra a continuación:
Cómo obtener sólo la parte numérica de una cadena en Excel
Si quieres extraer sólo la parte numérica o sólo la parte de texto de una cadena, puedes crear una función personalizada en VBA.
A continuación, puede utilizar esta función VBA en la hoja de trabajo (al igual que las funciones regulares de Excel) y extraerá sólo la parte numérica o de texto de la cadena.
Algo como lo que se muestra a continuación:
Abajo está el código VBA que creará una función para extraer la parte numérica de una cadena:
'This VBA code will create a function to get the numeric part from a stringFunction GetNumeric(CellRef As String)Dim StringLength As IntegerStringLength = Len(CellRef)For i = 1 To StringLengthIf IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)Next iGetNumeric = ResultEnd Function
Es necesario colocar en código en un módulo, y luego puede utilizar la función =GetNumeric en la hoja de trabajo.
Esta función sólo tomará un argumento, que es la referencia de la celda de la que se quiere obtener la parte numérica.
De forma similar, a continuación te mostramos la función que obtendrá únicamente la parte de texto de una cadena en Excel:
'This VBA code will create a function to get the text part from a stringFunction GetText(CellRef As String)Dim StringLength As IntegerStringLength = Len(CellRef)For i = 1 To StringLengthIf Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)Next iGetText = ResultEnd Function
Así que estos son algunos de los códigos de macros de Excel útiles que puedes utilizar en tu día a día para automatizar tareas y ser mucho más productivo.
Otros tutoriales de Excel que te pueden gustar:
- Cómo eliminar macros en Excel
.