En este artículo, voy a mostrarle algunos de los códigos VBA Excel más increíbles que puede usar para optimizar su trabajo. VBA es un lenguaje de programación que se puede utilizar para ampliar las capacidades de MS Excel y otras aplicaciones de MS Office. Es extremadamente útil para los usuarios de MS Excel, ya que se puede utilizar para automatizar su trabajo y mejorar significativamente su eficiencia. Este artículo le presentará a VBA y le mostrará algunos de los códigos VBA más útiles y listos para usar que existen. Puede usar estos ejemplos de macro para crear sus propios scripts que se ajusten a sus propias necesidades.

No necesita experiencia en programación para aprovechar la información de este artículo, pero se espera que tenga conocimientos básicos de Excel. Si es un usuario principiante, le recomendaría leer el artículo 20 Fórmulas de Excel Que Debe Comenzar a Usar Ahora para obtener más información sobre las funcionalidades principales de Excel.

He preparado para usted una serie de ejemplos de macro de Excel VBA listos para usar con una gran funcionalidad que puede usar para optimizar su trabajo. Para usarlos, debe «instalarlos» en su archivo de Excel. El siguiente párrafo trata de la instalación de macros de Excel. Omita esta parte si ya está familiarizado con esto.

Cómo instalar una macro

En Excel, presione la combinación de teclas alt + F11. Esto lo llevará al editor de VBA en MS Excel. A continuación, haga clic con el botón derecho en la carpeta Objetos de Microsoft Excel a la izquierda y seleccione Módulo Insert =>. Este es el lugar donde se almacenan las macros. Para hacer uso de la macro, debe guardar el documento de Excel como macro habilitado. En la pestaña archivo => guardar como, elija guardar como libro de trabajo habilitado para macros (el .extensión xlsm) ¡Ahora es el momento de escribir tu primera macro!

1. Copia datos de un archivo a otro.

Macro muy útil, ya que muestra cómo copiar un rango de datos desde el interior de vba y cómo crear y nombrar un nuevo libro de trabajo. Puede actualizarlo fácilmente para que se ajuste a sus propios requisitos:

Sub CopyFiletoAnotherWorkbook () ' Copie las hojas de datos ("Ejemplo 1").Rango ("B4: C15").Copiar ' Crear un nuevo libro de trabajo Libros de trabajo.Añadir ' Pegar la hoja de datos activos.Pegar ' Desactivar la aplicación de alertas de aplicaciones.DisplayAlerts = False ' Guarda el nuevo archivo. Cambie el nombre del directorio. ActiveWorkbook.Guardar Nombre de archivo:="C:\Temp\MyNewBook.xlsx "' Volver a activar alertas de aplicación en la aplicación.DisplayAlerts = TrueEnd Sub

2. Mostrar filas ocultas

Ocasionalmente, los archivos Excel grandes contienen líneas ocultas para una mayor claridad. Aquí hay una macro que mostrará todas las filas de una hoja de trabajo activa:

Sub ShowHiddenRows() Columnas.Toda la columna.Hidden = False Filas.En todo momento.Hidden = FalseEnd Sub

3. Eliminar filas y columnas vacías

Las filas en blanco en Excel son un problema con el procesamiento de datos. Aquí está cómo deshacerse de ellos:

Sub DeleteEmptyRowsAndColumns () ' Declare sus variables. Dim myRange As Range Dim iCounter As Long ' Define el Rango objetivo. Set myRange = ActiveSheet.UsedRange ' Comienza el bucle inverso a través del rango de filas. Para iCounter = myRange.Filas.Cuente Hasta 1 Paso -1 'Si toda la fila está vacía, elimínela. Si La Aplicación.Counta (Filas (iCounter).EntireRow ) = 0 Y Luego Filas (iCounter).Eliminar 'Eliminar comentario para Ver cuáles son las filas vacías 'MsgBox" row "& iCounter & "está vacío" Termina Si 'Incrementa el contador hacia abajo En el siguiente iCounter 'Paso 6: Comienza el bucle inverso a través del rango de columnas. Para iCounter = myRange.Columna.Cuente Hasta 1 Paso -1 ' Paso 7: Si toda la columna está vacía, elimínela. Si La Aplicación.CountA (Columnas (iCounter).EntireColumn ) = 0 Y Luego Columnas (iCounter).Borrar Fin Si " Paso 8: Incremente el contador hacia abajo En el siguiente extremo de iCounter Sub

4. Encontrar una celda en blanco

Sub FindEmptyCell() ActiveCell.Desplazamiento (1, 0).Seleccione Hacer Mientras No IsEmpty(ActiveCell) ActiveCell.Desplazamiento (1, 0).Seleccione Bucle Sub

5. Reemplace celdas vacías con un valor.

Como se mencionó anteriormente, las celdas en blanco interfieren con el procesamiento de datos y la creación de tablas dinámicas. Aquí hay un código que reemplaza todas las celdas en blanco con 0. Esta macro tiene una aplicación muy grande porque puede usarla para buscar y reemplazar resultados N/A, así como otros caracteres como puntos, comas o valores duplicados:

Sub FindAndReplace() 'Declare sus variables Dim myRange Como Rango Dim myCell Como rango '¿Guardar el libro de trabajo antes de cambiar las celdas? Seleccione Case MsgBox ("No se puede Deshacer esta acción. "& _ " ¿Guardar el Libro de Trabajo Primero?", vbIesNoCancel) Case Is = vbYes this Workbook.Guardar caso Es = vbCancel Exit Sub End Select ' Definir el rango de destino. Set myRange = Selection ' Comienza a recorrer el rango en bucle. Para cada MiCela En la comprobación de longitud cero de myRange, agregue 0. If Len (myCell.Valor) = 0 Entonces myCell = 0 Fin Si ' Obtener la siguiente celda en el rango Siguiente MyCellEnd Sub

6. Ordenar números

La siguiente macro ordena en orden ascendente todos los números de la columna de la celda activa. Simplemente haga doble clic en cualquier celda de una columna que desee ordenar.NOTA: Para que funcione, debe poner el código en la hoja 1 y no en un módulo:

Private Sub Worksheet_BeforeDoubleClick (ByVal Target as Range, Cancel as Boolean) 'Declarar Variables Dim LastRow siempre 'Encontrar la última fila LastRow = Células (Filas.Cuenta, 1).Fin (xlUp).Orden ascendente en filas de columnas con doble clic ("6:" & LastRow) .Ordenar _ Clave 1: = Celdas (6, ActiveCell.Columna), _ Orden1: = Xlascedingend Sub

7. Eliminar espacios vacíos

Ocasionalmente, los datos del libro de trabajo contienen espacios adicionales (espacios) que pueden interferir con el análisis de datos y corromper fórmulas. Aquí hay una macro que eliminará todos los espacios de un rango de celdas preseleccionado:

Sub TrimTheSpaces() 'Declare sus variables Dim myRange Como Rango Dim myCell Como Rango 'Guarde el libro de trabajo antes de cambiar las celdas Seleccione Case MsgBox("No se puede Deshacer esta acción. "& _ " ¿Guardar el Libro de Trabajo Primero?", vbIesNoCancel) Case Is = vbYes this Workbook.Guardar caso Es = vbCancel Exit Sub End Select ' Definir el rango de destino. Set myRange = Selection ' Comienza a recorrer el rango en bucle. Para Cada MiCela En myRange, Recorta los Espacios. Si No es Vacío (MiCela), Entonces MiCela = Trim (MiCela) End Si ' Obtiene la siguiente celda en el rango Siguiente Micela Sub

8. Resaltar valores dublados

A veces hay valores duplicados en las varias columnas que nos gustaría iluminar. Aquí hay una macro que hace exactamente eso:

 Sub HighlightDuplicates () 'Declare sus variables Dim myRange Como Rango Dim myCell Como Rango' Defina el rango de destino. Set myRange = Selection ' Comienza a recorrer el rango en bucle. Para cada micela En myRange, Asegúrese de que la celda tenga formato de texto. Si WorksheetFunction.CountIf (myRange, myCell.Valor) > 1 Luego myCell.Interior.ColorIndex = 36 Fin Si ' Obtener la siguiente celda en el rango Siguiente Micelend Sub

9. Resaltar los diez valores principales

Este código resaltará los diez valores principales de una selección de celdas:

 Selección Sub TopTen ().FormatConditions.Añadir selección op10.Condiciones de formato (Selección.FormatConditions.Contar).Establecer Prioridad Inicial Con Selección.Condiciones de formato(1).TopBottom = xlTop10Top ' Cambie el rango aquí para resaltar un número diferente de valores .Rango = 10 .Porcentaje = Final Falso Con Con Selección.Condiciones de formato(1).Letra .Color = -16752384 .TintAndShade = 0 Termina Con Con Selección.Condiciones de formato(1).Interior .Patrón ColorIndex = xlautomático .Color = 13561798 .TintAndShade = 0 Termina Con La Selección.Condiciones de formato(1).StopIfTrue = FalseEnd Sub

Puede modificar fácilmente el código para resaltar diferentes números de valores.

10. Resalte valores superiores a

Cuando ejecute este código, aparecerá una ventana. Le preguntará el valor que desea comparar las celdas que ha seleccionado.

Selección Sub HighlightGreaterThanValues() Dim i As Integer i = InputBox("Enter Greater Than Value", "Enter Value").FormatConditions.Delete ' Cambie el operador a xlLower para resaltar la Selección inferior a los valores.FormatConditions.Añadir Tipo: = xlCellValue, Operador: = xlGreater, Formula1: = i Selección.Condiciones de formato (Selección.FormatConditions.Contar).Establecer Prioridad Inicial Con Selección.Condiciones de formato(1).Letra.Color = RGB (0, 0, 0).Interior.Color = RGB(31, 218, 154) End WithEnd Sub

Puede modificar este código para resaltar también los valores más bajos.

Una macro simple que resalta todas las celdas que contienen comentarios:

Selección Sub HighlightCommentCells ().SpecialCells(xlCellTypeComments).Seleccione Selección.Style = "Nota" Final Sub

12. Resaltar celdas con palabras mal escritas

Esto es extremadamente útil cuando trabaja con funciones que toman cadenas, sin embargo, alguien ingresó la cadena con un error y sus fórmulas no funcionan. He aquí cómo solucionar este problema:

 Sub ColorMispelledCells() Para Cada cl En ActiveSheet.Rango De Uso Si No Es La Aplicación.CheckSpelling (Palabra:= cl.Texto) Luego _ cl.Interior.ColorIndex = 28 Siguiente clEnd Sub

13. Crear una tabla dinámica

Aquí le mostramos cómo crear una tabla dinámica desde MS Excel (versión 2007). Especialmente útil, cuando está haciendo un informe personalizado todos los días. Puede optimizar la creación de la tabla dinámica de la siguiente manera:

Sub PivotTableForExcel2007() Dim SourceRange Como Conjunto de rangos SourceRange = Sheets("Sheet1").Range("A3:N86") ActiveWorkbook.Memorias dinámicas.Create (_sourceType:=xlDatabase, _ SourceData:=SourceRange, _ Version: = xlPivotTableVersion12).CreatePivotTable _ TableDestination:="", _ TableName:="", _ DefaultVersion:=xlPivotTableVersion12End Sub

14. Adjunte el libro de trabajo activo en un correo electrónico

Mi código VBA favorito. Le permite adjuntar y enviar el archivo en el que está trabajando con una dirección de correo electrónico predefinida, el título del mensaje y el cuerpo del mensaje. Primero debe establecer una referencia a Microsoft Outlook (en su editor de VBA, haga clic en herramientas => referencias y elija Microsoft Outlook).

Sub SendFIleAsAttachment () 'Declare sus variables' Establezca la referencia a la biblioteca de objetos de Microsoft Outlook Dim OLApp Como Outlook.Aplicación Dim OLMail Como objeto ' Abrir Outlook iniciar un nuevo elemento de correo Establecer OLApp = Nuevo Outlook.Conjunto de aplicaciones OLMail = OLApp.CreateItem (0) OLApp.Sesion.Inicia sesión 'Crea tu elemento de correo y envíalo con OLMail. To = "[email protected]; [email protected]". CC="".BCC="".Subject = "Esta es la línea de asunto".Body = "Hola" .Adjunto.Agregar ActiveWorkbook.Nombre completo .Mostrar ' Cambiar a .Enviar a enviar sin revisar Termina Con ' Limpieza de memoria Set OLMail = Nothing Set OLApp = NothingEnd Sub

15. Enviar todos los gráficos de Excel a una presentación de PowerPoint

Una macro muy útil que le permite agregar todos los gráficos de Excel en su presentación de Powerpoint con un solo clic:

 Sub SendExcelFiguresToPowerPoint () 'Establecer referencia a la biblioteca de objetos de Microsoft Powerpoint' Declarar sus variables Dim PP Como PowerPoint.Aplicación Dim PPPres Como PowerPoint.Presentación Dim PPSlide Como PowerPoint.Deslice Dim i Como Comprobación de enteros para gráficos; salga si no existen hojas de gráficos ("Datos de diapositivas").Seleccione Si ActiveSheet.Objetos gráficos.Contar < 1 Luego MsgBox "No hay gráficos existentes en la hoja activa" Salir del Sub-Extremo Si " Abrir PowerPoint y crear un nuevo conjunto de presentaciones PP = Nuevo PowerPoint.Conjunto de aplicaciones PPPres = PP.Representación.Añadir PP.Visible = Verdadero ' Inicie el bucle basado en el recuento de gráficos Para i = 1 A ActiveSheet.Objetos gráficos.Count ' Copie el gráfico como una hoja de actividad de imagen.ChartObjects (i).Gráfico.CopyPicture _ Tamaño: = xlScreen, Formato: = Aplicación xlPicture.Espera (Ahora + Valor de tiempo ("0:00:1")) 'Cuente las diapositivas y agregue una nueva diapositiva como el siguiente número de diapositiva disponible ppSlideCount = PPPres.Deslizar.Conjunto de recuento PPSlide = PPPres.Deslizar.Añadir (SlideCount + 1, ppLayoutBlank) Deslizamiento de PP.Seleccione ' Pegar la imagen y ajustar su posición; Vaya a la siguiente tabla PPSlide.Forma.Pegar.Seleccione PP.Ventana activa.Selección.ShapeRange.Alinee los centros Msoalign, Verdadero PP.Ventana activa.Selección.ShapeRange.Alinear msoAlignMiddles, True Next i ' Conjunto de Limpieza de memoria PPSlide = Nada Establecido PPPres = Nada Establecido PP = NothingEnd Sub

16. Enviar tabla de Excel en MS Word

Las tablas de Excel generalmente se colocan dentro de documentos de texto. Aquí hay una forma automatizada de exportar su tabla de Excel a MS Word:

 Sub ExcelTableInWord() 'Establecer referencia a la biblioteca de objetos de Microsoft Word 'Declarar sus variables Dim myRange Como Excel.Rango Dim wd Como Palabra.Aplicación Dim wdDoc Como Palabra.Documento Dim WdRange Como Word.Rango ' Copie las hojas de rango definidas ("Tabla de Ingresos").Rango ("B4: F10").Cop ' Abra el conjunto de documentos de Word de destino wd = Nueva Palabra.Conjunto de aplicaciones wdDoc = wd.Documento.Abra _ (ThisWorkbook.Ruta && "PasteTable.docx") wd.Visible = True ' Establecer foco en el marcador de destino Establecer WdRange = wdDoc.Marcadores("DataTableHere").Rango ' Elimine la tabla antigua y pegue nueva En caso de error, Reanude el siguiente rango de WdRange.Cuadros (1).Eliminar WdRange.Pegar 'pegar en la tabla' Ajustar los anchos de columna WdRange.Cuadros (1).Columna.setWidth _ (myRange.Ancho / MyRange.Columna.Count), wdAdjustSameWidth 'Vuelva a insertar el marcador wdDoc.Marcadores.Agregue "DataTableHere", el conjunto de limpieza de memoria de WdRange wd = Nothing Set wdDoc = Nothing Set WdRange = NothingEnd Sub

17. Extraer una palabra específica de una celda

Podemos usar fórmulas si queremos extraer cierto número de símbolos. Pero, ¿qué pasa si queremos extraer solo la segunda palabra de una oración o un rango de palabras en una celda? Para hacer esto, podemos crear una función de Excel personalizada con VBA. Esta es una de las funcionalidades de VBA más importantes, ya que le permite crear sus propias funciones que no existen en MS Excel. Vamos a crear dos funciones: findword () y findwordrev (). Aquí está el código vba para esto:

Function FindWord (Fuente Como Cadena, Posición Como Entero) Como Cadena En Caso De Error Resume Next FindWord = Split(Función de Trabajo.Trim (Fuente),"") (Posición - 1) En Caso De Error GoTo 0end FunctionFunction FindWordRev(Fuente Como Cadena, Posición Como Entero) Como Cadena Dim Arr () Como Cadena Arr = VBA.Dividir (función de trabajo.Trim (Fuente),"") En caso de error Resume Next FindWordRev = Arr(UBound(Arr) - Posición + 1) En caso de error GoTo 0End Function

Muy bien, hemos creado dos funciones de Excel cstom. Ahora, intente usarlos en Excel. La función = FindWordRev (A1,1) toma la última palabra de la celda A1. La función = FindWord (A1,3) toma la tercera palabra de la celda A1, etc.

18. Proteja su libro de trabajo

A veces queremos proteger los datos de nuestro archivo para que solo nosotros podamos cambiarlos. Aquí le mostramos cómo hacer esto con VBA:

 Sub ProtectSheets() 'Declare sus variables Dim ws Como Hoja de trabajo 'Comience a recorrer todas las hojas de trabajo Para Cada ws En ActiveWorkbook.Las hojas de trabajo protegen y se enlazan a la siguiente hoja de trabajo ws.Proteger contraseña: = "1234" Siguiente Sub de wsEnd

¡Felicitaciones! Ya que todavía estás leyendo esto, estás realmente interesado en aprender VBA. Como ya ha visto, el lenguaje de programación VBA es extremadamente útil y nos puede ahorrar mucho tiempo. Espero que haya encontrado esta información útil y la use para convertirse en un maestro en MS Excel, VBA y software de computadora en general.