Gerencie.com. Manual de macros.

1. PREAMBULO

¿Qué es una macro? Una Macro es una serie de instrucciones que son guardadas dentro de un archivo de Excel para poder ser ejecutadas cuando lo necesitemos. Es una herramienta que nos ayuda a automatizar procesos o aquellas tareas que hacemos repetidamente. Las macros se escriben en un lenguaje de programación llamado Visual Basic para Aplicaciones (VBA), y permite hacer o resolver los problemas de forma más rápida y sencilla. Este lenguaje permite acceder a prácticamente todas las funcionalidades de Excel y con ello también ampliar la funcionalidad del programa.

Utilidad de una macro La hoja de cálculos Excel es una poderosa herramienta en el tratamiento de datos y su análisis. Con el conocimiento de las macros se le abre al usuario un universo con el que puede construir plantillas y todo tipo de modelos, para diversos usos y aplicaciones. Dentro de esas aplicaciones se encuentran: 1. Automatización de tareas y procesos que involucran muchos pasos: supongamos que todos los días debemos trabajar en nuestro libro Excel en el cual debemos seleccionar un rango, centrarlo, cambiarle la fuente, poner la fuente en cursiva, aplicarle negrita y finalmente aplicarle bordes a toda la selección. Estas 6 acciones las hacemos manualmente y no parecen muchas, ¿pero qué tal si hay que hacer esto en repetidas ocasiones y todos los días? Una macro nos permite ejecutar los 6 pasos automáticamente, tan solo presionando un botón o una tecla. Las tareas ideales para automatizar son aquellas que se hacen de forma repetida e involucran muchos pasos, por ejemplo: imprimir informes, configurar la vista de la hoja, actualizar datos de tablas dinámicas, etc... 2. Creación de nuevas funciones “a la medida” (aparte de las que ya posee Excel): las funciones y fórmulas Excel son quizás la esencia de la hoja de cálculos (si no se pudieran utilizar funciones y fórmulas no habría cálculos). Excel trae incorporada unas 330 funciones estándar las cuales se pueden utilizar en forma aislada o en forma combinada (anidadas). Sin embargo suele suceder que justo la función que necesitamos no existe. Nuevamente, las macros vienen a nuestra salvación. Podemos programar funciones a medida que hagan exactamente lo que nosotros queremos. Y esas funciones se comportarán igual que las de Excel (aparecerán en el menú de funciones en la categoría que nosotros indiquemos, tendrán sus respectivos argumentos, etc.) 3. Creación de nuevos comandos, complementos y menús: Excel trae una gran cantidad de comandos y menús predefinidos que nos asisten para las operaciones más ISBN 978-958-46-6730-4

Gerencie.com. Manual de macros. habituales. Las macros nos permiten crear nuestros propios comandos y menús personalizados, e incorporarlos al Excel. La utilidad de los mismos depende tan solo de nuestras necesidades. Los complementos Excel también están creados con macros. Si ves en el menú Herramientas > Complementos verás una lista de los que tienes instalado en tu Excel. Los mismos suelen ser distribuidos de forma gratuita o también se compran. Su utilidad reside en agregar alguna funcionalidad extra al Excel. 4. Creación de aplicaciones completas “a la medida”: Excel es utilizado en diversos campos y por una gran cantidad de usuarios. Las macros te permitirán construir complejas y elegantes aplicaciones para cualquier uso que quieras darles. El límite solo es tu imaginación. Una aplicación Excel consiste en algo más que una simple plantilla con datos y fórmulas. Una aplicación Excel es un verdadero programa de software con una serie de características que lo hacen utilizable por cualquier usuario sin que el mismo tenga que entender la lógica "Exceliana" que hay por detrás.

Objetos, propiedades y métodos. A la hora de trabajar con macros en Excel, deben tenerse claros ciertos conceptos de lo que se llama programación orientada a objetos (OOP). No nos extenderemos demasiado sobre la OOP, pero si definiremos a continuación los conceptos de Objeto, Propiedades y Métodos. Hay un ejemplo muy práctico para comprender lo que son los Objetos, Propiedades y Métodos (OPM). Supongamos que tenemos una canasta con frutas. ¿Cuáles serían los OPM de la misma? Objetos: los objetos de la canasta de frutas serían las mismas frutas (manzanas, naranjas, peras…). Propiedades: las propiedades serían las características de las frutas (color, olor, sabor, textura…). Métodos: finalmente los métodos son las acciones que podríamos ejercer sobre las frutas (comprarlas, venderlas, comerlas, almacenarlas, limpiarlas, quitarles la piel,…). Pasemos ahora a ver un sencillo ejemplo de algunos OPM de Excel. Objetos: prácticamente cada cosa que vea en Excel es un objeto. Ejemplos de objetos son: un libro Excel, una hoja, un rango, una celda, un menú, un gráfico, una tabla dinámica, un cuadro de diálogo, las etiquetas de hojas, las columnas, las filas, etc. En fin, cada partecita de Excel es un objeto. Un objeto es algo que se puede ver e identificar con un nombre. Propiedades: las propiedades son las características de los objetos. Por ejemplo, para el objeto "celda" algunas de sus propiedades serían: alto, ancho, color, bloqueada o desbloqueada, vacía, con un número o con una fórmula, etc. Por ejemplo para el objeto ISBN 978-958-46-6730-4

Gerencie.com. Manual de macros. "hoja" algunas de sus propiedades serían: visible u oculta, con o sin líneas de división, con o sin barras de desplazamiento vertical y horizontal, etc. Métodos: un método es una acción que podemos realizar sobre el objeto o una de sus propiedades. Por ejemplo sobre el objeto "hoja" podemos: activar, mover, copiar o borrar. En la medida que adquieras mayores conocimientos sobre como programar macros irás manejando y conociendo cada vez más objetos, con sus propiedades y sus métodos. En definitiva, de eso se trata programar una macro: conocer el nombre del objeto, conocer el nombre de alguna propiedad del mismo que quieras modificar y también conocer el nombre de algún método que se desee aplicarle.

Tipos de Macros en Excel Las macros se pueden agrupar en dos categorías principalmente: 1. Macros de funciones 2. Macros de comandos

Macros de funciones Si ejecuta frecuentemente una tarea en Microsoft Excel, puede automatizarla mediante una macro. Una macro consiste en una serie de comandos y funciones que se almacenan en un módulo de Visual Basic y que puede ejecutarse siempre que sea necesario ejecutar la tarea. A continuación, se ejecuta la macro para que repita los comandos. Con el Editor de Visual Basic, se pueden modificar macros, copiar macros de un módulo en otro, copiar macros entre diferentes libros, cambiar de nombre a los módulos que almacenan las macros o cambiar de nombre a las macros.

Macros de comandos La creación de estas macro funciones consiste en poner una serie de ARGUMENTOS, en las cuales podemos ir poniendo los datos que lleva una función normal de Excel para Windows y al final le indicamos que operaciones hacer con estos ARGUMENTOS y de esta manera se optimiza el uso de varias fórmulas para llegar a un resultado. Es decir la creación de una función simplifica el uso de varias fórmulas, dejándolas como una sola. Por ejemplo: Dim Texto as String Sub Macro1() ' ActiveCell.Range("A1").Select ActiveCell.Value= Texto ActiveCell.Range("I1").Select Texto= ActiveCell.Value ISBN 978-958-46-6730-4

Gerencie.com. Manual de macros. . . . End Sub

Escribir macros desde el Editor de Visual Basi c. Para escribir macros manualmente desde el Editor de Visual Basic, debo seguir los siguientes pasos: 1. Selecciono la Hoja donde deseo ubicar la macro para que sea ejecutada 2. Si dentro de mi proyecto yo voy a utilizar campos variables, lo primero que debo hacer es definir, nombrar y declarar dichas variables. 3. Si dentro de mi proyecto yo voy a utilizar matrices u otro tipo de funciones que requieran alguna definición, debo declararlas y dimensionarlas también. 4. Posteriormente utilizando el comando Sub – End Sub, voy a declarar el nombre de la Macro. 5. Habiendo declarado la macro, se procede a ejecutar con comandos la acción que se quiere realizar , utilizando las funciones correspondientes 6. Se finaliza la macro con el comando End Sub Veamos un ejemplo para ejecutar la función de Copiar y Pegar en la misma Hoja (Worksheet) una información Una variable es simplemente un trozo de memoria que la función o procedimiento se reserva para guardar datos, la forma general de declarar una variable es DIM variable AS tipo.

Ejemplo: Nombre de la Macro : Macro 2 Nombre de la Variable: Texto Dim Texto as String Sub Macro2() ' ISBN 978-958-46-6730-4

(Significado)

Gerencie.com. Manual de macros. ActiveCell.Range("E1").Select ActiveCell.Value= Texto celda) Selection.Copy ActiveCell.Range("I1").Select ActiveSheet.Paste

(Ubícate en la celda E1 y selecciónala) (La variable Texto toma el valor de la (Copia el valor de la celda) (Ubícate en la celda I1 y selecciónala) (Pega ese valor)

End Sub

Ejecutar una macro. Una vez creada una macro, la podremos ejecutar las veces que queramos. Antes de dar la orden de ejecución de la macro, dependiendo del tipo de macro que sea, será necesario seleccionar o no las celdas que queramos queden afectadas por las acciones de la macro. Por ejemplo si hemos creado una macro que automáticamente da formato a las celdas seleccionadas, tendremos que seleccionar las celdas previamente antes de ejecutar la macro. Para ejecutar la macro debemos acceder al menú Ver Macros..., que se encuentra en el menú Macros de la pestaña Vista, y nos aparece el cuadro de diálogo Macro como el que vemos en la imagen donde tenemos una lista con las macros creadas, en este caso la MacroPrueba. Debemos seleccionar la macro deseada y pulsar sobre el botón Ejecutar o utilizar la tecla F5 desde el Editor de VBA. Se cerrará el cuadro y se ejecutará la macro.

ISBN 978-958-46-6730-4

Gerencie.com. Manual de macros. En cuanto al resto de botones: Cancelar: Cierra el cuadro de diálogo sin realizar ninguna acción. Paso a paso: Ejecuta la macroinstrucción por instrucción abriendo el editor de programación de Visual Basic o utilizando la tecla F8 Modificar: Abre el editor de programación de Visual Basic para modificar el código de la macro. Estos dos últimos botones son para los que sapan programar. Eliminar: Borra la macro. Opciones: Abre otro cuadro de diálogo donde podemos modificar la tecla de método abreviado (combinación de teclas que provoca la ejecución de la macro sin necesidad de utilizar el menú) y la descripción de la macro.

Pasos para aprender a programar una macro Antes de comenzar a programar macros es necesario que conozca la barra de herramientas VBA.

Barra de Herramientas VBA El primer paso para empezar a programar macros es familiarizarnos con las herramientas que nos ofrece Excel para programarlas. Excel posee una Barra de Herramientas de Macros o VBA. Puede acceder a la misma desde el menú Herramientas > Macros. También tiene la opción de dejar siempre visible dicha barra de herramientas. Puede hacer esto desde el menú Ver > Barras de Herramientas > Visual Basic. Verás como aparece una nueva barra de herramientas de macros. Puede ubicar esta barra de herramientas donde le sea más cómodo.

Editor VBA El Editor de Visual Basic es la aplicación que trae Excel desde donde se escriben y guardan las macros. Tienes 3formas de acceder al editor: A. Desde el menú Herramientas > Macros > Editor de Visual Basic ISBN 978-958-46-6730-4

Gerencie.com. Manual de macros. B. Desde el botón Editor de Visual Basic de la Barra de Herramientas Visual Basic. C. Desde el teclado (Teclas de Método Abreviado): ALT+F11 (el acceso a la Barra de Herramientas de la opción 1 y 2 los explicamos anteriormente) Cualquiera de las 3 formas abrirá el editor de Visual Basic (la número 3, presionar ALT + F11 es más rápida que abrir los menús desde el ratón). Una vez que se haya accedido al editor de Visual Basic se verá algo similar a la figura siguiente (no importa si no se ve estrictamente similar, eso dependerá de las ventanas que se tengan visibles y ocultas).

El editor contiene 3 ventanas principales: la Ventana Proyecto (parte izquierda superior), la Ventana de Código (parte derecha) y la Ventana Propiedades (parte izquierda inferior).

ISBN 978-958-46-6730-4

Gerencie.com. Manual de macros. La Ventana Proyecto - VBA Project: esta ventana muestra los libros Excel (xls) o los complementos (xla) abiertos. Usualmente verá nombres del tipo "VBAProject" y entre paréntesis el nombre del archivo o complemento Excel. Veamos el caso de VBAProject (Libro1). Simplemente significa que se tiene abierto un libro Excel llamado Libro 1. Luego cuelga una carpetas más: Hoja1 (Hoja1), ThisWorkbook y Modulo (no importa si no ve todos estos elementos ahora, luego se te enseñará cómo activarlos). Estas carpetas es donde habitan las macros. Haciendo doble clic en ellas se activará la ventana donde se escriben las macros. Ventana de Código: esta es el lugar donde escribiremos el código propiamente dicho de las macros. Como no hemos escrito ninguna macro todavía veremos la hoja en blanco. Recuerde bien estas dos ventanas, ya que las usaremos a continuación para escribir nuestra primera macro.

Como verá el Editor de VB tiene varias ventanas. Pero de momento solo nos interesan las dos ventanas que le indicamos anteriormente: la Ventana Proyecto – VBA Project y la Ventana de Código donde se escribe el código de las macros. La Ventana de Propiedades la dejaremos para más adelante porque de momento no nos interesa.

ISBN 978-958-46-6730-4

Gerencie.com. Manual de macros. Recuerde: presionando las teclas ALT+F11 puede ir desde Excel al Editor o desde el Editor al Excel de forma alterna (debes mantener presionada la tecla ALT y presionar F11 repetidas veces, verá como pasa de Excel al editor y viceversa). Si está en el editor y quiere regresar a la hoja Excel también puede utilizar el ícono de Excel que se encuentra en el menú superior del editor (primer ícono de la izquierda, con la X de Excel).

Objetivo de nuestra macro Se debe aclarar perfectamente y tener muy en cuenta primero que nada, cuál será el objetivo de nuestra macro y que queremos hacer exactamente. Al tener definido esto podemos tener claro cómo vamos a programar.

Escribir la macro Al tener en claro el objetivo de nuestra macro, procedemos a escribirla como tal, para ello debemos seguir los siguientes pasos: 1. 2. 3. 4. 5. 6.

Prepara el Editor de Visual Basic Crea un nuevo libro Excel y guárdalo con el nombre Libro 1. Abre el editor de Visual Basic con las teclas ALT+F11. Doble clic en VBAProject (Libro1) para ver las carpetas. Doble clic en Hoja1 (Hoja1) para empezar a escribir el código de la macro. Escribe la macro

Comprender la macro Una vez que se haya escrito la macro, debemos comprenderla para proceder a ejecutarla paso a paso para que pueda funcionar. Si la macro no funciona como lo deseas no te preocupes, solo necesitas tener paciencia y comprender las acciones de lo que se está haciendo.

Crear o modificar instrucciones para una macro. Una vez que la macro esta creada, y al comprenderla y ejecutarla paso a paso, lo que queda es modificar las instrucciones para que la macro funcione completamente. Para modificar o crear instrucciones se recomienda lo siguiente:

1. A pesar de que es la forma más larga de verificar las instrucciones, el Botón Grabar Macros es de mucha utilidad. ¿Cómo lo hacemos? Procedemos a ejercer la acción que nos está dando el error con el botón grabar macros y verificamos el código. Así podemos arreglarlo poco a poco.

ISBN 978-958-46-6730-4

Gerencie.com. Manual de macros. 2. Otra de las formas de modificar instrucciones para una macro, es a través de la ayuda de EXCEL. En el tutorial de ayuda, aparece correctamente como deben estar escritas las instrucciones, lo cual es de mucha ayuda a la hora de modificar una instrucción, porque pudiese ser eso. 3. Lo que debes hacer finalmente, es verificar y comprender como trabajan las funciones o comandos que estas utilizando. Para ello es recomendable que sigas el PASO A PASO en la ejecución de la macro, o, también el DEPURACION EJECUTAR HASTA EL CURSOR PASO A PASO: Permite ir corrigiendo y comprendiendo paso a paso lo que hace tu macro, para ingresar debes hacer lo siguiente:  

Ingresar a Depuración en la opción Paso a paso y con la tecla F8 ir revisando la macro línea a línea, hasta completarla. Esto permitirá corregir el lenguaje de la macro.

EJECUTAR HASTA EL CURSOR Permite “correr” la macro hasta cierto punto para continuar revisando y hacer más fácil el avance.  Si ya reviso paso a paso su macro, y hay una parte que ya te funciona y otra no, puedes “correr” la macro hasta ese punto, marcarlo con esta función, y continuar revisando hacia delante.

ISBN 978-958-46-6730-4

Gerencie.com. Manual de macros.

Algunos elementos comunes en los códigos de las macros. (Lenguaje VBA) Sub – End Sub. Comienzan con la palabra Sub y terminan con End Sub. Se identifican por un nombre (seguido de paréntesis)

ISBN 978-958-46-6730-4

Gerencie.com. Manual de macros.

Variables Una variable es simplemente un trozo de memoria que la función o procedimiento se reserva para guardar datos, la forma general de declarar una variable es

DIM variable AS tipo.     

Cada variable se identifica por su nombre (recomendable que sea significativo). El nombre de una variable puede contener casi cualquier letra o símbolo, pero debe comenzar siempre por una letra. No se deben usar espacios ni comas como parte de un nombre de variable, como tampoco los símbolos: #, $, %,& o !, ni operadores aritméticos. Por ejemplo: o edad, cantidad_asistentes, NivelLiquido, GASTOS No hay distinción entre mayúsculas y minúsculas, por lotanto los identificadores: balanceFinal, BalanceFinal yBALANCEFINAL hacen referencia a la misma variable.

Operadores aritméticos: Los argumentos de los operadores aritméticos son valores numéricos (números o variables numéricas) y generan como resultado un número. ISBN 978-958-46-6730-4

Gerencie.com. Manual de macros. 

Operadores: o Suma: + o Resta: o Multiplicación: * o División: / o División entera: División entera: \ o Elevación a potencia: ^

Operadores relacionales: Los operadores relacionales permiten comparar valores en expresiones. Generan como resultado un valor lógico (verdadero o falso).

Operadores relacionales:     

Mayor: > Menor: < Mayor o igual: >= Menor o igual: Propiedades de VBAProject … .. y luego hacer clic en la pestaña de Protección. Verá una ventana como la de la que se observa a la derecha: Allí puede proteger su Macro.

ISBN 978-958-46-6730-4

Gerencie.com. Manual de macros.

.

ISBN 978-958-46-6730-4