Creación de macros en Microsoft Excel. Macros en Excel - Instrucciones de uso
Comencé a crear este sitio como un cuaderno sobre las prácticas funciones de Excel. En cualquier momento, puede actualizar sus conocimientos sobre una u otra característica del programa visitando el sitio web, además puede enviar un enlace al artículo a amigos o colegas que soliciten ayuda con Excel. Muy rápidamente, la audiencia del sitio creció y, según veo, la información grabada es útil no sólo para mí y para otras personas, sino también para muchas personas que no conozco y está altamente valorada en los motores de búsqueda. Si bien confío en las capacidades de Excel, no puedo considerarme un especialista en VBA de muy alto nivel. Por lo tanto, me gustaría crear un artículo auxiliar extenso donde registraré las macros útiles que uso con frecuencia. Estoy seguro de que esta colección de funciones de VBA será útil no sólo para mí.
El sitio ya tiene varios artículos sobre VBA, incluidos, pero creo que será útil mantener los gadgets de código que se usan con frecuencia en un solo lugar.
Desactiva la visualización de acciones macro en la pantalla con una función sencilla. Esto no sólo mejorará visualmente la ejecución, sino que también acelerará significativamente la ejecución de macros.
Sub Makros1() Application.ScreenUpdating = 0 "Su código Application.ScreenUpdating = 1 End Sub
No olvides incluir la función al final de la macro.
¿Cómo puedo eliminar la selección de copia después de ejecutar una macro?
Si tiene todo esto en su macro, seleccione un rango para copiar y pegar. No olvides desactivar la selección al final de la macro.
Sub Makros1() "Su código Application.CutCopyMode = 0 End Sub
Macros útiles. Cómo encontrar la última fila o columna de un rango
Esta construcción le ayudará a encontrar fácilmente el número de la última fila o columna del rango completado. Es especialmente conveniente usarlo en ciclos; no es necesario especificar 1000 líneas de ciclo con un margen; el propio Excel encontrará dónde está el final del rango usando esta construcción:
Sub makros1() Dim mLastRow As Long Dim nLastCol As Long mLastRow = Cells(Rows.Count, 1).End(xlUp).Row "Encuentra el último número de fila del rango completo nLastCol= Cells(1, Columns.Count). End(xlToLeft).Columna "Busca el número de la última columna poblada en el rango End Sub
Además, declaro inmediatamente la variable como Long (longitud 2.147.483.647) para no llegar a una situación en la que el popular Integer pueda no ser suficiente (32.767) para tablas grandes.
También debes prestar atención a que la búsqueda se realice en la primera columna o primera fila, selecciona el número deseado para la correcta determinación.
Para bucle y comprobación de condiciones en el bucle.
Si ha encontrado los últimos números de fila y columna de los rangos, puede usarlos en bucles. Por ejemplo, debe recorrer la primera columna y contar el número de celdas vacías.
Sub makros1() Application.ScreenUpdating = 0 Dim mLastRow As Long Dim Kol As Long "Variable de conteo Dim i As Long "Variable de bucle mLastRow = Cells(Rows.Count, 1).End(xlUp).Row Kol = 0 For i = 1 A mLastRow Si Cells(i, 1).Value = "" Entonces Kol = Kol + 1 Fin Si Siguiente i MsgBox Kol Application.ScreenUpdating = 1 Fin Sub
Msgbox también se usa aquí; con esta función, puede mostrar datos en una ventana separada. Para mi ejemplo se verá así:
Calcular el tiempo de ejecución de la macro
Sub makros1() TimeStart = Ahora TimeFinish = Ahora MsgBox "Hora: " & Formato(TimeFinish - TimeStart, "h:mm:ss") End SubMsgBox produce el siguiente resultado: 
Es muy conveniente realizar un seguimiento del tiempo de ejecución de las macros para su posterior optimización.
Estas son macros útiles que uso muy a menudo. Poco a poco iré ampliando esta lista. ¡Automatización efectiva para todos!
Comparte nuestro artículo en tus redes sociales:VBA se considera el lenguaje de secuencias de comandos estándar para aplicaciones de Microsoft y ahora se incluye con todas las aplicaciones de Office e incluso con aplicaciones de otras empresas. Por lo tanto, una vez que domine VBA para Excel, podrá pasar inmediatamente a crear macros para otros productos de software de Microsoft. Además, podrá crear productos de software completos que utilicen simultáneamente las funciones de una amplia variedad de aplicaciones.
Cómo habilitar macros en Excel
De forma predeterminada, la pestaña responsable de administrar y navegar por las macros en Excel está oculta. Para activar esta opción, vaya a la pestaña Archivo al grupo Opciones. En el cuadro de diálogo que aparece OpcionesSobresalir ir a la pestaña Personalización de cinta, en el cuadro combinado derecho, coloque un marcador frente a la pestaña Revelador. Estos pasos son relevantes para las versiones de Excel 2010 y anteriores.
Aparecerá una nueva pestaña en la cinta. Revelador con controles de automatización de Excel.
Escribir macros en Excel
en la pestaña Revelador en el grupo Código, haga clic en el botón Graba una macro. Aparecerá un cuadro de diálogo Grabar una macro, que solicita cierta información sobre el código futuro que se escribirá. Si es la primera vez que crea una macro, simplemente puede hacer clic en el botón DE ACUERDO. A partir de ahora, Excel registrará cada acción del usuario en un módulo VBA, ya sea entrada de datos, formato o creación de gráficos. Para detener la grabación de una macro, haga clic en el botón dejar de grabar que esta en el mismo grupo Código.
También puede aprovechar una opción alternativa para grabar macros usando el botón Grabar una macro, que se encuentra en la esquina inferior izquierda del libro de Excel (a la derecha del estado Listo).
Ahora puede ver una lista de todas las macros creadas haciendo clic en el botón Macro, ubicado en el grupo Código. En el cuadro de diálogo que aparece, puede dar nombres más descriptivos a sus códigos o configurar atajos de teclado que ejecutarían una macro en particular. Una opción alternativa para abrir esta ventana es presionar Alt + F8.
Edición de macros
¡Felicidades! Has escrito tu primera macro. Sería lógico comprobar ahora qué código nos generó Excel. El código generado está escrito en VBA (Visual Basic para Aplicaciones). Tienes que abrirlo para verlo. EditorV.B.(VBE), que se inicia presionando Alt + F11 o el botón VisualBásico en la pestaña Revelador.
Para evitar confusiones en el editor, puede trabajar con una sola pestaña en un libro, hoja o módulo. Así es como se ve el editor en la vida real.
En esta etapa, propongo estudiar con más detalle las distintas ventanas y menús del editor VBA. Esto le ayudará a ahorrar mucho tiempo en el futuro.
Para ver el código, haga clic en el hilo. Módulos en la ventana de proyectos y haga doble clic en la rama que aparece Módulo1 . El editor abrirá una ventana con el código, como se muestra en la imagen.
Aquí puede editar el código generado que se escribió mientras trabajaba en Excel. Por ejemplo, necesitas llenar una determinada columna con valores del 1 al 10. Ya tienes los primeros tres pasos, que ingresan los valores 1, 2 y 3 en las primeras tres celdas de la columna A. Necesitamos Complete los siete pasos restantes.
Si observa el código anterior, verá que la macro está estructurada de cierta manera. La aplicación primero mueve el cursor a la celda usando el comando Range("A1").Select, luego edita su contenido usando ActiveCell.FormulaR1C1 = "1". Entonces, para los pasos restantes podemos repetir estos pasos, cambiando la dirección de la celda y el valor que desea escribir en esa celda. Por ejemplo, para configurar la celda A4 en 4, escribiría:
Rango("A4").Seleccionar |
Y repita pasos similares para los valores restantes.
Una vez que haya terminado de editar, guarde su libro. Puede ejecutar la macro presionando el botón F5 o, volviendo al libro de Excel, vaya a la pestaña Revelador al grupo Código -> macros y seleccione de la lista la macro que le interesa.
Tómese unos minutos para estudiar detenidamente el código que generó Excel. Si es principiante, invertir unos minutos en aprender el código le dará resultados sorprendentes para conocer los objetos de VBA más adelante. Tenga en cuenta que el ejemplo que analizamos es sólo una ilustración. Existen formas más rápidas y efectivas de lograr resultados similares, que discutiremos a continuación.
Incrementar la velocidad de ejecución de macros de Excel
Hasta ahora, todo bien. Veamos un par de trucos que ayudarán a acelerar la ejecución de macros. Tomemos el fragmento de código anterior como ejemplo. Las computadoras modernas ejecutarán el código en cuestión tan rápido que ni siquiera lo notarás. Pero, ¿qué pasa si necesita realizar la operación 50.000 veces? Esto llevará algún tiempo. Si la macro que escribe tiene cientos de líneas, puede acelerar la ejecución del código recortando la parte de los procesos que no se utiliza durante la ejecución de la macro.
Usando el comando Application.ScreenUpdating
El primer truco consiste en evitar actualizar la pantalla mientras se ejecuta la macro. Esto permitirá que Excel ahorre la potencia de procesamiento de la computadora y actualice la pantalla con valores nuevos solo después de que se haya ejecutado todo el código. Para hacer esto, necesita agregar un comando para deshabilitar la actualización de pantalla al comienzo de la macro y un comando para habilitar la actualización de pantalla al final de la macro.
1 | Submacro1() |
El comando Application.ScreenUpdating le dice a Excel que deje de mostrar los datos recalculados en la pantalla y devuelva los valores terminados al final de la ejecución del código.
Usando el comando de la aplicación. Cálculo
El segundo truco consiste en desactivar los cálculos automáticos. Déjame explicarte. Cada vez que un usuario o proceso actualiza una celda, Excel intenta recalcular todas las celdas que dependen de ella. Entonces, digamos que si la celda que la macro intenta actualizar afecta a otras 10,000 celdas, Excel intentará recalcularlas todas antes de que el código termine de ejecutarse. En consecuencia, si hay varias celdas que influyen, el recálculo puede ralentizar significativamente la ejecución del código. Para evitar que esto suceda, puede instalar el comando Aplicación. Cálculo al comienzo del código, que cambiará el recálculo de fórmulas al modo manual y luego devolverá el cálculo automático al final de la macro.
1 | Submacro1() |
Tenga cuidado de no olvidar volver a cambiar esta opción al modo automático al final de la macro. De lo contrario, deberá hacer esto en Excel haciendo clic en la pestaña Fórmulas al grupo Cálculo y elige Opciones de cálculo –> Automático.
Evitar seleccionar celdas y rangos
En el modo de grabación automática de macros, puede notar que Excel usa con mucha frecuencia el comando de selección de celdas, por ejemplo, Rango("A1").Seleccionar. En nuestro ejemplo, usamos este comando varias veces para seleccionar una celda y cambiar su valor. Puede evitar esto simplemente especificando la dirección de la celda y dándole el valor requerido (la macro registró el movimiento del cursor de una celda a otra, por lo que insertó estos pasos. Sin embargo, no son necesarios). Entonces, un código más eficiente se vería así.
1 | Submacro1() |
En este caso, simplemente hicimos referencia a la celda y le dimos el valor requerido, sin seleccionarla en absoluto. Este método es más rápido que el anterior.
Ejemplos de macros de Excel
A continuación se muestran algunos códigos VBA de muestra que le ayudarán a automatizar las tareas más comunes.
Macro para resaltar la celda A1 en cada hoja del libro activo. Esto también hace que la pantalla se mueva.
Sub A1SelectionEachSheet() Dim i As Integer Application.ScreenUpdating = False For i = 1 To Sheets.Count Sheets(i).Select ActiveWindow.ScrollColumn = 1 ActiveWindow.ScrollRow = 1 Range("a1").Select Next Sheets(1) .Seleccione Aplicación.ScreenUpdating = True End Sub
Macro para copiar la hoja actual un número específico de veces. Útil para probar algunas macros: realizó cambios, verificó una copia de los datos. Nos quedamos sin copias: ejecute la macro nuevamente
Sub SimpleCopy() Dim i As Integer, j As Integer i = Application.InputBox("Ingrese el número de copias de la hoja actual") Application.ScreenUpdating = False For j = 1 To i ActiveSheet.Copy after:=Sheets(Hojas .Count) ActiveSheet .Name = "Copiar" & j Siguiente j Application.ScreenUpdating = True End Sub
Crear hojas con títulos de un rango específico en una hoja
Sub CreateFromList() Atenuar celda como rango para cada celda en hojas de selección. Agregar después: = Hojas (Hojas.Count) ActiveSheet.Name = cell.Value Siguiente celda Fin Sub
Markros por enviar una carta con retraso. Macro modificada del libro Professional VBA Programming de John Walkenbach
Sub SendLetter() Atenuar OutApp como objeto Atenuar OutMail como objeto Establecer OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon En caso de error Limpieza GoTo Establecer OutMail = OutApp.CreateItem(0) En caso de error Reanudar a continuación con OutMail .To = " [correo electrónico protegido]" .Subject = "Informe de ventas" .Attachments.Add "C:\Test.txt" .Body = "Texto de correo electrónico" .DeferredDeliveryTime = Reemplazar(Fecha, ".", "/") & " 11:00:00 " .send ".Display para generar una carta y abrirla End With On Error GoTo 0 Set OutMail = Nothing cleanup: Set OutApp = Nothing End Sub
Macro de tabla de contenidos ligeramente modificada de Nikolai Pavlov.
Si ya existe una hoja de "Tabla de contenido" en el libro, la macro le solicita que la elimine. De lo contrario, crea una hoja de “Tabla de contenido” e inserta enlaces con los nombres de las hojas.
Sub TableOfContent() Hoja atenuada como hoja de trabajo Celda atenuada como rango Respuesta atenuada como entero Application.ScreenUpdating = False Con ActiveWorkbook para cada hoja de trabajo en ActiveWorkbook.Worksheets Si Worksheet.Name = "Tabla de contenido" Entonces responda = MsgBox("El libro tiene un hoja con el nombre Tabla de contenido ¿Eliminarla?", vbYesNo) Si Respuesta = vbNo Entonces Salir Sub Si Respuesta = vbSí Entonces Application.DisplayAlerts = False Worksheet.Delete Application.DisplayAlerts = True End If End If Next End With Sheets(Array). (1)).Seleccione Sheets.Add Sheets(1).Name = "Tabla de contenido" con ActiveWorkbook para cada hoja en ActiveWorkbook.Worksheets si es hoja.Name<>"Tabla de contenido" Luego establezca la celda = Worksheets(1).Cells(sheet.Index, 1).Worksheets(1).Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:=""" y hoja. .Name & """ & "!A1" cell.Formula = hoja.Nombre Fin si la siguiente hoja termina con filas ("1:1"). Eliminar aplicación.ScreenUpdating = True End Sub
Clasificación de hojas de los asistentes de VBA. La macro también clasifica las hojas ocultas. No funcionará si el libro tiene una estructura protegida.
Sub SORT_ALL_SHEETS() Application.ScreenUpdating = False: Application.EnableEvents = False Dim iSht As Worksheet, oDict As Object, i%, j% Set oDict = CreateObject("Scripting.Dictionary") " recuerda el estado de visibilidad de cada hoja y hazlo todo lo visible para cada iSht en ActiveWorkbook.Sheets oDict.Item(iSht.Name) = iSht.Visible: iSht.Visible = True Siguiente con ActiveWorkbook "clasificando las hojas visibles para i = 1 a .Sheets.Count - 1 para j = i + 1 A .Sheets.Count Si UCase(.Sheets(i).Name) > UCase(.Sheets(j).Name) Entonces .Sheets(j).Move Before:=.Sheets(i) Siguiente j Siguiente i Terminar con " restaurar el estado de visibilidad original de cada hoja para cada iSht en ActiveWorkbook.Sheets iSht.Visible = oDict.Item(iSht.Name) Siguiente Application.EnableEvents = True: Application.ScreenUpdating = True End Sub
Importar las columnas “Campo1” y “Campo2” de la hoja “Hoja1” del archivo Excel “C:\Manager.xls” a través de una conexión ADODB e insertar contenido a partir de la celda A1 de la hoja actual
Pocas personas saben que la primera versión del popular producto Microsoft Excel apareció en 1985. Desde entonces, ha pasado por varias modificaciones y tiene una gran demanda entre millones de usuarios en todo el mundo. Al mismo tiempo, muchas personas trabajan sólo con una pequeña fracción de las capacidades de este procesador de hojas de cálculo y ni siquiera se dan cuenta de cómo la capacidad de programar en Excel podría facilitarles la vida.
¿Qué es VBA?
La programación en Excel se realiza utilizando el lenguaje de programación Visual Basic para aplicaciones, que originalmente se incorporó al procesador de hojas de cálculo más famoso de Microsoft.
Los expertos atribuyen a sus ventajas su relativa facilidad de aprendizaje. Como muestra la práctica, incluso los usuarios que no tienen conocimientos profesionales de programación pueden dominar los conceptos básicos de VBA. Las características de VBA incluyen la ejecución de scripts en un entorno de aplicaciones de oficina.
La desventaja del programa son los problemas asociados con la compatibilidad de diferentes versiones. Ocurren porque el código del programa VBA accede a la funcionalidad que está presente en la nueva versión del producto pero no en la versión anterior. Además, las desventajas incluyen la apertura excesivamente alta del código a cambios por parte de un extraño. Sin embargo, Microsoft Office e IBM Lotus Symphony permiten al usuario cifrar el código inicial y establecer una contraseña para verlo.
Objetos, colecciones, propiedades y métodos.
Estos son los conceptos que deben comprender quienes van a trabajar en el entorno VBA. En primer lugar, es necesario comprender qué es un objeto. En Excel, estas funciones son hoja, libro, celda y rango. Estos objetos tienen una jerarquía especial, es decir. obedecerse unos a otros.
La principal es Aplicación, que corresponde al propio programa Excel. Luego vienen los libros de trabajo, las hojas de trabajo y el rango. Por ejemplo, para acceder a la celda A1 en una hoja de trabajo específica, debe especificar una ruta que tenga en cuenta la jerarquía.
En cuanto al concepto de “colección”, se trata de un grupo de objetos de la misma clase, que en la entrada tiene la forma ChartObjects. Sus elementos individuales son también objetos.
El siguiente concepto son las propiedades. Son una característica necesaria de cualquier objeto. Por ejemplo, para Rango es Valor o Fórmula.
Los métodos son comandos que indican lo que se debe hacer. Al escribir código en VBA, deben estar separados del objeto por un punto. Por ejemplo, como se mostrará más adelante, muy a menudo cuando se programa en Excel, se utiliza el comando Celdas(1,1).Seleccionar. Significa que necesitas seleccionar una celda con coordenadas.
Selection.ClearContents se utiliza a menudo junto con él. Ejecutar esto significa borrar el contenido de la celda seleccionada.
Cómo empezar
Luego debes ir a la aplicación VB, para lo cual solo necesitas usar la combinación de teclas “Alt” y “F11”. Próximo:
- en la barra de menú ubicada en la parte superior de la ventana, haga clic en el ícono al lado del ícono de Excel;
- seleccione el comando Mudule;
- guardar haciendo clic en el icono con la imagen;
- escriben, digamos, un borrador del código.
Se parece a esto:
Subprograma()
"Nuestro código
Tenga en cuenta que la línea "Nuestro código" se resaltará en un color diferente (verde). El motivo es el apóstrofe colocado al principio de la línea, que indica que sigue un comentario.
Ahora puede escribir cualquier código y crear una nueva herramienta en VBA Excel (vea ejemplos de programas a continuación). Por supuesto, será mucho más fácil para quienes estén familiarizados con los conceptos básicos de Visual Basic. Sin embargo, incluso aquellos que no los tienen pueden acostumbrarse rápidamente si así lo desean.
Macros en Excel
Este nombre oculta programas escritos en Visual Basic para Aplicaciones. Así, programar en Excel significa crear macros con el código necesario. Gracias a esta característica, el procesador de hojas de cálculo de Microsoft se desarrolla por sí solo, adaptándose a las necesidades de un usuario específico. Una vez que haya descubierto cómo crear módulos para escribir macros, puede comenzar a mirar ejemplos específicos de programas VBA Excel. Lo mejor es comenzar con los códigos más básicos.
Ejemplo 1
Tarea: escriba un programa que copie el valor del contenido de una celda y luego lo escriba en otra.
Para hacer esto:
- abra la pestaña "Ver";
- vaya al icono “Macros”;
- haga clic en “Grabar macro”;
- Complete el formulario que se abre.
Para simplificar, deje "Macro1" en el campo "Nombre de macro" e inserte, por ejemplo, hh en el campo "Atajo de teclado" (esto significa que puede iniciar el programa con el comando rápido "Ctrl+h"). Presione Entrar.
Ahora que la grabación de la macro ya ha comenzado, el contenido de una celda se copia a otra. Volver al icono original. Haga clic en "Grabar macro". Esta acción significa el final del programa.
- volver a la línea “Macros”;
- seleccione "Macro 1" de la lista;
- Haga clic en “Ejecutar” (la misma acción se inicia iniciando la combinación de teclas “Ctrl+hh”).
Como resultado, se produce la acción que se realizó durante la grabación de la macro.
Tiene sentido ver cómo se ve el código. Para hacer esto, regrese a la línea "Macros" y haga clic en "Cambiar" o "Iniciar sesión". Como resultado, se encuentran en el entorno VBA. En realidad, el código de la macro en sí se encuentra entre las líneas Sub Macro1() y End Sub.
Si se realizó la copia, por ejemplo, de la celda A1 a la celda C1, entonces una de las líneas de código se verá como Rango(“C1”).Seleccionar. Traducido, parece “Rango(“C1”).Seleccionar”, en otras palabras, va a VBA Excel, a la celda C1.
La parte activa del código se completa con el comando ActiveSheet.Paste. Significa escribir el contenido de la celda seleccionada (en este caso A1) en la celda seleccionada C1.
Ejemplo 2
Los bucles de VBA te ayudan a crear varias macros en Excel.
Los bucles de VBA te ayudan a crear varias macros. Supongamos que tenemos una función y=x + x 2 + 3x 3 - cos(x). Necesita crear una macro para obtener su gráfico. Esto sólo se puede hacer usando bucles VBA.
Los valores inicial y final del argumento de la función son x1=0 y x2=10. Además, debe ingresar una constante: el valor del paso de cambio de argumento y el valor inicial del contador.
Todos los ejemplos de macros de VBA Excel se crean utilizando el mismo procedimiento presentado anteriormente. En este caso particular, el código se ve así:
Subprograma()
paso = 0,1
Hacer mientras x1< x2 (цикл будет выполняться пока верно выражение x1 < x2)
y=x1 + x1^2 + 3*x1^3 - Cos(x1)
Celdas(i, 1).Valor = x1 (el valor x1 se escribe en la celda con coordenadas (i,1))
Celdas(i, 2).Valor = y (el valor de y se escribe en la celda con coordenadas (i,2))
i = i + 1 (el contador está en vigor);
x1 = x1 + shag (el argumento cambia según el valor del paso);
Subtítulo final.
Como resultado de ejecutar esta macro en Excel, obtenemos dos columnas, la primera de las cuales contiene los valores de x y la segunda de y.
Luego se construye un gráfico a partir de ellos de forma estándar para Excel.
Ejemplo 3
Para implementar bucles en VBA Excel 2010, como en otras versiones, junto con la construcción Do While ya proporcionada, se utiliza For.
Considere un programa que creará una columna. En cada una de sus celdas se escribirán los cuadrados del número de fila correspondiente. Usar la construcción For te permitirá escribirlo muy brevemente, sin usar un contador.
Primero necesitas crear una macro como se describe arriba. A continuación escribimos el código en sí. Suponemos que nos interesan los valores de 10 celdas. El código se ve así.
Para i = 1 a 10 Siguiente
El comando se traduce al lenguaje “humano” como “Repetir del 1 al 10 en pasos de uno”.
Si la tarea es obtener una columna con cuadrados, por ejemplo, de todos los números impares del rango del 1 al 11, entonces escribimos:
Para i = 1 a 10 paso 1 Siguiente.
Aquí paso es un paso. En este caso es igual a dos. Por defecto, la ausencia de esta palabra en el ciclo significa que el paso es único.
Los resultados obtenidos deben guardarse en las celdas numeradas (i,1). Luego, cada vez que se inicia el ciclo, con i aumentando en un tamaño de paso, el número de fila aumentará automáticamente. De esta forma, el código se optimizará.
En general, el código se verá así:
Subprograma()
Para i = 1 a 10 Paso 1 (simplemente puedes escribir Para i = 1 a 10)
Celdas(i, 1).Valor = i ^ 2 (es decir, el valor del cuadrado i se escribe en la celda (i,1)
Siguiente (en cierto sentido desempeña el papel de un contador y significa otro comienzo del ciclo)
Subtítulo final.
Si todo se hace correctamente, incluida la grabación y ejecución de la macro (consulte las instrucciones anteriores), cuando la llame, cada vez obtendrá una columna del tamaño especificado (en este caso, que consta de 10 celdas).
Ejemplo 4
En la vida cotidiana muchas veces surge la necesidad de tomar una u otra decisión en función de alguna condición. No puedes prescindir de ellos en VBA Excel. En los ejemplos de programas en los que el curso posterior de ejecución del algoritmo se elige y no se predetermina inicialmente, se utiliza con mayor frecuencia la construcción If...Then (para casos complejos) If...Then...END If.
Consideremos un caso específico. Supongamos que necesita crear una macro para Excel de modo que en la celda con coordenadas (1,1) se escriba lo siguiente:
1 si el argumento es positivo;
0 si el argumento es nulo;
-1 si el argumento es negativo.
La creación de una macro de este tipo para Excel comienza de la forma estándar, mediante el uso de las teclas de acceso rápido Alt y F11. A continuación se escribe el siguiente código:
Subprograma()
x= Celdas(1, 1).Valor (este comando asigna a x el valor del contenido de la celda en las coordenadas (1, 1))
Si x>0 entonces Celdas(1, 1).Valor = 1
Si x=0 Entonces Celdas(1, 1).Valor = 0
si x<0 Then Cells(1, 1).Value = -1
Subtítulo final.
Todo lo que queda es ejecutar la macro y obtener el valor requerido para el argumento en Excel.
Funciones VBA
Como ya habrás notado, programar en el procesador de hojas de cálculo más famoso de Microsoft no es tan difícil. Especialmente si aprendes a utilizar las funciones de VBA. En total, este lenguaje de programación, creado específicamente para escribir aplicaciones en Excel y Word, tiene alrededor de 160 funciones. Se pueden dividir en varios grupos grandes. Este:
- Funciones matemáticas. Aplicándolos al argumento obtienen el valor del coseno, logaritmo neperiano, parte entera, etc.
- Funciones financieras. Gracias a su disponibilidad y al uso de la programación en Excel, podrás obtener herramientas efectivas para cálculos contables y financieros.
- Funciones de procesamiento de matrices. Estos incluyen Array, IsArray; LEncuadernado; UBound.
- Funciones de Excel VBA para cadenas. Este es un grupo bastante grande. Esto incluye, por ejemplo, las funciones Espacio para crear una cadena con un número de espacios igual al argumento entero, o Asc para convertir caracteres a código ANSI. Todos ellos son muy utilizados y permiten trabajar con filas en Excel, creando aplicaciones que facilitan enormemente el trabajo con estas tablas.
- Funciones de conversión de tipos de datos. Por ejemplo, CVar devuelve el valor del argumento Expresión convirtiéndolo al tipo de datos Variante.
- Funciones para trabajar con fechas. Amplían significativamente los estándar, por lo que la función WeekdayName devuelve el nombre (completo o parcial) del día de la semana por su número. Aún más útil es Timer. Da el número de segundos que han pasado desde la medianoche hasta un punto específico del día.
- Funciones para convertir un argumento numérico a diferentes sistemas numéricos. Por ejemplo, Oct genera números en octal.
- Funciones de formato. El más importante de ellos es el formato. Devuelve un valor Variante con una expresión formateada según las instrucciones especificadas en la declaración de formato.
- etc.
El estudio de las propiedades de estas funciones y su aplicación ampliará significativamente el alcance de Excel.
Ejemplo 5
Intentemos pasar a resolver problemas más complejos. Por ejemplo:
Se proporciona un documento en papel que informa el nivel real de costos de la empresa. Requerido:
- desarrollar su parte de plantilla utilizando la hoja de cálculo Excel;
- crear un programa VBA que solicitará datos iniciales para completarlo, realizar los cálculos necesarios y completar con ellos las celdas de la plantilla correspondiente.
Consideremos una de las opciones de solución.
Crear una plantilla
Todas las acciones se realizan en una hoja estándar en Excel. Las celdas libres están reservadas para ingresar datos sobre el nombre de la empresa consumidora, el monto de los costos, su nivel y su facturación. Dado que el número de empresas (empresas) para las que se elabora el informe no es fijo, las celdas para ingresar valores en función de los resultados y el nombre del especialista no se reservan de antemano. La hoja de trabajo recibe un nuevo nombre. Por ejemplo, "informes".
variables
Para escribir un programa que complete automáticamente una plantilla, debe seleccionar notaciones. Estos se utilizarán para las variables:
- NN - número de la fila de la tabla actual;
- TP y TF: volumen de negocios comercial planificado y real;
- SF y SP: monto de costos reales y planificados;
- IP e IF: nivel de costos planificado y real.
Denotemos la acumulación del total de esta columna usando las mismas letras, pero con el "prefijo" Itog. Por ejemplo, ItogTP: se refiere a la columna de la tabla titulada "volumen de negocios planificado".
Resolver un problema usando programación VBA
Utilizando las notaciones introducidas, obtenemos fórmulas para las desviaciones. Si necesita calcular en %, tenemos (F - P) / P * 100, y en total - (F - P).
Los resultados de estos cálculos se pueden ingresar mejor directamente en las celdas correspondientes de la tabla de Excel.
Para los totales reales y previstos, se obtienen utilizando las fórmulas ItogP=ItogP + P e ItogF=ItogF+ F.
Para desviaciones utilizar = (ItogF - ItogP) / ItogP * 100 si el cálculo se realiza en porcentaje, y en el caso de un valor total - (ItogF - ItogP).
Los resultados se escriben nuevamente inmediatamente en las celdas correspondientes, por lo que no es necesario asignarlos a variables.
Antes de ejecutar el programa creado, debe guardar el libro de trabajo, por ejemplo, con el nombre "Report1.xls".
Solo es necesario presionar el botón "Crear tabla de informes" una vez después de ingresar la información del encabezado. Hay otras reglas que debes conocer. En particular, se debe hacer clic en el botón "Agregar fila" cada vez que se ingresan valores para cada tipo de actividad en la tabla. Después de ingresar todos los datos, debe hacer clic en el botón "Finalizar" y luego cambiar a la ventana de Excel.
Ahora ya sabes cómo resolver problemas de Excel usando macros. Es posible que también se necesite la capacidad de usar vba excel (ver ejemplos de programas arriba) para trabajar en el editor de texto más popular en este momento, Word. En particular, puede crear botones de menú escribiendo, como se muestra al principio del artículo, o escribiendo código, gracias al cual se pueden realizar muchas operaciones en el texto presionando las teclas de servicio o mediante la pestaña "Ver" y el icono “Macros”.
Los siguientes ejemplos simples de macros de Excel ilustran algunas de las características y técnicas descritas en el tutorial de Excel VBA.
Macro de Excel: ejemplo 1
Inicialmente este procedimiento Sub se dio como ejemplo del uso de comentarios en código VBA. Sin embargo, aquí también puedes ver cómo se declaran las variables, cómo funcionan las referencias de celda de Excel y el uso de un bucle. Para, operador condicional Si y mostrar una ventana de mensaje.
"El procedimiento Sub busca una celda que contenga la cadena especificada "en el rango de celdas A1:A100 de la hoja activa Sub Find_String(sFindText As String) Dim i As Integer "Un entero de tipo Integer, usado en un bucle For Dim iRowNumber As Integer "Un entero de tipo Integer para almacenar el resultado iRowNumber = 0 "Busca en las celdas A1:A100 una por una hasta encontrar la cadena sFindText For i = 1 To 100 If Cells(i, 1).Value = sFindText Then " Si se encuentra una coincidencia con la cadena especificada " guarde el número de fila actual y salga del bucle For iRowNumber = i Salir para finalizar si es siguiente i "Informar al usuario en una ventana emergente si se ha encontrado la fila requerida "Si se ha especificado se encuentra la fila, indique en qué celda se encuentra la coincidencia. Si iRowNumber = 0 Then MsgBox "Row " & sFindText & " not found" De lo contrario MsgBox "Row " & sFindText & " encontrado en la celda A" & iRowNumber End If End Sub
Macro de Excel: ejemplo 2
Próximo procedimiento Sub– ejemplo de uso de un bucle hacer mientras. También puede ver cómo se declaran las variables, trabajar con referencias de celdas de Excel y usar una declaración condicional. Si.
"El procedimiento Sub genera números de Fibonacci que no exceden 1000 Sub Fibonacci() Dim i As Integer "Un contador para indicar la posición de un elemento en la secuencia Dim iFib As Integer "Almacena el valor actual de la secuencia Dim iFib_Next As Integer "Almacena el siguiente valor de la secuencia Dim iStep As Integer "Almacena el tamaño del siguiente incremento "Inicializa las variables i e iFib_Next i = 1 iFib_Next = 0 "El bucle Do While se ejecutará hasta que el valor del "número de Fibonacci actual exceda 1000 Do While iFib_Siguiente< 1000 If i = 1 Then "Особый случай для первого элемента последовательности iStep = 1 iFib = 0 Else "Сохраняем размер следующего приращения перед тем, как перезаписать "текущее значение последовательности iStep = iFib iFib = iFib_Next End If "Выводим текущее число Фибоначчи в столбце A активного рабочего листа "в строке с индексом i Cells(i, 1).Value = iFib "Вычисляем следующее число Фибоначчи и увеличиваем индекс позиции элемента на 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub
Macro de Excel: ejemplo 3
este procedimiento Sub escanea las celdas de una columna A hoja activa hasta que encuentre una celda vacía. Los valores se escriben en una matriz. Esta sencilla macro de Excel muestra cómo trabajar con matrices dinámicas y cómo utilizar un bucle. hacer hasta. En este ejemplo, no realizaremos ninguna acción con la matriz, aunque en la práctica de programación real, después de escribir los datos en la matriz, dichas acciones generalmente se realizan en ellos.
"El procedimiento Sub almacena los valores de celda de la columna A de la hoja activa en la matriz Sub GetCellValues() Dim iRow As Integer "Almacena el número de la fila actual Dim dCellValues() As Double "Una matriz para almacenar los valores de las celdas iRow = 1 ReDim dCellValues(1 To 10) "Do Loop Until itera secuencialmente a través de las celdas de la columna A de la hoja activa "y extrae sus valores en una matriz hasta que se encuentra una celda vacía Do Until IsEmpty(Cells( iRow, 1)) "Compruebe que la matriz dCellValues sea de tamaño suficiente" Si no, aumente el tamaño de la matriz en 10 usando ReDim If UBound(dCellValues)< iRow Then ReDim Preserve dCellValues(1 To iRow + 9) End If "Сохраняем значение текущей ячейки в массиве dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop End Sub
Macro de Excel: ejemplo 4
En este ejemplo, el procedimiento Sub lee valores de una columna A hoja de trabajo Hoja2 y realiza operaciones aritméticas sobre ellos. Los resultados se ingresan en las celdas de la columna. A en la hoja de trabajo activa. Esta macro demuestra el uso de objetos de Excel. En particular, la apelación se lleva a cabo mediante el procedimiento. Sub al objeto columnas y muestra cómo se accede a este objeto a través del objeto Hoja de trabajo. También se muestra que al acceder a una celda o rango de celdas de la hoja activa, no es necesario especificar el nombre de esta hoja al escribir el enlace.
"El procedimiento Sub, usando un bucle, lee los valores en la columna A de la hoja de trabajo Hoja2, "realiza operaciones aritméticas con cada valor y escribe el resultado en la "columna A de la hoja de trabajo activa (Hoja1) Sub Transfer_ColA() Dim i Como Entero Dim Col Como Rango Dim dVal Como Doble "Asignar a la variable Col la columna A de la hoja de trabajo Hoja 2 Establecer Col = Hojas("Hoja2").Columnas("A") i = 1 "Usando un bucle, leemos los valores de las celdas de la columna Col hasta "hasta que se encuentre una celda vacía Do Until IsEmpty(Col.Cells(i)) "Realice operaciones aritméticas en el valor de la celda actual dVal = Col.Cells(i).Value * 3 - 1 "El siguiente comando escribe el resultado en la columna A de la hoja de trabajo activa "No es necesario indicar el nombre de la hoja en el enlace ya que esta es la hoja activa Celdas (i, 1) = dVal i = i + 1 Loop End Sub.
Macro de Excel: ejemplo 5
Esta macro muestra un ejemplo de código VBA que monitorea un evento de Excel. El evento al que se adjunta la macro ocurre cada vez que se selecciona una celda o un rango de celdas en la hoja de trabajo. En nuestro caso, al seleccionar una celda B1, aparece una ventana de mensaje en la pantalla.
"Este código muestra un cuadro de mensaje si la celda B1 está seleccionada en la hoja de trabajo actual. Private Sub Worksheet_SelectionChange(ByVal Target As Range) "Compruebe si la celda B1 está seleccionada si Target.Count = 1 y Target.Row = 1 y Target.Column = 2 Luego "Si se selecciona la celda B1, realice la acción requerida MsgBox "Ha seleccionado la celda B1" End If End Sub
Macro de Excel: ejemplo 6
Este procedimiento ilustra el uso de operadores. En caso de error Y Reanudar para el manejo de errores. Este código también muestra un ejemplo de cómo abrir y leer datos de un archivo.
"El procedimiento Sub asigna a los argumentos Val1 y Val2 los valores de las celdas A1 y B1" del libro Data.xlsx ubicado en la carpeta C:\Documents and Settings Sub Set_Values(Val1 As Double, Val2 As Double) Dim DataWorkbook As Libro de trabajo en caso de error Ir a ErrorHandling " Abra el libro de trabajo con los datos Set DataWorkbook = Workbooks.Open("C:\Documents and Settings\Data") "Asigne los valores de las variables Val1 y Val2 del libro de trabajo dado Val1 = Sheets("Sheet1 ").Cells(1, 1) Val2 = Sheets("Sheet1").Cells(1, 2) DataWorkbook.Close Exit Sub ErrorHandling: "Si no se encuentra el archivo, se le pedirá al usuario que coloque el archivo buscado " en la carpeta deseada y luego continúe ejecutando la macro MsgBox "¡No se encuentra el archivo Data.xlsx!" & _ "Agregue el libro a la carpeta C:\Documentos y configuración y haga clic en Aceptar" Reanudar Fin Sub
