Cómo escribir macros para Excel. Macros en Excel - Instrucciones de uso. Agregar botones de macro a pestañas

Tema 2.3. Software de presentación y conceptos básicos de programación de oficina.

Tema 2.4. Sistemas de gestión de bases de datos y sistemas expertos.

2.4.11. Base de datos de formación con el botón principal "Formación_estudiantes" - Descargar

Programación VBA y Macros

2.3. Software de presentación y conceptos básicos de programación de oficina.

2.3.7. Conceptos básicos de programación de Office

Lenguaje de programación visual BÁSICO para aplicaciones: Visual Basic para Aplicaciones (VBA)

VBA es un subconjunto del lenguaje de programación visual Visual Basic (VB) que incluye casi todas las herramientas para crear aplicaciones VB.

VBA se diferencia del lenguaje de programación VB en que VBA está diseñado para funcionar directamente con objetos de Office y no se puede utilizar para crear un proyecto independiente de las aplicaciones de Office. Así, en VBA, el lenguaje de programación es VB y el entorno de programación se implementa en forma de un editor VB, que se puede activar desde cualquier aplicación de MS Office.

Por ejemplo, para abrir el editor VBA desde PowerPoint, debe ejecutar el comando Herramientas/Macro/Editor VBA. Puede regresar del editor a la aplicación seleccionando el comando Microsoft PowerPoint en el menú Ver o presionando la combinación de teclas Alt + F11.

Utilizando el conjunto de controles y el editor de formularios del editor VBA integrado, el usuario puede crear interfaz de usuario para el proyecto que se desarrolla con formulario de pantalla. Los controles son objetos y cada objeto tiene un conjunto definido de posibles eventos (por ejemplo, hacer clic o hacer doble clic con el mouse, presionar una tecla, arrastrar un objeto, etc.).

Cada evento se manifiesta en determinadas acciones del programa (respuestas, reacciones). Un formulario de usuario le permite crear ventanas de diálogo de aplicación. El lenguaje de programación VBA se utiliza para escribir código de programa, como la creación de funciones de usuario en Excel.

El hecho de que el sistema de programación VBA esté diseñado para funcionar con objetos de Office permite su uso eficaz para automatizar actividades de desarrollo. varios tipos documentos.

Veamos el algoritmo para crear funciones de usuario en VBA:

1. Abra la ventana del editor de código VBA ejecutando el comando Herramientas / Macro / Editor de Visual Basic o presionando Alt+F11.

2. Seleccione el elemento del menú del editor Insertar / Módulo.

3. A continuación, realice Insertar/Procedimiento. En el cuadro de diálogo Agregar procedimiento que se abre, ingrese el nombre de la función (por ejemplo, SUM5) y configure los interruptores: Tipo: en la posición Función; Alcance (Alcance): a la posición Público (General) y haga clic en Aceptar.

Arroz. 1.

4. En la ventana del editor para la programación VBA, aparecerá una plantilla de función: título - Función pública SUM5() y final - Función final, entre las cuales debe colocar el código del cuerpo de la función.

Arroz. 2.

5. A continuación, ingresamos una lista de parámetros de la función, por ejemplo, entre paréntesis indicamos (x, y, z, i, j), el tipo de datos (para cálculos precisos) y el tipo de valor devuelto por la función (nosotros no lo ingresaré en este ejemplo). Además, introducimos el cuerpo de la función, por ejemplo, SUMA5 = x + y + z + i + j. Como resultado, obtenemos el siguiente texto del programa:

Función pública SUM5(x, y, z, i, j)
SUMA5 = x + y + z + i + j
Función final

6. Volviendo a la ventana aplicaciones excel, en el que, por ejemplo, necesitamos sumar cinco valores.

7. Ejecute “Insertar/Función” y en la ventana del asistente de funciones que se abre, seleccione la categoría “Definido por el usuario”, y en la ventana “Seleccionar una función”, seleccione SUM5 y haga clic en Aceptar.

Arroz. 3.

Arroz. 4.

macros

Creando una macro

Creemos una macro para sumar automáticamente dos números en las celdas A1, B1 y colocar el resultado en la celda C1, además de llenar la celda C1 con color turquesa.

Algoritmo para crear una macro para la tarea:

1. Seleccione Herramientas/Macro, Iniciar grabación.

2. En el campo Nombre de la macro, ingrese un nombre para la macro. El primer carácter del nombre de la macro debe ser una letra. No se permiten espacios en el nombre de la macro; Puede utilizar guiones bajos como separadores de palabras.

3. Para ejecutar una macro usando un método abreviado de teclado, ingrese una letra en el campo Método abreviado de teclado. Puede utilizar las combinaciones CTRL+ letra (para letras minúsculas) o CTRL+SHIFT+ letra (para letras mayúsculas), donde letra es cualquier tecla de letra del teclado. No seleccione un método abreviado de teclado estándar porque el método abreviado de teclado seleccionado anula los métodos abreviados de teclado estándar Microsoft Excel en ese momento con este libro.

4. En el campo "Guardar", seleccione el libro en el que desea guardar la macro. Guarde la macro en "Este libro de trabajo". Para crear breve descripción macro, ingrese el texto requerido en el campo Descripción. La captura de pantalla muestra un ejemplo de cómo completar el cuadro de diálogo "Grabar macro"

Arroz. 5.

5. Haga clic en Aceptar.

6. Ejecute los comandos macro que desea grabar.

Arroz. 6.

7. Haga clic en el botón Detener grabación en la barra de herramientas flotante del sistema operativo (Detener grabación) o Herramientas / Macro / Detener macro.

Cuando una macro ha terminado de grabar, aparece en la lista de macros bajo su nombre.

El editor VBA grabó automáticamente una secuencia de comandos macro o un programa en función de las acciones realizadas (Fig. 7).

Arroz. 7.

Para llamar a una macro, debes ejecutar el comando Herramientas/Macro/Macros. Después de eso, en el cuadro de diálogo con una lista de macros, puede buscarla por nombre y hacer clic en el botón Ejecutar.

Asignar un botón de la barra de herramientas para ejecutar una macro

La macro se puede iniciar usando el botón en la barra de herramientas incorporada, para hacer esto, debe hacer:

1. Seleccione Configuración en el menú Herramientas.
2. En el cuadro de diálogo Configuración, seleccione la pestaña Comandos y seleccione la opción Macros en la lista Categorías, y seleccione "Botón personalizado" en la lista Comandos.
3. Desde la lista de Comandos, use el mouse para arrastrar el botón personalizado a la barra de herramientas.
4. Haga clic derecho en este botón y seleccione Asignar macro a Menú de contexto.
5. Ingrese un nombre para la macro en el campo Nombre de macro.

Asignar un área de un objeto gráfico para ejecutar una macro:

1. Crea un objeto gráfico.
2. Aplicar el menú contextual al objeto gráfico seleccionado.
3. Seleccione el comando Asignar macro en el menú contextual.
4. En el cuadro de diálogo Asignar macro a objeto que aparece, ingrese el nombre de la macro en el campo Nombre de macro y luego haga clic en Aceptar.

La edición de una macro se realiza mediante el editor VBA, para ello debes hacer lo siguiente:

1. Seleccione el comando Herramientas / Macros / Macros.
2. Seleccione el nombre de la macro que desea cambiar de la lista Nombre.
3. Haga clic en el botón Editar, se abrirá una ventana de Visual Basic, en la que podrá editar los comandos de la macro seleccionada escrita en Visual Basic.

Eliminar una macro:

1. En el menú Herramientas, seleccione Macros y luego seleccione Macros.
2. En la lista de macros del libro actual, debe seleccionar la macro que desea eliminar y hacer clic en el botón Eliminar.

Cambiar el nombre de una macro

Para cambiar el nombre de una macro, debe ingresar al modo de edición de macros y cambiar el título en el texto del programa. El nuevo nombre reemplazará automáticamente al anterior en las listas de macros y las teclas de método abreviado llamarán a la macro con el nuevo nombre.

Primero, un poco sobre terminología.

Macro- este es un código escrito en el lenguaje integrado de Excel Vba(Visual Basic para Aplicación). Las macros se pueden crear manualmente o grabarse automáticamente utilizando la llamada grabadora de macros.

grabador de macros es una herramienta en Excel que registra paso a paso todo lo que haces en Excel y lo convierte en código VBA. La grabadora de macros produce un código muy detallado (como veremos más adelante) que puedes editar más adelante si es necesario.

La macro grabada se puede ejecutar un número ilimitado de veces y Excel repetirá todos los pasos registrados. Esto significa que incluso si no sabes nada sobre VBA, puedes automatizar algunas tareas simplemente registrando tus pasos y luego reutilizándolos más tarde.

Ahora profundicemos y veamos cómo grabar una macro en Excel.

Mostrar la pestaña Desarrollador en la cinta del menú

Antes de grabar una macro, debe agregar una pestaña Desarrollador a la cinta del menú de Excel. Para hacer esto, siga estos pasos:

Como resultado, aparecerá la pestaña "Desarrollador" en la cinta del menú.

Grabar una macro en Excel

Ahora escribamos una macro muy simple que seleccione una celda e ingrese texto en ella, como "Excel".

Estos son los pasos para grabar una macro de este tipo:

¡Felicidades! Acaba de grabar su primera macro en Excel. Aunque la macro no hace nada útil, nos ayudará a comprender cómo funciona la grabadora de macros en Excel.

Ahora veamos el código que grabó la grabadora de macros. Siga estos pasos para abrir el editor de código:

Verá que una vez que haga clic en el botón Ejecutar, el texto "Excel" se insertará en la celda A2 y se seleccionará la celda A3. Esto sucede en milisegundos. Pero, de hecho, la macro realizó las acciones registradas de forma secuencial.

Nota. También puede ejecutar una macro usando el método abreviado de teclado Ctrl + Shift + N (mantenga presionadas las teclas Ctrl y Shift y luego presione la tecla N). Esta es la misma etiqueta que le asignamos a la macro cuando la grabamos.

¿Qué registra una macro?

Ahora vayamos al editor de código y veamos qué tenemos.

Estos son los pasos para abrir el editor VB en Excel:

1. En el grupo Código, haga clic en el botón Visual Basic.

También puedes usar la combinación de teclas Alt + F11 e ir al editor de código VBA.

• Barra de menús: Contiene comandos que puede utilizar mientras trabaja con el editor VB.
• Barra de herramientas- parece un panel acceso rapido en Excel. Puede agregar herramientas adicionales que utilice con frecuencia.
• Explorador de proyectos- Aquí Excel enumera todos los libros y todos los objetos de cada libro. Por ejemplo, si tenemos un libro con 3 hojas de trabajo, aparecerá en el Explorador de proyectos. Aquí hay varios objetos adicionales, como módulos, formularios de usuario y módulos de clase.
• Ventana de código- el código VBA se encuentra en esta ventana. Para cada objeto enumerado en el explorador de proyectos, hay una ventana de código, como hojas de trabajo, libros de trabajo, módulos, etc. En este tutorial, veremos que la macro grabada está en la ventana de código del módulo.
• Ventana de propiedades- Puedes ver las propiedades de cada objeto en esta ventana. A menudo uso esta ventana para etiquetar objetos o cambiar sus propiedades.
• Ventana inmediata(ventana de vista previa): en la etapa inicial no la necesitará. Es útil cuando desea probar pasos o durante la depuración. No se muestra de forma predeterminada y puede mostrarla haciendo clic en la pestaña Ver y seleccionando la opción Ventana inmediata.

Cuando grabamos la macro "EnterText", sucedieron las siguientes cosas en el editor VB:

• Se ha agregado un nuevo módulo.
• La macro se grabó con el nombre que especificamos: "InputText"
• Se ha agregado un nuevo procedimiento a la ventana de código.

Entonces, si hace doble clic en un módulo (en nuestro caso, el módulo 1), aparecerá una ventana de código como se muestra a continuación.

Aquí está el código grabado por la grabadora de macros:

SubTextEnter() " "TextEnter Macro " " Rango("A2").Seleccione ActiveCell.FormulaR1C1 = "Excel" Rango("A3").Seleccione End Sub

En VBA, cualquier línea que siga a " (el apóstrofe) no se ejecuta. Este es un comentario que tiene solo fines informativos. Si elimina las primeras cinco líneas de este código, la macro seguirá funcionando.

Ahora repasemos cada línea de código y describamos qué y por qué.

El código comienza con Sub seguido del nombre de la macro y paréntesis vacíos. Sub es la abreviatura de subrutina. Cada subrutina (también llamada procedimiento) en VBA comienza con Sub y termina Subtítulo final.

• Rango ("A2"). Seleccionar: esta línea selecciona la celda A2.
• ActiveCell.FormulaR1C1 = "Excel": esta línea ingresa el texto "Excel" en la celda activa. Como seleccionamos la celda A2 como primer paso, se convierte en nuestra celda activa.
• Rango("A3").Seleccione: seleccione la celda A3. Esto sucede cuando presionamos la tecla Enter después de ingresar texto que da como resultado seleccionar la celda A3.

Esperemos que tenga algunos conocimientos básicos sobre cómo grabar una macro en Excel.

Tenga en cuenta que el código grabado a través de una grabadora de macros generalmente no es un código eficiente ni optimizado. La grabadora de macros a menudo agrega pasos adicionales innecesarios. Pero esto no significa que no necesite utilizar una grabadora de macros. Para aquellos que recién están aprendiendo VBA, una grabadora de macros puede ser una excelente manera de analizar y comprender cómo funciona todo en VBA.

Grabación de macros absoluta y relativa

¿Ya conoces los enlaces absolutos y relativos en Excel? Si usa una referencia absoluta para grabar una macro, el código VBA siempre hará referencia a las mismas celdas que usó. Por ejemplo, si selecciona la celda A2 e ingresa el texto "Excel", cada vez, sin importar dónde se encuentre en la hoja de trabajo y sin importar qué celda esté seleccionada, su código ingresará el texto "Excel" en la celda A2.

Si utiliza la opción de referencia relativa para grabar una macro, VBA no se vinculará a una dirección de celda específica. En este caso, el programa se "moverá" con respecto a la celda activa. Por ejemplo, digamos que ya seleccionó la celda A1 y comienza a grabar su macro en modo de enlace relativo. Ahora seleccionas la celda A2, ingresas texto excel y presione Entrar. Ahora, si ejecuta esta macro, no volverá a la celda A2, sino que se moverá en relación con la celda activa. Por ejemplo, si se selecciona la celda B3, se moverá a B4, escribirá el texto "Excel" y luego se moverá a la celda K5.

Ahora grabemos la macro en modo de enlace relativo:

Se guardará la macro en modo de enlace relativo.

Ahora haz lo siguiente.

1. Seleccione cualquier celda (excepto A1).
2. Vaya a la pestaña "Desarrollador".
3. En el grupo Código, haga clic en el botón Macros.
4. En el cuadro de diálogo Macro, haga clic en la macro Referencias relativas guardada.
5. Haga clic en el botón "Ejecutar".

Como notarás, la macro no escribió el texto "Excel" en las celdas A2. Esto sucedió porque grabaste la macro en modo de referencia relativa. Por tanto, el cursor se mueve con respecto a la celda activa. Por ejemplo, si hace esto mientras la celda B3 está seleccionada, irá al texto de Excel: celda B4 y terminará seleccionando la celda B5.

Aquí está el código que grabó la grabadora de macros:

Tenga en cuenta que el código no hace referencia a las celdas B3 o B4. La macro usa Activecell para hacer referencia a la celda actual y el desplazamiento relativo a esa celda.

Ignore la parte Rango ("A1") del código. Este es uno de esos casos en los que la grabadora de macros agrega código innecesario que no sirve para nada y puede eliminarse. Sin él, el código funcionará bien.

¿Qué no puedes hacer con una grabadora de macros?

La grabadora de macros es excelente para usted en Excel y registra sus pasos exactos, pero puede no ser la opción correcta para usted cuando necesita hacer más que eso.

• No puede ejecutar código sin seleccionar un objeto. Por ejemplo, si desea que la macro vaya a la siguiente hoja de trabajo y resalte todas las celdas llenas en la columna A sin salir de la hoja de trabajo actual, la grabadora de macros no podrá hacerlo. En tales casos, deberá editar manualmente el código.
• No puede crear una función personalizada utilizando una grabadora de macros. Con VBA, puede crear funciones personalizadas que se pueden usar en una hoja de trabajo como funciones normales.
• No puede crear bucles utilizando la grabadora de macros. Pero puedes grabar una acción y agregar un bucle manualmente en el editor de código.
• No puede analizar las condiciones: puede verificar las condiciones en el código utilizando una grabadora de macros. Si escribe código VBA a mano, puede usar declaraciones IF Then Else para evaluar la condición y ejecutar el código si es verdadero (u otro código si es falso).

Extensión de archivos de Excel que contienen macros

Cuando graba una macro o escribe manualmente código VBA en Excel, debe guardar el archivo con una extensión de archivo habilitada para macros (.xlsm).

Antes de Excel 2007, un formato de archivo era suficiente: .xls. Pero desde 2007, .xlsx se introdujo como extensión de archivo estándar. Los archivos guardados como .xlsx no pueden contener una macro. Entonces, si tiene un archivo con extensión .xlsx y escribe/graba una macro y la guarda, le advertirá que debe guardarla en un formato habilitado para macros y le mostrará el siguiente cuadro de diálogo:

Si selecciona No, Excel guarda el archivo en un formato habilitado para macros. Pero si hace clic en Sí, Excel eliminará automáticamente todo el código de su libro y guardará el archivo como un libro en formato .xlsx. Entonces, si tiene una macro en su libro de trabajo, debe guardarla en formato .xlsm para guardar esa macro.

¿Qué es una macro? Macro es una secuencia guardada de acciones o un programa creado en VBA (Visual Basic for Application). Preguntas frecuentes¿Cómo escribir una macro en Excel? Justo.

Aquellos. Si necesitamos realizar las mismas acciones varias veces, podemos recordarlas y ejecutarlas con un solo botón. No te obligaré a aprender VBA ni te sugeriré muchas macros estándar en este artículo. En realidad, cualquiera puede crear/escribir una macro en Excel. Para ello, existe la característica más interesante e inusual de Excel: grabador de macros(registrando sus acciones en forma de código). Aquellos. puedes grabar tus acciones, como en video, y traducirlas a código (secuencia).

En resumen, si realizas las mismas acciones todos los días, vale la pena descubrir cómo automatizar este proceso. ¿Sigue leyendo para aprender cómo escribir una macro en Excel?

1.Permitir macros

Menú (botón redondo arriba a la izquierda) - Opciones de Excel— Centro de confianza — Configuración del centro de confianza — Configuración de macros. Marque la casilla de verificación "Habilitar todas las macros".

O haz lo mismo en la pestaña Desarrollador

2. Habilite el menú Desarrollador para trabajo rapido con macros

Menú (botón redondo en la parte superior izquierda) - Opciones de Excel - General - Mostrar pestaña Desarrollador.

Para que la macro creada se guarde en el libro de trabajo, debe guardar el archivo en .xlsm o .xlsb. Haga clic en Guardar como: libro de Excel habilitado para macros o libro de trabajo binario.

Entonces, ¿cómo escribir una macro en Excel?

Es sencillo. Vayamos a Excel. En la parte inferior de la página, debajo de las etiquetas de las hojas, hay un botón "Grabar macro".

Haga clic en el botón y se abrirá la ventana "Grabar macro". Nombramos la macro como queramos en el campo Nombre de macro:. Configuramos la combinación de teclas que usaremos más adelante para llamarlo (opcional).

Haga clic en Aceptar. La grabadora de macros ha comenzado a grabar.

Todo. Ahora hacemos las acciones que necesitamos recordar como macro. Por ejemplo, necesitamos eliminar una fila y colorear la celda A1 de amarillo.

Durante estas manipulaciones, en lugar del botón "Grabar macro" debajo de las hojas, se iluminará un cuadrado, al hacer clic en él se detendrá la ejecución de la macro.

¿Cómo lanzar lo sucedido? Haga clic en el botón, aparecerá la ventana Seleccionar macro, seleccione la macro que desee y luego haga clic en el botón Ejecutar.

¿Cómo puedo ver lo que pasó? Clic en el botón. Seleccione la macro que desee y haga clic en Editar. Se abrirá la ventana Grabar macro (ventana VBA).

El código de macro debería verse así.

Sub Ejemplo1() " Ejemplo1 Filas de macro ("2:2"). Seleccionar selección. Eliminar cambio: = xl Rango superior ("A1"). Seleccionar con selección. Interior. Patrón = xl Sólido .PatternColorIndex = xlAutomático.Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 Terminar con Sub final

' es un símbolo de comentario, es decir esta línea no está incluida en el código. Una macro debe tener un nombre (Sub Ejemplo1()) y una terminación (End Sub).

Puede leer cómo crear un botón para una macro.

¡Así que puedes grabar cualquiera de tus acciones para poder repetirlas tantas veces como quieras!

Puede leer cómo guardar una macro para todos los libros en su computadora.

Para hacer esto, use el Personal.

Puedes leer cómo ejecutar una macro por condición.

¡Buena suerte, estoy seguro de que esto te resultará muy útil!

Comparte nuestro artículo en tus redes sociales:

Con tablas u otros objetos de Office, a menudo surgen situaciones en las que es necesario realizar las mismas acciones. Esto no sólo requiere más tiempo, sino que también resulta muy molesto. Por suerte, existen macros para solucionar este problema.

El entorno VBA está diseñado para crear macros, pero no es necesario ser programador ni estudiar Visual Basic para aplicaciones para aprender a escribirlas. Para ello, existen herramientas especialmente desarrolladas que crean código VBA a sus órdenes, sin requerir conocimientos adicionales por su parte. Sin embargo, dominar este idioma no es tan difícil.

Las macros se crean en aplicaciones que utilizan herramientas de grabación. En este caso, a una determinada combinación de teclas se le asigna la secuencia de acciones deseada. Abra una aplicación de Office. Seleccione el fragmento que formateará.

Seleccione el elemento del menú “Herramientas” -> “Macro” -> “Iniciar grabación” (en Office 2007 – “Ver” -> “Macros” -> “Grabar macro”). En la ventana "Grabar macro" que aparece, establezca el nombre de la nueva macro; por defecto es "Macro1", pero es mejor darle un nombre, especialmente si habrá varias macros. Talla máxima Campos de nombre: 255 caracteres, no se permiten caracteres de punto ni espacio.

Decide la elección del botón o combinación de teclas que utilizará tu macro en el futuro. Intente elegir la opción más conveniente, especialmente si está destinada a un uso frecuente. Seleccione el elemento apropiado en el campo "Asignar macro": "botón" o "teclas".

Si selecciona "botón", se abrirá la ventana "Configuración rápida". Cuando seleccionas "teclas", solo necesitas ingresar la combinación en el teclado. Revise "Combinaciones actuales" para evitar repeticiones. Haga clic en "Asignar".

La macro creada en Word y PowerPoint será válida para todos los documentos en el futuro. Para que una macro de Excel esté disponible para todos los documentos, guárdela en el archivo personal.xls, que se ejecuta automáticamente al abrir la aplicación. Ejecute el comando “Ventana” -> “Pantalla” y seleccione la línea con el nombre del archivo personal.xls en la ventana que aparece.

Introduzca una descripción de la macro en el campo Descripción. Haga clic en Aceptar y volverá a su documento, pero ahora podrá ver un icono de grabación en el cursor del mouse. Formatee el texto con la secuencia de acciones que desea automatizar. Ten mucho cuidado y no hagas acciones innecesarias, ya que la macro las grabará todas, y esto afectará su tiempo de ejecución en el futuro.

Ejecute el comando “Herramientas” -> “Macro” -> “Detener grabación”. Creaste un objeto VBA sin escribir una sola línea de código. Sin embargo, si aún necesita realizar cambios manualmente, ingrese el objeto a través de la sección "Macros", el comando "Cambiar" o usando la combinación de teclas Alt+F8.

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. Son causados ​​por el hecho de que el código del programa VBA accede funcionalidad, que están presentes en nueva versión Producto, pero falta en el antiguo. Además, las desventajas incluyen la apertura excesivamente alta del código a cambios por parte de un extraño. Sin embargo oficina de microsoft, así como 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 a la 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”. Más:

• 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) debido al apóstrofe colocado al principio de la línea, que indica que sigue un comentario.

Ahora puedes escribir cualquier código y crear una nueva herramienta para ti en Excel(ver 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 es crear macros con el código requerido. 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. Habiendo descubierto cómo crear módulos para escribir macros, puede comenzar a considerar ejemplos específicos 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 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 se optimizará el código.

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 Muy a menudo surge la necesidad de tomar una decisión u otra 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 para 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.

Comienza la creación de dicha macro para Excel de 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 natural, parte entera, etc.
• Funciones financieras. Gracias a su disponibilidad y utilizando la programación 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: así, 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 (sociedades) 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.

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

Resolver un problema usando programación VBA

Usando las notaciones introducidas, obtenemos fórmulas para 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 una hoja de cálculo 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 como 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 tarea o mediante la pestaña "Ver" y el icono “Macros”.