Excel Avanzado

Éste es un libro de proyectos con macros.

De modo que comencemos por ver qué es

1

Capítulo uno

una macro y cómo se crea. En este capítulo se tratan los elementos básicos del tema con unos cuantos ejemplos simples. Entre otras cosas, aprenderemos las primeras instrucciones de Visual Basic para Aplicaciones, el lenguaje de las macros.

Las primeras macros Cómo se crea una macro: el grabador La macro por dentro: el código Entendiendo el código Modificando el código Creando una macro en el Editor Una macro, dos operaciones Un caso más complejo

Las primeras macros

14 15 17 19 20 21 23 24

S E R V I C I O D E AT E N C I Ó N A L L E C T O R : l e c t o r e s @ t e c t i m e s . c o m

PROYECTOS CON MACROS EN EXCEL

Las primeras macros Una macro es un supercomando: un comando que, en un único paso, realiza lo que, normalmente, requeriría de muchas operaciones. Permite agilizar y automatizar aún más el trabajo con Excel. Vamos directamente a un ejemplo.

La planilla de la Figura 1 muestra un informe de sueldos y ventas en una empresa. Se desean imprimir copias impresas de esta planilla para todos los empleados de la lista. Pero, por razones confidenciales, en algunos casos se quiere que no aparezcan los sueldos.

Figura 1. Necesitamos una forma rápida y sencilla de ocultar o mostrar a voluntad la columna C de esta planilla.

En otras ocasiones especiales, la lista deberá mostrar todos los datos. De modo que, a la hora de imprimir, habrá que ocultar o mostrar, según el caso, la columna C. Por ejemplo, para ocultarla:

Ocultar una columna

PASO A PASO

Seleccionamos cualquier celda de la columna a ocultar. Por ejemplo, C1.

14

Cómo se crea una macro: el grabador

Las primeras macros

1

Tomamos las opciones Formato/Columna/Ocultar.

Entonces la planilla aparecerá como en la Figura 2.

Figura 2. La planilla de la Figura 1, luego de ocultar la columna C.

Cuando queramos recuperar la columna, usaremos las opciones Formato/ Columna/Mostrar.

Ocultar y mostrar la columna puede resultar un poco fastidioso. Es la clase de operación ideal para agilizar con una macro.

Cómo se crea una macro: el grabador Es muy grande la tentación de ponernos a hablar acá de sentencias, de lenguaje Visual Basic y de otros temas de programación. Pero lo dejamos para más adelante. Ahora vamos directamente a los hechos:

15

PROYECTOS CON MACROS EN EXCEL

Crear una macro

PASO A PASO

Tomamos las opciones Herramientas/Macro/Grabar nueva macro. Aparece el cuadro de la Figura 3.

Donde dice Nombre de la macro escribimos OcultarSueldos (o cualquier otro nombre adecuado).

Donde dice Método abreviado apretamos la combinación SHIFT+A. Estas dos teclas, junto con CTRL, servirán luego para ejecutar la macro.

Hacemos un clic en Aceptar.

Figura 3. En este cuadro se indican las opciones previas a la creación de la macro.

Con esto acabamos de encender el grabador de macros. Es como un duende que tomará nota de todo lo que hagamos a continuación y escribirá la macro equivalente a esas acciones. También aparece la barra de herramientas de la Figura 4. El primer botón de esta barra servirá para apagar el grabador cuando hayamos terminado.

Figura 4. La barra de herramientas Detener grabación.

El primer botón sirve para apagar el grabador.

16

• Seleccionamos cualquier celda de la columna C. • Tomamos las opciones Formato/Columna/Ocultar.

Realizando simplemente estas operaciones, el procedimiento estará finalizado. Por lo tanto, ya podemos apagar el grabador haciendo clic en el botón que aparece en la Figura 4 (al apoyar el mouse sobre él, se visualiza la leyenda Detener grabación).

Hecho esto entonces, ya está creada la macro y podemos hacerla funcionar: apretamos la combinación CTRL+SHIFT+A, que es la que indicamos cuando iniciamos la grabación. Si todo salió bien, la columna de sueldos habrá quedado oculta.

Ahora tendríamos que crear una segunda macro para volver a mostrarla.

Pero antes, vamos a ver qué es exactamente lo que acabamos de hacer.

La macro por dentro: el código Parece que la macro funciona. Vamos a verla por dentro:

Mostrar el código de una macro

PASO A PASO

Tomamos las opciones Herramientas/Macro/Macros. Aparece el cuadro de la Figura 5, con la “lista” de macros disponibles (lista que por ahora consta solamente de la macro OcultarSueldos).

Hacemos un clic sobre la macro OcultarSueldos.

Hacemos un clic en Modificar.

17

Las primeras macros

Con el grabador encendido, procedemos a ocultar la columna, tal como contamos antes:

1

La macro por dentro: el código

PROYECTOS CON MACROS EN EXCEL

Figura 5. El cuadro con la lista de macros creadas hasta el momento.

Se abrirá una ventana con un nuevo programa: el Editor de Visual Basic (Figura 6). Hasta ahora no lo habíamos dicho, pero las macros son programas y los programas se escriben en algún lenguaje. En el caso de las macros ese lenguaje se llama Visual Basic.

Figura 6. El Editor de Visual Basic. El panel de la derecha muestra el código de la macro OcultarSueldos.

18

Entendiendo el código El código de una macro

GUÍA VISUAL 1

La macro comienza con la instrucción especial Sub, seguida del nombre elegido.

Las líneas precedidas por un apóstrofo son comentarios para el programador y no forman parte de la macro.

Esta instrucción corresponde a la operación de seleccionar la celda C1 (que fue, justamente, lo primero que hicimos luego de encender el grabador).

Esta instrucción oculta las columnas correspondientes al rango seleccionado.

La macro termina con la instrucción End Sub. Dos de las instrucciones de la macro merecen una explicación detallada. La primera es: Range(“C1”).Select

19

Las primeras macros

Cada línea de la macro es una instrucción que, traducida a Visual Basic, equivale a un comando u operación de Excel. A ver si podemos entenderlo.

1

La macro por dentro: el código

PROYECTOS CON MACROS EN EXCEL

Esta instrucción tiene dos partes: el objeto y la acción, separadas por un punto. Concretamente, el objeto es el rango C1 y la acción es seleccionar ese rango. Esta instrucción, entonces, corresponde a la operación de seleccionar el rango C1.

La siguiente instrucción también tiene partes separadas por puntos, pero es un poco más compleja: Selection.EntireColumn.Hidden = True

Significa que, de la dirección actualmente seleccionada, se toma toda la columna y se le da la propiedad oculta (en inglés, hidden). Esta instrucción podríamos considerarla de la forma objeto.propiedad. El objeto es la columna a la cual pertenece la selección actual. La propiedad es su carácter de oculto. Las estructuras objeto.acción y objeto.propiedad aparecen mucho en Visual Basic.

Aunque éste no es un manual de Visual Basic, con un poco de ingenio y lógica podemos comenzar a programar. Vamos a hacerlo.

Modificando el código Nuestra macro tiene dos instrucciones: • Seleccionar un rango.

• Ocultar las columnas correspondientes al rango seleccionado.

Pero según lo que acabamos de decir acerca de objetos, propiedades y acciones, estas dos instrucciones se podrían reducir a una: ocultar las columnas correspondientes al rango C1. Traducido a Visual Basic sería: Range(“C1”).EntireColumn.Hidden = True

Y, efectivamente, es así. Podemos meternos en el editor, trabajando como si fuera un procesador de textos, y modificar el código reemplazando las dos instrucciones en la forma que acabamos de mostrar para que quede como en la Figura 7. Volviendo a Excel, la macro deberá funcionar tan bien como antes.

20

Las primeras macros

1

Creando una macro en el Editor

Figura 7. La versi贸n modificada de la macro OcultarSueldos.

Si todo esto es cierto, al oprimir la combinaci贸n CTRL+SHIFT+A se ocultar谩 la columna C.

Creando una macro en el Editor Vamos a crear la segunda macro: una que vuelva a mostrar la columna de los sueldos. Pero no vamos a usar el grabador. En la ventana del Editor escribimos el c贸digo que aparece en la Figura 8.

Figura 8. Esta macro debe hacer visible la columna C en la planilla de la Figura 2.

21

PROYECTOS CON MACROS EN EXCEL

¿Se entiende el código? Esta nueva macro es como OcultarSueldos, pero le pusimos otro nombre y cambiamos el True (en inglés, verdadero) por False (falso).

Esta instrucción le da el carácter “falso” a la propiedad “oculta” del objeto “columnas correspondientes al rango C1”. Se tarda más en decirlo que en entenderlo. Para hacer funcionar esta segunda macro tenemos que asignarle una combinación de teclas:

Asignar un teclazo a una macro

PASO A PASO

Tomamos las opciones Herramientas/Macro/Macros. Vuelve a aparecer el cuadro de la Figura 9, con la lista de macros disponibles. Primer Eureka: en la lista aparece la macro MostrarSueldos.

Hacemos un clic sobre la macro MostrarSueldos.

Hacemos un clic en Opciones. Aparece el cuadro de la Figura 10.

Donde dice Tecla de método abreviado indicamos SHIFT+S (o cualquier otra combinación que nos parezca adecuada).

Hacemos un clic en Aceptar.

Hacemos un clic en Cancelar (ya no tenemos nada que hacer acá).

Figura 9. La lista de macros disponibles incluye ahora a MostrarSueldos.

22

Las primeras macros

1

Una macro, dos operaciones

Figura 10. Acá asignamos una combinación de teclas para la ejecución de la macro.

Veamos si esto funciona: apretamos la combinación CTRL+SHIFT+S. Segundo Eureka: reaparece la columna C. Y si ejecutamos sucesivamente las dos macros (con CTRL+SHIFT+S y CTRL+SHIFT+A) veremos cómo la columna de los sueldos aparece y desaparece. Y aún no han visto nada.

Una macro, dos operaciones Todavía podemos ir un paso más allá. Sería mucho más cómodo trabajar con una única macro. Ocultaría la columna C si estuviera visible, y la haría visible si estuviera oculta. Esta macro debería ser como la de la Figura 11.

Figura 11. Esta macro reemplaza a las dos que creamos antes. Sirve tanto para ocultar la columna C como para hacerla visible.

23

PROYECTOS CON MACROS EN EXCEL

Esta nueva macro usa una instrucción nueva: la condicional If, que en inglés quiere decir si. Las instrucciones que aparecen en el código de la Figura 11 podrían traducirse así: • Si la columna correspondiente al rango C1 está a la vista, entonces (en inglés, then)...

• ...ocultar esa columna.

• Si no (en inglés, else), es decir, si está oculta...

• ...mostrarla.

• Fin (end) de la condicional.

Las instrucciones de la forma If... Then... Else... se llaman condicionales porque ejecutan una u otra operación según el cumplimiento o no de una condición. Este tipo de estructura aparece en muchos lenguajes y Visual Basic no es la excepción.

Un caso más complejo Del ejemplo anterior aprendimos que las instrucciones que forman parte de una macro pueden ser de dos tipos: • Las que equivalen a los comandos de Excel. Por ejemplo, seleccionar un rango u ocultar una columna.

• Instrucciones especiales, para controlar la operación de la macro. Por ejemplo, la instrucción condicional If... Then... Else...

Las instrucciones del segundo tipo son más complejas, porque no podemos obtenerlas automáticamente del grabador, pero nos permiten crear macros más poderosas.

Veremos ahora un ejemplo que nos permitirá comprender este concepto con mayor claridad. Sea el recibo de la Figura 12. Queremos imprimir cien copias de este recibo, numeradas correlativamente de uno en uno. El número de recibo debe aparecer en la celda F4.

24

Las primeras macros

1

Un caso más complejo

Figura 12. Tenemos que imprimir cien copias de este recibo, numeradas correlativamente.

Esto puede hacerse con la macro cuyo código aparece en la Figura 13.

Figura 13. Esta macro imprime las cien copias del recibo anterior.

En esta macro aparecen varias instrucciones nuevas. Pero no es difícil deducir qué es lo que hace cada una. Por ejemplo, la instrucción ActiveWindow.SelectedSheets.PrintOut imprime la planilla, es decir, el recibo. En la misma instrucción se dice que se debe imprimir una copia por vez.

La impresión debe repetirse cien veces, cambiando el número de recibo. Para eso necesitamos un ciclo: una forma de indicar que una instrucción se ejecuta varias veces, cíclicamente. En la macro usamos uno de los ciclos más simples: For... Next.

25

PROYECTOS CON MACROS EN EXCEL

Las instrucciones contenidas en el ciclo se ejecutan mientras una variable auxiliar cambia su valor desde un valor inicial hasta alcanzar un máximo. En la macro de la Figura 13, la variable auxiliar la llamamos i y su valor comienza en 1 y termina en 100. Podemos imaginar que i es como un contador que controla cuántas veces se ejecuta una tarea.

Por otra parte, la instrucción Range(“F4”).Value = i escribe el valor de la variable i en la celda F4 de la planilla. Aquí aparece otra vez la estructura objeto.propiedad: del rango F4 tomamos su propiedad valor (en inglés, value).

Es decir que la variable i sirve tanto para controlar que la impresión se realice cien veces como para numerar cada copia.

Esta macro la vamos a escribir sin ayuda del grabador. Necesitamos crear primero una ventana de texto en el Editor de Visual Basic. Comenzando desde el principio:

Crear una macro en el Editor

PASO A PASO

Tomamos las opciones Herramientas/Macro/Editor de Visual Basic. Esto abre el Editor, que es un programa aparte.

Una vez dentro del Editor, tomamos las opciones Insertar/Módulo. Se abre una ventana de texto donde debemos escribir el código de la Figura 13.

Cerramos el Editor y volvemos a Excel.

A esta macro le podemos asignar un teclazo tal como hicimos en los ejemplos anteriores. Como al ejecutarla se imprimirán cien copias de la planilla de la Figura 12, conviene reemplazar el número 100 del código por otro más razonable (por ejemplo, 5) para comprobar que la macro funciona correctamente.

En los próximos capítulos veremos más instrucciones especiales con las cuales crear macros más útiles, versátiles, interesantes y poderosas.

26