Contenido
Incluso si no sabe cómo programar, hay muchos lugares (libros, sitios web, foros) donde puede encontrar código macro VBA listo para usar para una gran cantidad de tareas típicas en Excel. En mi experiencia, la mayoría de los usuarios tarde o temprano recopilan su colección personal de macros para automatizar procesos de rutina, ya sea traduciendo fórmulas en valores, mostrando sumas en palabras o sumando celdas por color. Y aquí surge el problema: el código de macro en Visual Basic debe almacenarse en algún lugar para poder usarlo más adelante en el trabajo.
La opción más fácil es guardar el código de macro directamente en el archivo de trabajo yendo al editor de Visual Basic usando el atajo de teclado otro+F11 y agregando un nuevo módulo vacío a través del menú Insertar – Módulo:
Sin embargo, hay varias desventajas con este método:
- Si hay muchos archivos de trabajo y se necesita una macro en todas partes, como una macro para convertir fórmulas en valores, tendrá que copiar el código en cada libro.
- no debe ser olvidado guardar archivo en formato habilitado para macros (xlsm) o en formato de libro binario (xlsb).
- Al abrir un archivo de este tipo protección de macros cada vez emitirá una advertencia que debe reconocerse (bueno, o deshabilitará la protección por completo, lo que no siempre es deseable).
Una solución más elegante sería crear su propio complemento (Complemento de Excel) – un archivo separado de un formato especial (xlam) que contiene todas sus macros "favoritas". Las ventajas de este enfoque:
- Será suficiente conectar el complemento una vez en Excel, y puede usar sus procedimientos y funciones de VBA en cualquier archivo de esta computadora. Por lo tanto, no es necesario volver a guardar sus archivos de trabajo en formatos xlsm y xlsb, porque. el código fuente no se almacenará en ellos, sino en el archivo del complemento.
- Protección tampoco te molestarán las macros. los complementos son, por definición, fuentes confiables.
- Puede hacer pestaña separada en la cinta de opciones de Excel con bonitos botones para ejecutar macros complementarias.
- El complemento es un archivo separado. Su Fácil de cargar de computadora a computadora, compartirlo con colegas o incluso venderlo 😉
Veamos paso a paso todo el proceso de creación de su propio complemento de Microsoft Excel.
Paso 1. Cree un archivo de complemento
Abra Microsoft Excel con un libro en blanco y guárdelo con cualquier nombre adecuado (por ejemplo MiExcelAddin) en formato de complemento con el comando Archivo – Guardar como o llaves F12, especificando el tipo de archivo Complemento de Excel:
Tenga en cuenta que, de forma predeterminada, Excel almacena los complementos en la carpeta C:UsersYour_nameAppDataRoamingMicrosoftAddIns, pero, en principio, puede especificar cualquier otra carpeta que le resulte conveniente.
Paso 2. Conectamos el complemento creado
Ahora el complemento que creamos en el último paso MiExcelAddin debe estar conectado a Excel. Para hacer esto, vaya al menú Archivo – Opciones – Complementos (Archivo — Opciones — Complementos), haga clic en el botón Sobre (Vamos) en la parte inferior de la ventana. En la ventana que se abre, haga clic en el botón Revisar (Navegar) y especifique la ubicación de nuestro archivo de complemento.
Si hiciste todo bien, entonces nuestro MiExcelAddin debería aparecer en la lista de complementos disponibles:
Paso 3. Agregue macros al complemento
Nuestro complemento está conectado a Excel y funciona correctamente, pero todavía no contiene una sola macro. Vamos a llenarlo. Para hacer esto, abra el editor de Visual Basic con el atajo de teclado otro+F11 o por botón Visual Basic de la pestaña. revelador (Desarrollador). Si pestañas revelador no visible, se puede mostrar a través de Archivo – Opciones – Configuración de cinta (Archivo — Opciones — Personalizar cinta).
Debería haber una ventana en la esquina superior izquierda del editor. Proyecto (si no está visible, enciéndalo a través del menú Ver — Explorador de proyectos):
Esta ventana muestra todos los libros abiertos y los complementos de Microsoft Excel en ejecución, incluido el nuestro. Proyecto VBA (MiExcelAddin.xlam) Selecciónelo con el mouse y agréguele un nuevo módulo a través del menú Insertar – Módulo. En este módulo, almacenaremos el código VBA de nuestras macros complementarias.
Puede escribir el código desde cero (si sabe cómo programar) o copiarlo desde algún lugar ya hecho (que es mucho más fácil). Para probar, ingresemos el código de una macro simple pero útil en el módulo vacío agregado:
Después de ingresar el código, no olvide hacer clic en el botón Guardar (disquete) en la esquina superior izquierda.
nuestra macro Fórmulas a valores, como puedes imaginar fácilmente, convierte fórmulas a valores en un rango preseleccionado. A veces, estas macros también se denominan procedimientos. Para ejecutarlo, debe seleccionar celdas con fórmulas y abrir un cuadro de diálogo especial Macros de la pestaña revelador (Desarrollador — Macros) o atajo de teclado otro+F8. Normalmente, esta ventana muestra las macros disponibles de todos los libros abiertos, pero las macros de complemento no están visibles aquí. A pesar de esto, podemos ingresar el nombre de nuestro procedimiento en el campo nombre de la macro (nombre de la macro)y luego haga clic en el botón Ejecutar (correr) – y nuestra macro funcionará:
| |
Aquí también puede asignar un atajo de teclado para iniciar rápidamente una macro: el botón es responsable de esto parámetros (Opciones) en la ventana anterior Macro:
Al asignar teclas, tenga en cuenta que distinguen entre mayúsculas y minúsculas y la distribución del teclado. Así que si asignas una combinación como Ctrl+Й, entonces, de hecho, en el futuro tendrás que asegurarte de tener el diseño activado y presionar adicionalmente Shiftpara obtener la letra mayúscula.
Para mayor comodidad, también podemos agregar un botón para nuestra macro a la barra de herramientas de acceso rápido en la esquina superior izquierda de la ventana. Para hacer esto, seleccione Archivo – Opciones – Barra de herramientas de acceso rápido (Archivo — Opciones — Personalizar barra de herramientas de acceso rápido), y luego en la lista desplegable en la parte superior de la ventana la opción Macros. Después de eso, nuestra macro Fórmulas a valores se puede colocar en el panel con el botón Añada (Añadir) y seleccione un icono para ello con el botón Cambios (Edit):
Paso 4. Agregue funciones al complemento
Pero macro-procedimientos, también hay macros de funciones o como se llaman UDF (Función definida por el usuario = función definida por el usuario). Vamos a crear un módulo separado en nuestro complemento (comando de menú Insertar – Módulo) y pegue allí el código de la siguiente función:
Es fácil ver que esta función es necesaria para extraer el IVA de la cantidad que incluye el IVA. No es el binomio de Newton, por supuesto, pero nos servirá como ejemplo para mostrar los principios básicos.
Tenga en cuenta que la sintaxis de una función es diferente de un procedimiento:
- se utiliza la construcción Función …. función final Sub... End Sub
- después del nombre de la función, sus argumentos se indican entre paréntesis
- en el cuerpo de la función se realizan los cálculos necesarios y luego el resultado se asigna a una variable con el nombre de la función
También tenga en cuenta que esta función no es necesaria y es imposible ejecutarla como el procedimiento de macro anterior a través del cuadro de diálogo. Macros y botón Ejecutar. Dicha función de macro debe usarse como una función de hoja de cálculo estándar (SUMA, SI, BUSCARV...), es decir, simplemente ingrese en cualquier celda, especificando el valor de la cantidad con el IVA como argumento:
… o ingrese a través del cuadro de diálogo estándar para insertar una función (botón fx en la barra de fórmulas), seleccionando una categoría Definido por el usuario (Usuario definido):
El único momento desagradable aquí es la ausencia de la descripción habitual de la función en la parte inferior de la ventana. Para agregarlo tendrás que hacer lo siguiente:
- Abra el Editor de Visual Basic con un atajo de teclado otro+F11
- Seleccione el complemento en el panel Proyecto y presione la tecla F2para abrir la ventana Explorador de objetos
- Seleccione su proyecto de complemento de la lista desplegable en la parte superior de la ventana
- Haga clic con el botón derecho en la función que aparece y seleccione el comando Propiedades.
- Introduzca una descripción de la función en la ventana Descripción
- Guarde el archivo del complemento y reiniciar Excel.
Después de reiniciar, la función debería mostrar la descripción que ingresamos:
Paso 5. Crea una pestaña de complementos en la interfaz
El toque final, aunque no obligatorio, pero agradable, será la creación de una pestaña separada con un botón para ejecutar nuestra macro, que aparecerá en la interfaz de Excel después de conectar nuestro complemento.
La información sobre las pestañas que se muestran de forma predeterminada se incluye en el libro y debe formatearse en un código XML especial. La forma más fácil de escribir y editar dicho código es con la ayuda de programas especiales: editores XML. Uno de los más convenientes (y gratuitos) es el programa de Maxim Novikov. Editor XML de la cinta.
El algoritmo para trabajar con él es el siguiente:
- Cierre todas las ventanas de Excel para que no haya conflicto de archivos cuando editemos el código XML del complemento.
- Inicie el programa Ribbon XML Editor y abra nuestro archivo MyExcelAddin.xlam en él
- Con botón fichas en la esquina superior izquierda, agregue el fragmento de código para la nueva pestaña:
- Tienes que poner comillas vacías id nuestra pestaña y grupo (cualquier identificador único), y en Label – los nombres de nuestra pestaña y un grupo de botones en ella:
- Con botón . en el panel izquierdo, agregue un código en blanco para el botón y agréguele etiquetas:
- etiqueta es el texto del boton
— imagenMso — este es el nombre condicional de la imagen en el botón. Usé un ícono de botón rojo llamado AnimationCustomAddExitDialog. Los nombres de todos los botones disponibles (¡y hay varios cientos de ellos!) se pueden encontrar en una gran cantidad de sitios en Internet si busca las palabras clave "imageMso". Para empezar, puedes ir aquí.
- enAcción – este es el nombre del procedimiento de devolución de llamada – una macro corta especial que ejecutará nuestra macro principal Fórmulas a valores. Puedes llamar a este procedimiento como quieras. Lo añadiremos un poco más tarde.
- Puede verificar la corrección de todo lo que ha hecho usando el botón con una marca de verificación verde en la parte superior de la barra de herramientas. En el mismo lugar, haga clic en el botón con un disquete para guardar todos los cambios.
- Cierre el editor XML de la cinta
- Abra Excel, vaya al editor de Visual Basic y agregue un procedimiento de devolución de llamada a nuestra macro KillFórmulaspara que ejecute nuestra macro principal para reemplazar fórmulas con valores.
- Guardamos los cambios y, volviendo a Excel, comprobamos el resultado:
Eso es todo: el complemento está listo para usar. Llénelo con sus propios procedimientos y funciones, agregue hermosos botones y será mucho más fácil usar macros en su trabajo.
- Qué son las macros, cómo usarlas en su trabajo, dónde obtener código de macro en Visual Basic.
- Cómo hacer una pantalla de bienvenida al abrir un libro de trabajo en Excel
- ¿Qué es un libro de macros personal y cómo usarlo?