VBA Excel: ejemplos de programas. Macros en Excel

Pocas personas saben que la primera versión del popular producto de Microsoft Excel apareció en 1985. Desde entonces, ha sufrido varias modificaciones y está en demanda entre millones de usuarios en todo el mundo. Al mismo tiempo, muchos trabajan con sólo una pequeña parte de las capacidades de este procesador de tablas y ni siquiera adivinar cómo podrían hacer la vida más fácil en Excel habilidades de programación.

¿Qué es VBA?

La programación en Excel se lleva a cabo a través del lenguaje de programación Visual Basic for Application, que está incorporado originalmente en el procesador de mesa más famoso de Microsoft.

A sus méritos, los expertos atribuyen la facilidad comparativa de desarrollo. Como muestra la práctica, los usuarios de VBA pueden dominar incluso aquellos que no tienen habilidades de programación profesional. La peculiaridad de VBA es la ejecución del script en el entorno de las aplicaciones de oficina.

La desventaja del programa son los problemas asociados con la compatibilidad de diferentes versiones. Se deben al hecho de que el código del programa VBA se refiere a la funcionalidad que está presente en la nueva versión del producto, pero no en la antigua. Además, las desventajas incluyen demasiada apertura del código para el cambio por una persona no autorizada. Sin embargo, Microsoft Office, así como IBM Lotus Symphony, permiten al usuario usar cifrado del código inicial y establecer una contraseña para verlo.

Objetos, colecciones, propiedades y métodos

Es con estos conceptos que usted necesita entender quién va a trabajar en el entorno de VBA. En primer lugar, es necesario entender lo que es un objeto. En Excel, se trata de una hoja, un libro, una celda y un rango. Estos objetos tienen una jerarquía especial, es decir, Obedecerse uno al otro.

El principal es la aplicación, que corresponde a Excel mismo. A continuación, siga los libros, hojas de cálculo y Range. Por ejemplo, para acceder a la celda A1 en una hoja en particular, debe especificar una ruta de acceso que incluya la jerarquía.

En cuanto al concepto de "colección", entonces este es un grupo de objetos de la misma clase que en la entrada se parece a 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, esto es Valor o Fórmula.

Los métodos son comandos que muestran lo que hay que hacer. Al escribir código en VBA, debe separarlos del objeto por un punto. Por ejemplo, como se mostrará más adelante, muy a menudo cuando se programa en Excel, utilice el comando Celdas (1,1) .Seleccione. Esto significa que debe seleccionar una celda con coordenadas (1,1) es decir A1.

Con él, Selection.ClearContents se utiliza a menudo. Realizarlo significa borrar el contenido de la celda seleccionada.

Cómo empezar

En primer lugar, es necesario crear un archivo y guardarlo asignando un nombre y seleccionando el tipo de libro "Excel workbook with macro support".

Entonces usted necesita ir a la aplicación VB, sólo tiene que usar las teclas "Alt" y "F11". Siguiente:

• En la barra de menús situada en la parte superior de la ventana, haga clic en el icono junto al icono de Excel;
• Seleccione el comando Mudule;
• Guardar haciendo clic en el icono con el disquete de la imagen;
• Escribe, digamos, un bosquejo del código.

Se ve así:

Subprograma ()

Nuestro código

End Sub

Tenga en cuenta que la línea "Nuestro código" se resaltará en un color diferente (verde). La razón está en el apóstrofo al principio de la línea, que indica que un comentario sigue.

Ahora puede escribir cualquier código y crear una nueva herramienta para usted en VBA Excel (vea los ejemplos de los programas a continuación). Por supuesto, aquellos que están familiarizados con los conceptos básicos de Visual Basic, será mucho más fácil. Sin embargo, incluso aquellos que no los tienen, si quieren, pueden acostumbrarse rápidamente a ella.

Macros en Excel

Para este nombre, los programas escritos en Visual Basic para el lenguaje de aplicación están ocultos. Por lo tanto, la programación en Excel es la creación de macros con el código deseado. Gracias a esta característica, el procesador de hojas de cálculo de Microsoft se desarrolla, adaptándose a los requisitos de un usuario en particular. Después de comprender cómo crear módulos para escribir macros, puede comenzar a buscar ejemplos específicos de los programas de 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 escriba a otro.

Para hacer esto:

• Abra la pestaña "Ver";
• Vaya al icono "Macros";
• Presione el botón "Grabar macro";
• Llene el formulario abierto.

Por simplicidad, el campo "Macro name" se deja con "Macro1", y en el campo "Atajo de teclado", por ejemplo, hh se inserta (esto significa que puede iniciar el programa usando el comando "Ctrl + h"). Pulse Intro.

Ahora que la grabación de macros ya ha comenzado, copie el contenido de una celda a otra. Vuelve al icono original. Haga clic en "Grabar Macro". Esta acción significa el final del programa.

Siguiente:

• Vaya otra vez a la línea "macros";
• En la lista, seleccione "Macro 1";
• Haga clic en "Ejecutar" (la misma acción se inicia iniciando el atajo de teclado "Ctrl + hh").

Como resultado, hay una acción que se realizó durante la grabación de la macro.

Tiene sentido ver cómo se ve el código. Para ello, vuelva a la línea "Macros" y haga clic en "Editar" o "Iniciar sesión". Como resultado, están en el entorno de VBA. En realidad, el código de macro en sí se encuentra entre las líneas Sub Macro1 () y End Sub.

Si la copia se realizó, por ejemplo, de la celda A1 a la celda C1, entonces una de las líneas de código se verá como Range ("C1"). En la traducción, se ve como "Range (" C1 "). Seleccione", en otras palabras, cambia a VBA Excel, en la celda C1.

La parte activa del código termina con el comando ActiveSheet.Paste. Significa registrar el contenido de la celda seleccionada (en este caso A1) en la celda C1 seleccionada.

Ejemplo 2

Los ciclos de VBA le ayudan a crear varias macros en Excel.

Los ciclos de VBA le ayudan a crear diferentes macros. Supongamos que existe una función y = x + x ² + 3x ³ – cos (x). Es necesario crear una macro para obtener su gráfico. Sólo puede hacer esto utilizando bucles VBA.

Para el valor inicial y final del argumento de la función, tomar x1 = 0 y x2 = 10. Además, debe introducir un valor constante para el paso de cambiar el argumento y el valor inicial del contador.

Todos los ejemplos de macros de Excel de VBA se crean utilizando el mismo procedimiento que el anterior. En este caso particular, el código se ve así:

Subprograma ()

X1 = 1

X2 = 10

Shag = 0,1

Yo 1

Hacer Mientras x1 <x2 (el bucle se ejecutará mientras la expresión x1 <x2 es verdadera)

Y = x1 + x1 ^ 2 + 3 * x1 ^ 3 – Cos (x1)

Células (i, 1) .Valor = x1 (el valor x1 se escribe en la celda con coordenadas (i, 1))

Células (i, 2) .Valor = y (el valor de y está escrito en una celda con coordenadas (i, 2))

I = i + 1 (el contador funciona);

X1 = x1 + shag (el argumento se cambia por el tamaño del paso);

Lazo

End Sub.

Como resultado de ejecutar esta macro en "Excel" obtendremos dos columnas, la primera de las cuales contiene valores para x, y en la segunda – para y.

Luego trazan el gráfico de una manera que es estándar para Excel.

Ejemplo 3

Para implementar los ciclos en VBA Excel 2010, al igual que en otras versiones, junto con la construcción Do Do ya dada, se utiliza For.

Considere un programa que crea una columna. En cada una de sus celdas se registrarán los cuadrados del número de fila correspondiente. El uso de la construcción For le permitirá escribirlo muy pronto, sin usar un contador.

Primero debe crear una macro, como se describió anteriormente. A continuación, escribimos el código en sí. Creemos que estamos interesados en valores para 10 células. El código se ve así.

Para i = 1 a 10 Siguiente

El comando se traduce al lenguaje "humano", como "Repetir de 1 a 10 en pasos de uno".

Si la tarea es obtener una columna con cuadrados, por ejemplo, todos los números impares del rango de 1 a 11, entonces escribimos:

Para i = 1 a 10 paso 1 Siguiente.

Aquí el paso es un paso. En este caso, es igual a dos. De forma predeterminada, la ausencia de esta palabra en el bucle significa que el paso es único.

Los resultados obtenidos deben almacenarse en celdas con el número (i, 1). Entonces cada vez que el ciclo comienza, con el aumento i por el tamaño del paso, el número de la fila también aumentará automáticamente. Así, el código será optimizado.

En general, el código se verá así:

Subprograma ()

Para i = 1 a 10 Paso 1 (puede escribir simplemente para i = 1 a 10)

Las celdas (i, 1) .Valor = i ^ 2 (es decir, el valor del cuadrado i) se escribe en la celda (i, 1)

A continuación (en cierto sentido, desempeña el papel de un contador y significa un ciclo más de inicio)

End Sub.

Si todo se hace correctamente, incluyendo la grabación y ejecución de la macro (vea la instrucción anterior), cada vez que se llame, se obtendrá una columna del tamaño especificado (en este caso, que consta de 10 celdas).

Ejemplo 4

En la vida cotidiana, muy a menudo hay una necesidad de tomar una decisión dependiendo de algún tipo de condición. No se puede prescindir de ellos en VBA Excel. Ejemplos de programas en los que se elige el curso de ejecución del algoritmo, en lugar de predefinirse inicialmente, a menudo utilizan la construcción If … Then (para casos complejos) If … Then … END If.

Consideremos un caso concreto. Supongamos que necesita crear una macro para Excel, de modo que la celda con coordenadas (1,1) esté escrita:

1 si el argumento es positivo;

0 si el argumento es cero;

-1 si el argumento es negativo.

La creación de una macro de este tipo para Excel se inicia de la manera estándar, utilizando las teclas "hot" Alt y F11. Entonces se escribe el siguiente código:

Subprograma ()

X = Cells (1, 1) .Value (este comando asigna el valor del contenido de la celda con coordenadas (1, 1))

Si x> 0 Entonces Cells (1, 1) .Value = 1

Si x = 0 Entonces las celdas (1, 1). Valor = 0

Si x <0 entonces las celdas (1, 1) .Valor = -1

End Sub.

Queda para ejecutar la macro y obtener el valor correcto para el argumento en Excel.

Funciones de VBA

Como habrán notado, la programación en el procesador de mesa más famoso de Microsoft no es tan difícil. Especialmente si aprende a usar las funciones de VBA. Totalmente en este lenguaje de programación, creado específicamente para escribir aplicaciones en Excel y Word, hay alrededor de 160 funciones. Pueden ser divididos en varios grupos grandes. Estas son:

• Funciones matemáticas. Aplicándolos al argumento, se obtiene el valor del coseno, el logaritmo natural, toda la parte, y así sucesivamente.
• Funciones financieras. Debido a su disponibilidad y el uso de la programación en Excel, puede obtener herramientas eficaces para contabilidad y contabilidad financiera.
• Funciones de procesamiento de matrices. Estos incluyen Array, IsArray; LBound; UBound.
• Funciones de VBA Excel para una cadena. Este es un grupo bastante grande. Incluye, por ejemplo, las funciones de espacio para crear una cadena con un número de espacios iguales a un argumento entero o Asc para traducir caracteres en código ANSI. Todos ellos son ampliamente utilizados y le permiten trabajar con cadenas 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 Expression, convirtiéndolo en un tipo de datos Variant.
• Funciones de trabajo con fechas. Expandir significativamente las características estándar de Excel. Por lo tanto, 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 transcurridos desde la medianoche hasta el momento específico del día.
• Funciones para convertir un argumento numérico a diferentes sistemas numéricos. Por ejemplo, Oct emite una representación octal de un número.
• Funciones de formato. El más importante de ellos es el formato. Devuelve un valor del tipo Variant con una expresión formateada de acuerdo con las instrucciones que se especifican en la descripción del formato.
• Y otros.

El estudio de las propiedades de estas funciones y su aplicación ampliará significativamente el ámbito de aplicación de Excel.

Ejemplo 5

Vamos a tratar de resolver problemas más complicados. Por ejemplo:

Dado un documento en papel del informe del nivel real de costos de la empresa. Requerido:

• Desarrollar su parte plantilla mediante un procesador de mesa "Excel";
• Cree un programa VBA que consultará los datos de origen para rellenarlo, realizar los cálculos necesarios y rellenarlos con las celdas de plantilla adecuadas.

Consideremos una de las variantes de la solución.

Creación de una plantilla

Todas las acciones se realizan en una hoja estándar en Excel. Células libres reservadas para la introducción de datos para el mes, año, nombre de la empresa-consumidor, cantidad de costes, su nivel, facturación. Dado que el número de empresas (empresas) respecto de las cuales se elabora el informe no es fijo, las celdas para hacer valores basados en los resultados y el nombre del especialista no están previamente reservadas. A la hoja de trabajo se le da un nuevo nombre. Por ejemplo, "Օ tchet".

Variables

Para escribir un programa para rellenar automáticamente la plantilla, debe seleccionar los símbolos. Se utilizarán para las variables:

• NN- número de la fila actual de la tabla;
• TP y TF – volumen de negocios planificado y efectivo;
• SF y SP – la cantidad real y prevista de los costos;
• IP y IF – el nivel de costes planificado y real.

Denotan por las mismas letras, pero con el "prefijo" Itog la acumulación del total para esta columna. Por ejemplo, ItogTP se refiere a la columna de una tabla titulada "volumen de negocios planificado".

Solución de problemas mediante la programación VBA

Usando la notación introducida, obtenemos fórmulas para desviaciones. Si desea calcular en%, tiene (F – P) / P * 100, y en la suma – (F – P).

Los resultados de estos cálculos pueden mejor agregarse inmediatamente a las celdas correspondientes de la tabla de Excel.

Para los resultados de hecho y el pronóstico se obtiene mediante las fórmulas ItogP = ItogP + P e ItogF = ItogF + F.

Para las desviaciones use = (ItogF – ItogP) / ItogP * 100, si el cálculo es en porcentajes, y en el caso del valor total – (ItogF – ItogP).

Los resultados se escriben inmediatamente en las celdas correspondientes, por lo que no es necesario asignarlas a variables.

Antes de iniciar el programa creado, es necesario guardar el libro, por ejemplo, bajo el nombre "Report1.xls".

El botón "Crear tabla de informes" sólo debe ser pulsado una vez después de introducir la información del encabezado. Usted debe saber otras reglas. En particular, se debe presionar el botón "Añadir línea" cada vez que ingrese valores para cada actividad de 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 que ya sabe cómo resolver el problema de Excel con macros. La capacidad de utilizar VBA Excel (ver programas de ejemplo. Arriba) y puede que tenga que trabajar en el entorno de los más populares en este momento, el editor de texto "Palabra". En particular, mediante el registro, como se muestra en el principio de este artículo, o escribiendo código para crear los botones de menú a través del cual muchas de las operaciones sobre el texto que se puede hacer pulsando las teclas de turno oa través del icono "Ver" y la pestaña "macros".