Macros con Excel 2007 - Gail Perry by terranoide

.BDSPT DPO Â&#x192; &YDFM

.BDSPT DPO Â&#x192; &YDFM Gail Perry

TraducciÃ³n:

Eloy Pineda Rojas Traductor profesional

.Ã&#x17D;9*$0 t #0(05Ã&#x2022; t #6&/04 "*3&4 t $"3"$"4 t (6"5&."-" t ."%3*% t /6&7" :03, 4"/ +6"/ t 4"/5*"(0 t 4Ã&#x201C;0 1"6-0 t "6$,-"/% t -0/%3&4 t .*-Ã&#x2022;/ t .0/53&"/6&7" %&-)* t 4"/ '3"/$*4$0 t 4*/("163 t 45 -06*4 t 4*%/&: t 5030/50

Director editorial: 'FSOBOEP $BTUFMMBOPT 3PESÂHVF[ Editor: .JHVFM Ã&#x2022;OHFM -VOB 1PODF Supervisor de producciÃ³n: ;FGFSJOP (BSDÂB (BSDÂB MACROS CON EXCELÂ® 2007 1SPIJCJEB MB SFQSPEVDDJÂ§O UPUBM P QBSDJBM EF FTUB PCSB QPS DVBMRVJFS NFEJP TJO MB BVUPSJ[BDJÂ§O FTDSJUB EFM FEJUPS

%&3&$)04 3&4&37"%04 Ã&#x153; SFTQFDUP B MB QSJNFSB FEJDJÂ§O FO FTQBÂ©PM QPS .D(3"8 )*-- */5&3".&3*$"/" &%*503&4 4 " %& $ 7 A Subsidiary of The McGraw-Hill Companies, Inc. $PSQPSBUJWP 1VOUB 4BOUB 'F 1SPMPOHBDJÂ§O 1BTFP EF MB 3FGPSNB 5PSSF " 1JTP $PMPOJB %FTBSSPMMP 4BOUB 'F %FMFHBDJÂ§O Ã&#x2022;MWBSP 0CSFHÂ§O $ 1 .Â±YJDP % ' .JFNCSP EF MB $Â¸NBSB /BDJPOBM EF MB *OEVTUSJB &EJUPSJBM .FYJDBOB 3FH /Â¡N ISBN: 978-607-15-0237-7 5SBOTMBUFE GSPN UIF TU &OHMJTI FEJUJPO PG Excel 2007 Macros Made Easy #Z (BJM 1FSSZ $PQZSJHIU Ã&#x153; CZ 5IF .D(SBX )JMM $PNQBOJFT "MM SJHIUT SFTFSWFE *4#/

*NQSFTP FO .Â±YJDP

Printed in Mexico

"DFSDB EF MB BVUPSB Gail Perry es contadora pública, periodista financiera y autora de más de 20 libros de software financiero, impuestos y finanzas personales. Es editora de AccountingWEB, editora colaborador de la revista SMB Finance, e instructora.

$POUFOJEP Capítulo 1

Grabación de macros

Capítulo 2

Edición de macros

Capítulo 3

Creación de macros en Visual Basic

Capítulo 4

Almacenamiento de macros

Capítulo 5

Comprensión de los comandos de las macros

Capítulo 6

Uso de subrutinas y creación de funciones de Visual Basic

Capítulo 7

Creación de macros interactivas

Capítulo 8

Uso de macros para formato de celdas

Capítulo 9

Uso de variables en macros

111

Capítulo 10

Creación de rutinas If/Then/Else

123

Capítulo 11

Exploración de bucles

133

Capítulo 12

Adición de controles a sus hojas de cálculo

145

Índice

155

*OUSPEVDDJÂˇO Bienvenido a Macros con Excel 2007. Este libro fue escrito para quienes usan Excel todos los dĂas, como usted, que quieren liberarse de la pesadilla de repetir las mismas tareas una y otra vez. En este libro aprenderĂĄ accesos directos poderosos y mĂŠtodos que ahorran tiempo para agilizar y recordar las tareas. A diferencia de otros libros de esta serie, se concentra en enseĂąarle sĂłlo las cosas que necesita saber para empezar a usar macros de hoja de cĂĄlculo de manera rĂĄpida y fĂĄcil. No necesita tener un tĂtulo de programador ni reservar horas y horas para estudiar con el ďŹ n de comprender las lecciones de este pequeĂąo libro y ponerlas a trabajar para usted de inmediato. En realidad, debe tener la capacidad de crear su primera macro en unos cuantos minutos. En este libro descubrirĂĄ cĂłmo enseĂąar a Excel a hacer tareas repetitivas que ocupan tiempo y energĂa valiosos que podrĂa dedicar a esfuerzos mĂĄs productivos. AprenderĂĄ a proteger la integridad de sus datos al crear macros que eviten que se ingrese informaciĂłn incorrecta en sus hojas de cĂĄlculo. Con el uso de macros de Excel, puede incluso interactuar con otros usuarios y pedirles datos especĂďŹ cos de entrada. AsĂ, ÂĄestarĂĄ listo para dar el salto! Es hora de hacer que las macros de Excel lo liberen de la pesadilla y hagan mĂĄs suave y fĂĄcil su vida en el trabajo.

Elementos usados en este libro Leer pĂĄgina tras pĂĄgina de un texto corrido, sobre todo si se trata de un libro prĂĄctico, puede ser mortalmente aburrido. TratĂŠ de luchar contra eso al insertar gran cantidad de ilustraciones que le ayuden a visualizar lo que necesita hacer en su computadora, ademĂĄs de incorporar interĂŠs visual. TambiĂŠn agreguĂŠ varios elementos especiales en este libro y otros de esta serie, que destacan la informaciĂłn importante adicional y le ayudan a encontrar rĂĄpidamente lo que necesita cuando toma como referencia este libro. He aquĂ una descripciĂłn de ellos: Â&#x2014;

Memo Notas marginales, consejos y recordatorios que aclaran un tema, proporcionan consejos Ăştiles o lo previenen sobre posibles peligros

Â&#x2014;

La manera fĂĄcil Grupos prĂĄcticos de sugerencias que proporcionan listas de mĂŠtodos abreviados o de trucos que facilitan aĂşn mĂĄs la realizaciĂłn de tareas.

Â&#x2014;

VĂnculos Apuntadores a recursos externos Ăştiles para descargas de software, noticias e informaciĂłn, ademĂĄs de recursos tĂŠcnicos.

A (SBCBDJPO EF NBDSPT

La manera más fácil de crear una macro en Excel es mediante la grabación de sus pasos con la grabadora de macros de Excel. Luego, la próxima ocasión que quiera dar estos pasos, simplemente encienda la macro grabada previamente y siéntese con una buena taza de té mientras observa cómo la macro hace el trabajo por usted. Por ejemplo, si quisiera automatizar una tarea en Excel, debe grabarse a sí mismo realizando la tarea al encender una pequeña grabadora de cinta virtual, que grabará cada paso a medida que lo ingresa. Cuando haya terminado de ingresar sus pasos, apague la grabadora y ¡listo! ¡Tiene una macro! Bueno, casi. Hay algunas tareas técnicas que deben realizarse, como asignar nombre a la macro y decidir dónde almacenarla, pero para todos los ﬁnes prácticos la grabación de macros es muy simple.

Macros con Excel 2007

He aquí los pasos básicos para grabar una macro: 1. Encienda la grabadora de macros. 2. Ingrese un nombre para la macro. 3. Indique una tecla de método abreviado para la macro (opcional). 4. Indique dónde quiere que se almacene la macro. 5. Ingrese una descripción para la macro (opcional). 6. Inicie la grabadora. 7. Ingrese sus pasos. 8. Detenga la grabadora. Una vez que haya grabado una macro, puede ejecutarla de una de dos maneras:

Si asignó su macro a una combinación de teclas, puede oprimirla en cualquier momento para ejecutar la macro.

Ya sea que haya asignado o no su macro a una combinación de teclas, puede acceder a ésta desde la cinta de opciones. Despliegue una lista de macros, elija la macro que quiere ejecutar y luego elija Ejecutar.

Recorreremos cada uno de estos pasos de grabación y ejecución de macros y aprenderemos lo que signiﬁcan, mientras grabamos algunas macros reales. Luego, más adelante en el capítulo, aprenderemos cómo probar sus macros, junto con métodos alternos para guardarlas y recuperarlas.

Despliegue de la cinta de opciones Programador La creación de macros es mucho más fácil con el uso de la cinta de opciones Programador, una barra de herramientas especialmente diseñada para ayudarle con la creación, ejecución y revisión de sus macros. Puede acceder a algunos de los comandos de la macro desde la barra de herramientas Ver, pero sus experiencias con macros serán mucho más fáciles si tiene la cinta de opciones Programador en la pantalla. Siga estos pasos para agregar la cinta de opciones Programador a su pantalla de Excel: 1. Haga clic en el Botón de Ofﬁce, en la esquina superior izquierda de la pantalla de Excel. Aparece el menú Ofﬁce.

Capítulo 1

Grabación de macros

Opciones más frecuentes Casilla de veriﬁcación de la ﬁcha Programador

Figura 1-1

Despliegue de la cinta de opciones Programador

2. Haga clic en el botón Opciones de Excel, de la ventana de menús. 3. Asegúrese de que la opción Más frecuentes (véase la figura 1-1) está desplegada en el lado izquierdo de la ventana Opciones de Excel. 4. Haga clic para marcar la casilla de verificación Mostrar ficha Programador en la cinta de opciones. 5. Haga clic en Aceptar. La cinta de opciones Programador está disponible ahora al hacer clic en la ficha Programador, en la parte superior de su pantalla de Excel. Observe que la cinta de opciones Programador es ahora una parte permanente de sus opciones en la cinta. En caso de que decida ya no tener acceso a ella, puede regresar a la ventana Opciones de Excel y desmarcar la casilla de verificación correspondiente a la cinta de opciones Programador.

Creación de una macro La primera macro que vamos a crear es una que reemplaza su nombre en una celda. Por lo general, cuando grabamos macros tratamos de grabar una tarea que esperamos usar una y

Macros con Excel 2007

otra vez. De modo que si colocar su nombre en una celda es algo que haría repetidamente, esta macro funcionará bien al ahorrarle escribir. Si utiliza con frecuencia un nombre de una empresa en sus hojas de cálculo, o algún otro texto, puede ingresarlo en la macro en lugar de su propio nombre. He aquí una frase que escuchará una y otra vez en este libro: dé todos los pasos. Esto significa que cada paso es importante, de modo que cuando analicemos la creación y grabación de macros no vamos a omitir ningún paso. Aunque éstos parezcan obvios (como colocar un espacio entre su nombre y su apellido), vamos a mencionar cada uno de los pasos. De esa manera, nada se omitirá y sus macros serán perfectas. Así, he aquí los pasos que habrán de seguirse (todos los pasos) para nuestra primera macro: 1. Con su hoja de cálculo de Excel abierta, coloque el puntero en la celda A1. De esta manera, todos estaremos exactamente en el mismo lugar cuando empecemos a grabar la macro. 2. Haga clic en la ficha Programador para abrir la cinta de opciones correspondiente. 3. Haga clic en la opción Grabar macro de la cinta de opciones. De manera alterna, podría hacer clic en el botón Macro que aparece en la parte inferior izquierda de su pantalla de Excel. 4. En el cuadro de diálogo Grabar macro, como se muestra en la figura 1-2, en el campo Nombre de la macro, ingrese NOMBRE1 como nombre de la macro. No debe haber espacios en el nombre de una macro. 5. En el campo Método abreviado, mantenga oprimida la tecla MAYÚS y escriba n para que CTRL+MAYÚS+N sea el método abreviado de teclado para esta macro. (Nota: No usé CTRL+N para mi macro, porque ese método abreviado ya existe en

Nombre de la macro Tecla de método abreviado Ubicación de almacenamiento Descripción adicional

Figura 1-2

Grabación de una macro

Capítulo 1

Grabación de macros

Excel como el comando para cambiar un texto a negritas. Si oprime CTRL+N para el método abreviado de la macro, no recibirá una advertencia de que ya tiene otro uso; en cambio, el uso de la macro se superpondría al del comando original CTRL+N.) 6. En el campo Guardar macro en, elija “Libro de macros personal”. Se trata del libro universal de macros que está accesible para todas las hojas de cálculo de Excel. Las otras opciones son “Libro nuevo” y “Este libro”. Si elige alguna de esas opciones, la macro se asocia sólo con uno de los libros y no está accesible para los demás. 7. Ingrese una descripción adicional para esta macro. Tiene la opción de ingresar algún texto que describa lo que hace la macro o cuándo habrá de usarse. 8. Haga clic en Aceptar. Ahora la grabadora de macros se está ejecutando y grabará todos sus tecleos. 9. De regreso en la hoja de cálculo, ingrese en la celda A1 su nombre como desea grabarlo en esta macro, dejando un espacio entre cada palabra. Yo he ingresado “Gail Perry” en la celda A1. 10. Oprima ENTER cuando haya terminado de ingresar su nombre. El puntero se mueve a la celda que se encuentra debajo de donde ingresó su nombre (en este caso, A2). 11. Haga clic en la opción Detener grabación, en la cinta de opciones Programador. De manera alterna, puede hacer clic en el botón Macro, en la parte inferior izquierda de su pantalla de Excel, como se muestra en la ﬁgura 1-3. Ahora la macro está grabada. Detenga o inicie la grabadora de macros con este botón Figura 1-3

El botón Macro

Pruebe la macro NOMBRE1 Después de grabar su macro, querrá darle una prueba de manejo para asegurarse de que su macro hace lo previsto. Siga estos pasos para probar su macro NOMBRE1: 1. Abra una nueva hoja de cálculo de Excel y coloque su puntero en la celda A1. 2. Oprima CTRL+MAYÚS+N. 3. Ahora su nombre debe aparecer en la celda A1 y el puntero tiene que moverse a la celda A2. ¡La macro es correcta!

Macros con Excel 2007

¿Qué sucede si coloca su puntero en una celda diferente de A1 y oprime el método abreviado de teclado para el comando de la macro? Intentémoslo. Haga clic en cualquier otra celda (diferente de la celda A1) en la hoja de cálculo, y luego oprima CTRL+MAYÚS+N. ¿Qué sucede? Su nombre aparece en la celda donde residía el puntero, pero ahora éste pasa a la celda A2. En realidad, no importa en qué celda empiece cuando ejecute la macro NOMBRE1. Su nombre se ingresará en la celda donde inicie y su puntero regresará a la celda A2. ¿Por qué sucede esto? Si piensa de nuevo en la creación de la macro NOMBRE1, recordará que grabó la introducción de su nombre en la celda A1, habiendo colocado ya el puntero en esa celda antes de empezar el proceso de grabación. Luego oprimió ENTER y el puntero se movió a la celda A2. Esta macro grabó sólo eso: el proceso de ingresar su nombre en la celda ocupada en ese momento, y luego el proceso de su puntero moviéndose a la celda A2. Así, no importa dónde reproduzca esa macro, su puntero regresará a la celda A2 después de ingresar su nombre.

Una perspectiva diferente de la macro Aﬁnemos un poco la macro NOMBRE1. Esta vez, digamos que queremos asegurar que su nombre se ingrese siempre en la celda A1 cuando ejecuta su macro. Sólo necesitamos hacer un pequeño cambio en la macro para realizar esta tarea. En lugar de colocar el puntero en la celda A1 antes de empezar la grabación, para nuestra segunda macro, encenderemos la grabadora de macros antes de mover el puntero. De esa manera, el movimiento del puntero a la celda A1 se volverá parte de la macro. He aquí los pasos a seguir para crear una macro que siempre coloque su nombre en la celda A1: 1. Haga clic en la opción Grabar macro de la cinta de opciones. 2. En el campo Nombre de la macro, ingrese NOMBRE2 como nombre de esta macro. 3. Dejemos en blanco el campo Método abreviado para esta macro. 4. En el campo Guardar macro en, elija “Libro de macros personal”. 5. Si quiere, ingrese una descripción adicional para esta macro. 6. Haga clic en Aceptar. Ahora la grabadora de macros está registrando sus pasos. 7. Oprima CTRL+INICIO para enviar su puntero a la celda A1. 8. Ingrese su nombre como desea grabarlo en esta macro.

Capítulo 1

Grabación de macros

9. Oprima ENTER cuando haya terminado de ingresar su nombre. El puntero se mueve a la celda A2. 10. Haga clic en la opción Detener grabación en la cinta de opciones Programador.

Pruebe la macro NOMBRE2 Abra una nueva hoja de cálculo. Probaremos esta nueva macro para ver si obtenemos los resultados deseados. Haga clic en cualquier lugar de la hoja de cálculo (pruebe haciendo clic en una celda diferente de A1). Debido a que no asignamos un método abreviado de teclado para esta macro, necesitamos acceder a ésta de manera diferente. Siga estos pasos para ejecutar la macro NOMBRE2: 1. Haga clic en el botón Macros de la cinta de opciones Programador. Aparece la ventana Macro. 2. Haga clic en la macro NOMBRE2 (debe aparecer como PERSONAL. XLSB!NOMBRE2. 3. Haga clic en el botón Ejecutar. Ahora su nombre debe aparecer en la celda A1, y el puntero descansa en la celda A2.

Una variación más de las macros Hemos creado dos macros de nombre y cada una hace algo un poco diferente. Pero cada macro usó lo que llamamos referencias absolutas. Con el uso de referencias absolutas, Excel graba la ubicación exacta a la que mueve el puntero. En cambio, si usamos referencias relativas, Excel graba el movimiento de su puntero en relación con el lugar que ocupa en la hoja de cálculo. Esta macro debe ilustrar la diferencia entre los dos tipos de referencias. Vamos a crear una macro ﬁnal para ingresar su nombre. En ésta nos gustaría mostrar su capacidad para colocar su nombre en cualquier celda del libro, y haremos que el puntero descanse en la celda que se encuentra debajo de su nombre cuando la macro se complete. Llamaremos a esta macro NOMBRE3. He aquí los pasos para crear la macro NOMBRE3: 1. Haga clic en la opción Usar referencias relativas en la cinta de opciones Programador. 2. Haga clic en la opción Grabar macro de la cinta de opciones. 3. En el cuadro de diálogo Grabar macro, en el campo Nombre de la macro, ingrese NOMBRE3 como nombre de esta macro. 4. Deje en blanco el campo Método abreviado para esta macro. 5. En el campo Grabar macro en, elija “Libro de macros personal”.

Macros con Excel 2007

6. Si lo preﬁere, ingrese una descripción opcional para esta macro. 7. Haga clic en Aceptar. La grabadora de macros está grabando ahora sus pasos. 8. Sin mover su puntero a una nueva celda, escriba su nombre. 9. Oprima ENTER cuando haya terminado de ingresar su nombre. El puntero se mueve a la celda debajo de su nombre. 10. Haga clic en la opción Detener grabación en la cinta de opciones Programador.

Pruebe la macro NOMBRE3 Haga clic en cualquier lugar de la hoja de cálculo (no importa en cuál celda haga clic). Ahora probaremos NOMBRE3. 1. Haga clic en el botón Macros de la cinta de opciones Programador. Aparece la ventana Macro. 2. Haga clic en la macro NOMBRE3 (debe aparecer como PERSONAL. XLSB!NOMBRE3. 3. Haga clic en el botón Ejecutar. Ahora su nombre debe aparecer en la celda donde estaba el puntero, que descansa en la celda que se encuentra debajo de su nombre. 4. Experimente haciendo clic en diferentes celdas de la hoja de cálculo y ejecutando la macro NOMBRE3. Su nombre debe aparecer cada vez donde lo quiera y el puntero descansa en la celda inferior. Observe que cuando elige ejecutar una macro, no importa si la opción Usar referencias relativas está habilitada o no. Esta opción sólo afecta a la macro cuando se está grabando. Preste atención a la importancia de decidir en qué celdas está su puntero antes y después de empezar a grabar la macro. Pequeñas decisiones como éstas son la esencia de la escritura correcta de macros. Necesita desarrollar una mentalidad para considerar cada contingencia como si tomara decisiones acerca de la manera en que creará sus macros. Como puede ver, cada una de estas tres macros funciona de manera diferente:

NOMBRE1 coloca su nombre en cualquier celda, sin importar dónde esté localizado su puntero y regresa éste a la celda A2 cuando ha terminado.

NOMBRE2 coloca su nombre en la celda A1, sin importar en qué celda ejecuta su macro. Su puntero regresa a la celda A2 cuando termina la macro.

NOMBRE3 coloca su nombre en cualquier celda, sin importar dónde está localizado el puntero y regresa éste a la celda que se encuentra debajo de su nombre.

Capítulo 1

Grabación de macros

Estas pequeñas macros son simples y sólo realizan una tarea. Las diferencias entre estas macros son sutiles, pero importantes. A medida que avancemos en este libro, aprendiendo más acerca de la manera como se graban y crean las macros, verá que los problemas atendidos en estas tres macros se considerarán con frecuencia.

Macro simple de fecha Empleando los conocimientos que ya obtuvo en este capítulo, creemos una macro sencilla que sitúe la fecha de hoy en la celda de su elección. En primer lugar, decida la manera como desea que aparezca la fecha. Ya hay una fórmula integrada en Excel que coloca la fecha de hoy en una celda. Pero esa fecha no es estática (cambia cada vez que reabre la hoja de cálculo). Suponga que quiere la fecha de hoy en una celda, que la fecha se quede de manera permanente en la celda sin actualizar y que aparezca en un formato como éste: 2 de febrero de 2009 En primer lugar, decida cómo le gustaría ingresar la fecha si estuviera escribiéndola. Recuerde seguir todos los pasos. Pienso que lo primero es hacer clic en la celda donde quiero que aparezca la fecha. Luego usaré la fecha HOY para ingresar la fórmula de la fecha de hoy. Pero esa fórmula usa un formato 2/2/09, y quiero ver el formato que desplegué arriba. Así que ahora necesito cambiar el formato de la celda al estilo que preﬁero. Pero aún tengo un problema: la celda se actualizará cada vez que abra la hoja de cálculo. Y quiero que la fecha permanezca estática. Así que a continuación vamos a copiar la fecha al portapapeles. Por último, usaré el comando Pegado especial para pegar el valor de la celda en la propia celda, reemplazando la fórmula. Al ﬁnal, oprimiré ESC para vaciar el portapapeles. Esto requiere varios pasos. Por ello deseo que una macro haga esto por mí, de modo que no tenga que recorrer todos estos pasos en el futuro cuando quiera asignar una fecha estática a una celda. Aquí vamos. 1. Haga clic en la celda donde quiere que aparezca la fecha. 2. Haga clic en la cinta de opciones Programador. 3. Haga clic para deshabilitar la consulta Usar referencias relativas, si está activada (no necesitamos esta característica para esta macro). 4. Haga clic en el botón Grabar macro.

Figura 1-4

Grabación de la macro de fecha

5. En el cuadro de diálogo Grabar macro (véase la ﬁgura 1-4), ingrese un nombre de macro (he usado FechaDeHoy; recuerde que no puede usar espacios).

Macros con Excel 2007

6. Almacene esta macro en el Libro de macros personal. 7. Ingrese una descripción opcional. 8. Haga clic en Aceptar. 9. Ahora es el momento de realizar los pasos descritos al principio de esta sección. En primer lugar, haga clic en la cinta de opciones Fórmulas. 10. Haga clic en la opción Fecha y hora. 11. Haga clic en HOY. 12. En el cuadro Argumentos de función que aparece, haga clic en Aceptar. 13. Haga clic con el botón derecho en la fecha. 14. Elija Formato de celdas del menú emergente. 15. Haga clic en la categoría Fecha, en la ventana Formato de celdas. 16. Elija 14 de marzo de 2001 como Tipo. (Nota: Hay una lista de tipos de datos disponibles en la cinta de opciones Inicio, pero este tipo en particular no aparece en esa lista.) 17. Haga clic en Aceptar. Ahora su fecha aparece correctamente. Pero esta fecha aún incorpora la función HOY, lo que signiﬁca que cambiará si abre la hoja de cálculo mañana. Así que aún no hemos terminado. 18. Haga clic en la cinta de opciones Inicio. 19. Haga clic en la opción Copiar. (Nota: También podría hacer clic con el botón derecho en la fecha y elegir Copiar del menú emergente.) 20. En la cinta de opciones Inicio, haga clic en la ﬂecha que se encuentra bajo la opción Pegar. 21. Elija Pegar valores. Observe que el valor en la barra de fórmulas cambia de HOY() a la fecha real de hoy. 22. Oprima ESC. 23. Haga clic en la cinta de opciones Programador. 24. Haga clic en Detener grabación. ¡Uff! Son 24 pasos para poner una fecha en una celda. ¿No está contento de que hayamos grabado esta tarea para uso futuro? Pruebe su macro al hacer clic en otra celda, eligiendo la opción Macros en la cinta de opciones Programador y haciendo clic en la macro FechaDeHoy y luego en el botón Ejecutar. Estupendo, ¿no?

Capítulo 1

Grabación de macros

Formato con macros He aquí otra tarea fácil pero repetitiva que puede automatizar con una macro. Supongamos que tiene una pequeña hoja de cálculo que prepara cada mes. Los títulos, el formato y las fórmulas en la hoja de cálculo permanecen sin cambio, mes tras mes. A menudo lo que hace cuando es hora de elaborar una nueva hoja de cálculo es abrir la hoja de cálculo del último mes, borrar los números, guardarla como una hoja de cálculo mensual y llenarla con los números de este mes. No me sorprendería si por lo menos alguna vez ha eliminado los números del último mes y guardado los nuevos para este mes usando el nombre de la hoja de cálculo del último mes. ¡Uy! Al crear el esqueleto de su hoja de cálculo como una macro, con fórmulas, formato y títulos, es fácil abrir un nuevo archivo de hoja de cálculo, reproducir su macro para configurar la hoja y luego colocar los números de este mes de manera segura, sin tener que preocuparse por guardar sobre la información del último mes. Probémoslo. En la figura 1-5 se muestra la muy simple hoja de cálculo que crearemos. He aquí un resumen de los pasos necesarios para crear esta hoja de cálculo: en una hoja de cálculo vacía, ingrese Mes Figura 1-5 Hoja de cálculo simple en la celda A1, luego ingrese los nombres de las ciudades México, Bogotá y Cuzco en las celdas B2, C2 y D2. Ingrese Total en la celda E2. Ponga estos totales en negritas a medida que los ingresa. Ingrese los títulos Ventas, Gastos y Utilidades en las celdas A3, A4 y A5. Ponga estos títulos en negritas a medida que los ingresa. En la celda B5, ingrese una fórmula para restar los gastos a las ventas. Copie la fórmula a C5 y D5. Ingrese una fórmula SUMA para sumar de la celda B3 a la D3 e ingrese el resultado en la celda E3. Copie la fórmula a las celdas E4 y E5. He aquí los pasos para grabar esto como una macro llamada InformeMensual. Observe que se evitó el uso del ratón para arrastrar sobre las celdas Podría pensar que una plantilla con el fin de aplicar formato o crear fórmulas. es una buena herramienta opcional

.&.0

para crear el esqueleto de una hoja de cálculo, y tendría razón. Sin embargo, la ventaja de usar una macro para esta tarea es que puede llamar al esqueleto de la hoja de cálculo en cualquier lugar usando la macro, incluso desde una hoja de cálculo existente.

1. Con una hoja de cálculo abierta, haga clic en la cinta de opciones Programador. 2. Habilite la característica Usar referencias relativas. 3. Haga clic en Grabar macros. 4. Ingrese InformeMensual como nombre de esta macro.

Macros con Excel 2007

5. Guarde la macro en el Libro de macros personal. 6. Ingrese una descripción, si lo desea. 7. Haga clic en Aceptar. 8. Ingrese Mes en la celda A1. 9. Ingrese México en la celda B2 y oprima CTRL+N para que el título se ponga en negritas. 10. Ingrese Bogotá en la celda C2 y cámbiela a negritas; ingrese Cuzco en la celda D2 y póngala en negritas; ingrese Total en la celda E2 y aplíquele negritas. 11. Ingrese Ventas en la celda A3 y cámbiela a negritas; ingrese Gastos en la celda A4 y póngala en negritas; e ingrese Utilidades en la celda A5 y aplíquele negritas. 12. Lleve el puntero a la celda B5. 13. Escriba un signo de igual (=) y luego coloque el puntero en B3, escriba un guión (-) y llévelo a B4. Oprima ENTER para completar la fórmula. 14. Copie el contenido de la celda B5 (yo usé CTRL+INSERT; usted también puede hacer clic con el botón derecho y elegir Copiar, o hacer clic en Copiar en la cinta de opciones Inicio). 15. Pegue en las celdas C5 y D5. 16. Coloque el puntero en la celda E3. 17. Ingrese la fórmula =SUMA( y luego haga clic en la celda B3, mantenga oprimida la tecla MAYÚS y haga clic en la celda D3. 18. Oprima ENTER. 19. Copie el contenido de la celda E3 en las celdas E4 y E5. 20. Haga clic en la cinta de opciones Programador y deshabilite la grabadora de macros, o haga clic en el botón Macro en su barra de tareas para apagarla.

.&.0 Si no funciona alguna de sus macros, no se preocupe por eso en este momento. Analizaremos la depuración de macros en el siguiente capítulo.

Pruebe primero su macro al ingresar datos en la hoja de cálculo para asegurarse de que sus fórmulas funcionan, y luego haga clic en cualquier lugar de su hoja de cálculo o en una nueva y ejecute la macro InformeMensual para desplegar el esqueleto de su informe.

Capítulo 1

Grabación de macros

Guardado de macros Cuando creó sus tres macros NOMBRE, se le dieron tres opciones para guardar las macros. Elegimos guardarlas en el Libro de macros personal. Este libro se carga automáticamente cuando abre Excel, y está disponible para todos los libros, de modo que estas tres macros están ahora disponibles para todos sus libros de Excel, incluidos los que pudo haber creado y guardado antes.

Guardado en el Libro de macros personal Encontrará que cuando trata de cerrar Excel, recibirá un mensaje que le pregunta si quiere guardar los cambios que hizo a su Libro de macros personal. Si hace clic en Sí, sus macros se guardarán y estarán disponibles para usted la próxima vez que abra EXCEL. El Libro de macros personal es un libro oculto, que normalmente no está accesible como un libro visible. El nombre del archivo es Personal.xlsb. En páginas posteriores de este libro nos referiremos más al Libro de macros personal.

Guardado en Este libro (el actual) Otra opción consiste en guardar las macros en este libro. Algunas macros se relacionan con libros especíﬁcos y no son útiles en otros. Por ejemplo, si crea una macro que ofrece al usuario la opción de realizar ciertos cálculos sobre los datos de un libro existente, y los cálculos sólo se relacionan con ese libro, no hay necesidad de hacer que la macro quede disponible para todos en el Libro de macros personal. En cambio, guardaría una macro como ésta en el libro actual.

Ubicación del libro de trabajo

Nombre del libro Activar macros

Figura 1-6

Guardado de una hoja de cálculo que contiene macros

Macros con Excel 2007

Cuando elige guardar una macro en su libro actual, también debe guardar el libro para conservar la macro. Cuando esté listo para guardar un libro que contiene una macro, debe elegir Libro de Excel habilitado para macros como tipo de libro. Cuando aparezca la ventana Guardar como, siga estos pasos: 1. Elija la carpeta donde desea guardar el libro en el campo Guardar en (véase la ﬁgura 1-6). 2. Ingrese el nombre del libro en el campo Nombre de archivo. 3. Elija Libro de Excel habilitado para macros en el campo Guardar como tipo. 4. Haga clic en Guardar.

Guardado en un nuevo libro La tercera opción consiste en guardar una macro en un nuevo libro. Algunas personas quieren crear macros que estén disponibles para usarlas en otros libros, pero no desean que queden disponibles para todos en el Libro de macros personal. Al guardar las macros en un nuevo libro, elija cuando quiere que la macro esté disponible con sólo abrir ese libro. En cuanto decida guardar su macro en un nuevo libro, Excel abrirá un nuevo libro en su pantalla. Excel le da al nuevo libro el nombre Libro seguido por un número. Si sólo ha abierto un libro en esta sesión de Excel, se le llamará Libro2, hasta que lo guarde y le asigne un nuevo nombre. Si antes ha abierto dos libros, el nuevo se ¡Cuidado! ¡Parecerá que no llamara Libro3 y así sucesivamente. hay nada en el nuevo libro! Sin embargo, cuando trate de Cuando quiera usar la macro que ha guardado en guardarlo, se le indicará que lo un nuevo libro, debe abrir éste. Su macro quedará guarde como un libro habilitado disponible para todos los libros de Excel que estén para macros. abiertos en su equipo, mientras el archivo esté abierto. En el capítulo 2, “Edición de macros”, echaremos Preste atención que si decide no un vistazo al funcionamiento interno de las macros, guardar el nuevo libro, perderá su y aprenderá a editar y corregir problemas en macros nueva macro. que ya ha creado.

.&.0

A &EJDJPO EF NBDSPT

En este capítulo nos referiremos a la aplicación de correcciones y cambios simples a las macros que ha grabado. Utilizaremos el Editor de Visual Basic para acceder a las macros que ya ha creado en el capítulo 1. Revisaremos esas macros, estudiaremos la manera como se crearon y haremos algunas ediciones fáciles. Más adelante, en este libro, aprenderemos acerca del diseño de macros desde cero al usar el Editor de Visual Basic, y eso será importante porque muchas macros no pueden grabarse y en cambio tienen que escribirse, paso a paso. Cuando estemos listos para trabajar en la escritura de macros, se sentirá cómodo con el Editor, y la idea de diseñar una macro escribiendo los comandos no le parecerá aterradora.

Macros con Excel 2007

Haga clic en la opción Visual Basic

Apertura del editor de Visual Basic

Figura 2-1 El grupo Código de la cinta de opciones Programador

La apertura del editor de Visual Basic es tan fácil que lo puede hacer en un solo paso:

Botón Alternar Submenú Botón Objeto Ver objeto carpetas

1. En el grupo Código de la cinta de opciones Programador, como se muestra en la ﬁgura 2-1, haga clic en Visual Basic.

Botón Ver código

Es todo: ¡ya ha abierto el editor! Títulos de libros

Módulos

Objetos

Examine el editor de Visual Basic Hagamos un pequeño recorrido por el editor de VB, porque esta pantalla tiene un aspecto muy diferente de su hoja de cálculo normal de Excel. Revisaremos algunos de los elementos del editor con los que querrá familiarizarse.

Explorador de proyectos

El Explorador de proyectos aparece a la izquierda de la pantalla de Visual Basic, como se muestra en la ﬁgura 2-2. Si no lo ve, elija Ver | Explorador de proyectos, del menú, u oprima CTRL+R. El Explorador despliega Figura 2-2 El Explorador de proyectos una lista de todos los libros abiertos en Excel, incluido el Libro de macros personal (PERSONAL.XLSB), que siempre está abierto, pero que es un libro oculto. Los objetos de Microsoft Excel aparecen como un submenú. Debajo de cada título de libro hay entradas para cada hoja del libro y una entrada para todo el libro. Al hacer clic en cualquiera de los nombres de una hoja o en la entrada This Workbook se le permite ver una lista de las propiedades asociadas con esa hoja o libro en la ventana Propiedades (que se explicará con más detalle en la sección “Ventana Propiedades”, en páginas posteriores de este capítulo). Si se han creado macros dentro del libro (y creamos varias en el capítulo 1 que se encuentran en el Libro de macros personal), verá un submenú para los módulos debajo del título del libro (Módulo1, Módulo2, etc.). Los módulos contienen procedimientos (macros) que Excel puede ejecutar.

Capítulo 2

Edición de macros

h -" ."/&3" '"$*-

Hay tres botones en la parte superior del Explorador de proyectos. Haga clic en el botón Ver Al hacer doble clic en el nombre código para desplegar una ventana de código a la de un módulo se abre una derecha de su pantalla de VB. (Esta ventana Código ventana de código que despliega a éste para todas las macros se explicará con más detalle en la sección “Ventana que residen dentro de dicho Código”, en páginas posteriores de este capítulo.) módulo. Si la ventana de código ya está desplegada, al hacer clic en el botón no ocurre nada. Observe que debe seleccionar primero un módulo para activar este botón. Haga clic en el botón Ver objeto con un objeto seleccionado (como una de las hojas o libros desplegados en la lista de proyectos) y aparecerá el código de ese objeto. Por ejemplo, puede desplegar rápidamente Hoja1 de Libro1 al hacer clic en ese nombre de hoja y luego en el botón Ver objeto. El botón Alternar carpetas oculta y muestra de manera alternada las carpetas de la ventana Proyecto.

Módulos Los módulos son los lugares en donde se almacenan las macros. Si ha creado macros que se guardaron en un libro determinado, ese libro tendrá un módulo asociado con él y aparecerá en el Explorador de proyectos cuando se abra ese libro. El Libro de macros personal contiene módulos, y el código de las macros que creó en el capítulo reside en éstos. Puede hacer clic una vez en un nombre de módulo y usar el botón Ver código para desplegar el código.

Ventana Propiedades La ventana Propiedades despliega los atributos que están asociados con los diversos libros y hojas de Excel que se encuentran desplegados en el Explorador de proyectos. Si la ventana Propiedades no está visible en su ventana, elija Ver | Ventana Propiedades, del menú de VB, o sólo oprima F4. Hay dos fichas en la ventana Propiedades, Alfabética y Por categorías, como se muestra en la figura 2-3. Cada ficha presenta la misma información, sólo que en orden diferente. Haga clic en una hoja de cálculo o un libro en el Explorador de proyectos y verá las propiedades asociadas en la ventana Propiedades. Un módulo no tiene ninguna propiedad asociada (aparte de su nombre).

Figura 2-3

Atributos del libro seleccionado

Macros con Excel 2007

Ventana Código Ésta ventana despliega el código de Visual Basic asociado con el elemento que está seleccionado en el proyecto. Puede hacer doble clic en un elemento del Explorador de proyectos para ver el código asociado con él. Por ejemplo, en la ﬁgura 2-4 se muestra el código de las macros almacenadas en el módulo 1 del Libro de macros personal. Figura 2-4

Código de macro en la ventana de código

Aprendizaje de la lectura de macros Examinemos las tres macros NOMBRE que creamos en el capítulo 1. Estas macros son muy similares entre sí, pero cada una nos proporciona la oportunidad de aprender algunos de los fundamentos acerca del funcionamiento del lenguaje de programación de macros. En la ﬁgura 2-5 se muestra el código de la macro NOMBRE1.

Macro NOMBRE1 Como recordará, en la macro NOMBRE1 creada en el capítulo 1 primero colocaba su puntero en la celda A1 antes de grabar, y luego encendía la grabadora, escribía su nombre y oprimía ENTER. Al mirar el código de la macro de NOMBRE1, esto es lo que ve:

Inicio del procedimiento Sub Texto informativo Código de la macro

Final del procedimiento Sub

Figura 2-5

La macro NOMBRE1

Capítulo 2

.&.0 Además de macros, puede grabar funciones deﬁnidas por el usuario en el editor de Visual Basic. Las funciones empiezan con la palabra Function en lugar de Sub. Las funciones se analizan en el capítulo 3.

Edición de macros

* Sub NOMBRE1()

A cada macro que realiza una tarea se le llama procedimiento Sub. La primera línea de un procedimiento Sub empieza con la palabra Sub seguida por el nombre de la macro. En este caso, el nombre de la macro es NOMBRE1. Si la macro se llamara “GrupoProductos”, la primera línea sería: * Sub GrupoProductos()

Después del nombre de la macro hay un par de paréntesis. Si la macro requiere alguna información determinada para realizar su tarea, esa información, llamada argumentos, aparece dentro de los paréntesis. Como se observa en la ventana de código, notará que algunas líneas empiezan con apóstrofos y otros no.

Líneas de comentario Varias líneas que siguen a Sub Nombre empiezan con un apóstrofo. Éste distingue a las líneas como texto informativo que no afecta la operación de la macro y a menudo se le denomina comentarios. La información que podría ver en esta área de comentarios incluye el nombre de la macro, cualquier información de descripción opcional que ingresó cuando creó la macro y un método abreviado de teclado asociado con la macro. En el caso de la macro NOMBRE1, la información descriptiva incluye una línea que muestra el nombre de la macro y una que muestra el método abreviado de teclado CTRL+MAYÚS+N. Usted tiene permitido escribir información adicional aquí. Siempre y cuando empiece sus líneas con un apóstrofo, puede ingresar comentarios que no tendrán impacto en la operación de la macro.

Líneas de comando Las líneas que siguen al texto informativo en la ﬁgura 2-5 son las de comando. Es la parte de la macro que contiene el lenguaje en código de Visual Basic que ejecuta la operación de la macro. Dependiendo de la complejidad de la macro y el número de comandos que debe aplicar, esta área podría ser muy corta (NOMBRE1 sólo incluye dos líneas de código) o muy larga. Miremos más de cerca las líneas de comando incluidas en la macro NOMBRE1. ActiveCell.FormulaR1C1 = "Gail A. Perry, CPA"

ActiveCell alude a la celda en que reside su cursor. FormulaR1C1 signiﬁca que, para esta macro, la celda activa está designada como ﬁla 1 columna 1 (R1C1, Row 1, Column 1). Éste es el punto del que se lanza la macro, de modo que cualquier referencia al movimiento de la celda en la macro se hará en relación con esta celda, la que se encuentra en la primera

Macros con Excel 2007

ﬁla y la primera columna a partir de la que está empezando su puntero. No importa cuál celda designe como activa, cuando empiece a ejecutar esta macro, esa celda se considera R1C1 para los ﬁnes de esta macro. Consulte el análisis acerca de la macro NOMBRE3, en la sección “Macro NOMBRE3”, para conocer más información acerca de esta instrucción. En la macro NOMBRE1, esta información no se usa. = "Gail A. Perry, CPA"

La información entre comillas, "Gail A. Perry, CPA", es la que la macro colocará en la celda. Range("A2").Select

Range alude al rango de celdas que se seleccionará en esta macro. ("A2") es el rango especíﬁco de celdas al que hace referencia esta línea de comando. De modo que el rango de celdas “A2” alude a la celda única A2. Select es lo que se ha indicado a la macro que debe hacer con el rango de celdas especiﬁcado. En este caso, la instrucción a la macro es seleccionar la celda A2. End Sub es siempre la última línea de la macro. Además de aprender los comandos básicos de las macros que hemos visto en nuestra macro NOMBRE1, debe examinar el formato de la macro. La primera línea y la última aparecen en el margen izquierdo. Los apóstrofos a la izquierda del texto informativo también se encuentran en tal margen. Las líneas de comando de la macro se muestran con sangría. Podrá ver los detalles que acompañan a los cambios más ligeros en los comandos de las macros NOMBRE2 y NOMBRE3.

Macro NOMBRE2 La diferencia entre las macros NOMBRE1 y NOMBRE2 es que cuando grabamos la macro NOMBRE1, empezamos por colocar nuestro puntero en la celda A1 antes de grabar. La macro NOMBRE2 en el editor de Visual Basic (véase la ﬁgura 2-6) es casi igual que la macro NOMBRE1, excepto por estas diferencias:

Información descriptiva Código para empezar la macro en la celda A1

Figura 2-6

La macro NOMBRE2

Capítulo 2

Edición de macros

No hay un método abreviado de teclado para NOMBRE2.

Hay un breve texto descriptivo para NOMBRE2 (“Nombre en la celda A1”).

Hay un comando adicional en NOMBRE2:

Range("A1").Select

El código que aparece en la primera línea del código en NOMBRE2 es el que se grabó cuando se movió el puntero a la celda A1 después de encender la grabadora de macros.

Macro NOMBRE3 La diferencia entre la macro NOMBRE3 y las otras es que ésta funciona desde cualquier posición, en lugar de suponer que el usuario colocará el puntero en la celda A1 para forzarlo a ir a esa celda. Con NOMBRE3, el usuario empieza con el puntero en cualquier celda, ejecuta la macro y el puntero termina en la celda debajo de la celda inicial. El código que coloca el nombre en la celda (ActiveCell.FormulaR1C1 = "Gail Perry") sigue siendo el mismo en las tres macros. Observe la ligera diferencia en el código para la macro NOMBRE3 que se muestra en la ﬁgura 2-7. La última línea contiene este código: ActiveCell.Offset (1, 0).Range("A1").Select

El comando Offset indica el movimiento del cursor. El comando de la macro está indicando que la nueva celda activa es la que se encuentra una ﬁla abajo y 0 columnas a la derecha (1, 0) de la celda anterior. La instrucción Range("A1").Select indica que, para los ﬁnes de esta macro, la nueva ubicación del puntero se denominará como la celda “A1”. La ubicación de la celda puede estar en cualquier lugar de la hoja de cálculo, dependiendo de dónde se encuentre su puntero cuando ejecute la macro NOMBRE3; sin embargo, en lo que respecta a la macro, la nueva ubicación de la celda es A1, y así es como se hará referencia a la celda si se agrega cualquier código adicional a esta macro.

El código Offset aparece

Figura 2-7

La macro NOMBRE3

Macros con Excel 2007

Edición de macros Es fácil hacer cambios de edición en las macros que ve en el editor de Visual Basic. Puede escribir en las macros como lo haría en cualquier documento, teniendo en cuenta por supuesto, que cuando escribe código, debe usar comandos reales de Visual Basic. Pero puede cambiar el nombre de una macro, ingresar o cambiar la información explicativa en las áreas de comentarios de la macro y cambiar, agregar y eliminar código de macros. He aquí un cambio fácil. Supongamos que quiere cambiar el nombre que usó en la macro NOMBRE3. Ingresé mi nombre, “Gail Perry”, pero tal vez quiero usar la inicial de mi nombre y mi título profesional: “Gail A. Perry, CPA”. (¡O tal vez escribió mal su nombre y quiere corregirlo!) Puedo editar el nombre como aparece en la primera línea del código de la macro para que se lea de la manera en que quiero: ActiveCell.FormulaR1C1 = "Gail A. Perry, CPA"

Si voy a una página de la hoja de cálculo y ejecuto la macro NOMBRE3, el cambio ya habrá aplicado y aparecerá el nombre revisado.

Guardado de una macro editada Aunque haga un cambio en su macro, y la pruebe y vea que el cambio se ha grabado, hay un paso necesario más para guardar una macro. Ese cambio que hizo será efectivo en cualquiera de sus hojas de cálculo, mientras que no cierre Excel. En cuanto trate de cerrarlo, se le preguntará si quiere guardar los cambios que hizo al Libro de macros personal. Debe responder Sí a esta pregunta, si quiere que los cambios se guarden de manera permanente. En lugar de esperar a guardar sus macros cuando salga de Excel, puede guardarlo en cualquier momento en la pantalla de Visual Basic empleando cualquiera de estas técnicas:

Oprima CTRL+G.

Haga clic en el ícono Guardar, de la barra de herramientas de Visual Basic (véase la ﬁgura 2-8). Haga clic aquí para guardar

Figura 2-8

Guarde los cambios hechos en Visual Basic

Capítulo 2

Edición de macros

Búsqueda de ayuda en Visual Basic Cuando tenga interrogantes, encontrará que hay una amplia ayuda disponible para usted en el editor de Visual Basic.

Si quiere información adicional acerca de cualquier código que aparece en su macro, haga clic con el botón derecho en una línea de código y elija Información rápida. Se considera que cada elemento de código es un objeto. Cuando pide Información rápida, aparece un recuadro con información acerca del tipo de objeto de que se trata.

Puede encontrar información más detallada acerca de objetos de Visual Basic en el Examinador de objetos. Desde el interior del editor de Visual Basic, oprima F2, elija Ver | Examinador de objetos del menú o haga clic en el botón Examinador de objetos en la barra de herramientas (véase la ﬁgura 2-9). Botón Examinador de objetos

Figura 2-9

La barra de herramientas de Visual Basic

Haga clic para realizar la búsqueda

Haga clic sobre los resultados

Ayuda

Ingrese aquí el comando o la instrucción

Figura 2-10 La ventana Examinador de objetos le lleva a obtener más ayuda

Aparece la ventana Examinador de objetos, como se muestra en la ﬁgura 2-10. Ingrese el comando o la instrucción que desee explorar en el campo Texto de búsqueda. Haga clic en el botón con los binoculares para realizar esta búsqueda. Cuando aparezca el resultado, haga clic en la instrucción o el comando en la lista de resultados, y luego haga clic en el botón Ayuda para conocer más información.

Macros con Excel 2007

Otra manera de ingresar en el sistema de ayuda de Visual Basic es elegir Ayuda | Ayuda de Microsoft Visual Basic del menú. Ingrese el nombre del objeto acerca del que desee información adicional, y luego haga clic en Buscar. Como opción, puede ingresar la información que está buscando en el campo de Ayuda, en el extremo derecho de la barra de herramientas de VB, donde dice “Escriba una pregunta” (véase la ﬁgura 2-11) y luego oprima ENTER. Cuando examine la información de ayuda para un objeto de VB, verá una deﬁnición, algún texto narrativo sobre la Ingrese aquí su manera en que puede usarse el objeto, un pregunta ejemplo de código de VB empleando el objeto y una descripción de los resultados Figura 2-11 Acceso rápido a la ayuda de Excel que producirá.

Puede buscar el contenido del material referenciado del programador de Excel, y tal vez le resulte mejor organizado y menos abrumador que buscar en todo el sistema de ayuda. Elija Ayuda | Ayuda de Microsoft Visual Basic del menú, u oprima F1. Aparece la opción Desarrollador de Excel 2007. Haga clic en Referencia del modelo de objetos de Excel. Aquí verá una lista detallada de objetos de VB. Haga clic en un objeto y desplácese hacia abajo para encontrar información adicional acerca de ese objeto.

Una de las mejores maneras de aprender a usar los objetos de VB consiste en grabar macros como lo hicimos en el capítulo 1 y luego estudiar el código resultante en el editor de VB. Al examinar el código asociado con una macro que realiza con éxito los pasos que grabó, aborda su sesión de aprendizaje sabiendo ya lo que hace el código. Luego sólo es cuestión de familiarizarse con las instrucciones de código que realizan cada tarea.

La manera más fácil de obtener ayuda Digamos que queremos regresar a la macro NOMBRE3 y, además de ingresar nuestro nombre en una celda, deseamos que la macro centre el nombre en esa celda. Necesitamos determinar el comando de la macro para centrar el texto. Puede encontrar lo que está buscando en la referencia Programador de Excel, pero primero sirve de algo saber que la alineación de la celda a la izquierda, al centro y a la derecha, se considera alineación horizontal. Al saber esto, puede abrirse paso en la referencia del desarrollador para ﬁnalmente encontrar la pantalla que describe la manera de asignar el valor de centrar a la alineación No es necesario que ingrese su horizontal. Pero hay una manera mucho más fácil: consulta en forma de pregunta. ¡grabar una macro!

.&.0

Capítulo 2

En cualquier momento, puede grabar una macro que realice una tarea. Luego examine la macro en el editor de VB, encuentre el código que necesite, y copie y pegue ese código en su macro existente. Después de eso, puede eliminar la macro de ejemplo que creó porque ya no la necesita. Para el ejemplo del centrado del texto, siga estos pasos:

Edición de macros

h -" ."/&3" '"$*Es más probable que su ventana de VB aún esté abierta. Al oprimir ALT+TAB puede ir rápidamente a la ventana de VB.

1. Regrese a su hoja de cálculo (haga clic en el botón Ver Microsoft Excel en el extremo izquierdo de la barra de herramientas de VB). 2. Habilite la grabadora de macros (Grabar macros en la cinta de opciones Programador). 3. Asigne a esta macro el nombre Prueba1 y guárdela en este libro. No hay necesidad de usar el Libro de macros personal para esta macro (no se requiere acceder a esta macro desde cualquier otra hoja de cálculo). 4. Con su puntero en cualquier celda, despliegue la cinta de opciones Inicio, y luego haga clic en el botón Centrar el texto. No es necesario que haya algún texto en la celda para realizar esta tarea, y no importa cuál celda use. Todo lo que estamos haciendo aquí es cosechar código. 5. Despliegue la cinta de opciones Programador y haga clic en Detener grabación. 6. Haga clic en el botón Visual Basic para regresar al editor de VB. 7. Verá que se ha agregado un módulo para el libro actual a la ventana Proyecto. Haga doble clic en ese módulo para desplegar la ventana de código. Aparece la macro Prueba1 (véase la ﬁgura 2-12).

Código para centrar el contenido de la celda

Figura 2-12

Código de macro para formar celdas

Macros con Excel 2007

8. Observe que se han asignado varios atributos a la selección en la macro Prueba1. Cada vez que aplique un cambio de formato, como el comando de centrado a una celda, el código de la macro asociado muestra un grupo de atributos. Para nuestra situación, sólo necesitamos el código para centrar texto. Éste sería: With Selection .HorizontalAligment = xlCenter End With

El resto de las instrucciones de formato no son parte necesaria del comando para centrar texto en una celda. 9. Copie y pegue las tres líneas de código mostradas en el paso 8 en su macro NOMBRE3, debajo de la línea que describe el contenido de la celda activa (ActiveCell) y antes de la línea que contiene el comando Offset. (Copié y pegué todo el bloque de código y luego borré las líneas que no necesitaba.) Su macro NOMBRE3 ahora tendrá este aspecto (con su nombre en lugar del mío): Sub NOMBRE3() ' ' NOMBRE3 Macro ' Nombre en cualquier celda; el puntero va a la celda debajo del nombre. ' ' ActiveCell.FormulaR1C1 = "Gail A. Perry, CPA" With Selection .HorizontalAlignment = xlCenter End With ActiveCell.Offset(1, 0).Range("A1").Select End Sub

10. Regrese a su hoja de cálculo y pruebe la macro NOMBRE3 al hacer clic en cualquier celda y luego en Macros de la cinta de opciones Programador. Elija NOMBRE3 y haga clic en Ejecutar. Verá que su nombre aparece en la celda actual y está centrado.

.&.0 Recuerde que cuando cierre Excel, se le preguntará si quiere guardar los cambios en el Libro de macros personal. Asegúrese de responder Sí para guardar los cambios que hizo a la macro NOMBRE3.

11. Ya no necesita la macro Prueba1. Regrese al editor de VB y en la ventana Proyecto, haga clic con el botón derecho en el módulo que contiene esa macro y elija Quitar Módulo1. Se le preguntará si desea exportar el módulo antes de eliminarlo. Responda No. El módulo se habrá ido, la macro Prueba1 se habrá ido también, y habrá realizado algo de limpieza.

A $SFBDJPO EF NBDSPT FO 7JTVBM #BTJD

Vamos a entrar en el mundo de la construcción de macros de la manera fácil (empleando los principios que ya empezamos a explorar en los capítulos 1 y 2). En este capítulo crearemos varias macros útiles mientras seguimos familiarizándonos con Visual Basic. Debido a que el objetivo de este libro es aprender a construir macros a un ritmo cómodo y comprensible, en este capítulo nos concentraremos en la grabación de macros y luego en el uso del código grabado para hacer nuevas macros. Como ya conoce la manera de realizar muchas tareas en Excel (estoy dando por hecho que si está listo para construir macros, ya domina los fundamentos), aprovecharemos el conocimiento que posee para crear macros que puedan hacer más fácil, eﬁciente y gratiﬁcante su experiencia con Excel.

Macros con Excel 2007

En primer lugar, crearemos macros que realizan algunas tareas repetitivas. Hay dos tipos de éstas:

Las tareas que realiza con frecuencia, en diferentes hojas de cálculo.

Las tareas que requieren repetición dentro de una sola hoja de cálculo.

Aquí nos ocuparemos principalmente del primer tipo de tareas repetitivas. Cuando lleguemos a los capítulos 10 y 11, aprenderemos acerca de macros que se repiten a sí mismas dentro de una sola hoja de cálculo. Además, aprenderemos acerca de la aplicación de lo que conocemos acerca de macros para crear funciones personalizadas.

Despliegue de fórmulas como valores Hay ocasiones en que quiere que una hoja de cálculo esté disponible para alguien más que no necesita ver las fórmulas que usó para calcular los números de la hoja. Esta macro le permite eliminar rápidamente las fórmulas de celdas seleccionadas. Después de que aplique la macro, el valor que aparece en la barra de fórmulas es el mismo que en la celda; ninguna fórmula aparece en la hoja de cálculo. Empiece por abrir una hoja de cálculo que contiene fórmulas. La hoja de cálculo de la ﬁgura 3-1 incluye fórmulas que calculan la comisión de varios vendedores; esas fórmulas son conﬁdenciales, así que queremos ocultar el cálculo en la columna Bono. El primer paso al crear una macro debe ser planear el proceso de cómo debe realizarse el procedimiento completo. ¡Recuerde realizar todos los pasos! Para cambiar la fórmula en una celda por un valor, he aquí los pasos que debo seguir: Flecha hacia abajo del botón Pegar

Figura 3-1

Cambie esta fórmula por un valor

Las fórmulas aparecen en la barra de fórmulas

Capítulo 3

Creación de macros en Visual Basic

1. Hacer clic en la celda que contiene la fórmula. 2. Copiar la fórmula al portapapeles (hacer clic en el botón Copiar de la cinta de opciones Inicio). 3. Abrir el menú Pegar (hacer clic en la ﬂecha hacia debajo de Pegar, en la cinta de opciones Inicio). 4. Elegir Pegar valores. 5. Eliminar el recuadro que indica que el contenido de la celda aún se encuentra en el portapapeles (oprima ENTER o ESC). Ahora que ha comprendido los pasos, es hora de grabar ese proceso en una macro. He aquí los pasos para volver automático este proceso y hacer que la macro quede disponible para otras hojas de cálculo. 1. Con la hoja de cálculo que contiene las fórmulas abiertas, haga clic en una celda que contiene una fórmula que desee ocultar. 2. Haga clic en Grabar macro en la cinta de opciones Programador. No es necesario usar referencias relativas para esta macro, porque queremos tener la posibilidad de aplicarla celda tras celda, no hacer que tenga relación con celdas especíﬁcas. 3. Asigne un nombre a la macro. Yo he usado FórmulaAValor. 4. Asegúrese de que la macro va al Libro de macros personal.

.&.0 Estamos seleccionando la celda antes de grabar la macro; no queremos que la macro seleccione una celda por nosotros.

5. Haga clic en Aceptar. No di a esta macro una descripción; el nombre de la macro parece suﬁcientemente descriptivo. 6. Haga clic en Copiar en la cinta de opciones Inicio. 7. Haga clic en la ﬂecha hacia abajo del botón Pegar en la cinta de opciones Inicio.

.&.0 Si en una celda cambia una fórmula por un valor, el proceso es permanente. Una vez que se ha eliminado la fórmula, no va a desplegar la fórmula de nuevo.

8. Haga clic en Pegar valores. Observe que la fórmula en la barra de fórmulas cambia de inmediato por un valor. 9. Oprima ESC. 10. Apague la grabadora de macros.

Macros con Excel 2007

Pruebe la macro Haga una ejecución de prueba con su macro. Haga clic en una celda que contenga una fórmula y, luego, en el botón Macros de la cinta de opciones Programador. Elija FórmulaAValor y haga clic en Ejecutar. Ahora probemos la selección de un rango de celdas y la nueva ejecución de la macro. Así como puede seleccionar un rango de celdas y realizar otras operaciones, puede usar su macro de la misma manera. Resalte un rango de celdas que contiene fórmulas y luego haga clic en Macros y elija FórmulaAValor. Ahora todas las celdas resaltadas sólo contienen valores y la fórmula ya no aparece.

Visualización del código de la macro Es hora de echar un vistazo a esta macro en el editor de Visual Basic. Haga clic en el botón Visual Basic, en la cinta de opciones Programador. El código de su macro estará localizado en uno de los módulos del Libro de macros personal. Verá este libro en la ventana Proyecto. Para ver el código de los módulos dentro de ese libro, haga doble clic en los nombres de los módulos, en la ventana Proyecto. Como opción, puede elegir Herramientas | Macros, en el editor de VB, seleccionar Proyecto VBA (PERSONAL.XLSB) en el campo Macros en, y cuando aparezca la macro que desee ver en la lista Nombre de macro, haga clic en ella y luego en el botón Modiﬁcar. He aquí el código que aparece: Sub FórmulaAValor() Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False End Sub

Al examinar el código, vemos que la primera línea de código bajo el nombre de la macro, Selection.Copy, copia la celda seleccionada al portapapeles. A continuación se ejecuta el comando Pegado especial, pero revise todas las instrucciones que siguen a Selection.PasteSpecial: Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Capítulo 3

Creación de macros en Visual Basic

Todas estas instrucciones representan los elementos que acompañan al uso del comando Pegado especial en Visual Basic. El único comando que en realidad ejecuta un cambio en la hoja de cálculo es Paste:=xlPasteValues. El resto de los comandos son superﬂuos e innecesarios para la ejecución de la macro. Puede eliminarlos, si desea limpiar el código de su macro, pero no pasa nada dejarlos allí. La última línea del código, Application.CutCopyMode = False, es el comando que se grabó cuando se oprimió la tecla ESC. Este comando elimina la información del portapapeles y elimina el marco alrededor de las celdas seleccionadas. Si quiere experimentar aún más, puede eliminar los elementos excesivos (las líneas Operation, SkipBlanks y Transpose) de la macro y luego probarla de nuevo. Verá que funciona bien y elimina las fórmulas de sus celdas, reemplazándolas con valores. El código de la macro ya limpio se verá así: Sub FórmulaAValor() ' FórmulaAValor Macro Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End Sub

Elaboración de una macro a partir de pequeñas macros Al revisar la nueva cinta de opciones en Excel, puede ver que Microsoft ha puesto a su disposición muchos comandos que puede aplicar con el rápido clic del ratón. Comandos que antes tenía que buscar entre menús ahora están disponibles en las cintas de opciones. Pero aunque los comandos están visibles, aún tiene que realizar algunos pasos para usarlos. En primer lugar, tiene que imaginar en cuál cinta de opciones se encuentra su comando y luego tiene que buscar su ícono o revisar en los menús desplegables que aparecen por aquí y por allá en las cintas de opciones. Algunos comandos se encuentran en el menú contextual (el menú de acceso directo que se despliega) o la minibarra de herramientas que aparece cuando hace clic con el botón derecho en la hoja de cálculo. Si usa de manera rutinaria varios comandos cuando crea o edita hojas de cálculo, ahorre tiempo al colocar estos comandos en una macro. De esa manera, cada vez que abra una nueva hoja de cálculo, si necesita aplicar esos cambios puede hacer todos a la vez al ejecutar una macro. Haga la prueba de grabar una macro que use todos los comandos a la vez, pero tal vez sea más fácil grabar cada comando por separado y luego integrar todos los comandos en una macro en el editor de VB.

Macros con Excel 2007

Para este ejercicio, grabaremos macros que realicen estas tareas:

Establecer las líneas guía para que se impriman.

Cambiar la orientación de la página de vertical a horizontal.

Expandir el ancho de la columna a 10.

Grabe macros pequeñas La primera macro que vamos a grabar habilitará las líneas guía para la impresión de la hoja de cálculo. Por lo general, éstas sólo son para despliegue; no se imprimen a menos que lo indique. La habilitación de las líneas guía imprimibles requiere que marque la casilla de verificación Imprimir, del área Líneas de cuadrícula, en la cinta de opciones Diseño de página. 1. Encienda la grabadora de macros. 2. Llame a esta macro LíneasGuía. 3. Guarde la macro en este libro. No vamos a necesitar esta pequeña macro (tomaremos el código que necesitamos y luego podemos eliminar la macro cuando cerremos el libro sin guardarla). 4. Haga clic en Aceptar. 5. Haga clic en la cinta de opciones Diseño de página. 6. En el área Opciones de la hoja, marque la casilla de verificación Imprimir, debajo de la característica Líneas de cuadrícula (véase la figura 3-2). 7. Apague la grabadora. Clic para revisar el cuadro Líneas de cuadrícula

Figura 3-2

Cambio de las opciones de la hoja

A continuación grabaremos una macro para cambiar la orientación de la página de vertical a horizontal. Para realizar esta tarea en Excel, activamos la cinta de opciones Diseño de página, hacemos clic en la ﬂecha de la opción Orientación y luego hacemos clic en Horizontal, como se muestra en la ﬁgura 3-3. Así que grabemos esto:

Capítulo 3

Creación de macros en Visual Basic

1. Encienda la grabadora de macros. 2. Nombre: Orientación. Ubicación: Este libro. 3. Haga clic en la ﬁcha Diseño de página.

Haga clic en la opción Horizontal Figura 3-3

Cambio de la orientación

4. Haga clic en la ﬂecha hacia abajo del botón Orientación. 5. Haga clic en Horizontal. 6. Apague la grabadora.

Como puede ver, este proceso de grabación de macros es muy fácil. Nos queda una macro por Podría preguntarse qué debe hacer grabar, y luego será hora de poner todo nuestro si, cuando despliegue la cinta de proyecto en una macro que podamos usar de manera opciones Diseño de página, la repetida. Para esta macro ﬁnal queremos expandir casilla de veriﬁcación Imprimir ya está marcada. La grabadora de el ancho de la columna para que despliegue 10 macros está ejecutándose pero caracteres (en oposición a la opción predeterminada no puede realizar la tarea. Siga de 8.43 caracteres). Necesitamos decidir cuántas adelante y haga clic en la casilla columnas tendrán esta designación. La manera más Imprimir. Esto tendrá el efecto fácil de proceder consiste en cambiar el ancho de las de desmarcarla. No importa. Corregiremos esto en el editor de columnas en toda la hoja de cálculo. De esa forma VB. habremos cubierto cualquier número de columnas que podríamos necesitar. Por tanto, cuando se grabe esta macro, seleccionaremos toda la hoja de cálculo y luego haremos el ajuste de columnas para que todas las columnas se vean afectadas. Por último, agregaremos un paso para hacer clic en la celda A1 para que la selección de todo el libro se deshabilite. He aquí los pasos:

.&.0

1. Encienda la grabadora de macros. 2. Nombre: AnchoColumna. Ubicación: Este libro. 3. Haga clic en el cuadro Seleccionar todo que aparece sobre la ﬁla número 1, a la izquierda de la columna A. 4. Haga clic con el botón derecho sobre una letra de columna y elija Ancho de columna del menú contextual. 5. En el cuadro de diálogo Ancho de columna (véase la ﬁgura 3-4), ingrese 10 como nuevo ancho. 6. Haga clic en Aceptar. 7. Haga clic en la celda A1. 8. Apague la grabadora de macros.

Macros con Excel 2007

Seleccionar todas las celdas

Figura 3-4

Establecimiento de un nuevo ancho de columna

Pruebe las macros Antes de seguir adelante, probemos nuestras macros para asegurarnos de que hacen lo que se supone que deben hacer. Abra un nuevo libro. En la cinta de opciones Programador, haga clic en Macros, seleccione la macro LíneasGuía y luego haga clic en Ejecutar. Repita estos pasos con las macros Orientación y AnchoColumna. Si hay algún problema en la ejecución de cualquiera h -" ."/&3" '"$*de las macros, se le redirigirá al editor de VB con un No es necesario que mensaje de error. En lugar de tratar de depurar estas despliegue la cinta de opciones pequeñas macros, lo más fácil es borrar el código de la Programador cada vez que quiera encender o apagar la macro en el editor de VB, regresar a su hoja de cálculo grabadora de macros. Puede y volver a grabar la macro. realizar cualquiera de estas acciones al hacer clic en el botón Macros que se encuentra en la parte inferior de su hoja de cálculo.

.&.0 ¿Le sorprendió que tuviera acceso a sus nuevas macros cuando abrió el nuevo libro? Recuerde que, mientras esté abierto el libro que contenga las macros, éstas se encontrarán disponibles para todos los demás libros abiertos. En cuanto cierre el libro donde están almacenadas sus macros, éstas ya no se encontrarán disponibles para todos los demás libros.

Recolección del código de la macro Ahora que estamos listos para ensamblar una macro que realizará todo lo que hicimos en las tres pequeñas macros, necesitamos dejar los conﬁnes amigables de la pantalla del libro y abrir el editor de VB. En la cinta de opciones Programador, haga clic en el botón Visual Basic, u oprima ALT+F11, y aparecerá el editor de VB. Como recordará, almacenamos las pequeñas macros en el libro actual. Ni siquiera hemos dado un nombre a ese libro, y eso está bien, porque, cuando hayamos ﬁnalizado, no será necesario guardarlo, ni las macros asociadas con él. Verá su libro y cualquier otro que tenga abierto, en la ventana Explorador de

Capítulo 3

Creación de macros en Visual Basic

proyectos de su pantalla de VBA, como se muestra en la ﬁgura 3-5. Si no aparece la ventana Explorador ¡No cierre su libro! Sus macros de proyectos, elija Ver | Explorador de proyectos en el están almacenadas en él. editor de VB, u oprima CTRL+R. Necesitamos encontrar las pequeñas macros que creó. ¿Ve el libro que estábamos usando? Es un VBAProject, que aparece en la ventana Explorador de proyectos. Si le dio un nombre al libro, ese nombre aparecerá entre paréntesis. Si no tiene un nombre, verá (Libro1) o (Libro2) o cualquier número de libro que esté usando. Las macros están almacenadas en el módulo que acompaña al libro. Verá el módulo en el menú que se encuentra debajo Las macros del título del libro. están Haga doble clic en el módulo para abrir la ventana almacenadas en los de código del módulo. Recorra la ventana y verá que módulos todas sus pequeñas macros están allí. Estas macros no se ven tan pequeñas, ¿verdad? Veremos que hay una gran cantidad de material de código en esas macros que no necesitamos. Para crear nuestra nueva Figura 3-5 El Explorador de proyectos macro, vamos a copiar, o recolectar, sólo el código que necesitamos. Mientras recorre las macros, verá que cada una empieza y termina con una línea Sub. También notará que una línea horizontal separa a cada macro (eso facilita la visualización del ﬁnal de una macro y el inicio de la otra).

.&.0

Abra un nuevo módulo Necesitamos encontrar un lugar para colocar nuestra nueva macro. Dependiendo de cuántas sesiones de creación de macros haya tenido y cuánto trabajo haya guardado, podría haber varios módulos o sólo un par de ellos en el área VBAProject (PERSONAL.XLSB) del Explorador de proyectos. Casi todos estos módulos ya tienen algún código almacenado. Puede hacer adiciones a uno de esos módulos, pero para mantener las cosas limpias y ordenadas abramos un nuevo módulo. Haga clic en el título del Libro de macros personal (VBAProject (PERSONAL.XLSB)) y luego elija Insertar | Módulo del menú. Aparece un nuevo módulo bajo el Libro de macros personal, y se muestra una ventana de código nueva y vacía.

Macros con Excel 2007

Organice sus ventanas de módulo Es probable que la nueva ventana aparezca justo sobre la otra ventana de código del módulo, la que contiene el código de sus pequeñas macros. Desplace las barras de título alrededor de las ventanas del módulo y cambie el tamaño de éstas para que pueda ver la ventana del módulo del Libro de macros personal y la del módulo del libro que contiene su código de las pequeñas macros (véase la ﬁgura 3-6).

Construya su nueva macro Estamos listos para empezar a elaborar su macro en el editor de VB, empleando las piezas de código que recolectará de las tres pequeñas macros que grabamos. He aquí los pasos para construir su nueva macro: 1. Ingrese la palabra Sub seguida por un nombre para su nueva macro, después incluya un juego de paréntesis de apertura y cierre. He decidido dar a mi nueva macro el nombre MiHojaConﬁguración. Recuerde que no puede incluir espacios en el nombre de su macro. 2. Oprima ENTER cuando la primera línea de su macro esté en su lugar. ¡Sorpresa! La última línea de su macro (End Sub) aparece automáticamente. Todo el código que ingrese se colocará entre estas dos líneas de texto.

Figura 3-6

La ventana Código

Capítulo 3

Creación de macros en Visual Basic

3. Ingrese algunas líneas de comentario debajo de la línea de título de la macro. Empiece una línea de comentario con un apóstrofo. De esta manera el programa de la macro no tomará por error sus comentarios como código ni tratará de interpretar comandos de sus comentarios. En la línea de comentario puede describir su macro. Cuando oprima ENTER después de cada línea de comentario, verá que la línea cambia de color para distinguirla del código de programación. 4. Estamos listos para colocar algún código en esta macro. Empecemos con el código de la macro LíneasGuía. Observe que hay tres secciones en el código de esta macro. La primera empieza con With ActiveSheet.PageSetup y termina con End With.

h -" ."/&3" '"$*-

El primer comando With... End With contiene código que se relaciona con la impresión de las ﬁlas y columnas de título. No tiene nada que ver con la impresión de las líneas guía, y puede ignorar este segmento de código de la macro LíneasGuía.

La siguiente línea, ActiveSheet. PageSetup.PrintArea = "", es un comando que se relaciona con el área de impresión de la hoja de cálculo. Observe que no hay un comando entre las comillas. Esta línea de código no realiza ninguna operación y es innecesaria en el proceso de impresión de las líneas guía.

La tercera sección del código en la macro LíneasGuía contiene otra instrucción With... End With. Ésta incluye muchas líneas de comando. Busque hacia abajo la línea que dice .PrintGridlines = True. Éste es el comando que buscamos, y es necesario que esté ﬂanqueado por las instrucciones With... End With. El resto del código sólo explica las características predeterminadas de conﬁguración de página, y como ninguna de estas características cambiará (aparte

Puede copiar y pegar la línea de título de una macro existente en la nueva ventana de módulo, y luego sobrescribir el nombre de la macro copiada con el de su nueva macro.

.&.0 Puede ingresar todas las líneas de comentarios que desee en una macro; sólo asegúrese de empezar cada una con un apóstrofo. Puede ingresar líneas en blanco si desea agregar algún espacio entre los comentarios y el código.

.&.0 La construcción With... End With es una característica común de las macros VBA. Cada vez que vea una instrucción que empiece con With, recuerde que toda la instrucción termina con la línea End With, y así toda la colección de información entre estas dos líneas es parte del mismo código.

Macros con Excel 2007

del comando Gridlines), este comando no es necesario para la operación de esta macro. He aquí el código que es importante para nosotros: With ActiveSheet.PageSetup .PrintGridlines = True End With

5. Puede copiar estas líneas de código en particular y pegarlas en la nueva macro. O puede copiar y pegar todo el bloque que empieza con la segunda línea de código With ActiveSheet.PageSetup y terminarla con el código End With, y luego borrar todas las líneas que no son necesarias para esta macro.

.&.0 También tiene la opción de simplemente escribir las líneas de código en la nueva macro, pero no lo recomiendo. Aunque suena que es muy fácil escribir, es aún más fácil escribir mal parte del texto u omitir signos de puntuación. No se arriesgue con su código. Al copiar y pegar, se asegurará de que su código sea exacto y correcto.

6. En la figura 3-7 se muestra la macro hasta el momento. 7. En este punto ha ingresado código suficiente en la nueva macro como para que realice una prueba. Tiene la estructura de macro correcta, con las líneas Sub y End Sub, y hay un comando de macros completo. Puede esperar hasta que hayamos terminado de probar la macro, o puede ir a una nueva hoja de cálculo y hacer ahora una prueba de manejo. Si decide probar la nueva macro, abra una nueva hoja de cálculo, haga clic en Macros en la cinta de opciones Programador, elija MiHojaConfiguración y haga clic en Ejecutar. Luego haga clic en la ficha de la cinta de opciones Diseño de página y verá que se ha marcado la casilla de verificación Imprimir de la característica Líneas de cuadrícula. 8. Estamos listos para el siguiente fragmento de código en nuestra nueva macro, de modo que regresemos al editor de VB si aún no está allí. Encuentre la macro Orientación. Esta vez estamos buscando el código que cambia la orientación

Figura 3-7 El código de la macro después de agregar la primera pequeña macro

Capítulo 3

Creación de macros en Visual Basic

de vertical (la opción predeterminada) a horizontal. Una vez más, tenemos las dos secciones With... End With de la macro y una línea de comandos independiente relacionada con el área de impresión. En la segunda sección With... End With se encuentra el código para la orientación: .Orientation = xlLandscape

Observe que las instrucciones With... End With que ﬂanquean a este código son idénticas a las que aparecían en la macro LíneasGuía. ¿Adivine lo que signiﬁca? No tiene que repetir estas instrucciones en su nueva macro. Puede repetir estas líneas de comando si lo desea, y agregar el siguiente código a su nueva macro: With ActiveSheet.PageSetup .Orientation = xlLandscape End With

O puede ahorrarse algunas líneas y colocar el código de orientación dentro de la instrucción With... End With existente, de la manera siguiente: With ActiveSheet.PageSetup .PrintGridlines = True .Orientation = xlLandscape End With

9. Tenemos que agregar una pieza más a nuestra macro: el comando para ampliar los márgenes a 10 caracteres. Encuentre la macro AnchoColumna y busque el código que aparece a continuación: Cells.Select Selection.ColumnWidth = 10 Range("A1").Select

La primera línea de código es el comando para seleccionar todas las celdas de la hoja de cálculo. La segunda línea ordena a Excel que tome la selección (todas las celdas) y cambie el ancho de columna a 10. La línea ﬁnal de código mueve el puntero a la celda A1 y selecciona esa celda. Recuerde que hicimos esto para que no siga seleccionada toda la hoja de cálculo cuando la macro deja de ejecutarse. Es necesario agregar estas tres líneas de comando a su nueva macro. Seguirán a la instrucción End With que ya aparece en su macro. Recuerde que puede copiar y pegar estas líneas de comandos. La macro ﬁnal tiene este aspecto:

Macros con Excel 2007

Sub MiHojaConfiguración() ' ' Macro que contiene comandos básicos de ' configuración, incluida la habilitación de ' líneas guía, el cambio de orientación y el ' aumento del ancho de columna a 10 caracteres. ' With ActiveSheet.PageSetup .PrintGridlines = True .Orientation = xlLandscape End With Cells.Select Selection.ColumnWidth = 10 Range("A1").Select End Sub

Es más bien sorprendente mirar todas esas líneas de código en las pequeñas macros que grabamos y ver que sólo necesitamos una cantidad tan pequeña de líneas de código para completar nuestras tareas.

Pruebe su nueva macro Es hora de hundir el acelerador y ver la manera en que corre la nueva macro. Regrese a su área del libro (ALT+F11 lo lleva allí rápidamente) y abra un nuevo libro. Haga clic en la ﬁcha Programador y luego en Macros, y encuentre su nueva macro en la lista. Haga clic en la macro MiHojaConﬁguración y haga clic en Ejecutar. Ahora se aplican todos los cambios a su nuevo libro. Haga una nueva revisión al abrir la cinta de opciones Diseño de página y revisar la orientación y el comando Imprimir de la característica Líneas de cuadrícula. Luego haga clic con el botón derecho en cualquier letra de columna y seleccione Ancho de columna. Verá que ahora el ancho de la columna es 10. Todos sus cambios deben haberse aplicado. Si la macro no funcionó como se planeó, regrese al editor de VB y examine la información precisa que se encuentra en la macro. Debe coincidir con el ejemplo dado en la sección anterior. Haga cualquier cambio necesario.

Elimine macros antiguas No es necesario que conserve todas las pequeñas macros que creó en el proceso para la elaboración de la nueva macro. En realidad, es aconsejable eliminar las macros si existe posibilidad de que haya cambiado algunos elementos (por ejemplo, cortar y pegar texto en lugar de copiar y pegar, lo que aﬁnaría la creación de su nueva macro, pero que evitaría

Capítulo 3

Creación de macros en Visual Basic

que la pequeña macro hiciera el trabajo). El objetivo de crear las pequeñas macros fue simplemente proporcionar elementos de código para nuestra nueva macro. Ya se utilizaron con este fin y no son necesarias. En el editor de VB puede simplemente eliminar el código de la macro antigua al resaltar el código y borrarlo. He aquí otra manera de eliminar una macro. Regrese al libro que contiene las macros. En la ficha Programador, haga clic en el botón Macros. En el cuadro Nombre de la macro (véase la figura 3-8), haga clic en el nombre de la macro que ya no quiere, y luego haga clic en el botón Eliminar. Se le preguntará si quiere eliminar la macro. Haga clic en Sí (véase la figura 3-9), y la macro se eliminará. Por último, puede eliminar macros no deseadas con sólo eliminar el libro que contiene las macros. Si cierra el libro sin guardarla, todo lo asociado con ese libro se eliminará, incluidas sus macros.

Eliminación de macros del Libro de macros personal Mientras seguimos en el tema de eliminar macros, este es un buen momento para mencionar que el proceso de eliminar macros del Libro de macros personal es un poco complicado. Puede abrir la lista de macros, como lo hicimos en el ejemplo anterior, y hacer clic en una macro que esté localizada en el Libro de macros personal, y luego hacer clic en Eliminar, Haga clic para eliminar la macro seleccionada

Seleccione una macro

Figura 3-8

Eliminación de macros no deseadas

Figura 3-9

Haga clic en Sí para eliminar la macro

Macros con Excel 2007

pero eso no lo llevará muy lejos. Aparecerá un mensaje que le indica que está tratando de editar una macro en un libro oculto y que tiene que mostrar el libro. Debido a que el Libro de macros personal está oculto, se ha agregado una capa adicional de protección y su macro es sólo un poco más segura de lo que sería si estuviera en un libro regular. Puede mostrar el Libro de macros personal al seguir estos pasos: 1. Despliegue la cinta de opciones Vista. 2. Haga clic en el botón Mostrar ventana. 3. Elija PERSONAL. 4. Haga clic en Aceptar. Ahora el libro se muestra y puede eliminar macros desde la opción Macros de la cinta de opciones Programador. Cuando haya terminado de eliminar macros, asegúrese de volver a ocultar el Libro de macros personal. Con éste como su ventana activa, haga clic en el botón Ocultar ventana de la cinta de opciones Vista y el libro volverá a ocultarse. Otro método, tal vez más fácil, de eliminar macros en el Libro de macros personal es ir al editor de VB y eliminar el código. No tiene que mostrar/ocultar la hoja de cálculo para hacer los cambios en el editor de VB. Cuando trate de cerrar Excel, se le preguntará si quiere guardar los cambios que ha hecho al Libro de macros personal. ¡Por todos los medios, responda Sí! No sólo se guardará cualquier eliminación que haya hecho, sino también todas las macros que creó durante esta sesión. De otra manera, si cierra Excel y decide no guardar los cambios que ha hecho al Libro de macros personal, ¡es momento de regresar al principio de este capítulo y volver a empezar!

"MNBDFOBNJFOUP EF NBDSPT Antes de que sigamos más allá con el desarrollo de las macros, debemos dominar la manera de guardar y seguir los pasos de nuestras macros. Antes de que se dé cuenta, tendrá docenas de macros y deseará contar con algunas maneras fáciles de encontrar y usar las macros que haya creado.

Macros con Excel 2007

Si está siguiendo este libro desde el principio, habrá creado un puñado de macros que están almacenadas en el Libro de macros personal. Además, creó algunas macros que pertenecían a un libro en particular, y luego esas macros desparecieron cuando decidimos no guardar el libro. Cuenta con algunas opciones diferentes cuando decide dónde guardar sus macros:

Guardar las macros en el Libro de macros personal. Estas macros están disponibles para todos sus libros.

Guardar las macros en el libro activo que está usando cuando crea la macro. Estas macros estarán disponibles para cualquier otro libro, siempre que esté abierto el archivo del libro que contiene las macros y, por supuesto, esas macros siempre estarán disponibles para cualquiera que use el libro en que se encuentran.

Guardar las macros en un libro dedicado a macros. La creación de libros de macros le permite organizar sus macros de la manera que tenga más sentido para usted y sus experiencias en Excel. Puede abrir un libro de macros cada vez que desee usar las macros almacenadas en él.

Dónde almacenar macros Ahora que conoce sus opciones relacionadas con el lugar donde almacenará las macros, puede empezar a pensar cómo desea organizarlas. ¿Quiere mantener las macros en categorías, como macros usadas para formar hojas de cálculo, para organizar datos, para el trabajo, para pasatiempos, y así sucesivamente? ¿O quiere tener ciertas macros disponibles para todos sus proyectos de Excel? ¿Tiene macros que sólo usa cuando está trabajando en una hoja de cálculo particular? La respuesta a estas preguntas le ayudará a decidir dónde guardar sus macros. La decisión del lugar en que se guardará una macro se toma cuando se empieza a crearla. Si está grabando una, se elige una ubicación en el cuadro de diálogo Grabar macro. Si está creando una macro en el editor de VB, debe abrir un módulo del libro donde desea que se almacene la macro e ingresar el código en la ventana de código asociada con ese módulo.

Guarde macros en el Libro de macros personal Casi todas las macros que hemos creado hasta ahora residen en el Libro de macros personal. Estas macros están disponibles cada vez que abre Excel y en todas las hojas de cálculo que esté usando. No tiene que buscarlas ni que abrir ningún archivo en especial para encontrarlas.

Capítulo 4

Almacenamiento de macros

Cuando hace cualquier cambio al Libro de macros personal, ya sea que grabe una nueva macro, edite una existente o cree una nueva en el editor de VB, o que elimine por completo una macro, necesitará guardar sus cambios. Puede guardarlos desde el interior del editor de VB. Asegúrese de que el puntero del ratón esté localizado en algún lugar dentro del Libro de macros personal, ya sea en una ventana de código, en uno de los elementos del Libro de macros personal en la ventana Proyecto o en la ventana Propiedades con un elemento del Libro de macros personal desplegado. Luego elija Archivo | Guardar Personal. XLSB del menú (véase la ﬁgura 4-1). Todos los cambios que haya hecho durante esta sesión se guardarán. Si planea dedicar una gran cantidad de tiempo a realizar tareas en el editor de VB, tiene sentido guardar con frecuencia su libro de esta manera (no sólo el Libro de macros personal, sino cualquier libro en que esté haciendo cambios). Como opción, puede cerrar el editor de VB sin guardar y no se perderá nada. Más adelante, cuando trate de cerrar Excel, verá un mensaje que le pregunta si quiere guardar los cambios que hizo al Libro de macros personal. Al hacer clic Figura 4-1 Guardar el Libro de guardará todo su trabajo. macros personal

Use libros para macros Las macros que se relacionan con tipos particulares de uso de Excel podrían almacenarse mejor en libros dedicados a ese uso. Por ejemplo, cada vez que necesite aplicar formato, puede abrir el libro que contiene las macros de formato. Luego, cuando sea necesario aplicar el formato a otras hojas de cálculo y tendrá acceso a todas las macros que necesite. Una ventaja de almacenar macros en libros separados es que son portátiles. Puede copiar un archivo que contiene sus macros, darlo a alguien más, y sus macros estarán disponibles en la computadora de esa persona. Otra ventaja de almacenar macros en libros individuales es que puede organizar sus macros por tema y evitar poner muchas macros en el Libro de macros personal, lo que dificulta organizarlas todas. Bajo las nuevas reglas de asignación de nombre a archivos para Excel 2007, los libros deben guardarse como archivos habilitados para macros con el fin de aprovechar las macros guardadas en ellos. Los archivos habilitados para macros tienen la extensión XLSM en lugar de la extensión XLSX que se aplica a los libros típicos de Excel. Trate de guardar un libro que contiene macros sin especificar que desea la extensión de archivo XLSM y recibirá un mensaje (véase la figura 4-2) explicando que, al guardar el libro sin habilitarlo para macros, lo guardará como si estuviera libre de macros. Todas las macros asociadas con ese libro se perderán. Cuando guarde un libro habilitado para macros, elija Archivo | Guardar, y aparecerá el cuadro de diálogo Guardar como. Ingrese el nombre de archivo que desee usar, y luego

Macros con Excel 2007

Figura 4-2

Al hacer clic en Sí guardará el archivo sin macros

haga clic en la ﬂecha hacia abajo del campo Guardar como tipo. Elija Libro de Excel habilitado para macros; se aplicará la extensión de archivo que necesita, y sus macros se guardarán y estarán disponibles para uso futuro.

Guarde macros en el libro actual Las macros de uso limitado pueden almacenarse en un libro particular. Por ejemplo, si crea una hoja de cálculo que analiza los datos de ventas ingresados por los vendedores y produce informes utilizando esos datos, tal vez quiera escribir una macro que le permita pedir a los vendedores que ingresen los datos necesarios para esos informes y luego generarlos. Esa macro tendría poco uso fuera de esa hoja de cálculo en particular, de modo que tiene sentido almacenar la macro dentro del libro. Otro ejemplo es el que vimos en el capítulo 3. Creamos macros exclusivamente con el propósito de recolectar material codiﬁcado y no había necesidad de mantenerlas más allá de ese uso limitado, de modo que tenía sentido almacenarlas en la hoja de cálculo actual, y luego las macros que ya no eran necesarias se eliminaron cuando se cerró la hoja de cálculo sin guardarla.

Uso de módulos en VBA De manera muy sencilla, un módulo es el lugar donde reside el código de una macro. Puede considerar a los módulos como hojas de cálculo adicionales; sin embargo, no son visibles a menos que esté en el editor de Visual Basic, donde puede ver los contenidos de todos los módulos asociados con cualquier libro abierto, como también los módulos asociados con el Libro de macros personal. Los nombres de los módulos pueden cambiarse en el editor de Visual Basic, de modo que una vez que empieza a elaborar macros por su cuenta, puede organizarlas al colocarlas en hojas de cálculo de módulo particulares y asignarles nombres a las hojas con nombres que tengan algún signiﬁcado. Si la ventana Propiedades no Para cambiar el nombre de un módulo, haga clic aparece en el editor de VB, elija Ver | Ventana Propiedades, del una vez en él para seleccionarlo, haga doble clic en menú, haga clic en el botón el nombre del módulo en la ventana Propiedades Ventana Propiedades de la barra y luego ingrese un nuevo nombre. Por ejemplo, se de herramientas, u oprima F4. cambió el nombre del módulo que contiene todas las

.&.0

Capítulo 4

Almacenamiento de macros

macros con nombre que se crearon en el capítulo 1 por MacrosNombre (véase la ﬁgura 4-3). Éstas son algunas cosas que puede hacer con los módulos:

Ver el contenido de cualquier módulo al hacer doble clic en el módulo, en la ventana Proyecto.

Insertar un nuevo módulo al elegir Insertar | Módulo, del menú.

Copiar un módulo de un libro a otro al arrastrar el módulo a otro libro en la ventana Proyecto. El módulo copiado tendrá el mismo nombre que el original.

Aquí aparece el nuevo nombre

Cambie aquí el nombre del módulo

Figura 4-3 Cambio de nombre de los módulos

Elaboración de macros que estén disponibles para los demás Ya analizamos la opción de guardar macros dentro de un libro. En ese caso, puede dar éste a alguien más, y esa persona tendrá entonces acceso a las macros. Tome en cuenta que el usuario tendrá que estar de acuerdo con habilitar las macros para usar las que se encuentran en el libro. Para copiar macros a otro libro, necesita tener abiertos en su computadora el libro de destino y el que contiene las macros. (Si las macros están en su Libro de macros personal, ese libro ya está abierto.) Siga esos pasos para copiar una macro a un nuevo libro. Haga clic en la opción Seguridad de macros en la cinta de opciones Programador. Aparece la ventana Centro de confianza (véase la figura 4-4). Observe cuál configuración de macro está habilitada. Elija la opción Habilitar todas las macros (no recomendado; puede ejecutarse código posiblemente peligroso) y luego haga clic en Aceptar. 1. Haga clic en el botón Visual Basic u oprima ALT+F11 para desplegar el editor de VB. 2. En la ventana Explorador de proyectos, encuentre el módulo o los módulos que contienen la macro o las macros que desea copiar.

Macros con Excel 2007

Cambie la conﬁguración de las macros

Figura 4-4

Habilitación de macros

3. Arrastre el módulo o los módulos al libro de destino. 4. Oprima ALT+F11 para regresar a las hojas de cálculo. 5. Abra de nuevo el Centro de conﬁanza (véase el paso 1) y regrese a la conﬁguración de macro que se había usado antes; luego haga clic en Aceptar. 6. Guarde el libro en que copió las macros con una extensión de archivo XLSM.

Protección de sus macros Hay varias razones por las que tal vez quiera salvaguardar sus macros. He aquí las que yo he tenido; tal vez las suyas sean más:

Trabajó mucho para crear su macro: no quiere que nadie más la cambie o, peor aún, juegue con el código para que no funcione nunca más.

La información de su macro es privada: no quiere que otros usuarios conozcan los secretos de la manera como operan sus macros.

Tiene tres opciones para proteger sus macros: 1. Puede hacer que las macros de VBA sean completamente inaccesibles para todos, excepto usted mismo. 2. Puede hacer visibles las macros, pero no permitir que alguien que conozca la contraseña que las protege haga cambios. 3. Puede permitir que sólo las personas que conocen su contraseña tengan derecho de cambiar su código de VBA. La opción de protección se encuentra dentro del editor de VBA. He aquí los pasos a seguir:

Capítulo 4

Marque aquí para restringir la visualización y edición del código de VBA

Almacenamiento de macros

1. Haga clic en el nombre del proyecto en la ventana Proyecto. 2. Haga clic con el botón derecho y elija Propiedades de VBAProject (este comando también está disponible en el menú Herramientas). 3. Haga clic en la ﬁcha Protección de la ventana Propiedades del proyecto que aparece, como se muestra en la ﬁgura 4-5.

Ingrese dos veces la contraseña Figura 4-5

Protección de macros

4. Marque Bloquear proyecto para visualización, si quiere aplicar un bloqueo completo de sus macros de VBA. Nadie podrá editar ni ver sus macros, a menos que posean la contraseña correcta. Ingrese la contraseña en los campos proporcionados y luego haga clic en Aceptar.

5. Como opción, deje la casilla de veriﬁcación sin marcar, pero ingrese una contraseña en los campos proporcionados si quiere que sus macros de VBA estén disponibles sólo para quienes conocen la contraseña. 6. Haga clic en Aceptar para guardar sus opciones.

.&.0 ¡Cuidado! Ya existen muchos métodos abreviados de teclado en Excel. Si elige utilizar uno existente para su macro (como CTRL+N, que se utiliza para activar la característica de Negritas), Excel cambiará el método abreviado predeterminado por el de su macro. El método abreviado ya no funcionará para el comando predeterminado. Consulte la lista de métodos abreviados de teclado existentes en Excel en el cuadro al ﬁnal de este capítulo.

Asignación de teclas de método abreviado a macros Las macros que sólo usa de manera ocasional tal vez no necesiten alguna característica especial de acceso directo. Ya es lo suﬁcientemente fácil hacer clic en el botón Macros de la barra de herramientas Programador, ver las macros disponibles, hacer clic en la que desee y luego en el botón Ejecutar para ejecutarla. Son sólo tres clics, o cuatro si tiene que hacer clic en la ﬁcha Programador (¿necesita ahorrar más tiempo?).

Macros con Excel 2007

.&.0 En el editor de VB hemos visto que asignó un método abreviado de teclado cuando grabó una macro, que ese método abreviado aparece en la sección de comentarios, en la parte superior de la macro, justo abajo del nombre de éste. No puede editar la información del método abreviado de teclado que aparece en el editor de VB y esperar que el método abreviado cambie. Esta información aparece como comentario, no es un comando, de modo que ningún cambio que haga al comentario afectará al comando del método abreviado.

Bueno, al parecer la respuesta es sí, así que Excel tiene una opción que le permite asignar macros a métodos abreviados de teclado. Una vez que haya asignado la combinación de teclas, puede oprimirla en su teclado y la macro se ejecutará, omitiendo por completo el menú Macros. Es muy simple, sobre todo si preﬁere usar más el teclado que el ratón, pero por supuesto el truco está en que ¡tiene que recordar el método abreviado que asignó a la macro! Hay tres maneras de asignar una tecla de método abreviado a una macro:

Grabación de macros Ingrese la combinación de teclas en el cuadro Grabar macros. Como se muestra en la ﬁgura 4-6, hay un campo Método abreviado, y la etiqueta del campo indica que, sin importar lo que ingrese, su método abreviado debe incluir la tecla CTRL. Puede ingresar una letra o un número en el cuadro Método abreviado. También tiene la opción de oprimir la tecla MAYÚS mientras ingresa su letra o número, y luego el método abreviado de la macro será CTRL+MAYÚS+cualquier tecla que ingrese. Sólo puede usar un número o letra para los métodos abreviados de su macro (o un carácter, si está usando la tecla MAYÚS con las teclas numéricas).

Edición de macros Cambie el método abreviado de teclado asociado con una macro al hacer clic, primero, en el botón Macros, de la cinta de opciones Programador. Encuentre la macro cuyo método abreviado quiere cambiar y

.&.0 ¡Recuerde! Los métodos abreviados de teclado siempre empiezan con CTRL y sólo contienen un carácter (una minúscula o un número). Tiene la opción de incluir la tecla MAYÚS después de CTRL, si quiere usar una letra mayúscula o un carácter que sólo se accede con la opción MAYÚS.

Figura 4-6 Asigne métodos abreviados de teclado cuando elija un nombre para su macro

Capítulo 4

haga clic una vez en esa macro. Haga clic en el botón Opciones. Aparece el cuadro de diálogo Opciones de macro (véase la ﬁgura 4-7), mostrando el nombre de la macro, la tecla de método abreviado (si la hay) asociada con esa macro y el campo Descripción. En este cuadro de diálogo, puede cambiar una tecla de método abreviado, borrar una existente o agregar una tecla donde no existía alguna. Haga clic en Aceptar para guardar sus cambios.

Almacenamiento de macros

Figura 4-7 Ingrese, edite o elimine un método abreviado de teclado

Creación de nuevas macros de VBA Cuando crea una nueva macro de VBA empleando el editor de VB, no hay posibilidad de asignar un método abreviado de teclado a la macro. En cambio, debe regresar al punto anterior, donde se indica cómo usar la característica Opciones de macro para asignar un método abreviado a una nueva macro.

.a50%04 "#3&7*"%04 %& 5&$-"%0 &9*45&/5&4 &/ &9$&CTRL+MAYÚS+(

Muestra las ﬁlas ocultas de la selección.

CTRL+MAYÚS+)

Muestra las columnas ocultas de la selección.

CTRL+MAYÚS+&

Aplica el contorno a las celdas seleccionadas.

CTRL+MAYÚS_

Quita el contorno de las celdas seleccionadas.

CTRL+E

Aplica el formato de número General.

CTRL+MAYÚS+$

Aplica el formato Moneda con dos posiciones decimales (los números negativos aparecen entre paréntesis).

CTRL+MAYÚS+%

Aplica el formato Porcentaje sin posiciones decimales.

CTRL+MAYÚS+^

Aplica el formato numérico Exponencial con dos posiciones decimales.

CTRL+MAYÚS+#

Aplica el formato Fecha con el día, mes y año.

CTRL+MAYÚS+@

Aplica el formato Hora con la hora y los minutos e indica AM o PM.

CTRL+MAYÚS+!

Aplica el formato Número con dos posiciones decimales, separador de miles y signo menos (–) para los valores negativos.

CTRL+MAYÚS+*

Selecciona el área actual alrededor de la celda activa (el área de datos delimitada por ﬁlas y columnas en blanco). En una tabla dinámica, selecciona todo el informe de tabla dinámica.

CTRL+MAYÚS+:

Inserta la hora actual.

CTRL+MAYÚS+"

Copia el valor de la celda situada sobre la celda activa, en la celda o en la barra de fórmulas.

CTRL+MAYÚS+SIGNO MÁS( +)

Muestra el cuadro de diálogo Insertar para insertar celdas en blanco.

Macros con Excel 2007

.a50%04 "#3&7*"%04 %& 5&$-"%0 &9*45&/5&4 &/ &9$&- $0/5

CTRL+SIGNO MENOS (â&#x20AC;&#x201C;)

Muestra el cuadro de diĂĄlogo Eliminar para eliminar las celdas seleccionadas.

CTRL+;

Inserta la fecha actual.

ALT+`

Cambia entre mostrar valores de celda y mostrar fĂłrmulas de la hoja de cĂĄlculo.

CTRL+'

Copia una fĂłrmula de la celda situada sobre la celda activa, en la celda o en la barra de fĂłrmulas.

CTRL+1

Muestra el cuadro de diĂĄlogo Formato de celdas.

CTRL+2

Aplica o quita el formato de negritas.

CTRL+3

Aplica o quita el formato de cursiva.

CTRL+4

Aplica o quita el formato de subrayado.

CTRL+5

Aplica o quita el formato de tachado.

CTRL+6

Cambia entre ocultar objetos, mostrarlos o exhibir marcadores de los objetos.

CTRL+8

Muestra u oculta sĂmbolos de esquema.

CTRL+9

Oculta ďŹ las seleccionadas.

CTRL+0

Oculta columnas seleccionadas.

CTRL+E

Selecciona toda la hoja de cĂĄlculo. Si la hoja de cĂĄlculo contiene datos, CTRL+E selecciona la regiĂłn actual. Si oprime CTRL+E una segunda vez, se selecciona la regiĂłn actual y sus ďŹ las de resumen. Oprimiendo CTRL+E por tercera vez, se selecciona toda la hoja de cĂĄlculo. Cuando el punto de inserciĂłn estĂĄ a la derecha de un nombre de funciĂłn en una fĂłrmula, muestra el cuadro de diĂĄlogo Argumentos de funciĂłn. CTRL+MAYĂ&#x161;S+A inserta los parĂŠntesis y nombres de argumento cuando el punto de inserciĂłn estĂĄ a la derecha de un nombre de funciĂłn en una fĂłrmula.

CTRL+N

Aplica o quita el formato de negritas.

CTRL+C

Copia las celdas seleccionadas. CTRL+C seguido de otro CTRL+C muestra el portapapeles.

CTRL+J

Utiliza el comando Rellenar hacia abajo para copiar el contenido y el formato de la celda situada mĂĄs arriba de un rango seleccionado a las celdas de abajo.

CTRL+B

Muestra el cuadro de diĂĄlogo Buscar y reemplazar con la ďŹ cha Buscar seleccionada. MAYĂ&#x161;S+F5 tambiĂŠn muestra esta ďŹ cha, mientras que MAYĂ&#x161;S+F4 repite la Ăşltima acciĂłn de buscar. CTRL+MAYĂ&#x161;S+F abre el cuadro de diĂĄlogo Formato de celdas con la ďŹ cha Fuente seleccionada.

CTRL+I

Muestra el cuadro de diĂĄlogo Ir a.

CapĂtulo 4

Almacenamiento de macros

.a50%04 "#3&7*"%04 %& 5&$-"%0 &9*45&/5&4 &/ &9$&- $0/5

F5 tambiĂŠn

muestra este cuadro de diĂĄlogo.

CTRL+L

Muestra el cuadro de diĂĄlogo Buscar y reemplazar con la ďŹ cha Reemplazar seleccionada.

CTRL+K

Aplica o quita el formato de cursiva.

CTRL+ALT+K

Muestra el cuadro de diĂĄlogo Insertar hipervĂnculo para hipervĂnculos nuevos o el cuadro de diĂĄlogo ModiďŹ car hipervĂnculo para hipervĂnculos existentes seleccionados.

CTRL+U

Crea un nuevo libro en blanco.

CTRL+A

Muestra el cuadro de diĂĄlogo Abrir para abrir o buscar un archivo. CTRL+MAYĂ&#x161;S+O

CTRL+P

selecciona todas las celdas que contienen comentarios.

Muestra el cuadro de diĂĄlogo Imprimir. abre el cuadro de diĂĄlogo formato de celdas con la ďŹ cha fuente seleccionada.

CTRL+MAYĂ&#x161;S+F

CTRL+D

Utiliza el comando Rellenar hacia la derecha para copiar el contenido y el formato de la celda situada mĂĄs a la izquierda de un rango seleccionado a las celdas de la derecha.

CTRL+G

Guarda el archivo activo con el nombre de archivo, la ubicaciĂłn y el formato de archivo actuales.

CTRL+F

Muestra el cuadro de diĂĄlogo Crear tabla.

CTRL+S

Aplica o quita el formato de subrayado. CTRL+MAYĂ&#x161;S+U cambia entre expandir y contraer de la barra de fĂłrmulas.

CTRL+V

Inserta el contenido del Portapapeles en el punto de inserciĂłn y reemplaza cualquier selecciĂłn. Disponible solamente despuĂŠs de haber cortado o copiado un objeto, texto o el contenido de una celda.

CTRL+R

Cierra la ventana del libro seleccionado.

CTRL+X

Corta las celdas seleccionadas.

CTRL+Y

Repite el Ăşltimo comando o acciĂłn, si es posible.

CTRL+Z

Utiliza el comando Deshacer para invertir el Ăşltimo comando o eliminar la Ăşltima entrada que escribiĂł. CTRL+MAYĂ&#x161;S+Z utiliza los comandos deshacer o rehacer para invertir o restaurar la Ăşltima correcciĂłn automĂĄtica cuando se muestran las etiquetas inteligentes de autocorrecciĂłn.

AsignaciĂłn de macros a la barra de herramientas Como ya sabe, el antiguo concepto de personalizar las barras de herramientas en versiones anteriores de Excel ya no existe en 2007. Las barras de herramientas con sus botones modiďŹ cables y los menĂşs con sus comandos intercambiables son del pasado.

Macros con Excel 2007

Bueno, casi. Hay un área de la central de comandos de Excel donde aún tiene cierta flexibilidad, y es la pequeña Figura 4-8 Barra de herramientas Acceso rápido de Excel barra de herramientas Acceso rápido que se encuentra en la parte superior de la pantalla de Excel (véase la figura 4-8). No tiene total flexibilidad con esta barra de herramientas, pero sí cuenta con más libertad que con las cintas de opciones. Haga clic para mostrar el menú

Agregue comandos comunes a la barra de herramientas La barra de herramientas Acceso rápido se personaliza al hacer clic en la flecha hacia abajo que se encuentra a la derecha de la barra de herramientas. Verá un menú corto de algunos de los comandos más familiares de Excel, incluidos Nuevo, Abrir, Guardar, Impresión rápida, así sucesivamente, como se muestra en la figura 4-9. Los comandos con una marca de verificación ya aparecen en su barra de herramientas. Haga clic en cualquiera de los comandos no marcados para agregarlos a la barra.

Marque los elementos para desplegarlos en el menú

Haga clic aquí para acceder a comandos adicionales

Figura 4-9

Personalización de la barra de herramientas Acceso rápido

Agregue comandos adicionales a la barra de herramientas Tiene la opción de personalizar aún más al elegir la opción Más comandos, que aparece en el menú Personalizar. En la ventana Opciones de Excel que aparece (véase la ﬁgura 4-10), haga clic en cualquier comando que aparezca en la lista del lado izquierdo de la ventana y luego haga clic en Agregar para agregar el comando a la barra de herramientas. Uno de

Capítulo 4

Almacenamiento de macros

Haga clic aquí para desplegar los comandos de la barra de herramientas La elección de Ver macros coloca el botón Macros en la barra de herramientas Haga clic para agregar un botón a la barra de herramientas Haga clic para guardar sus cambios

Figura 4-10

Elección de botones para agregar a la barra de herramientas

Elija Macros para desplegar todas las macros en la lista de comandos

Figura 4-11

Despliegue de sus macros

los comandos de la lista es Ver macros. La adición de este comando a la barra de herramientas Acceso rápido le da la capacidad de desplegar la ventana Macros en cualquier momento. Una vez que haya agregado este comando a la barra de herramientas, se ahorra la molestia (si puede llamarle así a hacer un clic) de abrir la cinta de opciones Programador antes de que pueda desplegar la ventana Macros. ¡Pero espere! ¡Hay más! En la parte superior de la lista hay un menú desplegable llamado Comandos disponibles en (véase la figura 4-10). Haga clic en la flecha para desplegar el menú de comandos (véase la figura 4-11) y verá que no sólo tiene acceso a todos los comandos de Excel, cualquiera de ellos puede agregarse a la barra de herramientas Acceso rápido, sino que también puede hacer clic en la opción Macros, y todas las macros que residen en su Libro de macros personal se desplegarán en la lista de

Macros con Excel 2007

comandos. Elija cualquier macro al hacer clic en ella, luego haga clic en Agregar y habrá agregado la macro a la lista de comandos del botón que quiere desplegar en su barra de herramientas.

Agregue macros a la barra de herramientas ¡Pero espere! ¡Todavía hay más! (sueno como en un infomercial, ¿no? ¡Estoy a punto de lanzar un juego de cuchillos sin costo adicional!). Si agrega más de una macro a su barra de herramientas Acceso rápido, verá muy pronto que todas las macros se parecen. Tienen el mismo diseño para sus botones. ¡Esto no sirve! ¡Usted quiere que se distingan entre sí! Por supuesto, puede dejar el aspecto del botón como está y hacer clic en Aceptar. Cuando los botones de macro aparezcan en su barra de herramientas serán iguales, pero puede colocar el cursor sobre el botón y ver el texto informativo que le indica el nombre de la macro. Otra opción consiste en cambiar el aspecto de los botones. De regreso a la ventana Personalizar, haga clic una vez en cualquier comando de su lista, a la derecha de la ventana (en este caso, querrá hacer clic en una de las macros que ha agregado a la barra de herramientas). Luego haga clic en el botón Modificar, que se encuentra en la parte inferior de la lista. Aparece la ventana Modificar botón, como se muestra en la figura 4-12, mostrando casi 200 diseños de botón para que elija uno. Más aún, en el campo Nombre para mostrar, puede cambiar el

Haga clic en una imagen para el botón de esta macro Haga clic en una macro

Ingrese un nombre descriptivo

Figura 4-12

Personalización del botón de una macro especíﬁca

Capítulo 4

Almacenamiento de macros

nombre de algo aburrido como “PERSONAL.XLSB!FórmulaAValor” por “Cambiar fórmulas por valores”. Haga clic en Aceptar para cerrar la ventana después de hacer sus selecciones.

Personalice su barra de herramientas para un libro determinado Algo más, antes de que deje la ventana Opciones de Excel. Arriba de la lista, a la derecha, hay una lista desplegable Personalizar barra de herramientas de acceso rápido. Como opción predeterminada, cualquier cambio que haga en esta ventana afecta la barra de herramientas Acceso rápido de todo Excel; pero si lo preﬁere, puede elegir la otra opción, Para Libro1, donde Libro1 es el nombre de su libro actual. En realidad cuenta con la opción de crear una barra de herramientas Acceso rápido h -" ."/&3" '"$*que pertenezca exclusivamente a un libro. Se trata de una herramienta poderosa para las ocasiones ¡Ya ha visto esta ventana Opciones de Excel! Cuando hace en que cree macros que pertenecen a un solo libro. clic en el botón Opciones de Facilite el acceso a todas las macros desde la barra de Excel, en la parte superior de la herramientas Acceso rápido de ese solo libro. pantalla, aparece esta ventana. Por último, haga clic en Aceptar para guardar sus Haga clic en Personalizar, en la parte izquierda de la ventana, cambios. Ahora puede echar un vistazo a su nueva para acceder a la información de barra de herramientas, completamente personalizada personalización de la barra de para hacer más fáciles sus experiencias en Excel herramientas. (ﬁgura 4-13). Barra de herramientas Acceso rápido personalizada

Figura 4-13 La barra de herramientas personalizada

A $PNQSFOTJPO EF MPT DPNBOEPT EF MBT NBDSPT

En este capítulo verá cómo escribir macros en lugar de simplemente grabarlas. La grabación es un excelente método, pero resulta más fácil hacer algunas cosas a mano, o simplemente no se pueden grabar. Por ejemplo, puede desplegar mensajes para el usuario en la barra de estado de Excel, como se ilustrará en un ejemplo del capítulo 7. Pero no hay manera de grabar esta acción. Debe programarse manualmente al escribir el código. Y recuerde que también hay un tercer método muy común para crear macros. Primero graba y usa el editor para modiﬁcar lo que ha grabado. Este capítulo concluye con una revisión general de los comandos de Visual Basic de uso más común, además de las características básicas más importantes del editor de VB.

Macros con Excel 2007

Escritura de su primera macro Inicie Excel y luego oprima ALT+F11. Verá el editor de VB como se muestra en la ﬁgura 5-1. Como opción predeterminada, se muestran tres ventanas principales en el editor de VB. En la esquina superior izquierda se encuentra el Explorador de proyectos, que despliega también el libro actual, además de las hojas de cálculo que contiene. Debajo se encuentra la ventana Propiedades, donde se muestran los diferentes atributos del objeto seleccionado (en este caso, Hoja1). También puede usar esta ventana para editar las propiedades desplegadas. Por ejemplo, podría hacer clic en la propiedad Nombre y cambiarlo de Hoja1 a Revisión o lo que desee. Cuando oprime ENTER, el nombre cambia en la ventana Propiedades y en el libro de Excel. Observe que no es el mismo que en la propiedad (Nombre) en la parte superior de la ventana Propiedades, que se usa internamente dentro del editor de VB. Lo más importante es que la ventana Propiedades le da una manera conveniente de editar las características de los objetos. Esta ventana es más útil cuando se crean ventanas personalizadas de interacción con el usuario llamadas UserForms (este tema se desarrolla en el capítulo 7). Es en la ventana de código donde usted escribe sus macros. Puede hacer doble clic en cualquier hoja o en Este libro, en el Explorador de proyectos, para abrir una ventana de código de hoja o del libro actual. La ventana pasa de gris a blanco, lo que signiﬁca que ahora puede escribir comandos de programación en ella.

Explorador de proyectos Ventana de código

Ventana Propiedades

Figura 5-1

El editor de VB, mostrando el Explorador de proyectos, la ventana Propiedades y la ventana de código

Capítulo 5

Comprensión de los comandos de las macros

h %0/%& "-."$&/"3 464 ."$304 Excel le permite almacenar macros en cualquier hoja de cálculo, en el libro actual (ThisWorkbook en el Explorador de proyectos) o en módulos. (Los módulos son contenedores convenientes donde puede poner las macros que desee que estén accesibles desde cualquier otra ubicación en un proyecto.) Además, si escribe o graba una macro realmente útil que quiere tener disponible siempre, hay una manera de almacenar macros para usarlas con cualquier libro de Excel que abra, ahora o en el futuro. Para que una macro quede disponible en cualquier lugar y momento, grábela o

escríbala en el Libro de macros personal. Cada vez que Excel inicia, carga este libro especial (es el equivalente al archivo Normal. dotm de Microsoft Ofﬁce Word 2007). Recuerde que cuando hace clic en el botón Grabar macro en Excel, se abre un cuadro de diálogo. En la ﬁgura 5-2 puede ver, en el cuadro de lista Guardar macro en, que una opción es almacenar esta macro grabada en el Libro de macros personal. Al elegir esta opción se agrega este libro y sus macros al Explorador de proyectos. Así que la grabación es una manera rápida de escribir también macros en la Libro de macros personal.

Siga adelante y agregue un módulo ahora. Elija Insertar | Módulo. Ahora se agrega Módulo1 al Explorador de proyectos, y su ventana de código queda disponible para que programe. Ahora puede escribir una macro en la ventana de código, oprimir F5 para probarla y, si es necesario, hacer algunas modificaciones para que funcione de la manera que desea. Imaginemos que para su primera macro hecha de manera manual, quiere crear una etiqueta Figura 5-2 Grabación en el Libro de macros vertical en lugar del típico encabezado de columna. personal en el Explorador de proyectos Tiene un rango de números, como se muestra en la figura 5-3, y quiere describirlos al desplegar una etiqueta a la izquierda. Puede ver un ejemplo en la figura 5-4. En el Módulo1 del editor de VB, escriba esta macro: Sub Girar() With Selection .MergeCells = True .VerticalAlignment = xlCenter .Orientation = 90 .Font.Size = 20 End With End Sub

Macros con Excel 2007

.&.0 La grabación de macros es una estupenda manera de hacer que VB cree código. Pero otro método abreviado consiste en revisar el sistema de ayuda del editor de VB para encontrar ejemplos de código. Tal vez no necesite trabajar desde cero si logra encontrar código de ejemplo que haga lo que busca, o por lo menos algo similar. Sólo copie el código de la pantalla de ayuda, péguelo en una ventana de código en el editor y luego modifíquelo de acuerdo con lo necesario.

Ahora pruébela al arrastrar su puntero para seleccionar de la celda A10 a la A1. Observe que esto incluye, en la celda A1, el título Puntos de junio, como se muestra en la ﬁgura 5-3. Con el rango seleccionado, regrese al editor: oprima ALT+F11, haga clic en cualquier lugar de su macro y luego oprima F5 para ejecutarla. Debe ver el resultado que se muestra en la ﬁgura 5-4.

Entendiendo el código

En esta macro no tiene que seleccionar manualmente las celdas que desea girar (sobre todo en el código). En cambio, permite que el usuario seleccione el rango y luego use el comando With Selection. Este método es obviamente más ﬂexible que un rango rígido (pero, por supuesto, exige que el usuario dé pasos adicionales). La estructura With (entre los comandos With Selection y End With) es práctica si quiere hacer varios cambios a la vez a un rango u otro objeto. No tiene que escribir varias veces el destino (Selection en este caso), como aquí:

Figura 5-3 Quiere crear una macro que gire estas etiquetas para que sean verticales

Capítulo 5

Figura 5-4

Comprensión de los comandos de las macros

Así es como desea que se vea la hoja después de ejecutar su macro

Selection.MergeCells = True Selection.VerticalAlignment = xlCenter Selection.Orientation = 90 Selection.Font.Size = 20

En lugar de esta redundancia, pone el conjunto completo de cambios en una estructura With. Las estructuras With también mejoran la legibilidad de su código, porque puede ver que todos los elementos de la lista de propiedades pertenecen al mismo objeto. Además, observe que sólo he incluido las propiedades necesarias en este código: MergeCells, VerticalAlignment, Orientation y Font.Size. No es necesario especiﬁcar propiedades adicionales (como FontStyle o Underline) porque no estoy cambiando la fuente ni agregando subrayado a este texto.

Limpieza del código Tenía la opción de grabar la macro del ejemplo anterior, en lugar de escribirla a mano. Probemos ese método para ver lo que sucede. Cuando se graba una macro, todas las propiedades del objeto se graban, no sólo las que en realidad se necesitan para la modiﬁcación que se está realizando. Esto signiﬁca que aunque sólo esté cambiando la orientación, la propiedad pertinente y muchas otras también se incluyen en la macro grabada.

Macros con Excel 2007

Veamos cómo funciona esto y cómo corregirlo. Siga estos pasos: 1. Seleccione una celda con algún texto en ella. 2. Haga clic en la ficha Programador de la cinta de opciones, y luego haga clic en el ícono Grabar macro para iniciar el proceso de grabación. 3. Haga clic con el botón derecho en esa celda y elija Formato de celdas en el menú contextual. 4. En el cuadro de diálogo Formato de celdas que se abre, haga clic en la ficha Alineación. 5. Arrastre el indicador de Orientación hasta que marque 90 grados (o ajuste el valor en el cuadro de texto Grados). 6. Haga clic en Aceptar para cerrar el cuadro de diálogo. 7. Haga clic en el ícono Detener grabación, en la sección Código de la cinta de opciones (la ficha Programador debe estar seleccionada). 8. Oprima ALT+F11, y luego abra la macro grabada en el editor de VB. Debe ver un código como éste: With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 90 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With

Ninguna de estas propiedades, excepto una, cambian: la única propiedad que cambió mientras grababa es Orientation. No obstante, la grabadora tomó una instantánea de todas las propiedades posibles. Muchos programadores editarán esta macro grabada. Eliminarán todo, excepto las propiedades importantes, como la que cambió en este ejemplo. Esta eliminación de las propiedades que no cambian facilita, más adelante, la visualización de lo que en realidad está haciendo la macro. Y la limpieza también evita que, por accidente, cambie importantes propiedades de la celda. El único cambio de propiedad necesario es la orientación, de modo que puede eliminar todas las demás líneas:

Capítulo 5

Comprensión de los comandos de las macros

With Selection .Orientation = 90 End With

En nuestro ejemplo anterior (la demostración de cómo escribir una macro a mano) también incluimos algunas modiﬁcaciones de propiedades adicionales: la combinación de las celdas seleccionadas, el centrado del texto y el aumento del tamaño de la fuente. Pero recordará que la sangría, el cambio de línea del texto y otras propiedades se dejaron fuera. No eran necesarios, aunque la grabadora siempre las incluya.

Los elementos de Visual Basic Basic tiene décadas de existencia, y durante varias de ellas fue el lenguaje de programación más popular. Sigue siendo el de uso más fácil, aunque ahora los programadores académicos y profesionales lo han abandonado a favor de C y sus descendientes. ¿Por qué Basic es tan fácil? Porque su objetivo explícito es ser lo más parecido al inglés en su dicción y su sintaxis. En otras palabras, para terminar la ejecución de una macro de Basic, utiliza el comando End. Para concluir una estructura With, utiliza el comando End With, etc. Para comparar dos años de impuestos, usa una “frase” muy legible de Basic como esta: If ImpuestosHoy = ImpuestosPasados Then Goto NoChange

Cuando es posible, Basic trata de usar palabras en inglés y frases similares a las del inglés. ¿Para alguien que habla ese idioma habría algo más comprensible?

Comandos de uso común Echemos un vistazo a algunos de los comandos de Basic más usados, incluidos ejemplos de código que ilustran cómo puede emplear estos comandos en su propia programación de Excel. Esta revisión general de ninguna manera pretende ser exhaustiva, pero le presentará algunos conceptos importantes de programación y le dará ideas Recuerde que si necesita realizar alguna de algunas cosas que puede tarea no descrita aquí, su primer paso debe ser oprimir F1 para abrir la característica de ayuda del editor hacer al codiﬁcar macros a de VB. Por ejemplo, si quiere permitir que su usuario vea mano. el cuadro de diálogo para abrir archivos que está integrado Varios comandos en Excel, busque en la ayuda de VBA algo como acceso a importantes se cubren en archivos. Uno de los temas mostrados es Propiedad FileDialog, y contiene un excelente ejemplo de código que puede pegar otras partes de este libro: en su macro. Si no encuentra algo en la ayuda integrada, elija cuadros de mensajes en el Ayuda | MSDN en Web y busque allí. capítulo 7, variables en el

7¶ODVMP

Macros con Excel 2007

capítulo 9, código de toma de decisiones If...Then en el capítulo 10 y bucles clave. For...Next en el capítulo 11. Sin embargo, lo que sigue en este capítulo es un repaso de varios comandos útiles adicionales de VBA que debe conocer, incluidos varios conjuntos de comandos agrupados en categorías como manipulación de texto, cálculos ﬁnancieros y administración de fechas y horas.

7¶ODVMP

Oprima F1 y luego busque en la ayuda de VBA Manipulación de cadenas, resumen de palabras

Manipulación de texto Cuando trabaja con texto en macros, es probable que el conjunto de comandos de manipulación de texto le resulte muy útil. Por ejemplo, en ocasiones necesita buscar una parte de un texto. Digamos que le pide al usuario que escriba su dirección de correo electrónico en un cuadro de entrada (descritos en el capítulo 7). Una manera de asegurarse de que en realidad ingresa una dirección de correo electrónico apropiada consiste en buscar el símbolo @ en la cadena, que se incluye en todas las direcciones de correo electrónico. Para buscar una subcadena en una cadena se utiliza el comando InStr, como se muestra a continuación: Sub IngresarCorreo() cadDirCorreo = InputBox("Por favor escriba su dirección de correo electrónico") prueba = InStr(cadDirCorreo, "@") If prueba = 0 Then cadDirCorreo = InputBox("Por favor, pruebe de nuevo; no incluyó el simbolo @ en su dirección de correo electrónico") End If End Sub

Cuando ejecuta esta macro, el comando InStr pone un cero en la variable prueba si no se encuentra un símbolo @ en el texto que el usuario ingresa en el cuadro de entrada (este texto se almacena en la variable cadDirCorreo). De este modo podemos probar lo anterior mediante esta línea de código y responde con una segunda solicitud de la dirección si encontramos un cero: If prueba = 0 Then

Tal vez pueda imaginar muchas situaciones en programación donde sería útil analizar una frase o un párrafo para ver si la palabra o frase determinada puede encontrarse dentro del bloque de texto.

Capítulo 5

Comprensión de los comandos de las macros

VBA incluye un grupo grande de comandos de manipulación de texto, incluidos los siguientes:

El comando Mid es similar a InStr, excepto que Mid regresa una subcadena cuando proporciona la posición de inicio y la longitud de la subcadena que busca. Por ejemplo: MsgBox Mid("HolaTodos", 2, 3)

Da como resultado: ola. ¿Lo comprendió?

El comando Replace también está relacionado con InStr, excepto que elimina una cadena de destino e inserta otra en su lugar, como esta: MsgBox Replace("HolaTodos", "Todos", "Mundo")

El resultado es: HolaMundo.

El comando Left extrae una subcadena (también hay un comando Right): MsgBox Left ("Señora López", 6)

Da como resultado: Señora LCase cambia todos los códigos a minúsculas (hay también un comando UCase para mayúsculas): MsgBox LCase("HolaTodos")

El resultado es: holatodos

El comando Format tiene muchas variaciones (argumentos), y le permite gran libertad sobre la manera de desplegar texto, fechas, resultados ﬁnancieros, etc. Oprima F1 y busque ayuda en Manipulación de cadenas, resumen de palabras clave. Len le indica el número de caracteres (la longitud de una cadena): MsgBox Len("HolaTodos")

El resultado es: 9

Fecha y hora Cuando necesita emplear información de hora o fecha en una macro, VBA le ofrece un conjunto de comandos útiles. He aquí algunos ejemplos: Sub FechaHora() MsgBox Now MsgBox Date MsgBox Time MsgBox Day(Now) MsgBox Month(Now) MsgBox Hour(Now) MsgBox Minute(Now) End Sub

Macros con Excel 2007

Si ejecuta esta macro, verá una serie de datos de fecha y hora desplegados. VBA incluso tiene algunos comandos que realizan cálculos de hora, como el comando DateDiff, que le indica cuántos días hay entre hoy y una fecha futura, como esta:

7¶ODVMP

Para ver los diferentes comandos de fecha y hora en VBA, oprima F1 y luego busque Fecha y hora, resumen de palabras clave.

Sub DiferenciaFechas() FechaFutura = "12/12/2012" MsgBox "Días a partir de hoy: " & DateDiff("d", Now, FechaFutura) End Sub

Matemáticas Todas las operaciones comunes, y no tan comunes, están disponibles en VBA. Tiene + para sumas, – para restas y * para multiplicaciones. El símbolo de la división es la diagonal (/): MsgBox 5 / 4

El resultado es: 1.25 Sin embargo, sólo por si acaso, hay otra forma de división que usa la diagonal invertida (\), llamada división de enteros. Todo esto elimina cualquier posición decimal de la respuesta: MsgBox 5 \ 4

El resultado es: 1 Si alguna vez ha encontrado un uso para eso, hágamelo saber. Observe que esto no redondea el número; lo trunca. Por ejemplo, 5 \ 3 también da 1 como resultado, aunque redondeado sería 2. Si realmente quiere redondear use el comando Round, y especiﬁque el número de posiciones decimales que quiere. Aquí queremos tres posiciones decimales: MsgBox Round(5 / 3, 3)

El resultado es: 1.667 Más allá de estos operadores, también encontrará algunas funciones matemáticas (busque Ayuda para Funciones matemáticas). Y para todas las ocasiones en que tiene que calcular una secante hiperbólica inversa, no se preocupe, está allí.

Capítulo 5

.&.0

Comprensión de los comandos de las macros

Cálculos ﬁnancieros

Observe que el comando MsgBox no requiere el uso de paréntesis:

VBA incluye un conjunto de comandos que proporcionan algunas de las características de MsgBox Date una calculadora financiera. Los contadores y funciona igual que otras personas relacionadas con las matemáticas MsgBox (Date) empresariales pueden usar estos comandos para Sin embargo, si está construir herramientas de cálculo financiero. Y proporcionando una lista de argumentos a un comando debido a que las macros son muy flexibles, puede (consulte el resumen del capítulo personalizar sus cálculos mucho más de lo que es 7), debe usar paréntesis, como posible aun con las calculadoras más costosas. este argumento Now proporciona al comando Hour: He aquí un ejemplo que muestra cómo encontrar Hour (Now) el monto total de interés que pagará por una hipoteca durante la vida del préstamo. Usted proporciona la siguiente información a la macro: tasai, rangodepago, periodostotales, valoractual, valorfuturo, vencido. Luego el comando IPmt le puede devolver el interés total que pagará durante la vida de ese préstamo. He aquí cómo funciona: tasai es la tasa de interés de su préstamo y debe expresarse como la tasa mensual porque sus pagos serán mensuales. Debido a que probablemente conocerá el interés anual, debe dividirlo entre 12. Nuestra tasa es 6%, de modo que la cifra de la tasa debe ser .06 (la tasa de interés) / 12 (los meses del año). La tasa resultante es .005. El rangodepago es la cantidad del tiempo total del préstamo del que quiere calcular el interés. Usaremos un bucle For...Next para este cálculo, de modo que la variable rangodepago cambie dinámicamente cuando estemos calculando, recorriendo la vida total del préstamo. Los periodostotales son el número de veces que paga la hipoteca en la vida del préstamo. La nuestra es una hipoteca a 15 años, y pagamos mensualmente. De modo que periodostotales es 15 * 12 (lo que da como resultado 180). El valorpresente representa el monto total de su préstamo. Nuestra casa cuesta $50 000 (es un valor máximo), pero debe expresar este número como negativo, de modo que es –50000. El valorfuturo es el saldo que quiere tener al final de la hipoteca. Para préstamos, el valorfuturo es cero. El valor vencido es 1 ó 0. Vale 1 si los pagos están Al igual que con los comandos de fechas, vencidos al principio de también encontrará un conjunto generoso cada mes. Vale 0 si están de comandos financieros en VBA. Oprima F1 y busque vencidos al final del mes. Financieras, resumen de palabras clave. (En ocasiones, palabras Pagamos al final, de modo clave se utiliza como sinónimo de la palabra comando.) que vencido es 0.

7¶ODVMP

Macros con Excel 2007

Ahora que hemos respondido estas preguntas, podemos usar la siguiente macro para calcular el interés: Private Sub Interés() tasai = 0.005 periodostotales = 180 valorpresente = -50000 valorfuturo = 0 vencido = 0 For rangodepago = 1 To periodostotales interésTemp = IPmt(tasai, rangodepago, periodostotales, valorpresente, valorfuturo, vencido) interésTotal = interésTotal + interésTemp Next rangodepago MsgBox "El total que pagará por este préstamo es: " & Format(interésTotal + Abs(valorpresente), "###,###,##0.00") MsgBox "De esto, los intereses son: " & Format(interésTotal, "###,###,##0.00") End Sub

Cuando ejecuta esta macro, le indica que el total que pagará por el préstamo es de $75 947.11. Y de eso, $25 947.11 corresponde a intereses. No se alarme por el código. Es fácil comprender el comando Format, por ejemplo, cuando busca en la ayuda de VBA. Sólo haga clic en la palabra Format en su código de macro para poner el cursor de inserción parpadeante en él, luego oprima F1 y verá muchas docenas de ejemplos de su uso. Para comprender la manera de emplear los diversos comandos ﬁnancieros en VBA, también encontrará gran cantidad de ejemplos de código para ellos.

Error En ocasiones querrá colocar un manejador de errores en sus macros. De esta forma, si algo sale mal, su macro no detendrá misteriosamente la ejecución, o atemorizará de otra manera al usuario. En cambio, puede desplegar un mensaje que explique lo que sucedió y lo que puede hacer el usuario al respecto. He aquí una estructura típica de manejo de errores. En primer lugar, le debe indicar a VBA adónde ir: un lugar en la macro donde puso la etiqueta Mostrarlo, si ocurre un error: On Error GoTo Mostrarlo

Puede usar el nombre que quiera en lugar de Mostrarlo; es sólo un destino en que la macro empieza a ejecutar el código si ocurre un error.

Capítulo 5

Comprensión de los comandos de las macros

A continuación tenemos una línea que induce un error falso (Error 70) para que podamos probar el manejador de errores. Esta línea se reemplazará por el código actual de su macro. Generalmente, aparece el comando Exit Sub justo sobre cualquier manejador de código. Esto evita que el programa alcance al manejador, a menos que en realidad un error nos envíe a éste. Luego la sección Mostrarlo imprime el mensaje de error, en este caso Permiso denegado (porque indujimos el error 70). Por último, el comando VB Resume Next se usa para enviar VB de regreso a la línea que sigue a On Error GoTo: Sub Errors () On Error GoTo showit Error 70 Exit Sub Showit: MsgBox Error (Err) Resume Next End Sub

Este código despliega la descripción del error en un cuadro de mensaje para el usuario. Sin embargo, antes de que termine su macro y la dé a otros, tal vez quiera reemplazar el mensaje integrado por uno propio, más útil, más descriptivo, como: MsgBox ("Consulte al administrador de la red. No tiene permiso de seguridad para hacer esto.")

h '".*-*"3*$&4& $0/ &- &%*503 %& 7# Desarrollado durante décadas de retroalimentación por parte del usuario, grupos de enfoque y reﬁnamiento, el editor de VB es una gema pulida. No importa cuántos años dedique, ni qué tan a fondo vaya en la programación con VBA, dudo que siquiera llegue a desear una característica en particular. Posee todo lo que un programador necesita. Sin embargo, los principiantes pueden tener un buen inicio al conocer de antemano varias características importantes. No vale la pena tropezarse con ellas. Sólo le daré lo esencial en este resumen.

Los menús Archivo y Edición Los menús Archivo y Edición contienen casi todas las herramientas esenciales que encontraría en un procesador de palabras. Y cualquier editor para el programador es un procesador de palabras, aunque bastante especializado. El menú Edición incluye las usuales opciones de búsqueda, corte, copia, pegado y otras características de manipulación de texto, pero también engloba opciones especíﬁcas para la programación. Haga clic en un comando como MsgBox en el editor de código, para seleccionarlo, y luego pruebe a elegir Edición | Información rápida para ver la sintaxis de este comando.

Macros con Excel 2007

h '".*-*"3*$&4& $0/ &- &%*503 %& 7# $0/5

El menĂş Ver En el menĂş Ver puede elegir que se muestren las importantes ventanas Explorador de proyectos o Propiedades, si las ha cerrado antes. TambiĂŠn puede desplegar la Caja de herramientas, si estĂĄ trabajando en un UserForm (consulte el capĂtulo 7).

El menĂş Formato El menĂş Formato contiene diversas opciones, casi todas Ăştiles cuando estĂĄ optimizando un UserForm.

El menĂş DepuraciĂłn En el menĂş Depurar encontrarĂĄ todo tipo de caracterĂsticas Ăştiles que pueden ayudarle a rastrear errores elusivos en su cĂłdigo.

El menĂş Ejecutar En el menĂş Ejecutar, la opciĂłn mĂĄs Ăştil aquĂ para los principiantes es Ejecutar | Restablecer. Haga clic en ella si alguna vez ha visto el mensaje mostrado en la ilustraciĂłn.

Los principiantes se sienten abrumados con este mensaje de error. Se despliega cuando ejecuta una macro que tiene un error, y el editor de VB ingresa automĂĄticamente en el â&#x20AC;&#x153;modo de interrupciĂłnâ&#x20AC;?, una condiciĂłn especial donde se detiene la ejecuciĂłn de la macro. Sin embargo, tambiĂŠn sucede algo mĂĄs: la ejecuciĂłn adicional de esa macro o cualquiera otra no es posible ahora en este modo (los programadores usan varias herramientas en el menĂş DepuraciĂłn durante el modo de interrupciĂłn). Pero con frecuencia desearĂĄ salir de este modo y regresar al modo normal. Para salir de este modo, elija Ejecutar | Restablecer. Eso es todo. No he cubierto cada opciĂłn ni, por supuesto, cada menĂş. Algunos se explican por sĂ solos, como la opciĂłn Ventana | Cascada. Si no sabe lo que signiďŹ ca esto en un procesador de palabras, pruĂŠbelo y vea.

6TP EF TVCSVUJOBT Z DSFBDJ·O EF GVODJPOFT EF 7JTVBM #BTJD

Una subrutina es como una pequeña macro que opera dentro de una macro más grande. El código de la subrutina reside dentro de la macro principal. Cuando una macro llama a una subrutina, la ejecución se desvía al área de la subrutina de la macro. Un uso común para las subrutinas consiste en dividir una macro compleja en partes más pequeñas. Además, las subrutinas pueden ser útiles en macros que proporcionan una opción al usuario. Si selecciona la opción A, la macro se desvía a la subrutina A. Seleccione la opción B y entra en acción la subrutina B. Por ejemplo, digamos que ha deﬁnido dos áreas de impresión en la pantalla. La macro pregunta al usuario que indique cuál área se debe imprimir. La elección del área de impresión 1 hace que la macro se desvíe a la subrutina Impresión1. La elección del área de impresión 2 hace que la macro se desvíe a la subrutina Impresión2. En este escenario, cuando se ha completado o cancelado el trabajo de impresión, termina la ejecución de la macro. La elección de una tercera opción, Cancelar, hace que la macro siga sin imprimirse o, en este caso, llegue al ﬁnal.

Macros con Excel 2007

Como opción, si una función dentro de su macro produce un conjunto de resultados, se ejecuta una subrutina; otro conjunto de resultados hace que se ejecute una subrutina diferente. Por ejemplo, la macro examina una celda que contiene un número. Si éste es menor de 1 000, la celda se redondea a 1 000. Si el número es mayor, no hay cambio en el contenido de la celda.

Creación de una subrutina Debido a que una subrutina se ejecuta dentro de una macro existente, es necesario que haya una manera de separar los comandos de la subrutina del resto de la macro. Esto se hace al asignar un nombre a la subrutina. La macro llama entonces a la subrutina por su nombre con el comando GoTo. La propia subrutina empieza con su nombre, como primera línea, seguido por las líneas de comandos asociadas con la subrutina, y ésta termina con un comando End. Como alternativa, puede llamar a la subrutina con el comando GoSub. Cuando use GoSub, la operación regresa al punto de partida en la macro cuando la subrutina termina. He aquí un ejemplo. Digamos que ha designado dos áreas de su hoja de cálculo como áreas de impresión diferentes, y ha asignado nombres de rango a estas áreas, ÁreaImpresión1 y ÁreaImpresión2. Quiere crear una macro que le pregunte al usuario si quiere imprimir lo que conoce como Informe 1 (que es igual a ÁreaImpresión1) o Informe 2 (que es igual a ÁreaImpresión2). Su respuesta se almacena en la macro como 1 o 2. Si responde 1, la macro ejecuta una subrutina llamada Impresión1. Si responde 2, la macro ejecuta una subrutina llamada Impresión2. Si el usuario responde algo diferente de 1 o 2, la pregunta aparece de nuevo y tiene otra oportunidad de responder. También se trata a la pregunta como una subrutina, de modo que se le puede llamar, si es necesario.

Asignación de nombre a una subrutina Designe el nombre de una subrutina ingresando el que desee, seguido de dos puntos. Use sólo una palabra para el nombre de la subrutina. Para este ejemplo, se llamará de la siguiente manera a las tres subrutinas:

.&.0 Trate de evitar la asignación de nombres a subrutinas que sean iguales a un comando real de VBA. Esto se facilitará más a medida que se familiarice con VBA.

Respuesta: Impresión1: Impresión2: A estos nombres se les denomina etiquetas.

Capítulo 6

Uso de subrutinas y creación de funciones de Visual Basic

Llamado a las subrutinas La primera subrutina necesaria para esta macro realiza las tareas de plantear la pregunta al usuario. Este código es muy simple: utiliza el concepto de un cuadro de entrada con un campo disponible para que el usuario ingrese su respuesta. Los cuadros de entrada se analizan con más detalle en el capítulo 7. Por ahora sólo usaremos el código InputBox con una descripción mínima. El código empieza con una variable que se asigna al valor del InputBox. Así que cada vez que el usuario ingrese su respuesta (en este caso 1 o 2), ese valor se asignará a la variable, que llamaremos Informe. He aquí la línea de código: Informe = InputBox("Ingrese 1 para imprimir Informe 1; Ingrese 2 para imprimir Informe 2")

La información que aparece entre comillas es el texto que se mostrará en el cuadro de entrada que el usuario verá. Su entrada de 1 o 2 se convierte en el valor de la variable, Informe. Una vez que Informe tiene un valor, la macro puede seguir adelante al determinar cuál subrutina ejecutar, con base en el valor de Informe. Su macro establecerá eso: si el valor de Informe es 1, entonces se ejecutará la subrutina Impresión1. Si el valor de Informe es 2, entonces se ejecutará la subrutina Impresión2. Si Informe tiene cualquier otro valor, vuelve a aparecer la pregunta. El código es muy sencillo: If Informe = 1 Then GoTo Impresión1 ElseIf Informe = 2 Then GoTo Impresión2 Else GoTo Pregunta End If

Con el uso del concepto de un comando If/Then/Else, la macro permite al usuario tomar una elección inteligente. Aprenderemos más acerca de las rutinas If/Then/Else en el capítulo 10, pero como puede ver, el concepto es muy fácil de comprender.

Escritura de subrutinas Las subrutinas de este ejemplo son comandos de impresión de VBA. Puede encender su grabadora de macros y grabar la impresión de un área para recopilar el código. Antes de grabar la macro, asigne al área de impresión el nombre ÁreaImpresión1 y ÁreaImpresión2 (seleccione el área, haga clic en el Cuadro de nombres, escriba el nombre y oprima ENTER). Luego, con la grabadora de macros encendida, haga clic en la ﬂecha hacia abajo

Macros con Excel 2007

en el Cuadro de nombres (véase la ﬁgura 6-1), elija ÁreaImpresión1 para seleccionar el área, y luego elija Botón de Ofﬁce | Imprimir | Selección | Aceptar. Apague la grabadora y encontrará este código en su editor de VB. Application.Goto Reference:="ÁreaImpresión1" ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,1,,,TRUE,,FALSE)"

Este es todo el código que necesita para su subrutina, por lo que la subrutina Impresión1 tendrá este aspecto: Impresión1: Application.Goto Reference:="ÁreaImpresión1" ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,1,,,TRUE,,FALSE)" End

La subrutina Impresión2 será idéntica a la subrutina Impresión1, con excepción de que se sustituye Impresión2 y ÁreaImpresión2. La última subrutina que necesitamos crear para que esto funcione es el área Pregunta de la macro, que utiliza el código ImputBox analizado antes, además de la instrucción If/Then/Else. En primer lugar, la macro le planteará al usuario la pregunta, y luego ejecutará los comandos de impresión apropiados: Sub ImpresiónEspecial() Pregunta: Informe = InputBox("Ingrese 1 para imprimir Informe 1; Ingrese 2 para imprimir Informe 2") If Informe = 1 Then GoTo Impresión1 ElseIf Informe = 2 Then GoTo Impresión2 Else GoTo Pregunta End If Impresión1: Application.Goto Reference:="ÁreaImpresión1"

Cuadro de nombre

Figura 6-1 Asignación de nombres de rango a áreas de impresión

Capítulo 6

Uso de subrutinas y creación de funciones de Visual Basic

ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,1,,,TRUE,,FALSE)" End Impresión2: Application.Goto Reference:="ÁreaImpresión2" ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,1,,,TRUE,,FALSE)" End End Sub

En la ﬁgura 6-2 se muestra el aspecto que tendrá la operación de la macro para el usuario. Si realmente quiere ser creativo y hacer que esta macro sea más accesible para el usuario, de modo que no tenga que buscarla en el menú Macros, puede colocar la orden para la macro en un botón y colocar dicho botón en la hoja de cálculo real. En el capítulo 12 se analiza cómo funciona este proceso.

El usuario ingresa 1 o 2 aquí

Figura 6-2

Llamada a una subrutina

Ejecución de macros como subrutinas Ha visto cómo podemos insertar subrutinas en una macro y llamar esos fragmentos de código, según sea necesario. También puede hacer que una macro llame a otra como si fuera una subrutina, porque en realidad todas las macros son técnicamente subprocedimientos para el uso de los comandos Sub, End Sub al empezar y terminar cada macro. Una vez que se crea una macro, su nombre sirve como un comando. Por lo tanto, puede designarse a la macro ObténMiNombre para que llame a la macro NOMBRE1 creada en el capítulo 1. Sub ObténMiNombre() NOMBRE1 End Sub

Aunque este ejemplo en particular podría parecer inútil (la creación de una nueva macro con el propósito de llamar a una macro existente), puede visualizar la utilidad de este proceso si piensa crear muchas macros diferentes y llamarlas cuando las necesite

Macros con Excel 2007

en el curso de una nueva macro. Por ejemplo, en el capítulo 1 creamos una macro que aplica varios cambios de formato a una hoja de cálculo. Es muy posible que la colocación de muchos comandos grabados en una sola macro podría agobiar al programador novato de macros, sobre todo si la persona que grabó los comandos cometió errores y luego los corrigió, o si eligió agregar diferentes características de formato. La macro puede crecer mucho y ser difícil de descifrar y depurar, en caso de que no se ejecute como está planeado. En cambio, mediante el uso de la técnica de llamar a otras macros que se muestra aquí, podría crear varias pequeñas macros de formato, asegurarse de que cada una haga su tarea de manera apropiada, y luego crear una macro que llame a las diversas piezas del proyecto general de formato, de una en una. Por ejemplo, tome una macro llamada ENCABEZADOS que cree y forme los encabezados de una hoja de cálculo, una macro llamada COLUMNAS que asigne el ancho de columna y numere códigos de formación, una macro llamada FILAS que forme los títulos de la ﬁla y una macro llamada TOTALES que coloque totales en la parte inferior de cada columna de números. Las cuatro macros pueden combinarse en una macro ConﬁguraciónInformeMensual como ésta: Sub ConfiguraciónInformeMensual ENCABEZADOS COLUMNAS FILAS TOTALES End Sub

Echaremos un vistazo a la formación de macros con mayor profundidad en el capítulo 8 y analizaremos este proceso de ejecución de macros como subrutinas en ese momento. Con el uso de las habilidades que aprendimos en el capítulo 7, puede incluso personalizar el formato aún más, al preguntar al usuario, con la ayuda de un cuadro de entrada, cuáles características de formato le gustaría utilizar, y luego usar las subrutinas para llamar a las diversas macros de formato, según se necesiten.

Creación de una función personalizada De manera similar a la creación de macros, puede usar el editor de VB para crear sus propias funciones personalizadas. Las funciones que cree se agregan a la lista de funciones de Excel, de modo que puede llamarlas con la misma facilidad de la función PROMEDIO o CUENTA. Esto ahorra mucho tiempo si necesita ejecutar cálculos complicados. No sólo puede condensar sus cálculos en una función simple, sino que dicha función puede quedar disponible para las demás hojas de cálculo de Excel. Otro uso para las funciones personalizadas es proteger los detalles de un cálculo.

Capítulo 6

Ingresos gravables Mayores de $

Uso de subrutinas y creación de funciones de Visual Basic

Menores de $

50 000

Tasa impositiva 15%

Use una función para facilitar un cálculo complicado

Suponga que necesita calcular de manera regular los 75 000 100 000 34% impuestos sobre la renta de una 100 000 335 000 39% corporación. Los impuestos 335 000 10 000 000 34% corporativos se calculan en 10 000 000 15 000 000 35% diferentes niveles, y la tasa 15 000 000 18 333 333 38% impositiva cambia a medida 18 333 333 .......... 35% que aumentan los ingresos de la corporación contribuyente. Tabla 6-1 Tasas impositivas de ingreso gravable empresarial de 2008 Así, por ejemplo, una corporación con ingresos gravables de $100 000 paga una tasa de 15% sobre los primeros $50 000, 25% sobre los siguientes $25 000 y 34% sobre el resto. Los impuestos sobre la renta corporativas para 2008 se muestran en la tabla 6-1. Es posible escribir una fórmula IF anidada que calcule estos impuestos, sin importar el nivel de ingresos, pero no hay duda de que será una fórmula complicada y difícil de reconstruir si tiene que usarla con frecuencia. En cambio, puede crear una función que haga el cálculo por usted. Con esto no tendrá que preocuparse nunca más de crear una fórmula de nuevo, pero puede compartir la función con alguien más que necesite calcular los impuestos corporativos. Para crear la nueva función, a la que llamaremos ImpCorp, abra el editor de VB. Haremos que quede accesible para todas las hojas de cálculo, de modo que abra un nuevo módulo en el Libro de macros personal (haga clic en VBAProject (PERSONAL.XLSB) y luego elija Insertar | Módulo). La primera línea de una función es diferente de un macro, aunque el proceso de creación sea básicamente el mismo. En lugar de Sub nombremacro, ingresará Function nombrefunción, o en este caso: 50 000

75 000

.&.0 La palabra Function le indica a Excel que ésta es una función. Llamar a ésta una función en lugar de un macro permite que Excel agregue la función a la lista maestra de funciones.

25%

Function ImpCorp

Además, como recordará, los nombres de las macros van seguidas por un conjunto de paréntesis. Necesitamos eso también para el nombre de la función. En este caso, vamos a colocar una palabra dentro de los paréntesis: Ingresos. Ingresos será

Macros con Excel 2007

la variable que se necesita para esta función (la función realiza cálculos sobre la cantidad de ingresos). Al colocar Ingresos entre paréntesis hará que esta función nos pida que identiﬁquemos cuál celda contiene los ingresos para los objetivos de los cálculos de ImpCorp. Así que la primera línea completa de nuestra función será: Function ImpCorp(Ingresos)

Ahora estamos listos para ingresar el código de esta función. Vamos a usar la instrucción de VBA Select Case, un procedimiento que es similar a la instrucción IF. La estructura Select Case le permite ordenar que una acción sólo se realice si se cumple cierta condición. En esta situación, estableceremos el Case igual a la cantidad especiﬁcada como Ingresos. Una vez que establecemos que estamos funcionando con la cantidad de Ingresos, podemos presentar la lista de criterios establecidos en la lista de impuestos de la tabla 6-1. He aquí su aspecto: Function ImpCorp(Ingresos) Select Case Ingresos Case Is > 18333333: ImpCorp = Ingresos * 0.35 Case Is > 15000000: ImpCorp = 515000 + (Ingresos - 15000000) * 0.38 Case Is > 10000000: ImpCorp = 3400000 + (Ingresos - 10000000) * 0.35 Case Is > 335000: ImpCorp = 113900 + (Ingresos - 33500) * 0.34 Case Is > 100000: ImpCorp = 22250 + (Ingresos - 100000) * 0.39 Case Is > 75000: ImpCorp = 13750 + (Ingresos - 75000) * 0.34 Case Is > 50000: ImpCorp = 7500 + (Ingresos - 1500) * 0.25 Case Is > 0: ImpCorp = Ingresos * 0.15 End Select End Function

.&.0 Si está confundido sobre la manera en que decidimos dónde van los paréntesis en las fórmulas que aparecen en esta función, consulte la sección “Operadores aritméticos en orden de preferencia”, en el capítulo 9.

h -" ."/&3" '"$*Guarde rápidamente sus cambios en el editor de VB, al oprimir CTRL+G.

La manera en que este procedimiento de caso funciona es que, en cuanto se cumplan los criterios del caso, se realiza el cálculo, y la función se detiene. Si los ingresos de la corporación son de $15 000 001, se examinará el primer criterio de caso, y como los ingresos no son > $18 333 333, esto será rechazado y la función pasará al segundo criterio. Debido a la aplicación de este criterio, el cálculo se realiza en este punto de la función, y las opciones de caso restantes se ignoran. Si los ingresos de la corporación son de $10 000, la función pasará a cada uno de los escenarios posibles, rechazando cada uno, hasta que el escenario ﬁnal Case Is > 0 se cumpla, y se calculen los impuestos.

Capítulo 6

Uso de subrutinas y creación de funciones de Visual Basic

Una vez que ingrese la función en el editor de VB, elija Archivo | Guardar PERSONAL. XLSB, y está listo para probar esta función. De regreso a la hoja de cálculo, ingrese algunos números en una columna. Estos números representan los ingresos gravables de algunas corporaciones. Siga estos pasos para ejecutar su nueva función:

Aquí aparecerá el cálculo de sus impuestos

1. Haga clic en la celda donde quiere que aparezca el primer cálculo de impuestos (véase la ﬁgura 6-3). 2. Haga clic en la ﬁcha Fórmulas.

Figura 6-3

Ingresos gravables que necesitan cálculo de impuestos

Elija Todas Haga clic en su función

Figura 6-4

Búsqueda de su nueva función

3. Haga clic en el botón Insertar función, en la cinta de opciones. 4. En el cuadro de diálogo Insertar función (véase la ﬁgura 6-4), seleccione la categoría Todas. 5. Recorra la lista alfabética hasta que encuentre su función ImpCorp. Estará alfabetizada bajo la “I”, pero aparecerá como PERSONAL. XLSB!ImpCorp. 6. Haga clic en Aceptar.

7. Haga clic en el cuadro identificador de celdas en la ventana Argumentos de función, luego en la celda que contiene los ingresos gravables y, al final haga clic, en el botón rojo para regresar a la ventana Argumentos de función. Como opción, puede ingresar la referencia a celda para la celda de ingresos gravables en el campo Ingresos de la Recuerde que su nueva función ventana Argumentos de función. Observe que se llama en realidad PERSONAL. el cálculo de impuestos aparece ahora en esta XLSB!ImpCorp. Si trata de ingresar ventana. Véase la figura 6-5. ImpCorp como nombre de la

.&.0

8. Haga clic en Aceptar para colocar el cálculo de impuestos en su hoja de cálculo.

función en su hoja de cálculo, obtendrá un mensaje de error.

Macros con Excel 2007

Aquí aparecen los cálculos

Figura 6-5

Ingrese aquí la referencia a celda

Haga clic aquí para usar su ratón para identiﬁcar la referencia a celda

Uso de la función ImpCorp

9. Observe que ahora puede copiar esta fórmula a otras celdas de su hoja de cálculo sin tener que abrir de nuevo la ventana Insertar función.

Use una función personalizada para ocultar datos confidenciales En lugar de planear una fórmula en una hoja de cálculo donde todos pueden verla, puede crear una función que viva en el editor de VB, donde sólo ojos autorizados pueden ver el proceso de cálculo. Por ejemplo, digamos que es hora de calcular las bonificaciones para los empleados y que el cálculo de éstas es una fórmula secreta. Empezaremos con una hoja de cálculo que presente una lista de los empleados y los ingresos trimestrales que generaron. En esta empresa ficticia, las ventas del segundo trimestre suelen ser más altas debido a la naturaleza estacional del negocio. Por lo anterior, los propietarios de la empresa desean recompensar a sus vendedores con una bonificación más elevada para las ventas que excedan la cuota en los trimestres 1, 3 y 4, y una menor para el trimestre 2. He aquí la fórmula secreta: Ventas T1 * .1% Ventas T2 * 0.05% Ventas T3 * 0.08% Ventas T4 * 0.075% He aquí la función que los propietarios de la empresa desean crear: Function Bono (T1, T2, T3, T4) Bono = T1 * .001 + T2 * .0005 + T3 * .0008 + T4 * .00075 End Function

Capítulo 6

Uso de subrutinas y creación de funciones de Visual Basic

Cuando esta función se ingresa en un módulo del Libro de macros personal y se guarda, se une a las demás en la lista de funciones y está accesible para cualquier macro, pero sin revelar la fórmula de cálculo. Aplique la función al hacer clic en la celda donde quiere que aparezca la bonificación, elegir Bono de la lista de Funciones (véase la figura 6-6) y luego ingresar las referencias a celda para los montos T1, T2, T3 y T4. Una vez ingresada una bonificación, la función puede copiarse a otras celdas. Observará que en la barra de fórmulas sólo se muestra el nombre de la función y las referencias a celdas, pero no la información de cálculos. Véase la figura 6-7.

Ingrese aquí la referencia a celda

Figura 6-6

Ingrese referencias a celda como argumentos de función No aparece aquí información del cálculo

Figura 6-7

Ocultamiento de los detalles de cálculo en funciones

$SFBDJ·O EF NBDSPT JOUFSBDUJWBT Como ha visto en capítulos anteriores, muchas macros pueden ejecutarse por sí mismas, sin necesidad de ninguna entrada por parte del usuario. Por ejemplo, si crea una macro que ingresa sus iniciales en la celda de la esquina superior izquierda, tendría este aspecto:

Sub InsertarIniciales() Range("A1").Select ActiveCell.FormulaR1C1 = "GP" End Sub

Sin embargo, otras macros requieren que detenga la ejecución de la macro y pida al usuario alguna entrada. ¿Tal vez quiera que el usuario escriba sus iniciales? Es correcto codiﬁcar directamente los comandos de acceso de las iniciales si usted es la única persona que usará la macro. Pero si quiere que la macro sea útil para otros, tiene que dejar que ingresen sus iniciales. Esto signiﬁca aceptar la entrada del usuario mientras la macro se está ejecutando. Una manera de aceptar la entrada del usuario mientras se ejecuta una macro consiste en desplegar un cuadro de entrada para el usuario.

Macros con Excel 2007

640 %& "3(6.&/504 En programación, un argumento es algún dato que usted proporciona para un comando como InputBox. En VBA, estos datos se encuentran entre paréntesis. He aquí la sintaxis completa para el comando InputBox en VBA: InputBox(Mensaje, Título, Predeterminado, Izquierda, Arriba, ArchivoAyuda, IDAyudaContextual, Tipo)

Cada elemento dentro de los paréntesis es un argumento. Pero casi todos los argumentos son opcionales (puede omitirlos y, con frecuencia, simplemente no los necesita, que es la razón por la que, en primer lugar, son opcionales). El único argumento necesario para un cuadro de entrada es Mensaje, que es una breve etiqueta que aparece arriba del cuadro de texto en donde el usuario escribe sus datos. Esta leyenda le indica al usuario lo que debe escribir. De modo que la programación que despliega el tipo más simple de cuadro de entrada sólo usa un argumento, Mensaje, como se muestra en seguida:

El tercer argumento, Predeterminado, también es opcional, pero puede ser útil en algunas situaciones. Digamos que tiene una idea de la información que el usuario escribirá. Puede ahorrarle tiempo al usuario si despliega esa información como opción predeterminada en el cuadro de texto. De esa manera, el usuario no tendrá que escribir nada si su opción predeterminada es correcta. Simplemente debe oprimir ENTER, o hacer clic en el botón Aceptar. Sin embargo, el usuario también tiene la libertad de reemplazar su texto predeterminado con su propia entrada, si es necesario. He aquí cómo agregar un argumento predeterminado para el comando InputBox: x = InputBox("Por favor ingrese sus iniciales", "Ingrese sus iniciales", "GP")

x = InputBox("Por favor ingrese sus iniciales","Ingrese sus iniciales")

Observe, en la ﬁgura 7-3, que los datos de opción predeterminada GP se seleccionan automáticamente (aparecen resaltados) para que el usuario sólo tenga que escribir algunos datos nuevos, si es necesario. Si estos datos predeterminados no estuvieran seleccionados, el usuario tendría que seleccionarlos para reemplazarlos. Como puede ver, cada argumento que agrega entre paréntesis está separado de los demás por comas. Y es necesario que tenga estos argumentos en el orden apropiado. En este caso, el orden es: Mensaje, Título, Predeterminado, etcétera. Este orden es la manera en que VBA sabe cuál argumento es cuál, y no confunde, por ejemplo, el título con el mensaje. Incluso los argumentos opcionales deben incluirse (o las comas que indican que se omiten), si se usan argumentos posteriores. Por ejemplo, si quiere incluir un texto predeterminado, pero omitir el título, debe usar el número adecuado de comas, como se muestra a continuación:

En la ﬁgura 7-2 se ilustra el resultado de agregar un título opcional.

x = InputBox("Por favor ingrese sus iniciales", , "GP")

x = InputBox("Por favor ingrese sus iniciales")

En la ﬁgura 7-1 se muestra este cuadro de entrada. El usuario ve el mensaje descriptivo y puede escribir lo que desee en el cuadro de texto. Si no proporciona el argumento Título, la barra de título del cuadro de entrada despliega Microsoft Excel. Sin embargo, puede, si lo desea, proporcionar su propio argumento para el título, de la siguiente manera:

Capítulo 7

.&.0 En programación, el término incluido en el código o conectado en el código signiﬁca que algo se incluyó en la macro que nunca cambia. En el ejemplo anterior, las iniciales GP se insertan en la celda de la esquina superior izquierda. Están conectadas en el código porque son mis iniciales. Sin embargo, a veces se requiere un método más ﬂexible. En lugar de proporcionar los datos GP en su macro, usted permite que el usuario proporcione sus propias iniciales. Usted obtiene estos datos al usar un cuadro de entrada o algún otro tipo de “control”, como un cuadro de mensaje, o un cuadro de lista, como verá más adelante en este capítulo.

Creación de macros interactivas

El usuario ingresa las iniciales en el campo de entrada

Figura 7-1

El tipo más simple de cuadro de entrada

Figura 7-2 Puede agregar un título adicional a su cuadro de entrada

Un cuadro de entrada se Figura 7-3 Puede proporcionar información predeterminada que el usuario tiene libertad de aceptar o reemplazar despliega ante el usuario al emplear el comando InputBox. Pero antes de que pueda usar de manera efectiva comandos Figura 7-4 La característica Información rápida muestra la lista de como éste, necesita comprender argumentos de un vistazo el concepto de argumentos en programación. InputBox, como muchos otros objetos, tiene una lista de argumentos. Observe que los argumentos opcionales se incluyen entre corchetes; los argumentos obligatorios, como Prompt, en la ﬁgura 7-4, no llevan corchetes.

Cómo funciona el cuadro de entrada Tal vez se haya preguntado acerca de la X en los ejemplos de código anteriores. Se trata de una variable, y exploraremos esta herramienta de programación esencial a profundidad en el capítulo 9. Por ahora, basta con comprender que cuando el usuario escribe algunos datos y hace clic en el botón Aceptar (u oprime ENTER) para cerrar el cuadro de entrada, la X contiene los datos que el usuario escribió. De modo que luego puede usar X en su macro para recuperar los datos.

Macros con Excel 2007

h -" ."/&3" '"$*Puede ver los argumentos de cualquier comando de VBA con sólo hacer clic en su código, para mover el cursor parpadeante de inserción a ese comando. Luego oprima F1 y verá abierta la ventana de ayuda con ese comando incluido. Por supuesto, no todos los comandos de VBA tienen argumentos, pero muchos sí.

h -" ."/&3" '"$*Al escribir un comando como InputBox en la ventana de código de VBA y luego oprimir la barra espaciadora, desencadena la característica Información rápida del editor de Visual Basic, que despliega toda la lista de argumentos disponible para el comando. En la ﬁgura 7-4 se muestra la lista de argumentos del comando InputBox.

Escritura de la macro completa En este ejemplo, quiere poner las iniciales del usuario en la celda de la esquina superior izquierda. He aquí la macro completa que despliega el cuadro de entrada y luego pone lo que escriba el usuario en la celda de la hoja de cálculo: Sub ObtenerIniciales() x = InputBox("Por favor ingrese sus iniciales", "Ingrese sus iniciales") Range("A1").Select ActiveCell.FormulaR1C1 = x End Sub

Este código despliega primero un cuadro de entrada para el usuario, y cuando éste cierra ese cuadro, cualquier cosa que haya escrito en el cuadro de texto del cuadro de entrada estará contenido ahora en la variable X.

Escritura y prueba del código del cuadro de entrada Es hora de que escriba el código que despliega un cuadro de entrada y luego pruebe ese código en el editor de VB. Siga estos pasos:

1. Con Excel ejecutándose y una hoja de cálculo visible, oprima ALT+F11. 2. Eso abre el editor de VB para que pueda escribir una macro. 3. En el editor de VB, elija Insertar | Módulo. Se abre un nuevo contenedor de macros (un módulo que contiene varias macros). El editor despliega una ventana en blanco a la derecha (la ventana de código) y resalta el módulo recién creado en el Explorador de proyectos (si tiene visible el explorador), como se muestra a la izquierda de la ﬁgura 7-5. El nuevo módulo será llamado Módulo1 si es el primero que agrega al proyecto. Al siguiente módulo se le asigna, como opción predeterminada, el nombre Módulo2 y así sucesivamente. Sin embargo, siempre puede hacer clic en un nombre del módulo en el Explorador de proyectos para seleccionarlo y luego cambiar su propiedad Nombre en la ventana Propiedades (que se muestra en la parte inferior

Capítulo 7

Creación de macros interactivas

.&.0 Si el usuario no escribe información en el cuadro de texto, la variable X no contendrá nada. En ocasiones es útil probar si el usuario escribió algo, y puede hacerlo al ver si X = "" (una cadena vacía), con un código como éste: If x = "" Then Exit Sub

Este código causa que la macro deje de ejecutarse (para salir de la subrutina) si el usuario no escribió nada en el cuadro de entrada. En otras palabras, cualquier código de programación que se encuentre debajo de esta línea será ignorado por VBA si el usuario deja el cuadro de texto en blanco. Usamos una estructura If... Then (descrita con destalle en el capítulo 10) para probar el valor de la variable X.

Figura 7-5 Escriba o modiﬁque macros en la ventana de código, a la derecha del editor de VB

izquierda, en la ﬁgura 7-5). A muchos programadores les gusta dar nombres descriptivos a sus módulos, como MacrosImpuestos o MacrosInventarios. 4. Escriba el siguiente código en el módulo de código: Sub ObtenerIniciales() Dim cadIniciales As String cadIniciales = InputBox("Por favor ingrese sus iniciales", "Ingrese sus iniciales") Range("A1").Select ActiveCell.FormulaR1C1 = cadIniciales End Sub

Observe que he usado el comando Dim aquí para declarar formalmente una variable (cadIniciales) que contendrá la entrada del usuario. Ésta se considera una buena práctica de programación porque la declaración de variables puede evitar algunos tipos de errores, y también hacer que el código sea un poco más fácil de comprender y leer. También usé otra convención común, asignar a la variable el preﬁjo cad,

Macros con Excel 2007

identiﬁcándolo como un tipo de variable de cadena (de texto). Por último, el empleo de la palabra Iniciales en el nombre de la variable facilita ver el propósito de ésta. 5. Por último, pruebe su macro recién escrita oprimiendo F5. Esto le indica al editor que ejecute la macro, y también oculta el editor para que pueda ver lo que sucede en la hoja de cálculo actual. Observe que al oprimir F5 se ejecutará cualquier macro en que se encuentre el cursor de inserción parpadeante. Por lo anterior, asegúrese de hacer clic dentro del código de la macro para poner el cursor en él antes de oprimir F5.

Cuadros de mensajes: la comunicación más simple con el usuario Si quiere desplegar un mensaje al usuario acerca de algo mientras se ejecuta una macro (pero no necesita ninguna entrada del usuario), utilice un cuadro de mensaje. Este control es similar a un cuadro de entrada, pero un cuadro de mensaje no tiene un cuadro de texto en que el usuario pueda escribir. Y, en su forma más simple, un cuadro de mensaje sólo tiene un botón, Aceptar, en que el usuario hace clic después de leer lo que diga el cuadro de mensaje. En el comando MsgBox, usted escribe el mensaje como el argumento Mensaje, y es el único argumento requerido. El comando para crear un cuadro de mensaje tiene la siguiente abreviatura: MsgBox. MsgBox ("Se ha cambiado el formato de las celdas.")

Cuando el mensaje se despliega (véase la ﬁgura 7-6), los usuarios no pueden proporcionar ninguna entrada a su macro. Todo lo que pueden hacer es clic en Aceptar u oprimir ENTER para elegir el cuadro de mensaje.

Uso de los botones del cuadro de mensaje para retroalimentación Sin embargo, puede usar un cuadro de mensaje para regresar información limitada del usuario a su macro. Para esto, puede desplegar varios conjuntos de botones integrados. He aquí la sintaxis formal para los argumentos que puede proporcionar a un cuadro de mensaje: MsgBox(mensaje[, botones] [, título] [, archivoayuda, contexto]

Observe que Mensaje es el único argumento necesario. Sin embargo, el argumento Botones

Figura 7-6 Un cuadro de mensaje simple sólo despliega información para el usuario, pero no puede regresar ninguna entrada del usuario a la macro

Capítulo 7

Creación de macros interactivas

puede incluirse como opción para desplegar botones diferentes de Aceptar, que es el predeterminado. Los cinco conjuntos opcionales de botones para un cuadro de mensaje se muestran en la tabla 7-1. Todo lo que tiene que hacer para desplegar un conjunto opcional de botones es proporcionar el número Valor como argumento de Botones. Digamos, por ejemplo, que quiere desplegar los botones comunes Sí, No y Cancelar. Usaría el valor 3 como argumento de Botones, como se ilustra en el siguiente código: x = MsgBox("¿Desea seguir adelante?", 3)

Ejecute este código y verá el cuadro de mensaje desplegado en la ﬁgura 7-7. Constante

Valor

Descripción

vbOKCancel

Despliega los botones Aceptar y Cancelar.

vbAbortRetryIgnore

Despliega los botones Abortar, Reintentar e Ignorar.

vbYesNoCancel

Despliega los botones Sí, No y Cancelar.

vbYesNo

Despliega los botones Sí y No.

vbRetryCancel

Despliega los botones Reintentar y Cancelar.

Figura 7-7 El cuadro de mensaje proporciona al usuario la retroalimentación para su macro, con base en el botón en que se hace clic

Tabla 7-1 Botones opcionales que pueden desplegarse en un cuadro de mensaje

Constante

Valor

Descripción

vbOK

Aceptar

vbCancel

Cancelar

vbAbort

Abortar

vbRetry

Reintentar

vbIgnore

Ignorar

vbYes

Sí

vb No

Tabla 7-2 Valores devueltos por el clic en uno de los botones del cuadro de mensaje

Observe que cuando utiliza el argumento Botones, debe proporcionar una variable que contendrá el resultado (el botón que haya elegido el usuario). En nuestro código de ejemplo usamos la variable X. Después de que el usuario hace clic en un botón, X contiene uno de los valores mostrados en la tabla 7-2. He aquí una macro completa que ilustra la manera de usar botones en su código: Sub Mensaje() x = MsgBox("¿Desea seguir adelante?", 3) If x = 7 Then Exit Sub

Macros con Excel 2007

h -" ."/&3" '"$*Algunos programadores preﬁeren usar constantes integradas en lugar de valores cuando realizan tareas como la especiﬁcación de argumentos o la prueba de variables. En ocasiones, cuando revisa una lista de argumentos en el sistema de ayuda de VBA, verá tablas como las 7-1 o 7-2. Cuando escribe su macro puede usar cualquier valor (como 2) o la constante (una palabra descriptiva, como vbCancel). He aquí la manera en que se vería el código de ejemplo si decide usar las constantes: Sub Mensaje() x = MsgBox("¿Desea seguir adelante?", vbYesNoCancel) If x = vbNo Then Exit Sub MsgBox ("Muy bien, seguirá ejecutándose esta macro")

MsgBox ("Muy bien, seguirá ejecutándose esta macro.") End Sub

Cuando se ejecuta esta macro, despliega un cuadro de mensaje con botones Sí, No y Cancelar. Obtenemos la respuesta del usuario en la variable X. Esa respuesta puede ser 6, 7 o 2, si el usuario hizo clic en el botón Sí, No o Cancelar, respectivamente. En este caso, usamos una estructura If...Then para probar el valor de X. Si es 7, el usuario respondió al hacer clic en No cuando se le preguntó si quería seguir adelante, de modo que se pasa a la subrutina de salida (Exit Sub), y con ello se detiene la ejecución de la macro. Pero si hizo clic en los botones Sí o Cancelar, no se va a la subrutina de salida; se ejecuta el resto de la macro y se despliega otro cuadro de mensaje.

Uso de la barra de estado para retroalimentación más sutil

Los cuadros de entrada y de mensaje son maneras excelentes de comunicarse entre su macro y el usuario. Pero tienen una posible desventaja: detienen End Sub la macro en su ejecución igual que un vendedor que pone su pie en la puerta. No puede seguir ejecutando una macro hasta que el cuadro de entrada o de mensaje esté cerrado. Se asienta allí y exige que se le note. A menudo esto es conveniente. Su macro quiere que el usuario esté seguro de ver el mensaje, o que proporcione alguna información. ¿Pero qué pasa con situaciones en que quiere desplegar información que el usuario tiene la opción de ignorar? Tome el ejemplo de páginas anteriores de este capítulo. Usó un cuadro de mensaje para indicar al usuario que se ha cambiado el formato de las celdas. Por lo general, el usuario sabe que se acaba de cambiar, de modo que no hay necesidad de detener todo para desplegar un cuadro de mensaje que les indique esto. Sin embargo, en ocasiones tal vez quiera revisar si se ha cambiado el formato de las celdas. Una solución consiste en mostrar este tipo de información opcional en la barra de estado de la ventana de Excel. La información será visible para el usuario, pero no detendrá

Capítulo 7

Creación de macros interactivas

la ejecución como lo hace un cuadro de mensaje. He aquí un ejemplo que muestra cómo usar el objeto StatusBar en su código para desplegar un mensaje: Sub MensajeBarraEstado() Application.StatusBar = "Se ha cambiado el formato de las celdas..." End Sub

Pruebe esta pequeña macro y vea cómo funciona. Para borrar el contenido de la barra de estado, use este código: Application.StatusBar = ""

Creación de cuadros de diálogo personalizados Ahora sabe cómo usar varios conjuntos de botones para proporcionar al usuario una manera de comunicar información simple a su macro (como cancelar, ignorar o reintentar). Pero en ocasiones quiere permitir que el usuario seleccione de un grupo más grande o variado de opciones. Por ejemplo, en lugar de forzar al usuario a escribir el nombre de un estado, puede proporcionar una lista de todos los estados desde Alabama hasta Wyoming. O tal vez necesite obtener información amplia y compleja del usuario. Probablemente requiera desplegar un formulario para que lo llenen. Para esto puede crear su propio cuadro de diálogo personalizado. No es un cuadro de entrada ni de mensaje, pero en cambio algo que cree para agregar requisitos especiales de interacción con el usuario. El editor de VB tiene herramientas soﬁsticadas que le permiten diseñar muchos tipos de formularios que el usuario puede llenar. Se les llama UserForms. Pueden ser pequeños, como cuadros de entrada, o abarcar toda la pantalla. Los UserForms permiten gran libertad al diseñarlos y decidir cuáles controles colocar en ellos.

Ofrecimiento al usuario de una lista de opciones Para ver cómo funcionan los UserForms, diseñemos uno ahora. Agregará un ListBox a un formulario, y también añadirá botones Cancelar y Aceptar para que el usuario pueda cerrar su cuadro de diálogo (a un UserForm, cuando se despliega ante el usuario, suele llamársele cuadro de diálogo). En este ejemplo, querrá desplegar una lista de opciones para el usuario. Supongamos que, de manera regular, necesita cambiar el tamaño de fuente en varias celdas a pequeño,

Macros con Excel 2007

mediano y grande. Quiere desplegar un cuadro de diálogo en que el usuario puede hacer clic para seleccionar estas opciones. Para agregar un UserForm a su proyecto, siga estos pasos: 1. Inicie la ejecución de Excel y oprima ALT+F11 para abrir el editor de VB. 2. Elija Insertar | UserForm. Ahora ve que el editor tiene el aspecto de la ﬁgura 7-8, con un nuevo formulario en blanco en la ventana de la derecha (a ésta se le llama ventana de diseño cuando está construyendo un formulario). También ve el Cuadro de herramientas a la izquierda, que contiene los diversos controles que puede agregar al formulario. Si no la ve, elija Ver | Cuadro de herramientas. 3. Mueva el puntero del ratón por el Cuadro de herramientas. Cada vez que detenga el ratón sobre un ícono, aparecerá una descripción de ese ícono. 4. Localice el control Botón de comando, en el Cuadro de herramientas, y luego arrástrelo y colóquelo en su formulario. 5. Cuando coloca un control, quedará seleccionado automáticamente (tiene un marco gris alrededor con varios controladores para arrastrar que puede usar para cambiar el tamaño).

Figura 7-8 He aquí su nuevo UserForm, listo para que le agregue controles

Capítulo 7

Creación de macros interactivas

6. Arrastre estos manejadores hasta que tenga un botón más o menos cuadrado (véase la ﬁgura 7-9 para referencia). 7. Repita los pasos 4 al 6 para agregar un segundo botón.

Figura 7-9 Ahora tiene los botones Aceptar y Cancelar familiares en su UserForm

Ver código Ver objeto

Figura 7-10 Haga clic en los íconos para cambiar entre Vista de código y Vista de diseño

.&.0 Si quiere cambiar rápidamente entre vista de código y de diseño, haga clic en los íconos Ver código o Ver objeto, en la barra de título del Explorador de proyectos, como se muestra en la ﬁgura 7-10.

8. Ahora haga clic en el botón de la izquierda para seleccionarlo. Luego localice su propiedad Caption en la ventana Propiedades. Observe que, como opción predeterminada, tiene la leyenda CommandButton1, CommandButton2, o cualquier número que represente el orden en que los agregó al formulario. En cualquier caso, quiere que el botón de la izquierda tenga la leyenda Aceptar. Así que Haga doble clic en la leyenda actual (como CommandButton1) en la columna de la derecha en la ventana Propiedades. Esto selecciona el nombre existente. Escriba Aceptar y oprima ENTER. Observe que la leyenda en el propio botón cambia de inmediato. 9. Repita el paso 8 para cambiar la leyenda del botón de la derecha por Cancelar. Ahora tiene el conjunto de botones típico Aceptar y Cancelar de casi todos los cuadros de diálogo, como se ve en la ﬁgura 7-9. 10. Ahora agregue un control ListBox a este formulario.

Adición de elementos a un cuadro de lista

Ahora que tiene la superﬁcie visible de su formulario, necesita escribir algún código que llene el ListBox con las opciones que desea desplegar ante el usuario. Para obtener la ventana de código, puede hacer doble clic en cualquier lugar del UserForm. Pero para este ejemplo, haga doble clic en el botón que ha llamado Cancelar. Ahora se abre la ventana, con algunos subs ya creados. El editor ha proporcionado lugares donde puede escribir el código que responda cuando el usuario hace clic en

Macros con Excel 2007

cualquier lugar de los botones, o el propio UserForm. (A estos subs que reaccionan a los clics del usuario se les denomina manejadores de eventos; un clic es un evento que sucede, y necesita manejarlos con algún código.) Usted simplemente quiere cerrar este cuadro de diálogo y terminar la ejecución de este pequeño programa si el usuario hace clic en el botón Cancelar. Así que agregue este simple código a cualquier botón de comando al que haya denominado Cancelar: Private Sub_CommandButton1_Click() End End Sub

Ahora pruebe esto. Oprima F5. Se despliega el UserForm. Haga clic en el botón Cancelar. Ahora desaparece el UserForm. Exactamente lo que quería. Ahora necesita agregar las opciones al ListBox. Para esto utilice el comando AddItem. De regreso al editor de VB, observe en la parte superior de la ventana de código que hay dos listas desplegables. Abra la de la izquierda y seleccione UserForm. La lista de la derecha contiene todos los eventos que puede manejar para el propio UserForm. En la lista de la derecha, haga clic en el evento Activate. El editor inserta el sub correcto para manejar este evento. La activación ocurre cuando se creó el UserForm, aún antes de que el usuario la vea desplegada. Así que éste es un buen lugar para poner código de limpieza, cualquier cosa que necesite hacérsele al formulario antes que el usuario interactúe con él. Desea desplegar tres opciones de tamaño de fuente al usuario: Pequeño, Mediano y Grande. Usted está diseñando este formulario, de modo que puede asignar el nombre que desee a estas opciones: pequeño, chiquito, lo que sea. He aquí el código que ahora debe escribir en el evento Activate: Private Sub UserForm_Activate() ListBox1.AddItem ("Pequeño") ListBox1.AddItem ("Mediano") ListBox1.AddItem ("Grande") End Sub

Pruebe esto al oprimir F5 y observar cómo estas opciones se despliegan para el usuario. Haga clic en el botón Cancelar para cerrar el UserForm. Tal vez quiera cambiar el tamaño del cuadro de lista en este momento, para que quepa adecuadamente en las opciones desplegadas (que no haya demasiado espacio en blanco, pero que tampoco se tape alguna opción). Si hace el cuadro demasiado pequeño, el editor agregará automáticamente barras de desplazamiento.

Respuesta a la selección del usuario Nuestro trabajo ﬁnal consiste en escribir el código que reacciona cuando el usuario selecciona una opción en el ListBox y hace clic en el botón Aceptar. Este código debe

Capítulo 7

Creación de macros interactivas

hacer que la fuente de la celda actual sea pequeña, mediana o grande (de acuerdo con el elemento del Tal vez se esté preguntando ListBox que elija el usuario). por qué 0 representa el primer Este código debe ir en el evento Clic del botón elemento de un ListBox, 1 representa el segundo, etcétera. Aceptar. Debe revisar en cuál opción se ha hecho clic, Esto es debido a un error en el ListBox, y luego hacer los cambios adecuados cometido hace décadas cuando la en el tamaño de la fuente. programación apenas empezaba. Para saber en cuál elemento se hizo clic en el Algún comité ya olvidado decidió: ¿por qué desperdiciar ListBox, debe usar la propiedad ListItem. He el 0? ¡Usemos el cero como aquí cómo funciona: un valor de –1 significa que no valor inicial para listas! No se seleccionó nada (de modo que sólo responderá fue una buena idea, pero es cerrando el cuadro de diálogo sin hacer nada). Un demasiado tarde para corregir valor de 0 significa que se hizo clic en el primer esta rareza en la programación de computadoras. elemento (pequeño en este ejemplo). Un valor de 1 significa que se seleccionó el segundo elemento, y uno de 2 significa que el usuario eligió el tercer elemento, Grande. He aquí el código que debe escribir en el evento Click del botón Aceptar:

.&.0

Private Sub CommandButton2_Click() If ListBox1.ListIndex = 0 Then Selection.Font.Size = 8 If ListBox1.ListIndex = 1 Then Selection.Font.Size = 12 If ListBox1.ListIndex = 2 Then Selection.Font.Size = 18 End End Sub

No es necesario probar –1 (sin selección), porque este código sólo cambia el tamaño si ListIndex es 0, 1 o 2. Si el número del índice de la lista es –1, no sucede nada, que es el resultado que queremos. Siga adelante y oprima F5 para ver el efecto de sus esfuerzos. Por supuesto, puede modificar este formulario para que se amolde a sus necesidades: agregue todos los elementos que desee a la lista. Asimismo, puede hacer mucho más con UserForms. Puede agregar controles del Cuadro de herramientas para desplegar imágenes, agregar Puede encontrar gran cantidad de código etiquetas o cuadros de texto, crear de ejemplo en línea. Un buen lugar para empezar es elegir Ayuda | MSDN en la Web. Esto lo lleva al conjuntos de botones o casillas de portal de una rica colección de ejemplos de programación. verificación para que el usuario O puede usar Google para buscar ejemplos de código haga clic, además de otros tipos específicos, como Excel ListBox. de interfaces.

7¶ODVMP

6TP EF NBDSPT QBSB GPSNBUP EF DFMEBT

Como sabe, hay gran cantidad de posibilidades de formato disponibles en las cintas de opciones de Excel. Pero en ocasiones la selección de formato no es exactamente lo que está buscando, o hay tantos cambios de formato que los pasos para alcanzar el aspecto deseado de su hoja de cálculo parecen interminables. Puede personalizar las herramientas de formato para que cubran sus necesidades con la ayuda de macros, y también puede automatizar una revisión completa del formato en macros que pueden llevarse al uso rápidamente.

100

Macros con Excel 2007

Uso de macros para cambiar formatos existentes Excel proporciona una selección de formatos de fecha que, a primera vista, parece cubrir toda presentación posible de fechas. Pero para algunos la selección no es lo suﬁcientemente completa. Puede publicar una fecha empleando su propio estilo único al asignar el comando de fecha y el estilo deseado a una macro. Al observar las opciones de formato de fecha americano (haga clic en una celda y elija Formato de celdas, luego haga clic en la categoría Fecha), veo que no hay una opción para 14/03/2001, una fecha que usa diagonales, despliega el día y el mes con el número de dígitos necesarios (sin ceros) y el año de cuatro dígitos. Por lo general, cuando crea un formato personalizado, el nuevo formato reside dentro del libro donde fue creado. Podemos crear un formato de fecha personalizada y hacer que quede disponible para otros libros al guardar el proceso en una macro. Podemos grabar este proceso de personalización de fechas, de modo que primero hagamos a un lado el cuadro de formato de nuestra hoja de cálculo. No queremos que la grabadora de macros grabe el movimiento del cursor, de modo que coloque el puntero en cualquier celda No se desespere cuando vea donde le gustaría ver que aparezca una fecha antes 14 de marzo como fecha en de que empiece a grabar. la lista de formatos de fecha. Para grabar la nueva macro de formato de fecha, Excel usa 14/3/01 como fecha siga estos pasos: predeterminada sólo para ﬁnes

.&.0

de despliegue de los diferentes estilos. La fecha real que ingrese en una celda aparecerá una vez que haya elegido un estilo.

1. En la cinta de opciones Programador, haga clic en Grabar macro. 2. Ingrese MiFormatoFecha como nombre de la macro. 3. Asigne esta macro al método abreviado CTRL+MAYÚS+D si planea usarla con frecuencia. 4. Almacene la macro en el Libro de macros personal, para que esté disponible para todos sus libros. 5. Ingrese una descripción, Formato de fecha 14/03/2001 como recordatorio (véase la ﬁgura 8-1).

Figura 8-1 Grabación de nuestro estilo de fecha personalizado

6. Haga clic en Aceptar.

Capítulo 8

Uso de macros para formato de celdas

101

7. Haga clic con el botón derecho en su celda activa y elija Formato de celdas. 8. Elija la categoría Personalizada. 9. Desplácese hasta encontrar las opciones de fecha y haga clic una vez en dd/mm/ aaaa, el formato más parecido al que queremos usar. 10. En el campo Tipo (véase la ﬁgura 8-2), personalice su formato de fecha para que se muestre d/m/aaaa. 11. Haga clic en Aceptar. 12. Apague la grabadora de macros al hacer clic en la opción Detener grabación. Diseñe aquí su formato de fecha personalizado

Figura 8-2

Figura 8-3

Personalización de un formato

Macro MiFormatoFecha personalizada

Pruebe su macro al ingresar una fecha en la celda que acaba de formatear. Yo ingresé 12/03/01 y mi nuevo formato presentó la fecha 12/3/2001, ¡tal como lo quería ver! Ahora pruebe más su macro al cerrar el libro, abrir uno nuevo e ingresar una fecha en la celda. Luego oprima CTRL+MAYÚS+D mientras su puntero está en la celda para asignar el formato deseado a la celda. Si examina el código en el editor de VB, verá que todos los pasos anteriores dieron como resultado una línea de código VBA muy sencilla, mostrado en la ﬁgura 8-3.

Cambio del aspecto de una hoja de cálculo Puede crear macros simples para formatear las diferentes áreas de su hoja de cálculo. Luego, en lugar de recorrer las cintas de opciones o tratar de

102

Macros con Excel 2007

.&.0 La selección de las celdas antes de encender la grabadora de macros se debe a que no querrá que el proceso de grabación incluya la selección de las celdas. Ese proceso se vuelve parte de la macro grabada y, por tanto, interﬁere con la aplicación de la macro a diferentes áreas de las celdas seleccionadas en el futuro.

recordar métodos abreviados de teclado o comandos de menú contextual, puede resaltar un área de su hoja de cálculo, mostrar su lista de macros y aplicar rápidamente el tipo de formato que le gustaría usar.

Una macro para formar encabezados de columna

Esta macro aplica su estilo de encabezado de columna favorito: fuente Arial en negritas, centrada y 12 puntos. Debido a que las características de formato están disponibles en pantalla, en la cinta de opciones, puede grabar esta macro. Seleccione algunas celdas a las que desea aplicar el formato, o con sólo una celda seleccionada, siga estos pasos: 1. Inicie la grabadora de macros y asigne un nombre a la macro. Por lo general, no asigno métodos abreviados de teclado a menos que los use con frecuencia. Debido a que voy a crear varias macros de formato diferentes, sólo voy a asignar nombres y no métodos abreviados, para que no confunda los métodos abreviados que van con las macros. (En la ﬁgura 8-4 se muestra que estoy asignando a la macro el nombre EncabezadosColumna.) Luego haga clic en Aceptar para iniciar la grabadora. 2. Con la celda o celdas ya seleccionadas, haga clic en la cinta de opciones Inicio y aplique sus opciones de formato: negritas, centrada y fuente Arial en 12 puntos. 3. Haga clic en el botón Detener grabación en la parte inferior de la hoja de cálculo. El ingreso de una descripción le ayudará a recordar cuáles características de formato aplicará esta macro

Figura 8-4 Creación de una macro de aplicación de formato

Si echa un vistazo al código VBA que se obtiene de esa macro, en la ﬁgura 8-5, puede ver en primer lugar que la macro empieza por aplicar la característica Negritas a las celdas seleccionadas. A continuación, hay una serie de instrucciones With Selection, aplicando las otras opciones que hemos hecho a las celdas seleccionadas. También puede ver que hay gran cantidad de código desperdiciado. Puede decidir que se deje intacto todo el código, pero recomiendo que se limpie la macro para que sólo se usen las líneas de código necesarias. Esto es así para que, dentro de meses o años, cuando revise de nuevo esta macro, no tenga que recorrer las líneas de código tratando de recordar lo que se ha realizado.

Capítulo 8

Uso de macros para formato de celdas

103

Además, si alguien más examina esta macro, el código necesario será obvio y las líneas adicionales de código se habrán ido. Será mucho más fácil comprender la macro. Tras una revisión, veo que el primer bloque With Selection produce una alineación al centro. El comando para esto aparece en la primera línea del código debajo del comando With Selection; ninguna de las demás instrucciones de la sección son necesarias para centrar y pueden eliminarse, dejándonos con la siguiente instrucción.

Figura 8-5

Examen del código de la macro grabada

With Selection .HorizontalAlignment = xlCenter End With

El segundo segmento o With Selection del código VBA de esta macro se aplica a la siguiente selección de fuente, Arial. El resto de ese segmento es innecesario, pero ¡espere! Eche un vistazo a la segunda línea del código de la selección: With Selection.Font .Name = "Arial" .Size = 11 End With

Cuando grabé la imposición de la fuente Arial en mis celdas seleccionadas, el comando de fuente Arial se colocó en la macro, y también se aplicó el tamaño de fuente predeterminado de 11, junto con otras opciones predeterminadas (como no subrayado, no índices, etcétera). En el siguiente segmento With Selection de mi código, se ordenó el cambio al tamaño de fuente de 12, pero no hay razón para no consolidar un poco más e incorporar el tamaño de fuente revisado en la misma área del código que en la selección de fuente. Por tanto, mostramos las dos líneas de código necesarias, después del resto de las líneas de código en la sección que se ha eliminado: With Selection.Font .Name = "Arial" .Size = 11 End With

104

Macros con Excel 2007

Al hacer este cambio en el código, podemos eliminar ahora la última área de With Selection del código VBA. De modo que ahora la macro ﬁnal tiene este aspecto: Sub EncabezadosColumna() ' EncabezadosColumna Macro ' Encabezados de columa en negritas, centrados, Arial 12 puntos Selection.Font.Bold = True With Selection .HorizontalAlignment = xlCenter End With With Selection.Font .Name = "Arial" .Size = 12 End With End Sub

Una macro para formatear el aspecto de los números Con frecuencia creará una hoja de cálculo llena de números, y descubrirá que el formato de número predeterminado aplicado por Excel no es el que quiere. No se usa el mismo formato siempre, pero hay algunos formatos estándar usados con tanta frecuencia que sería bueno poder seleccionarlas rápidamente, con la menor cantidad posible de clics. Mi estilo de formato de números favorito es Coma, sin posiciones decimales, y preﬁero que los números negativos se desplieguen en rojo y con un signo de menos. Una vez más, voy a seleccionar celdas (o incluso una sola celda) antes de encender la grabadora de macros (este proceso evita que la grabadora trate de grabar mis movimientos de celda o la selección del ratón). De esa manera puedo aplicar fácilmente el formato a cualquier celda con sólo seleccionar las celdas y luego ejecutar la macro. He aquí los pasos para crear una macro que aplicará mi estilo favorito de formato de número. 1. Encienda la grabadora de macros. Asigne un nombre a esta macro, guárdela en el Libro de macros personal y proporcione una breve descripción, similar al ejemplo de la ﬁgura 8-6. Luego haga clic en Aceptar para iniciar la grabación.

Figura 8-6 Grabación de la macro FormatoNúmero

2. Aplique los formatos de número que le gusten a la celda o las celdas seleccionadas. Puede hacer clic en la cinta de opciones Inicio para encontrar algunas de las herramientas de formato de número, o puede hacer clic con el botón derecho

Capítulo 8

Uso de macros para formato de celdas

105

en la celda o las celdas seleccionadas, elija Formato de celdas y luego Número, después haga sus selecciones de formato (véase la figura 8-7). Haga clic en Aceptar para aplicar sus elecciones. 3. Si hay formatos de fuente que desea aplicar a sus números, como una selección de fuente, una alineación o una característica de subrayado, también puede hacer esas selecciones en este momento. 4. Cuando se hayan hecho todas las selecciones de formato, apague la grabadora de macros. Pruebe su macro al ingresar algunos números en una nueva área de su libro, y luego elija su macro FormatoNúmero de la lista Macros. Una vez más, tiene sentido examinar el código de su macro en busca de líneas innecesarias de código. Si sólo aplicó un formato de número, el código es simple y no necesita mucha refinación. Si también aplicó selecciones de fuente, habrá líneas de código que pueden eliminarse al limpiar el código y hacerlo más comprensible. He aquí el código de mi macro FormatoNúmero: Haga clic en la ficha Número para desplegar las opciones de formato Seleccione opciones de posiciones decimales. Uso de coma y presentación de números negativos

Sub FormatoNúmero() ' FormatoNúmero Macro ' Coma, sin centavos, negativos en rojo Selection. NumberFormat = "#,##0_ ;[Red]-#,##0" End Sub

Cambie el aspecto de una hoja de cálculo

A veces querrá cambiar no sólo el aspecto de los Figura 8-7 Elección de un formato de número números o encabezados, sino el de la hoja de cálculo como un todo. Tal vez quiera desplegar la hoja de cálculo sin líneas guía, o ajustar el ancho de las columnas. Quizás prefiera un estilo y un tamaño específicos de fuente. Podemos crear una macro que aplica estas especificaciones a una hoja de cálculo. Dependiendo de los tipos de cambios que quiera aplicar, puede seleccionar previamente toda la hoja de cálculo (al hacer clic en el cuadro que se encuentra a la izquierda de la letra

106

Macros con Excel 2007

de la columna A), o puede seleccionar algunas columnas o filas. Como preparación para grabar esta macro, piense en lo que afectará. Si va a ajustar anchos de columna, necesitará seleccionar algunas columnas. De igual manera, necesitará seleccionar algunas filas si quiere ajustar la altura de las filas. Para este ejemplo de una macro que cambie el aspecto de una hoja de cálculo, voy a seleccionar toda la hoja después de que empiece a grabar. Mi macro, a la que llamaré MiConfiguraciónHoja, eliminará líneas guía, ajustará el ancho de la columna a nueve caracteres y establecerá Arial como fuente de toda la hoja de cálculo. 1. Encienda la grabadora de macros. Asigne un nombre a la macro, almacénela en el Libro de macros personal y proporcione una breve descripción, similar a la figura 8-8. 2. Haga clic en el botón Seleccionar todo, en la esquina superior izquierda de la hoja de cálculo. 3. Haga clic en la cinta de opciones Diseño de página, y luego desmarque la casilla Ver debajo del encabezado Líneas de cuadrícula. 4. Desplace una de las barras de columna entre los encabezados de letra de columna para que el nuevo ancho de las columnas seleccionadas sea de nueve caracteres. 5. Cambie la fuente a Arial en la cinta de opciones Inicio. Figura 8-8

Formateo de una hoja de cálculo

6. Apague la grabadora de macros.

Pruebe la macro al abrir un nuevo libro y ejecutar la macro. Al examinar el código VBA, verá que el único cambio que tal vez quiera hacer sea eliminar el código excedente en el área With Selection.Font, donde el único código que necesito es éste: With Selection.Font .Name = "Arial" End With

La macro completa se puede ver en la ﬁgura 8-9.

Capítulo 8

Uso de macros para formato de celdas

107

Cambio del esquema de color de una hoja de cálculo He aquí una pequeña macro que puede usar para aplicar un cambio de formato al color de Figura 8-9 Código de macro para cambiar formato de hoja de fondo de su hoja de cálculo. Con cálculo esta macro puede cambiar el aspecto de las filas de su hoja de cálculo para que cada tercera fila sea verde, como las viejas impresiones de computadora de las décadas de 1970 y 1980. Esta macro usa algunas características que se analizarán con mayor profundidad en otro apartado de este libro, pero como la macro trata principalmente con formato, es apropiado incluirla aquí. Hay un bucle For/Next en esta macro; aprenderá a crear sus propios bucles For/Next en el capítulo 11. También creará una variable en esta macro. Esto le ofrece un buen anticipo para el siguiente capítulo, el 9, que está dedicado por completo a las variables. Para usar esta macro, se selecciona un área de su hoja de cálculo. Dentro de esta macro, a la que he llamado ColorAlterno, se crea una variable para indicar el número de filas que seleccionó. Luego la macro cuenta de 2 en 2 y aplica un sombreado de color a filas alternas. Ésta no es una macro que pueda grabar, de modo que tiene que crearla en el editor de VB. Sub ColorAlterno Dim i as Long For i = 1 to Selection.Rows.Count Step 2 Selection.Rows(i).Interior.ColorIndex = 35 Next End Sub

La línea "Dim i as Long" usa la instrucción Dim o Dimension de VBA para asignar una variable, en este caso llamada “i”. La frase ‘Dim variable as long’ define la variable como un entero. El bucle For/Next, que se analiza con más detalle en el capítulo 11, simplemente establece que se toman las filas del área seleccionada, empezando con la 1 y continuando hasta el final de la selección, de 2 en 2, lo que significa que a cada tercera línea se aplica un color interior, o un sombreado. En este caso, he seleccionado 35 del Índice de colores de Excel (revise el siguiente Vínculo). Para usar un color diferente para su sombreado, simplemente cambie el 35 por otro número en el índice de color.

108

Macros con Excel 2007

Uso y combinación de macros de formato Ahora que hemos completado varias macros que proporcionan cambios de formato para nuestras hojas de cálculo, es hora de considerar las maneras en que pueden usarse. Cuando se construye una hoja de cálculo, de la misma manera en que seleccionaría un área y aplicaría cambios de formato, puede seleccionar un área y hacer clic en la lista Macros para desplegar las macros de formato que están disponibles. Cada macro que hemos creado combina varias herramientas de formato, y puede aplicar esas herramientas con una opción de macro, en lugar de tener que realizar cada tarea por separado. Cuando despliega una lista de macros, aparecen todas las macros de su Libro de macros personal, junto con cualquier macro que haya guardado en su libro actual, y cualquiera que haya guardado en otros libros abiertos. Elija una de sus macros de formato, haga clic en el botón Ejecutar y todas las tareas formateadas que cargó en la macro elegida se aplicarán al área de la hoja de cálculo que seleccionó. O si creó una macro como MiConﬁguraciónHoja, que fue diseñada para formatear toda la hoja Puede ver la paleta de Excel con sus números de cálculo, no necesitará correspondientes en http://www.mvps.org/ seleccionar ninguna celda dmcritchie/excel/colors.htm. antes de ejecutarla.

7¶ODVMP

Combinación de macros Puede tomar piezas de las macros que creó y hacer nuevas megamacros, combinando los fragmentos que le parezcan útiles. Por ejemplo, antes creamos la macro MiConfiguraciónHoja que formateó el aspecto de toda la hoja de cálculo. También creamos una macro FormatoNúmero, que cambia el formato de las celdas con números en un área seleccionada. La macro MiConfiguraciónHoja realiza la tarea de seleccionar todas las celdas de la hoja de cálculo. ¿Por qué no tomar el formato de número personalizado y aplicarlo también a todas las celdas? Podemos tomar el código de la macro FormatoNúmero, colocar ese código en la macro MiConfiguraciónHoja, y ahora toda la hoja de cálculo también tendrá formato de número personalizado, y sólo se ejecutó una macro. He aquí cómo funciona: 1. Abra el editor de VB. 2. Encuentre la macro FormatoNúmero. 3. Copie la línea de código que aplica su formato de número personalizado a la selección: Selection.NumberFormat = "#,##0_ ;[Red]-#,##0"

Capítulo 8

.&.0 Asegúrese de copiar y no cortar el código de la macro de formato de número. Aún queremos conservar la macro intacta, de modo que no elimine ninguna línea de código; sólo copie el código al portapapeles.

Uso de macros para formato de celdas

109

4. Encuentre el código de la macro MiConﬁguraciónHoja. 5. Inserte la línea de código que copió de la macro FormatoNúmero. El código puede colocarse en cualquier lugar después de la línea Cells. Select y antes del segmento With/End With. 6. Guarde sus cambios (CTRL+G).

He aquí la macro MiConﬁguraciónHoja revisada: Sub MiConfiguraciónHoja() ' MiConfiguraciónHoja Macro ' Toda la hoja, sin líneas guía, columna de 9 caracteres, fuente Arial Cells.Select ActiveWindow.DisplayGridlines = False Selection.ColumnWidth = 9 Selection.NumberFormat = "#,##0_ ;[Red]-#,##0" With Selection.Font .Name = "Arial" End With End Sub

Pruebe esta macro en un nuevo libro, y encontrará que, además de los otros cambios de formato que colocamos en la macro MiConﬁguraciónHoja, ahora se ha aplicado también el formateo de números.

Uso de comandos de VBA para seleccionar áreas de hoja de cálculo Hasta ahora hemos seleccionado un área de la hoja de cálculo antes de grabar una macro, de modo que cuando ejecutemos la macro seleccionaremos las celdas aplicables y luego ejecutaremos la macro, o también hemos seleccionado toda la hoja de cálculo dentro de la macro y luego hemos aplicado los comandos de las macro de formato a toda la hoja de cálculo. También puede organizar un área para que sea seleccionada dentro de la macro. He aquí algunos comandos de macro comunes que seleccionan áreas especíﬁcas de su hoja de cálculo.

ActiveCell.CurrentRegion.Select empieza con la celda actual.

Selecciona el rango especiﬁcado que

110

Macros con Excel 2007

Cells(numerofilas, númerocolumnas) Selecciona la ubicación de una celda determinada.

Range(“nombrerango”) Selecciona un rango con nombre (el nombre del rango aparece entre comillas).

Range(Cells(celdainicial),Cells(celdafinal)) Selecciona un rango al dar las coordenadas de inicio y ﬁnal. Las referencias a celdas se dan en términos numéricos (la celda B5 se muestra como Cells(2.5).

Selection.EntireColumn

Selection.EntireRow

Selecciona la columna actual.

Selecciona la ﬁla actual. Pruebe la creación de una macro al ingresar comandos en el editor de VB que incluya un comando de selección. He aquí un ejemplo de una macro que cambiará el aspecto de la región especiﬁcada, empezando con la celda actual, para que las celdas de la región se pongan en negritas y las columnas se redimensionen para que se ajusten mejor.

Figura 8-10 Antes de que se aplique la macro FormatoNegritas

Sub FormatoNegritas() ActiveCell.CurrentRegion.Select Selection.Font.Bold = True Selection.EntireColumn.AutoFit ActiveCell.Select End Sub

Para probar esta macro, abra un libro e ingrese alguna información en las celdas adyacentes. El comando CurrentRegion busca una región específica de celdas, de modo que en este caso las celdas adyacentes que están llenas reciban el beneficio de este formato de macro. En la figura 8-10 se muestra un rango de celdas antes de aplicar la macro FormatoNegritas, y la figura 8-11 muestra el mismo rango después de aplicar la macro FormatoNegritas. Figura 8-11 Después de que se ha aplicado la macro FormatoNegritas

6TP EF WBSJBCMFT FO NBDSPT

Las variables son una característica importante de cualquier tipo de programación, entre las que se encuentran las macros. En realidad, las variables son comunes en muchas situaciones normales en la vida. Por ejemplo, el clima de su ciudad, su deuda mensual en tarjetas de crédito y su apetito son variables. En su sentido más general, una variable es simplemente algo que cambia. En este capítulo se exploran las variables y temas relacionados como expresiones, operadores y matrices.

112

Macros con Excel 2007

¿Por qué usar variables? Las variables se crean en una macro por la misma razón que podría tener un sobre color Manila en su escritorio con el texto Tarjeta de crédito. Cada mes pone dentro sus estados de cuenta más recientes. Cada vez que obtiene uno nuevo, lo reemplaza por el del último mes. De modo que el concepto general de una variable es: un contenedor etiquetado que contiene una parte de información. Ahora, no es necesario que cambie el dato de una variable cuando se ejecuta un programa. (Digo dato porque cada variable contiene un valor único, un número o una cadena de texto). Por ejemplo, tal vez no use su tarjeta de crédito por varios meses, de modo que el valor de la deuda sigue siendo el mismo. O podría vivir en San Diego y el clima es idéntico por meses. Pero una variable tiene la capacidad de cambiar, y esto le da poder a un programa. El valor de una variable (sus datos) puede cambiar con base en la entrada de usuario o en acciones que toman lugar dentro de la macro mientras se ejecuta. Por ejemplo, si escribe una macro que administra sus ﬁnanzas personales, podría usar un cuadro de entrada para que le permita al usuario ingresar a la deuda de la tarjeta de crédito de este mes. De modo que esa variable cambia de acuerdo con la entrada del usuario. Pero una variable que contiene su valor neto actual cambiará con base en cálculos que la macro realiza, como restar los gastos de comida, sumar las ganancias por intereses, etc. En una macro puede usar el nombre de la variable en lugar de su dato (el número o la cadena de texto que contiene). Si coloca su cuenta de tarjeta de crédito actual cada mes en su sobre, siempre puede buscar en este sobre etiquetado Tarjeta de crédito para saber cuánto debe hasta el momento. De manera similar, una vez que se crea una variable en una macro en ejecución, un lugar en la memoria de la computadora contiene ese nombre de variable junto con sus “contenidos”, la información que esta variable “contiene” hasta que lo cambie el programa en ejecución, en caso de que cambie. Ya ha usado variables varias veces en los ejemplos de código de este libro. Sería difícil escribir macros sin el uso de variables. Usemos un ejemplo del capítulo 7: Sub ObtenerIniciales() x = InputBox("Por favor ingrese sus iniciales", "Ingrese sus iniciales") Range("A1").Select ActiveCell.FormulaR1C1 = x End Sub

En esta macro, le pide al usuario que escriba sus iniciales, y luego despliega éstas en una celda. La variable de esta macro es X. Lo más probable es que cada persona que ejecute esta macro escriba diferentes iniciales, de modo que el valor de X varía. Mientras se ejecuta la macro, este dato se retiene. Pero cuando la macro deja de ejecutarse, el dato se pierde y

Capítulo 9

Uso de variables en macros

113

debe volver a escribirse cada vez que la macro se ejecuta. Sin embargo, antes de que esta macro termine de ejecutarse, utiliza X por segunda vez para ingresar el dato en una celda.

Asignación de nombres a variables En el ejemplo anterior, usé el nombre simple X para mi variable. Pero usted puede usar cualquier nombre que desee, siempre y cuando siga algunas sencillas reglas. Una variable debe:

Empezar con una letra, de modo que no se permite 12Meses, pero sí Meses12.

No debe incluir ninguna de las palabras usadas por el propio VBA, como If, Sub o End.

No debe ser mayor de 255 caracteres.

No debe incluir caracteres especiales como signos de puntuación, corchetes, el símbolo de porcentaje (o ninguno de esos símbolos que se encuentran arriba de los números en el teclado). De modo que no está permitido Mi%, pero sí MiPorcentaje.

.&.0 Estas mismas restricciones de asignación de nombres se aplican a otras cosas a las que asigna nombre en VBA, como módulos, subs, etcétera. VBA le indicará si utiliza un nombre inapropiado al desplegar un mensaje de error de sintaxis (cuando asigna nombres a variables), o si trata de asignar un nombre incorrecto a un módulo, verá un mensaje de error No se trata de un nombre legal.

Por fortuna, no importa la manera en que use las mayúsculas y minúsculas. MiPorcentaje se ve como la misma variable que miporcentaje o MIPORCENTAJE. Así, puede combinar las mayúsculas y minúsculas como lo desee y VBA tratará estas variaciones como si fueran la misma variable. Por supuesto, si escribe mal el nombre, puede causar errores. Para VBA, MiPorcentaje y MiiPorcentaje son dos variables diferentes. (Este error en particular puede evitarse al declarar de manera explícita todas las variables. Consulte el resumen en páginas posteriores de este capítulo.)

Creación de una variable Puede crear una variable con sólo usarla. Puede hacer esto con un objeto como un cuadro de entrada (igual que en el ejemplo de código anterior del capítulo 7). Otra manera común de usar (y, por tanto, de crear) una variable es tan sólo asignar algún valor a la variable, como en este ejemplo: Burros = 15

114

Macros con Excel 2007

%&$-"3"$*0/&4 : 5*104 Declaración formal de la variable: algunas personas las aman y otras las odian. ¿Quién tiene razón? En realidad, declarar variables se considera esencial para programas grandes, pero no tanto con las macros. Las macros suelen ser pequeñas y autosuficientes, de modo que, por lo general, puede ignorar con toda seguridad un par de características relacionadas con las variables: declararlas y especificar su tipo. Sí, hay varios tipos de variables: string (texto), integer (número sin punto decimal), floating (números con punto decimal, de manera que pueden expresar fracciones), etc. Pero en programación más avanzada y compleja, se considera una buena idea declarar de manera formal y explícita cada variable. He aquí cómo se hace: Sub ObtenerIniciales() Dim x As String x = InputBox("Por favor ingrese sus iniciales", "Ingrese sus iniciales") Range("A1").Select ActiveCell.FormulaR1C1 = x End Sub

El comando Dim declara una variable. Dim le indica a VBA que ahora estamos creando

una nueva variable. En el ejemplo anterior, declaramos que el nombre de esta nueva variable es X y que es un tipo de variable string. Recuerde que si no incluye esta línea de código Dim en su macro, VBA creará automáticamente esta variable x por usted (a esto se le denomina declaración implícita). Entonces, ¿por qué son valiosas las declaraciones explícitas? La declaración formal de una variable ayuda a evitar un par de errores (escribir mal el nombre de una variable en otro lugar del código, o causar que VBA interprete de manera incorrecta el tipo de una variable y, por tanto, produzca una respuesta incorrecta). Sin embargo, se dice que para los novatos en la escritura de macros resulta abrumador preocuparse por las declaraciones explícitas. Pero siga adelante y declare todas sus variables si lo desea. Algo más: si deja fuera las declaraciones explícitas, ¿qué tipo de variable usa VBA cuando crea variables implícitas? Utiliza un tipo especial para todo propósito llamado variante, que permite que VBA administre automáticamente todo el problema de escribir variables.

Este acto, al mismo tiempo, crea el nombre de la variable y le asigna un valor. En este ejemplo, ha proporcionado una etiqueta (un nombre de variable) Burros, y se ha indicado que hay 15 burros. El usuario nunca ve esta etiqueta, Burros. Se usa por motivos de programación, y puede darle un nombre que tenga algún significado para usted. La mayoría de los programadores asignan nombres a las variables que los ayudan a comprender el significado o el propósito de la variable. Una variable llamada X es menos útil que una llamada Burros. Los nombres descriptivos de variable pueden facilitar aún más la lectura de su código, así como la prueba y modificación de su macro. Sin embargo, cuando el propósito de una variable es obvio (como un cuadro de entrada que solicita las iniciales del usuario), tiene la libertad de usar etiquetas breves como X, S, o cualquiera otra.

Capítulo 9

Uso de variables en macros

115

Combinación de variables en expresiones Las variables pueden interactuar entre sí. He aquí un ejemplo que muestra cómo pueden interactuar matemáticamente, agregando una variable a otra para producir una tercera: Burros = 15 Monos = 3 TotalAnimales = Burros + Monos

Como lo ilustra la tercera línea, puede usar nombres de variables como si fueran el propio contenido de éstas. Si dice Monos = 3, entonces ha asignado el valor 3 a la palabra Monos. Después de eso, puede usar Monos como lo haría con el número 3. TotalAnimales = Burros + Monos

La línea anterior es lo mismo que lo siguiente: TotalAnimales = Burros + 3

Al propio número (3 en el ejemplo) se le denomina literal porque es literalmente tres, literalmente un valor. Las literales de cadena se encierran entre comillas: “Tomás” es una literal de cadena. Cuando combina variables con variables (como en Burros + Monos) o variables con literales (como en Burros + 3), crea una expresión. ¿Qué es exactamente una expresión? Si alguien le dice que tiene un cupón de $1 de descuento por un CD que cuesta $15 de Amy Winehouse, de inmediato piensa en $14. De la misma manera, VB reduce a su forma más simple los diversos elementos vinculados en una expresión. A esta acción, reducir algo a su forma más simple, se le llama evaluación. En español simple: si escribe 15 – 1 en uno de sus programas, Visual Basic reduce ese grupo de símbolos, esa expresión, a un solo número: 14. Visual Basic simplemente evalúa lo que ha dicho y lo usa en el programa como la esencia de lo que está En programación, el término expresión suele usarse para tratando de decir. describir cualquier cosa que Una expresión está integrada por dos o más regresa un resultado mientras variables (o literales) conectadas por uno o más se ejecuta el programa. Algunas operadores. Llegaremos en breve a los operadores. El personas incluso consideran una sola variable como una expresión signo más en 2 + 2 es un operador. En total, hay 23 (o una referencia a un objeto). operadores que puede usar cuando programa macros. Sin embargo, estoy usando el Ciertos operadores, como > (mayor que), hacen signiﬁcado más tradicional y que las expresiones se evalúen como verdaderas o descriptivo de expresión en este falsas (cero representa falso, cualquier otro número libro. representa verdadero). Veamos cómo funcionan:

.&.0

116

Macros con Excel 2007

EdadJuan = 33 EdadBeatriz = 27 If EdadJuan > EdadBeatiz Then MsgBox "Él es mayor"

EdadJuan > EdadBeatiz es una expresión. Esta expresión asegura que EdadJuan es mayor que EdadBeatriz. El símbolo mayor que (>) es uno entre varios operadores de comparación. Visual Basic revisa las variables EdadJuan y EdadBeatriz y el operador de relación que los combina en la expresión. Luego VB determina si la expresión es verdadera. La estructura También puede usar operadores If...Then basa sus acciones en la veracidad o de comparación con texto. falsedad de la expresión. Cuando se usa con operadores En este caso, el cuadro de mensaje se despliega de texto, la comparación se basa porque la expresión es verdadera. Sin embargo, si en las cualidades alfabéticas del texto, y se dice que Andrés es cambia EdadBeatriz a 33 o algo mayor, entonces el menor que Antonio. cuadro de mensaje no se desplegará. La expresión se evaluará como Falsa.

.&.0

Nota especial sobre Mod: el operador Módulo (Mod) le da cualquier cantidad sobrante en una división, pero no el resultado de la propia división. Esta operación es útil cuando quiere saber si algún número se divide equitativamente entre otro número. De esa manera, podría escribir una macro que tome acciones a intervalos. Por ejemplo, si quiere imprimir el número de página en negritas cada cinco páginas, podría ingresar lo siguiente: If NúmeroPágina Mod 5 = 0 Then FontBold = True Else FontBold = 0 End If 15 Mod 5 da como resultado 0. 16 Mod 5 da como resultado 1. 17 Mod 5 da como resultado 2. Pero 20 Mod 5 da como resultado 0 una vez más.

Los operadores El operador > (mayor que) es sólo uno de muchos operadores. En la siguiente sección se describen todos ellos.

Operadores de comparación Los operadores de comparación siempre devuelven simplemente una respuesta de verdadero o falso. <

Menor que

Menor que o igual a

Mayor que

Mayor que o igual a

<> =

No es igual a Igual

¿Dos variables de objeto hacen referencia al mismo objeto?

Coincidencia de un patrón

Capítulo 9

Uso de variables en macros

117

Operadores aritméticos ^

Exponenciación (el número multiplicado por sí mismo; 5 ^ 2 es 25 y 5 ^ 3 es 125)

–

Negación (números negativos, como –25)

Multiplicación

División

División entera. (Este tipo de división no proporciona sobrantes, ni fracciones, ni punto ﬂotante de decimal: 8 \ 6 da como resultado 1. La división entera es más fácil, y el equipo la realiza más rápido que una división común.)

Mod

Módulo aritmético. (Consulte el Memo de la página 116 como referencia.)

Suma

–

Resta

Unión de cadenas

Operadores lógicos Not

Negación lógica

And

O inclusivo

XOR

(Uno, pero no ambos.)

Eqv

(Equivalente)

Imp

(Implicación: primer elemento falso, segundo verdadero.)

En la práctica, probablemente necesitará usar sólo And, Not y Or entre los operadores lógicos. Estos operadores trabajan, en esencia, de manera muy parecida a como lo hacen en una expresión normal. He aquí un ejemplo que nos muestra cómo usar Or: If 5 + 2 = 4 Or 6 + 6 = 12 Then MsgBox "Uno de los dos es verdadero."

En realidad, una de estas dos expresiones es verdadera. Seis más seis es igual a 12. De modo que se desplegará el mensaje. Sólo es necesario que una o la otra expresión sea verdadera en este caso. Sin embargo, si usa el operador AND, entonces ambas expresiones deben ser verdaderas para que el mensaje se despliegue: If 5 + 2 = 4 And 6 + 6 = 12 Then MsgBox "Ambos son verdaderos."

118

Macros con Excel 2007

Esto evalúa como Falso, de modo que no se despliega el mensaje.

El operador de cadena El operador & añade (une) piezas de texto: N = "Luisa" N1 = "Lane" J = N & " " & N1 MsgBox J

El resultado es Luisa Lane.

Precedencia de operadores Es necesario abordar uno de los problemas relacionados con las expresiones: la precedencia. Cuando usa más de un operador en una expresión, ¿cuál operador debe evaluarse primero? ¿Cuál operador toma precedencia sobre el otro?

Qué se evalúa primero He aquí un ejemplo que usa los operadores de multiplicación y suma: MsgBox 3 * 10 + 5

¿Esta expresión signiﬁca que se multiplique primero 3 por 10, obteniendo 30? ¿Y que luego se sume 5 al resultado? ¿Debe desplegar VBA 35 en el cuadro de mensaje? ¿O signiﬁca que se sumen 10 y 5, y que luego se multiplique el resultado por 3? Esto daría 45. Como puede ver, hay ambigüedad aquí. Esta expresión puede evaluarse de dos maneras distintas, dando dos resultados distintos. No podemos tener eso. Para asegurarse de que obtiene el resultado que pretende cuando usa más de un operador, use paréntesis para encerrar los elementos que desea que se evalúen primero. Si pretende que primero se ejecute 3 * 10 y luego se sume 5, escriba esto en su macro: MsgBox (3 * 10) + 5

Al encerrar ese 3 * 10 entre paréntesis, le indica a VBA que quiere que los elementos encerrados se consideren como un solo valor y que se evalúen antes de que ocurra otra cosa. Pero si trata de decir que primero se sume 10 + 5 y luego se multiplique el resultado por 3, escriba esto: MsgBox 3 * (10 + 5)

Capítulo 9

Uso de variables en macros

119

En expresiones complicadas, incluso puede anidar paréntesis para dejar en claro cuáles elementos deben calcularse en un orden determinado. Aquí he usado dos conjuntos de paréntesis: MsgBox 3 * ((9+1) + 5)

Sin embargo, VBA tiene un orden integrado de precedencia. Por tanto, si lo desea, puede dejar los paréntesis fuera. Si trabaja mucho con números, tal vez preﬁera memorizar la siguiente lista, que ilustra el orden de precedencia, donde la exponenciación se realiza primero, la negación después, y así sucesivamente. Aunque la mayoría sólo usan paréntesis y se olvidan del problema por completo, he aquí el orden en que VBA evaluará una expresión de la primera evaluada a la última:

Operadores aritméticos en orden de precedencia ^

Exponentes (6 ^ 2 es 36. El número multiplicado por sí mismo X número de veces.)

–

Negación (números negativos como –33)

Multiplicación y división

División entera

Mod

Módulo aritmético (cualquier valor que sobra en una división)

+–

Suma y resta

Los operadores relacionales Los operadores lógicos

Matrices: variables agrupadas Las matrices son variables que se han agrupado. Dentro de la estructura de una matriz, las variables comparten el mismo nombre de texto, pero cada una tiene su número de índice único. Como los números pueden manipularse matemáticamente (y los nombres de texto no), poner un grupo de variables en una matriz le permite trabajar de manera fácil y eﬁciente con ellas como grupo. Puede manipular los elementos (individuales) en la matriz al usar bucles como For...Next descritos en el capítulo 11. Las matrices se usan en programación de computadoras por la misma razón que los códigos postales se usan en correos. Imagine cientos de apartados postales con etiquetas de texto. Imagine la pesadilla de ordenar miles de cartas cada día en cajas que no están indexadas de alguna manera ni ordenadas numéricamente.

120

Macros con Excel 2007

Comparación entre números y nombres Las matrices pueden ser demasiado útiles, sobre todo en programas más largos y complejos. Por ejemplo, si quiere administrar datos acerca de un grupo de personas que vienen a cenar este ﬁn de semana, puede crear una matriz con sus nombres, como ésta: Dim Invitado (1 To 5) As String

Esto crea cinco “cajas vacías” en la memoria de la computadora y cada caja puede contener una sola parte de texto. Sin embargo, en lugar de cinco etiquetas individuales únicas para las cinco variables, éstas comparten el nombre Invitado, y cada caja se identiﬁca con el número de índice único de 1 a 5. Para llenar esta matriz con los nombres de los invitados, puede asignar los nombres sólo como los asignaría a variables normales, pero debe usar el nombre de la matriz más el número de índice, de la siguiente manera: Invitado(1) Invitado(2) Invitado(3) Invitado(4) Invitado(5)

= = = = =

"Luisa" "Sandra Pontevedra del trabajo" "Ricardo" "Juan" "Mamá"

Puede distinguir a una matriz de una variable regular porque las matrices siempre tienen paréntesis después del nombre. El número de índice va entre estos paréntesis. Ahora que ha llenado la matriz, puede manipularla de maneras que son mucho más eﬁcientes que usar variables ordinarias. Por ejemplo, buscando. ¿Qué pasa si queremos saber si existe un nombre determinado en una matriz? Puede usar un bucle For...Next para examinar la matriz: For I = 1 To 5 Imagen Invitado(I) = "Ricardo" Then Print "Se ha invitado a Ricardo." Next I

La clave para la utilidad de las matrices es que puede buscarlas, ordenarlas, eliminarlas o agregarles elementos empleando sus números de índice para identiﬁcar cada elemento. Es mucho más fácil acceder y manipular a números de índice que a nombres de variable. Suponga que necesita saber cuánto pagó de consumo de energía eléctrica en promedio durante el año. Podría recorrer la complicada ruta de usar un nombre de variable individual para cada mes, de la siguiente manera: EneElect = 90 FebElect = 122

Capítulo 9

Uso de variables en macros

121

MarElect = 125 AbrElect MayElect JunElect JulElect AgoElect

= = = = =

78 144 89 90 140

SepElect = 167 OctElect = 123 NovElect = 133 DicElect = 125 PagoElectAnual = EneElect + FebElect + MarElect + AbrElect + MayElect + JunElect + JulElect + AgoElect + SepElect + OctElect + NovElect + DicElect

O sólo podría usar una matriz para simpliﬁcar el proceso: Dim PagoElectAnual(1 To 12) PagoElectAnual(1) = 90 PagoElectAnual(2) = 122 PagoElectAnual(3) = 125 PagoElectAnual(4) = 78 PagoElectAnual(5) = 144 PagoElectAnual(6) = 89 PagoElectAnual(7) = 90 PagoElectAnual(8) = 140 PagoElectAnual(9) = 167 PagoElectAnual(10) = 123 PagoElectAnual(11) = 133 PagoElectAnual(12) = 125 For I = 1 To 12 Total = Total + PagoElectAnual(I) Next I

Al agrupar todas las variables bajo el mismo nombre de matriz, puede manipularlas por número de índice individual. Esto parece un pequeño ahorro de esfuerzo, pero recuerde que los programas de cálculo numérico pueden manipular grandes cantidades de datos, o tal vez necesiten reutilizar los mismos datos en varias partes diferentes del programa. Así que la moraleja es que si sólo está tratando con una pequeña cantidad de datos (como el nombre y la dirección del usuario), por lo general las variables son adecuadas. Pero si está trabajando con cantidades más grandes de datos (sobre todo datos que se relacionan entre sí de la misma manera, como el monto mensual pagado por consumo de energía eléctrica), las matrices son un método más eﬁciente.

122

Macros con Excel 2007

.&.0 Hay varios tipos de matrices, pero nos ceñiremos al más simple en este libro. También puede crear matrices con más de una “dimensión”. Son similares a una hoja de cálculo con varias columnas o, por cierto, a un conjunto de datos que cubren varias hojas de cálculo. Pero este tipo de matriz no suele encontrarse en macros.

Creación de una matriz La manera más simple de declarar una matriz consiste en usar el comando Dim: Dim NombreMatriz(1 To 12)

Se dice que el comando Dim asigna una dimensión (hace un espacio para) la nueva matriz. Se le indica a la computadora cuánto espacio apartar para la nueva matriz. Para crear espacio para 51 variables de texto que comparten el nombre Empleados y que se identiﬁcan de manera única por números de índice que van de 1 a 100, escriba lo siguiente en un módulo: Dim Empleados(1 To 100)

Puede visualizar esta matriz como si fuera similar a la primera columna de una hoja de cálculo de Excel: A1:A100. Cada celda puede contener un solo número o un texto, y se hace referencia a cada una de ellas con el mismo nombre (A en este caso) y también con el número de celda, que va de 1 a 100.

Reglas aplicables a las matrices He aquí unas cuantas reglas que deben seguirse para trabajar con matrices. Debe tratar de anticipar el número de elementos que necesitará en una matriz, pero sea generoso y agregue un poco más. Por ejemplo, si tiene 40 empleados, podría proporcionar espacio para 100, sólo en caso de que la empresa crezca. De modo que use 1 To 100 en lugar de 1 To 40 cuando declare esa matriz. Puede asignar nombres a las matrices de la misma manera que lo haría con variables: los nombres no son sensibles a mayúsculas y minúsculas, pero debe seguir las reglas explicadas en páginas anteriores de este capítulo en la sección “Asignación de nombres a variables”. A diferencia de las variables, las matrices no pueden declararse de manera implícita con sólo usarlas. Debe declarar formalmente cada matriz con el comando Dim (o comandos relacionados).

$SFBDJ·O EF SVUJOBT *G 5IFO &MTF

Puede usar rutinas If/Then/Else para dar lógica a sus macros. El proceso de la macro avanza en diferentes direcciones, dependiendo de los resultados de un comando If. Al igual que la función IF en Excel, el comando If/Then/Else depende de una instrucción lógica con un escenario verdadero y uno falso. Vimos un comando If/Then/Else en el capítulo 6, donde creamos una macro que preguntaba al usuario cuál área quería imprimir (consulte el siguiente recuadro). He aquí una descripción en lenguaje simple de la manera como funciona la macro. Ésta analiza la respuesta del usuario al determinar que, si ingresó 1, entonces debe imprimir ÁreaImpresión1 y luego terminar la macro. Sin embargo, la lógica de la macro continúa con el comando ElseIf que permite que el usuario ingrese algo diferente de 1. Si lo hace así, la macro no termina y, en cambio, avanza al siguiente paso, que determina que si el usuario ingresa 2, entonces debe imprimir ÁreaImpresión2 y luego terminar la macro. Por último, si ingresa una respuesta incorrecta, es decir, algo diferente de 1 o 2, la macro contiene una provisión para regresar a la pregunta original y dar otra oportunidad al usuario. (Observe que el usuario también tiene derecho de cancelar la operación de la macro en cualquier momento, al hacer clic en un botón Cancelar).

124

Macros con Excel 2007

h h ."$30 &41&$*"- %& *.13&4*0/ %&- $"1*56-0 Sub ImpresiónEspecial() Pregunta: Informe = InputBox("Ingrese 1 para imprimir Informe 1; ingrese 2 para imprimir Informe 2") If Informe = 1 Then GoTo Impresión1 ElseIf Informe = 2 Then GoTo Impresión2 Else GoTo Pregunta End If Impresión1: Application.Goto Reference:="ÁreaImpresión1" ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,1,,,TRUE,,FALSE)" End Impresión2: Application.Goto Reference:="ÁreaImpresión2" ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,1,,,TRUE,,FALSE)" End End Sub

El éxito de cualquier rutina If/Then/Else proviene de la anticipación de todas las respuestas posibles y de proporcionar comandos para tratar con cada condición posible.

La rutina If/Then/Else Cuando ingresa una instrucción If en su macro, el If precede a una instrucción lógica. Depende de Visual Basic determinar si esta instrucción es verdadera o falsa. A la instrucción que sigue al If se le llama expresión condicional. La condición de esta instrucción puede ser verdadera o falsa. Si la instrucción es verdadera, la macro hace lo que se le indica en la instrucción Then. Si sólo quiere que ocurra una acción cuando la instrucción es verdadera, entonces su macro se La instrucción Else en la rutina If/Then/Else no es un termina cuando tiene una instrucción If y una Then.

.&.0

comando obligatorio en esta estructura If/Then. Puede crear rutinas If/Then que no incluyan una dirección alterna proporcionada por la instrucción Else.

Cree una macro If/Then simple Por ejemplo, digamos que tiene una hoja de cálculo que contiene números que representan las cifras de ventas anuales. Si exceden 100 000, entonces querrá

Capítulo 10

Creación de rutinas If/Then/Else

125

que la macro calcule una boniﬁcación al colocar una cifra en la celda de la derecha que sea igual a la cifra original multiplicada por 2%. Antes de que pase a la programación en esta pequeña macro, piense en todos los pasos del proceso. He aquí todo lo que la macro necesita hacer:

Examinar el número en la celda actual y determinar si es mayor de 100 000.

Si el número es mayor de 100 000, mover el puntero una celda a la derecha.

Ingresar un cálculo en la nueva celda que multiplique el número en la celda original por 2%.

Parte de esta macro puede grabarse para que pueda recolectar el código. Puede grabarse a sí mismo moviendo el puntero una celda a la derecha y grabar la creación de la fórmula. Lo único que no puede grabar es la instrucción If/Then. La grabación del movimiento del puntero y la fórmula agrega este código de macro: ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=RC[-1]*0.02" ActiveCell.Offset(1, 0).Range("A1").Select

La primera línea de código refleja el movimiento del puntero sobre la celda original a la celda de la derecha: Offset(0,1) muestra el movimiento de 0 filas y 1 columna adelante (o la derecha). La segunda línea de código proporciona la fórmula: no hay movimiento de fila, pero se regresa una columna y se multiplica por 0.02. Debido a que queremos usar esta macro en varias celdas La tercera línea del código mueve al puntero una diferentes, asegúrese de habilitar celda hacia abajo, circunstancia que ocurre de manera la característica Usar referencias natural cuando oprime ENTER. Offset(1,0) alude relativas antes de habilitar la grabadora de macros. a un avance de 1 fila y 0 columnas. Si, en cambio, quiere que el puntero se mueva a la siguiente celda de la columna de números existentes (en anticipación de aplicar esta macro al número siguiente), entonces debe cambiar el desplazamiento a (1,–1). Las referencias a celda en Ahora todo lo que necesita para que esta macro macros se retratan siempre en este orden: fila y columna. Por tome la decisión lógica es la instrucción If que tanto, un desplazamiento de (1, pregunta si la celda original contiene un número –1) alude a una fila adelante o mayor de 100000. abajo y una columna atrás, o a la

.&.0

izquierda.

If ActiveCell.Value > 100000 Then

126

Macros con Excel 2007

La línea de código anterior pregunta si el valor de la celda activa es mayor que 100 000. Si la respuesta es verdadera, entonces se ejecuta la siguiente línea del código VBA. Si la respuesta es falsa, no sucede nada. Observe un tema ﬁnal: en cualquier momento en que use la instrucción If/Then, debe concluir la sección con una instrucción End If. Por tanto, este código de macro ﬁnal tiene este aspecto: Sub Bono() ' ' Macro para calcular bonificaciones. ' If ActiveCell.Value > 100000 Then ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=RC[-1]*0.02" ActiveCell.Offset(1, -1).Range("A1").Select End If End Sub

Para ejecutar esta macro, coloque su puntero en una celda que contenga un número que desea analizar. Haga clic en el botón Macros para encontrar su macro en la lista, haga clic en la macro y luego en Ejecutar. Si el número que elige es mayor que 100 000, su secuencia Then se activa y el cálculo aparece en la celda a la derecha del número original. El puntero regresa a la celda debajo del número original (véase la ﬁgura 10-1).

Figura 10-1 La hoja de cálculo después de que la macro se ha ejecutado

Agregue una operación Else para una respuesta falsa Hasta ahora hemos creado una macro que analiza una situación, y si ésta es verdadera, se ejecuta un comando. Si la situación no es verdadera, la macro termina. Pero no tenemos que detenernos aquí. Podemos llamar a la macro para que realice algunas otras tareas si la respuesta a la pregunta inicial es falsa. En la macro Bono que ya creamos, la macro no hace nada si la cifra es menor de 100 000. En cambio, podemos hacer que la macro siga a la columna de la boniﬁcación e ingrese un 0. Puede grabarse realizando esta tarea, si gusta, pero si revisa el código de macro que ya existe, debe ser un paso muy simple agregar la cláusula Else sin grabar la macro. Queremos que nuestra cláusula Else haga la operación de mover una celda a la derecha (ya tenemos ese código en el lugar) y luego ingrese cero. Este código debe hacer el truco:

Capítulo 10

Creación de rutinas If/Then/Else

127

ActiveCell.FormulaR1C1 = "0"

Entonces también necesita otra línea de código que proporcione las instrucciones para indicar dónde debe terminar el puntero, al igual que en la parte Then de la macro: ActiveCell.Offset(1, -1).Range("A1").Select

Y así, la macro completa, con instrucciones sobre cómo comportarse si la instrucción es verdadera y si es falsa, tiene este aspecto: Sub Bono() ' Macro para calcular bonificaciones. If ActiveCell.Value > 100000 Then ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=RC[-1]*0.02" ActiveCell.Offset(1, -1).Range("A1").Select Else ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "0" ActiveCell.Offset(1, -1).Range("A1").Select End If End Sub

Agregue una operación ElseIf Así que hemos visto cómo crear una macro que contenga una instrucción If/Then/ Else, y que maneje la situación cuando sólo hay una respuesta correcta y una incorrecta. Ahora ahondaremos en un nivel, y agregaremos una segunda instrucción If, conocida como ElseIf, de modo que si la macro devuelve una respuesta falsa a la primera instrucción If, hay otra oportunidad de que ocurra una instrucción verdadera. Esta vez agregaremos un nivel a la macro que se utiliza después de que se ejecuta la primera instrucción If y produce una respuesta falsa. En lugar de suponer de inmediato que no hay una bonificación para calcular, y colocar un cero en la celda de la bonificación, aplicaremos un segundo criterio: la capacidad de calcular una bonificación si la cifra de ventas excede 75 000. Esta vez el cálculo será de 1% en lugar de 2%. Así que el cálculo completo de la bonificación es de 2% si las ventas exceden 100 000 y 1% si las ventas están en el rango de 75 000 a 100 000. Es probable que pueda entender esta nueva parte del código sin grabar ningún paso. Como recordatorio, he aquí el código que calcula la primera bonificación: If ActiveCell.Value > 100000 Then ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=RC[-1]*0.02" ActiveCell.Offset(1, -1).Range("A1").Select

128

Macros con Excel 2007

Ahora, he aquí todo lo que tiene que hacer para agregar una capa ElseIf que pregunta si ActiveCell.Value excede 75 000 y aplica una boniﬁcación de 1%: ElseIf ActiveCell.Value > 75000 Then ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=RC[-1]*0.01" ActiveCell.Offset(1, -1).Range("A1").Select

El formato ElseIf funciona de la misma manera que el código If: debe acompañar la instrucción ElseIf con una Then. Esta parte de nuevo código debe insertarse en la macro. El producto terminado aparece en la ﬁgura 10-2. Los resultados aparecen en la ﬁgura 10-3.

Figura 10-3 Los resultados de la boniﬁcación basada en los cálculos de la macro Bono

Figura 10-2

La macro If/Then/Else terminada

Cree una macro If/Then/Else de varios niveles De regreso al capítulo 6, aprendió a crear una macro empleando el comando Case para ofrecer al usuario una selección de varios criterios diferentes. Creamos una macro que calculó los impuestos de una empresa de Estados Unidos. Podemos realizar una operación similar, empleando el formato If/Then/Else. Debido a que soy una contadora fiscal, quiero volver a los ejemplos fiscales para mis macros. Esta vez crearemos una macro llamada ImpuestoPersonal que calcula el impuesto sobre la renta de individuos estadounidenses, empleando el estilo de la macro If/Then/Else. De esta manera verá cómo anidar varias capas de condiciones If dentro de una sola macro. He aquí una gráfica que muestra las tasas de impuesto sobre la renta de Estados Unidos para un solo individuo.

Capítulo 10

Creación de rutinas If/Then/Else

10% sobre los ingresos entre $0 y $8 025

15% sobre los ingresos entre $8 025 y $32 550, más $802.50

25% sobre los ingresos entre $32 550 y $78 850, más $4 481.25

28% sobre los ingresos entre $78 850 y $164 550, más $16 056.25

33% sobre los ingresos entre $164 550 y $357 700, más $40 052.25

35% sobre los ingresos entre $357 700, más $103 791.75

129

Una macro debe examinar un monto de ingresos, establecer el rango en que cae el monto de ingresos, y En el capítulo 11 aprenderá luego calcula la tasa impositiva apropiada y coloca esa acerca de la creación de bucles cantidad en una celda. For/Next y podrá aplicar esa habilidad a macros como ésta Es más fácil empezar este cálculo en la parte If/Then/Else para que no superior, la tasa ﬁscal de 35%, porque entonces tenga que llamar a la macro puede preguntar si el ingreso se encuentra sobre en cada celda (la macro puede cierta cantidad, mientras que si quiere empezar en la recorrer toda la lista de cifras de ventas con un comando). parte inferior tiene que determinar si los ingresos se encuentran dentro de cierto rango. Puede estructurar la macro de cualquier manera, pero empezar en la parte superior da como resultado menos teclazos. Podemos seguir el ejemplo establecido en la macro anterior y hacer que la macro examine el contenido de una celda. En cambio, usaremos las técnicas aprendidas en el capítulo 7 y utilizaremos un InputBox. De esta manera, preguntaremos al usuario de la hoja de cálculo que escriba los ingresos que habrán de analizarse, y luego realizar la tarea sobre el monto ingresado en el cuadro. La primera parte del código establece el cuadro de entrada:

.&.0

IngresosGravables = InputBox("Ingrese sus impuestos gravables") If IngresosGravables > 357700 Then ActiveCell.Value = 103791.75 + (IngresosGravables - 357700) * 0.35 ElseIf IngresosGravables > 164550 Then ActiveCell.Value = 40052.25 + (IngresosGravables - 164550) * 0.33 ElseIf IngresosGravables > 78850 Then ActiveCell.Value = 16056.25 + (IngresosGravables - 78850) * 0.28 ElseIf IngresosGravables > 32550 Then ActiveCell.Value = 4481.25 + (IngresosGravables - 32550) * 0.25 ElseIf IngresosGravables > 8025 Then ActiveCell.Value = 802.50 + (IngresosGravables - 8025) * 0.15 ElseIf IngresosGravables > 0 Then ActiveCell.Value = IngresosGravables * 0.10 End If

130

Macros con Excel 2007

Figura 10-4 La macro ImpuestosPersonales usa un cuadro de entrada para solicitar un monto de ingresos gravables

Esta macro se colocó entre las líneas Sub ImpuestosPersonales() y End Sub, y obtiene una macro completa. Pruebe la macro al abrir una hoja de cálculo y ejecutar la macro ImpuestosPersonales. Aparece un cuadro de diálogo como el de la ﬁgura 10-4, pidiéndole que escriba sus ingresos gravables. Cuando ingrese la cantidad de ingresos y haga clic en Aceptar, el cálculo correcto de la tasa del impuesto aparece en la celda activa.

Cree una macro anidada If/Then En ocasiones una pregunta no es información suficiente para obtener los resultados que necesite. Puede crear una macro que le plantee más de una pregunta If, y produce diferentes capas de acciones con base en las respuestas. Por ejemplo, digamos que queremos confirmar que los ingresos gravables usados en la macro anterior son realmente de 2008, para que se apliquen las tasas correctas. En lugar de proporcionar sólo un cuadro de entrada que le pregunte sus ingresos, podemos primero preguntar los ingresos y luego pedir confirmación de que se trata de los ingresos de 2008. Si la respuesta es Sí, la operación de la macro continúa y los impuestos se calculan. Si la respuesta es No, la ejecución de la macro se detiene y aparece un mensaje indicando al usuario que no están disponibles las tasas de impuestos para años distintos. La primera línea de la macro permanece intacta (el comando InputBox le pregunta al usuario los ingresos gravables de 2008). A continuación necesitamos una nueva instrucción If, preguntando si se trata realmente de los ingresos gravables de 2008. El siguiente código Figura 10-5 La macro revisada le pide al da como resultado el mensaje mostrado en la figura usuario que confirme que introdujo los ingresos gravables de 2008 10-5: x = MsgBox("¿Se trata de ingresos gravables de 2008?", 3) If x = 6 Then

A continuación debe insertar todo el código de la macro ImpuestosPersonales. Es adecuado añadir sangría a este código para que pueda llevar registro de sus instrucciones If y la instrucción End If relacionada. Por último, necesita proporcionar la posibilidad de que el usuario no ingrese

.&.0 Los códigos de cuadro de mensaje (3 para un cuadro Sí/No, y 6 para la respuesta Sí) pueden encontrarse en el capítulo 7.

Capítulo 10

Figura 10-6 Este mensaje aparece si el usuario indica que no se introdujeron los ingresos de 2008

Creación de rutinas If/Then/Else

131

una respuesta Sí en el cuadro de mensaje. Nos ocuparemos de esta contingencia al proporcionar un cuadro de mensaje adicional que advierta al usuario que el impuesto no puede calcularse, como se muestra en la ﬁgura 10-6. Si el usuario hace clic en Aceptar (la única opción), el cuadro desaparece y termina la operación de la macro.

Else MsgBox ("No se pueden calcular sus impuestos sobre la renta") End If

La macro completa (llamada ImpuestosPersonales2 para distinguirla de su predecesora) se muestra en la ﬁgura 10-7.

Figura 10-7

Código completo de la macro ImpuestosPersonales2

A &YQMPSBDJPO EF CVDMFT

Cuando tiene gran cantidad de datos que necesita procesar, a menudo le indica al equipo que haga algo una y otra vez. En programación, a este funcionamiento repetitivo se le llama bucle. En la vida real se le llama trabajo. Los bucles son útiles en muchas situaciones diferentes. Buscar entre 5 082 facturas las que están vencidas requiere bucles. Poner dirección a 400 sobres también. En realidad, a los equipos suelen llamársele procesadores de datos, y los bucles son una parte importante del procesamiento. Uno de los comandos más útiles en VBA es For. Indica el inicio de una estructura de bucle de uso común. VBA realiza de manera repetida la instrucción entre el For y el comando que la acompaña, Next. El número de veces que el equipo recorrerá el bucle se especiﬁca con dos números que se encuentran después de For. He aquí un ejemplo. Vea si puede adivinar el valor de la variable X después de que termina la ejecución de este bucle: Sub ExplorarBucle For I = 1 To 12 X = X + 2 Next I MsgBox X End Sub

134

Macros con Excel 2007

Un bucle tiene una variable contadora de bucles, y en un bucle For...Next esta variable suele llamarse I. Nadie está muy seguro si la I es de iteración o de incremento. Pero la idea es que cada vez que se ejecuta el bucle, el valor de I aumenta automáticamente. De igual manera, también es atractiva la idea de que un bucle itera (se repite). En cualquier caso, la primera línea de este bucle se traduce así: cada vez que se ejecuta este bucle, se eleva el valor de la variable I en 1. Cuando I ﬁnalmente llega a 12, el bucle está completo y se detiene (la ejecución continúa con el código después de Next I). En este ejemplo, la ejecución continúa desplegando el cuadro de mensaje. En otras palabras, el bucle se recorre 12 veces; luego se detiene y se despliega el cuadro de mensaje. Así, cuando se ejecuta este código, recorre 12 veces el código dentro del bucle. En realidad, se suma 2 al contenido de X cada vez que el bucle se ejecuta. X inicia con 0, pero la primera vez que se recorre el bucle X contiene 2, y la siguiente vez contiene 4, luego 6, etc. La variable X aumenta 12 veces (1 To 12), y cada vez se suma 2. De modo que cuando se ejecuta esta pequeña macro, el cuadro de mensaje despliega 24 como valor ﬁnal de X.

/6&7"4 ."/&3"4 %& 130#"3 ."$304 Cuando ejecuta la macro con el cuadro de mensaje dentro del bucle, tiene que hacer clic en el botón Aceptar 12 veces. Sin embargo, hay una manera de salir antes de un bucle: oprima CTRL+INTERRUMPIR. Esto lo pone en el modo de interrupción. Se despliega un cuadro de mensaje especial, dándole tres opciones: O Continuar O Terminar

Reanuda la ejecución. Sale de la macro.

O Depurar Permanece en el modo de interrupción, donde puede usar herramientas de depuración como paso a paso a través del código, o la ventana Inmediato para revisar el valor de las variables. Por ejemplo, haga clic en el botón Depurar, en el cuadro de mensaje del modo de interrupción. Ahora oprima CTRL+G para desplegar la ventana Inmediato. Escriba ?X en la ventana Inmediato y oprima ENTER. Esta macro despliega el valor actual de la variable X. El valor se despliega en

la ventana Inmediato. Elija Ejecutar | Restablecer para detener la ejecución (dejando el modo de interrupción y regresando al modo de escritura de código normal en el editor). En cuanto a la ventana Inmediato, tiene otro uso especial cuando está escribiendo código que incluye bucles u otros funcionamientos repetitivos: el despliegue de resultados. En todo este libro, incluido este capítulo, ha visto cómo usar el comando MsgBox para ayudar a probar código de macros al mostrarle resultados. Pero como se ilustró en el segundo ejemplo de código, puede ser tedioso tener que hacer clic de manera repetitiva en el botón Aceptar para que el código avance cada vez que se despliega un cuadro de mensaje dentro de un bucle. La solución es imprimir resultados repetitivos en la ventana Inmediato en lugar de hacerlo en un cuadro de mensaje. Pruebe la ejecución de esta macro y vea los resultados en la ventana Inmediato:

CapĂtulo 11

ExploraciĂłn de bucles

135

/6&7"4 ."/&3"4 %& 130#"3 ."$304 $0/5

Sub ExplorarBucle()

Si lo desea, puede dejar la ventana Inmediato abierta todo el tiempo mientras se prueba el cĂłdigo. La ventana Inmediato se comporta de manera muy parecida al Bloc de notas. Por ejemplo, para limpiar la ventana Inmediato, sĂłlo oprima CTRL+A para seleccionar todo el contenido y luego oprima SUPR para eliminarlo.

For I = 1 To 12 X = X + 2 Debug.Print X Next I End Sub

Sub ExplorarBucle() For I = 1 To 12 X = X + 2 MsgBox X Next I End Sub

Tres tipos de bucles For...Next Puede especiďŹ car el nĂşmero preciso de bucles que se harĂĄ antes de pasar la estructura For...Next (como hicimos en los ejemplos de cĂłdigo anteriores de este capĂtulo): For I = 1 to 20

El segundo tipo de bucle For...Next emplea una variable (o expresiĂłn) para especiďŹ car el nĂşmero de bucles. Tal vez quiera permitir que el usuario decida cuĂĄntas copias de un documento deben imprimirse. Se despliega un cuadro de entrada con el indicador â&#x20AC;&#x153;ÂżCuĂĄntas copias?â&#x20AC;? y despuĂŠs el usuario escribe el nĂşmero, y se recorre el bucle el nĂşmero de veces que especiďŹ ca la variable: Observe que si agrega un

.&.0

punto y coma al ďŹ nal de la lĂnea Debug.Print, los resultados se imprimen horizontalmente dentro de la ventana Inmediato, en lugar de usar una lĂnea separada para cada resultado. AdemĂĄs, si tiene dudas acerca del comando Debug.Print en general, consulte el resumen en pĂĄginas anteriores de este capĂtulo.

Sub ExplorarBucle1() NĂşmeroDeCopias = InputBox("ÂżCuĂĄntas copias?") For I = 1 To NĂşmeroDeCopias Debug.Print I; Next I End Sub

136

Macros con Excel 2007

Salto de pasos en un bucle La tercera variación del bucle For...Next incluye la omisión de pasos. Hay un comando opcional que funciona con For...Next llamado Step. Step puede adjuntarse al ﬁnal de la línea For para que VBA salte iteraciones de bucle, para omitir algún paso. Step altera la manera en que cuenta el bucle. Como ha visto, un bucle suele contar de uno en uno: For I = 1 to 12 Debug.Print I; Next I

El resultado es 1 2 3 4 5 6 7 8 9 10 11 12 Sin embargo, cuando usa el comando Step, puede especiﬁcar que el bucle cuente cada cierto número (empleando, por ejemplo, Step 2): For I = 1 to 12 Step 2 Debug.Print I; Next I

El resultado es 1 3 5 7 9 11 O podría recorrerlo cada 15 veces (Step 15): For I = 15 to 90 Step 15 Debug.Print I; Next I

El resultado sería 15 30 45 60 75 90

.&.0

Anidamiento de bucles

Los bucles For...Next también pueden anidarse, uno dentro de otro. Al principio esta estructura parece confusa, y con frecuencia lo es. Pero sólo haga lo que la mayoría de los programadores hacen cuando están perplejos: hackear. El término hackeo tiene varios signiﬁcados en computación, y uno es probar varios métodos para ver cuál funciona. Con bucles anidados, puede probar varios números para las variables de conteo, y mover comandos “dentro” y “fuera” del bucle para al ﬁnal comprender cómo llegar a los resultados que desea. Los bucles anidados pueden resultar confusos porque agregan una nueva dimensión cuando usa un bucle interior. Éste interactúa con el bucle exterior de una manera que sólo queda inmediatamente clara para el matemático experimentado. En esencia, el Variaciones adicionales con el uso del comando Step incluyen el conteo hacia atrás empleando un paso negativo (For I = 10 To 1 Step -1), o incluso el conteo con fracciones (Step .25).

Capítulo 11

Exploración de bucles

137

bucle interno se repite el número de veces especiﬁcado por su propia variable de conteo, multiplicado por la variable de conteo del bucle externo. En esta situación, simplemente siga hackeando hasta que todo funcione de manera adecuada. Hackeo para un programador signiﬁca precisamente lo mismo que modelar para un escultor: ir quitando partes hasta que surge la forma deseada. Observe que en el siguiente ejemplo usamos un comando Debug.Print simple (sin variable) en el bucle externo. Esto tiene el efecto de avanzar una línea hacia abajo en la ventana Inmediato. Así se facilita la visualización de la actividad en los dos bucles: For I = 1 To 5 For J = 1 To 10 Debug.Print I; Next J Debug.Print Next I

Da como resultado: 1111111111 2222222222 3333333333 4444444444 5555555555 Observe que puede iniciar la variable de cuenta del bucle en cualquier lugar; no es necesario que inicie el contador con 1. Y el tamaño de Step puede ser lo que desee, incluidos números negativos si desea contar hacia abajo en lugar de hacerlo hacia arriba. For I = 10 To 1 Step - 2 Debug.Print I; Next I

Da como resultado: 10 8 6 4 2 Puede usarse cualquier expresión numérica con For...Next. (Consulte el capítulo 9 para conocer una deﬁnición de expresión.) Sin embargo, debe ser posible el rango que está contando. El siguiente rango no es posible: For I = -10 To -20 Step 2 Debug.Print "bucle"; I Next

.&.0 Observe que puede agregar palabras al comando Debug. Print. Esto puede ser útil como una manera de identiﬁcar la variable que está imprimiendo, como esta: Debug.Print “i = "; i

138

Macros con Excel 2007

Este bucle no hace nada cuando lo ejecuta. No puede. Está pidiendo que cuente hacia abajo, pero su comando Step es positivo. Como cualquier entidad inteligente haría cuando se confronta con una solicitud sin sentido, VBA no hace nada con las instrucciones. Lo ignora. Para que éste sea un verdadero bucle, tiene que hacer negativo Step con –2: For I = -10 To -20 Step -2 Debug.Print "bucle"; I Next

Cómo evitar el temible bucle sin ﬁn He aquí algunas notas adicionales acerca de los bucles. Las estructuras de bucle For...Next pueden ser tan grandes como lo desee: pueden contener todas las líneas de código entre el For y el Next que desee. Por otra parte, puede poner toda una pequeña estructura For...Next en una sola línea, si lo desea, al separar las “líneas de código” usando dos puntos: For J = 1 To 5: Debug.Print J: Next J

Y esa J ﬁnal es opcional, pero si la omite hará que su programa sea ligeramente menos fácil de comprender. Con el siguiente ejemplo se ilustra cómo puede quitar el nombre de la variable. Esta práctica no está respaldada por algunos maestros de programación (aunque se encuentra con frecuencia en programación real): For J = 1 To 5: Debug.Print J: Next

Ahora llegamos al famoso bucle sin ﬁn. También conocido como bucle interminable, no hay manera de detener esta estructura. No ha proporcionado salida, ni condición que permita que el bucle termine. He aquí un ejemplo:

.&.0 Es una práctica común indentar (añadir sangrías) a los comandos entre For...Next, If...Then y otras estructuras (Do...Loop, With, Select Case, etcétera). Esta sangría hace que el código sea más fácil de leer al indicar que los elementos con sangría son subordinados, que están controlados por una estructura que los rodea de alguna manera.

Sub BucleSinFin() For J = 1 To 5 J = 3 Next J End Sub

Cuando ejecute esto, la única manera de detenerlo consiste en oprimir CTRL+INTERRUMPIR si lo está ejecutando en el editor de VB, u oprimir INTERRUMPIR, si lo está lanzando desde el interior de Excel como un macro. Tal vez vea el problema aquí. La computadora está tratando de terminar un trabajo interminable.

Capítulo 11

Exploración de bucles

139

Cada vez que recorre el bucle, restablece la variable de cuenta J a 3, de modo que J nunca llega a 5 y, por tanto, sigue ejecutando el código debajo de la estructura (en este caso, el End Sub). Si usa Step 0, también creará un bucle que nunca termina. En efecto, un bucle sin fin causa que la computadora vaya a un estado de suspensión animada. Hay varias situaciones en que sí quiere un bucle interminable, como los que repiten de manera continua las demostraciones de producto que ve en tiendas. Pero estas situaciones son raras. Los bucles sin fin suelen ser un error que necesita corregir. Los bucles sin fin son aún más comunes en otras estructuras de bucle, como Do...Loop, que exploraremos a continuación.

Do...Loop: repita hasta que se cumpla una condición Una opción a For...Next es Do...Loop. Este tipo de bucle no usa un contador específico. En cambio, contiene una condición que especifica cuándo se supone que debe terminar el bucle. Aunque For...Next es la estructura de bucle más común en programación, Do...Loop es la estructura más flexible. La razón para que For...Next se use con tanta frecuencia es que cuando está escribiendo su programa, con frecuencia sabrá cuántas veces quiere que se haga algo. Así que puede proporcionar la variable de cuenta con su condición de salida. Como generalización, cuando quiera que algo se haga de manera repetida pero no sabe el número de veces que se repetirá, use un bucle Do...Loop en lugar de For...Next. For...Next es adecuado cuando sabe el número de veces que algo debe hacerse. Do...Loop es adecuado cuando conoce una condición que debe satisfacerse en lugar del número preciso de veces que debe realizarse una tarea. He aquí un ejemplo: For...Next significa “Cepilla tu cabeza 150 veces”. Do...Loop significa “Cepilla tu cabeza hasta que brille”.

La ﬂexibilidad de las estructuras Do...Loop proviene de las diversas maneras que puede conﬁgurar pruebas internas que llevan a la salida del bucle. He aquí un ejemplo simple: Do While x < 14 x = x +2 Debug.Print x Loop

140

Macros con Excel 2007

El resultado es: 2 4 6 8 10 12 14 Es muy fácil leer este código. Signiﬁca que recorra este bucle siempre y cuando el valor de la variable X sea menor de 14. Una vez que se cumple la condición, se sale del bucle y se continúa con cualquier código que se encuentre después del bucle.

Las cuatro variedades de Do...Loop Do...Loop viene en cuatro variedades. La primera se ilustra con el siguiente ejemplo de código: Do While x < 66 Loop

La segunda usa un comando Until como este: Do Until x < 66 Loop

La diferencia entre las versiones de Until y de While es que la primera sólo se recorre mientras la condición sea falsa. En otras palabras, recorre el bucle hasta que x sea menor que 66. (En oposición a que lo haga hasta que la condición siga siendo verdadera: se recorre el bucle mientras x sea menor que 66.) En realidad, estas dos variaciones son casi intercambiables. Es sólo cosa de saber cómo expresar las cosas, como la diferencia entre “Barre hasta que el patio esté limpio” en comparación con “Barre mientras el patio esté sucio”. La computadora no se preocupa mucho acerca de esas cosas. Sin embargo, expresar la condición de una manera determinada en ocasiones puede hacer que su signiﬁcado sea más claro para usted y otros seres humanos que leen su programa.

Desplazamiento de la condición de salida al final de la estructura del bucle La tercera y cuarta variaciones de Do...Loop prueban la condición al final en lugar del principio del bucle. Esto asegura que el bucle siempre se ejecute por lo menos una vez. Si coloca la condición de prueba al principio del bucle y la prueba falla, el bucle nunca se ejecutará aunque sea una sola vez. VBA omitirá por completo los comandos dentro del bucle. Por tanto, si quiere que un bucle siempre se ejecute por lo menos una vez, coloque la prueba de la condición al final del bucle, como se muestra en seguida: Do Y = Y + 1 Debug.Print Y Loop Until Y >= 0

Capítulo 11

Exploración de bucles

141

Da como resultado: 1 Pero regrese la prueba Y >= 0 a la parte superior del bucle y no se imprimirá nada. No sucede nada porque este bucle ni siquiera se ejecuta una vez. Y no es mayor o igual que cero al principio de esta estructura de bucle. Así que el bucle de inmediato sale de sí mismo sin realizar ninguna de las instrucciones dentro del bucle, incluida Debug. Print: Do Until Y >= 0 Y = Y + 1 Debug.Print Y Loop

El resultado es nulo.

While...Wend, un Do...Loop menos poderoso Otra variación en la estructuras de bucle es While...Wend. Esta estructura simplemente sigue recorriendo el bucle mientras una condición siga siendo verdadera. Para que se ejecute siempre y cuando X sea menor que 24, escribiría: While X < 24: X = X +1: Debug.Print X: Wend

While...Wend no tiene comando de salida. Puede pasar estructuras For...Next o Do...Loop empleando comandos Exit For o Exit Do. Pero While...Wend no tiene esos comandos de salida forzada. Además, While...Wend está limitado a probar la condición al principio de la estructura del bucle.

Administración de colecciones de objetos con bucles For Each...Next El propio VBA administra la condición de salida en un tipo especial de estructura de bucle llamada For Each...Next. Puede usar For Each...Next con una matriz, porque VBA sabe el tamaño de la matriz, de modo que sabe cuándo salir del bucle. En otras palabras, con la estructura For Each...Next no usa una variable de cuenta, o una condición especíﬁca de salida, como lo hace con las estructuras For...Next y Do...Loop. He aquí un ejemplo: Sub PruebaForEach() Dim MisNombres(1 To 6)

142

Macros con Excel 2007

For I = 1 To 6 MisNombres(I) = "Nombre" & I Next I For Each Cosa In MisNombres Debug.Print Cosa Next End Sub

El resultado es Nombre1 Nombre2 Nombre3 Nombre4 Nombre5 Nombre6 En este código, primero declara una matriz llamada MisNombres. Especiﬁca que esta matriz contiene seis elementos. Luego usa un bucle tradicional For...Next para almacenar seis cadenas (Nombre1, Nombre2, etcétera) en esta matriz. Pero cuando pasa a imprimir el contenido de esta matriz, usa la estructura For... Next para iterar en ella. Cuando VBA alcanza el límite superior de la matriz (índice del elemento 6), automáticamente sale del bucle For...Each. Observe que usé una variable llamada Cosa, pero puede usar cualquier nombre que quiera en el bucle For...Each. Sin embargo, la variable debe ser del tipo de variante u objeto (si no especiﬁca un tipo de variable, VBA lo hace automáticamente variante, como en el ejemplo anterior). For...Each suele usarse con colecciones (un conjunto de objetos). Puede crear sus propias colecciones, como ésta: Sub HacerColección() Dim MisNombres As New Collection For I = 1 To 20 MisNombres.Add "Nombre" & I Next I For Each Cosa In MisNombres Debug.Print Cosa Next End Sub

Puede usar el método Add para llenar una colección con datos, y luego usar For... Each para iterar en la colección. Hay colecciones integradas, como Sheets que contiene todas las hojas de cálculo (y gráﬁcas) del libro activo (o seleccionado). He aquí un ejemplo que emplea el método UsedRange para desplegar todos los datos en una hoja de cálculo: Sub VerColección() Dim r As Range For Each r In ActiveSheet.UsedRange Debug.Print r.Value Next End Sub

Capítulo 11

Exploración de bucles

143

Aquí crea una variable del tipo Range y luego la usa para acceder a cada celda del “rango usado” en la hoja activa (las celdas contenidas dentro del rectángulo imaginario dibujado alrededor de las celdas que contienen datos reales). Pruebe este código. Verá cómo esta técnica puede ser una manera muy rápida de buscar en una hoja de cálculo (para modiﬁcar celdas que cumplen un criterio determinado, o encontrar, por ejemplo, cualquier celda que contenga 33): For Each r In ActiveSheet.UsedRange If r = 33 Then Debug.Print r.Value End If Next

En el siguiente ejemplo se ilustra cómo usar la propiedad de dirección del objeto de rango para identiﬁcar cuáles celdas contienen el valor 33. Sub VerColección() Dim r As Range For Each r In ActiveSheet.UsedRange If r = 33 Then Debug.Print r.Address & ": "; Debug.Print r.Value Debug.Print 'mover una línea hacia abajo en la ventana Inmediato End If Next End Sub

&$)& 6/ 7*45";0 " -04 0#+&504 : $0-&$$*0/&4 %& &9$&Excel, como otras aplicaciones de Ofﬁce, contiene muchas colecciones integradas que puede emplear en su programación. Para ver los diversos objetos y colecciones de Excel, siga estos pasos: 1. De la ventana principal de Excel, oprima ALT+F11 para ir al editor de VB. 2. En el editor, elija Ayuda | Ayuda de Microsoft Visual Basic | MSDN en la Web. Verá una página Web con un campo de Live Search. 3. Escriba Mapa del modelo de objetos de Excel 2007 en el campo de búsqueda.

Aparece una lista de vínculos. 4. En la lista de vínculos, haga clic en Referencia del modelo de objetos de Excel (o mapa del modelo de objetos de Excel 2007). 5. Tal vez sea necesario hacer clic en un vínculo adicional: Mapa del modelo de objetos de Excel. En cualquier caso, lo que está buscando puede encontrarse al pegar esta dirección en su explorador de Internet: http://msdn2.microsoft.com/en-us/ library/bb332345.aspx

144

Macros con Excel 2007

&$)& 6/ 7*45";0 " -04 0#+&504 : $0-&$$*0/&4 %& &9$&- $0/5

Ahora podrĂĄ examinar el modelo de objetos, como se muestra en la ďŹ gura 11-1. Los objetos se muestran en azul, las colecciones en amarillo. Puede hacer clic en cualquiera de los objetos o colecciones desplegados en el mapa del modelo de objetos para abrir una

pantalla de Ayuda que describe la manera de usar ese objeto o colecciĂłn. En realidad, con frecuencia encontrarĂĄ buenos ejemplos de cĂłdigo que puede copiar y pegar en su editor de VB para explorar o modiďŹ car y usarlas en sus propias macros.

Figura 11-1 Excel tiene docenas de colecciones integradas que pueden manipularse, como se muestra en este mapa del modelo de objetos

"EJDJ·O EF DPOUSPMFT B TVT IPKBT EF D MDVMP

Puede ejecutar macros de varias maneras: J

Crear un método abreviado de teclado como ALT+F.

Agregar un botón a la barra de herramientas Acceso rápido. Oprimir ALT+F8 para desplegar el cuadro de diálogo Macros. Oprimir F5 mientras se encuentra en el editor de VB (después de hacer clic dentro de la macro para colocar el cursor de inserción parpadeante en la macro).

J J J

Colocar un control, como un botón, en una hoja de cálculo con el que pueda interactuar un usuario.

Es este último método el que exploraremos en este capítulo. Verá cómo agregar controles a su hoja de cálculo para facilitar a las personas la ejecución de sus macros. Hacer clic en un botón u otro control que esté allí, sobre la hoja de cálculo, tiene que ser la manera más rápida e intuitiva de ejecutar código. Recordará, de la sección “Creación de cuadros de diálogo personalizados”, en el capítulo 7, que puede crear un cuadro de diálogo personalizado al agregar controles del Cuadro de herramientas en el editor de VB. Luego puede escribir algún código en un manejador de eventos, como Click. Este método es muy similar a escribir una macro, excepto que el código del manejador de eventos se ejecuta cuando el usuario hace clic en el control.

146

Macros con Excel 2007

.&.0 Si lo preﬁere, puede hacer clic para colocar el botón, pero arrastrar le da control sobre el tamaño y la forma. Siempre puede volver a cambiar la posición y la forma de un control al hacer clic en él para seleccionarlo, y luego arrastrarlo como un todo para moverlo, o arrastrar uno de los ocho pequeños “manejadores” en los lados del control para cambiar su tamaño.

Puede poner controles en hojas de cálculo, y escribir código para que las cosas pasen cuando el usuario hace clic (o interactúa de otra manera) con los controles. Veamos cómo se hace.

Uso de botones para ejecutar código

En este ejemplo, supondremos que con frecuencia hace acercamientos para ver celdas seleccionadas, y luego regresa a la vista normal. De modo que, en lugar de hacer zoom usando las ﬁchas, la barra deslizable de acercamiento en la parte inferior de la ventana de la hoja de cálculo, o hace clic en íconos, trata de poner un par de botones en su hoja de cálculo. Hace clic en uno para realizar el acercamiento; hace clic en el otro para regresar a la vista normal.

Adición de botones a una hoja de cálculo Para agregar un botón a una hoja de cálculo, siga estos pasos: 1. Haga clic en la ficha Programador de la cinta de opciones. 2. Haga clic en el ícono Insertar para desplegar los controles disponibles, como se muestra en las figuras 12-1 y 12-2. 3. Haga clic en el ícono Botón de comando (control ActiveX), como se muestra en la figura 12-2.

h -" ."/&3" '"$*Si quiere clonar un control (para que esté seguro de que uno nuevo tendrá el mismo tamaño y la misma forma que el original), sólo haga clic para seleccionarlo, oprima CTRL+C para copiarlo y luego CTRL+V para hacer una o varias copias.

Suceden dos cosas cuando hace clic en el ícono de botón: se habilita el ícono Modo Diseño (se vuelve dorado) y su puntero toma la forma de una cruz. El ícono Modo Diseño de la cinta de opciones signiﬁca que no puede interactuar normalmente con la hoja de cálculo (en cambio, puede colocar y cambiar el tamaño de los controles, y además, como verá, escribir código para esos controles). Para regresar a la interacción con la hoja de cálculo normal en cualquier momento, sólo haga clic en el ícono Modo Diseño. Se deshabilitará.

Capítulo 12

Adición de controles a sus hojas de cálculo

147

Nuevo botón

Figura 12-1

Use el ícono Insertar para agregar controles a una hoja de cálculo

4. Con su ratón, arrastre a algún lugar de la hoja de cálculo para crear un botón, como se muestra en la ﬁgura 12-1. 5. Haga clic en el botón para seleccionarlo, y luego oprima CTRL+C para copiarlo. 6. Oprima CTRL+V para pegar un botón nuevo, clonado. Arrastre el clon para que quede junto al original. Figura 12-2 Haga clic en el primer botón de los controles ActiveX, Botón de comando

7. Ahora quiere cambiar la leyenda predeterminada (CommandButton1) por algo que signiﬁque algo para el usuario. Haga clic con el botón derecho en el primer botón y elija Objeto Botón de comando | Modiﬁcar del menú contextual. 8. Ahora el control del botón está enmarcado con líneas diagonales. En este momento puede escribir una nueva leyenda.

Figura 12-3 Cuando elige la opción Modiﬁcar, puede escribir directamente una leyenda para el botón

9. Escriba Acercar como leyenda para el botón de la izquierda, y repita los pasos 7 y 8 para cambiar la leyenda del botón de la derecha por Alejar (véase la ﬁgura 12-3).

148

Macros con Excel 2007

Ajuste de las propiedades del control Ahora tal vez quiera cambiar el aspecto o modificar de otra manera alguna de las propiedades de sus nuevos controles de botón. Haga clic en uno de los botones para seleccionarlo solo. (Si los botones están agrupados, entonces desagrúpelos usando la opción Agrupar del menú contextual. Si ambos botones están seleccionados, deselecciónelos al hacer clic en cualquier lugar de la hoja de cálculo. Sólo debe estar seleccionado un botón porque es la única manera de traer al frente la ventana Propiedades.) Haga clic con el botón derecho en uno de sus Si quiere reubicar dos o más botones para desplegar su menú contextual, y controles como una unidad, luego elija Propiedades (o haga clic en el elemento mantenga oprimida la tecla CTRL Propiedades de la sección Controles de la cinta de mientras hace clic en cada control. opciones). Aparece la ventana Propiedades, como se Esto crea un grupo de controles que ahora puede arrastrar por la muestra en la figura 12-4. hoja de cálculo y colocar en un Haga doble clic en la propiedad Font de la nuevo lugar (como un grupo). ventana mostrada en la figura 12-4, para desplegar En este modo, también puede el cuadro de diálogo Fuente, que se muestra a la hacer clic con el botón derecho en uno de los controles agrupado, derecha. Cambie la propiedad Name, en la ventana elija Formato de control del Propiedades, por Acercar (para el botón con la menú contextual y ajuste varias misma leyenda). Esta propiedad Name se volverá propiedades, incluido el tamaño. automáticamente el nombre de la sub (el manejador

.&.0

Figura 12-4

Puede cambiar muchas de las cualidades de un control en su ventana Propiedades

Capítulo 12

Adición de controles a sus hojas de cálculo

149

-04 %04 $0/+6/504 %& $0/530-&4 Estoy seguro de que debió notar el extraño conjunto de controles con sombra (casi duplicados del conjunto de ActiveX) que se muestra en la ﬁgura 12-2. Se trata de los Controles de formulario, y hemos evitado tratar con ellos en este capítulo. ¿Por qué? Porque son menos ﬂexibles y útiles que sus contrapartes de ActiveX. Son compatibles con versiones anteriores de Excel, pero es más difícil trabajar con ellos, tienen pocas propiedades, no pueden desencadenar eventos y, por lo general, permiten menos libertad. Por tanto, mi sugerencia es que los evite a favor del conjunto de controles ActiveX. Sin embargo, hay unas cuantas situaciones donde tal vez quiera usar un control de formulario. Los controles ActiveX necesitan que cree un manejador de eventos (como

Button_Click), pero los controles de formularios más antiguos pueden desencadenar directamente los macros. En realidad no es una gran diferencia (sólo copie el código de una macro existente y péguela en un manejador de eventos). Pero si quiere hacer algo realmente rápido y sencillo, como agregar un botón que sólo ejecute una macro existente, siga adelante y use botones de control de formulario, si lo desea. No puede seleccionar controles de formulario al hacer clic en el botón Modo Diseño. No tiene efecto en ellos. En cambio, haga clic con el botón derecho en él para seleccionarlo. Y para asignar una macro haga clic con el botón derecho y luego elija Asignar macro del menú contextual. Aparece una lista de macros.

de evento donde escribe su código para que las cosas sucedan cuando se hace clic en este botón). También cambie el tamaño por algo que le parezca adecuado. Ahora haga clic en el otro botón para seleccionarlo. Observe cómo la ventana Propiedades cambia para desplegar las propiedades de este control recién seleccionado. Repita los pasos anteriores para cambiar la propiedad Name del segundo botón por Alejar y el tamaño de fuente para que coincida con el otro botón. Si lo desea, juegue un poco con los colores, etcétera. Estas cosas son de gusto personal, o la falta de éste. Incluso puede agregar una imagen usando la propiedad Picture, lo que puede ser agradable. Ahora cierre la ventana Propiedades. Está listo para agregar código.

Escritura de código para botones y otros controles Muy bien. Ya tiene algunos botones en su hoja de cálculo, pero ¿dónde está la belleza sin la posibilidad de que realicen algún trabajo? Bueno, en algunos casos la belleza es su propia justiﬁcación. Pero queremos que estos botones hagan algún trabajo. Haga doble clic en el botón con la leyenda Acercar. Se abre el editor de VB, desplegando el manejador de eventos Click para el botón llamado Acercar. (Si no se abre el editor, ha dejado de seleccionar el ícono Modo Diseño en la ﬁcha Programador de la cinta de opciones. Selecciónelo e intente de nuevo.)

150

Macros con Excel 2007

El Acercar_Click que ve en la figura 12-5 maneja el evento Click (responde cuando el Como aprendió en páginas usuario hace clic) para este botón llamado Acercar. anteriores de este libro, para En otras palabras, es como una macro, pero en lugar encontrar el código que necesita insertar en este manejador de ejecutarse mediante una combinación de teclas de eventos sólo es necesario de acceso directo o algún otro desencadenador, este grabar una macro que realice código se ejecuta cuando el usuario hace clic en este las acciones que quiere que el manejador de eventos realice. botón en particular. Además, observe en la figura 12-5 que este manejador de eventos Button_Click está almacenado dentro (y por tanto sólo está disponible para) esta hoja de cálculo en particular. Esta disponibilidad restringida y local tiene sentido porque este botón sólo está localizado en esta hoja de cálculo. El código no está en el proyecto Personal (así estaría disponible para todas las hojas de cálculo actuales y futuras). Queremos hacer que el botón Acercar amplifique cualquier área seleccionada en la hoja de cálculo. Si nada está seleccionado, agranda todo. Escriba la siguiente línea en el evento Click:

.&.0

Private Sub Acercar_ Click() ActiveWindow.Zoom =True End Sub

Figura 12-5 Utilice el editor de VB para escribir código para manejadores de evento como el evento Click de este botón

Ahora regresemos a la hoja de cálculo, haga clic en la ﬁcha Programador y deje de seleccionar el ícono Modo Diseño. De esta manera puede probar su nuevo botón. Haga clic en el botón Acercar. Pruébelo con algunas celdas seleccionadas. Para regresar a la vista normal, haga clic en el ícono 100% de la ﬁcha Vista de la cinta de opciones. Para programar su botón Alejar, seleccione el ícono

Capítulo 12

.&.0 Un clic es un evento, pero casi todos los controles tienen eventos adicionales. Puede ver una lista de estos eventos al desplegar el cuadro de lista en el lado superior derecho del editor de VB cuando esté escribiendo código para un manejador de evento.

.&.0 Manejadores de eventos como estos dos eventos Botón_Click quedarán destruidos, perdidos para siempre, si elimina la hoja de cálculo dentro de la cual se crearon o incluyeron. De modo que si tiene algún código que desea preservar, cópielo y luego péguelo en un archivo del Bloc de notas o alguna otra área de almacenamiento segura para posible uso futuro.

Adición de controles a sus hojas de cálculo

151

Modo Diseño, luego haga doble clic en el botón Alejar para abrir el manejador de evento Click. Escriba la siguiente línea en el evento Alejar_Click(): Private Sub Alejar_Click() ActiveWindow.Zoom = 100 End Sub

Ahora puede hacer un acercamiento y restaurar la vista normal, mediante los dos botones.

Exploración de otros controles

Encontrará otros controles disponibles cuando haga clic en el ícono Insertar, de la ﬁcha Programador de la cinta de opciones. Revisemos varios de estos controles y veamos lo que hacen y cuándo son apropiados. Los controles del botón de alternar son útiles para situaciones similares al funcionamiento de un encendedor de luz: dos estados, las luces están encendidas o apagadas. Es similar a lo que ha estado haciendo con la característica de acercar que agregó a su hoja de cálculo en este capítulo. Podría ser más eﬁciente reemplazar los dos botones de acercamiento con un solo botón de alternar. Cuando hace clic, se acerca. Cuando hace clic por segunda vez, se aleja.

Adición de un botón de alternar Pasemos a la prueba. Haga clic en el ícono Modo Diseño para habilitar ese modo, y luego en su botón Acercar personalizado para seleccionarlo. Oprima SUPR para deshacerse del botón. De igual manera, seleccione y elimine su botón Alejar. Al eliminar estos controles no destruye el código de manejador de evento en el editor de VB, que reutilizaremos para nuestro nuevo botón de alternar. Ahora agregue el botón de alternar ActiveX al hacer clic en el ícono Insertar, junto al ícono Modo Diseño, y luego seleccione el botón de alternar y arrástrelo, o sólo haga clic para colocarlo en la hoja de cálculo. Haga doble clic en el nuevo botón de alternar para abrir el editor de VB, y luego escriba esto en el evento Click del botón:

152

Macros con Excel 2007

Private Sub ToggleButton1_Click() Static Oprimido As Boolean Oprimido = Not Oprimido If Oprimido Then ActiveWindow.Zoom = True Else ActiveWindow.Zoom = 100 End If End Sub

Este código merece explicación. El comando Static es muy útil en situaciones como ésta, de modo que debe tenerlo en su bolsa de trucos del programador. Static preserva variables. Cuando una variable se declara como Static (en oposición al comando Dim descrito en el resumen del capítulo 9), esa variable y el valor que contiene se retienen. Una variable ordinaria, no estática, se destruye junto con su contenido cada vez que una macro o un manejador de eventos finalizan su ejecución. Pero queremos preservar el valor en nuestra variable llamada Oprimido, porque eso nos indica el estado actual del botón de alternar. He aquí lo que sucede, paso a paso, en este código. 1. Declaramos una variable estática (no volátil) llamada Oprimido. Y especificamos que éste es un tipo de variable booleano. 2. Al escribir Oprimido = Not Oprimido, cambiamos el valor de Oprimido a su opuesto. En otras palabras, si contenía False, ahora contiene True. Una variable booleana sólo puede contener dos valores, False o True. Esto lo hace ideal para una situación de alternar. Cada vez que se ejecuta la línea de código Oprimido = Not Oprimido, cambia de False a True, o de True a False. En otras palabras, esta variable booleana funciona como un encendedor de luz o, para el caso, como un control de botón de alternar. Incluso, esta variable es Static, de modo que recuerda su contenido aunque el código del manejador de eventos haya terminado su ejecución. 3. El resto del código se comprende fácilmente, una vez que ve cómo cambia una variable booleana estática entre dos estados cuando utiliza el comando Not. If Oprimido significa “si la variable llamada Oprimido contiene el valor True”. Por ello, si contiene el valor True, el usuario ha hecho clic en ese botón (el botón tiene el aspecto de que está hundido en la hoja de cálculo, indicando que está “encendido” o “activo”). Por tanto, queremos hacer un acercamiento como respuesta a este clic: ActiveWindow.Zoom = True

Capítulo 12

Adición de controles a sus hojas de cálculo

153

Pero si ésta es la segunda vez que el usuario hace clic en este botón, se deshabilita el acercamiento. De modo que se desencadena la sección Else de la estructura If...Then, y este código se ejecuta para restaurar la vista normal: ActiveWindow.Zoom = 100

Uso de un botón de control de número El control de número tiene una flecha hacia arriba y una hacia abajo. En realidad, son dos botones en uno. Por lo general, los botones de control de número se usan para aumentar o reducir algo. Lo usaremos para aumentar o reducir el nivel de acercamiento cada vez que el usuario hace clic en él. Haga clic en la flecha hacia arriba y el acercamiento aumenta. La flecha hacia abajo lo reduce. Veamos cómo puede usar un botón de control de número para permitir que el usuario ajuste el porcentaje de acercamiento. Agregue un botón de control de número a su hoja de cálculo y también agregue un control de etiqueta. Podría pensar que tenemos que usar una variable Static en este código para recordar el valor de acercamiento actual, pero está equivocado. Puede acceder a muchas propiedades de controles durante la ejecución del código de VBA. Y este botón de control de número tiene una propiedad value que aumenta 1 cada vez que hace clic en la flecha hacia arriba y disminuye 1 cada vez que hace clic en la flecha hacia abajo. Y este value se retiene hasta que cierra la hoja de cálculo en que reside. La propiedad value del botón de control de número empieza en cero. Pero sabemos que el factor de acercamiento mínimo para una hoja de cálculo es 100 (lo que significa 100%). Y el acercamiento máximo es 400. Así que no queremos permitir que el usuario haga clic fuera de esos valores. Vamos a usar la propiedad de valor y a multiplicarlo por 100 para establecer el acercamiento. Así que desearemos un valor de 1, 2, 3 o 4. No permitiremos que el usuario vaya debajo de 1 o arriba de 4 al hacer clic (lo que causaría un error y detendría la ejecución del código). Podría escribir código que revise estos límites (>0 y <5) e imponerlos en su manejador de eventos. Pero eso es complicado y, en este caso, simplemente no es necesario revisar lo que los programadores llaman condiciones de límite. Para nuestra fortuna, el botón de control de número tiene un par de propiedades integradas que limitarán su rango. Así, haga clic con el botón derecho en el botón de control de número y elija Propiedades del menú contextual para abrir su ventana Propiedades. Establezca la propiedad Min en 1 y la propiedad Max en 4. Haga clic con el botón derecho en la etiqueta y elija Propiedades del menú contextual (si la ventana Propiedades aún está visible, puede hacer clic en el control de la etiqueta para seleccionarla y la ventana Propiedades cambiará automáticamente para desplegar las

154

Macros con Excel 2007

propiedades de la etiqueta). Cambie la propiedad de leyenda de la etiqueta por: Haga clic para acercar. Ahora haga doble clic en el botón de control de número para abrir su ventana de código. Private Sub SpinButton1_Change() v = SpinButton1.Value ActiveWindow.Zoom = 100 * v End Sub

En este código, accede a una propiedad del botón control de número tres veces. Primero lea (obtenga) el valor que se encuentra en la propiedad SpinButton1. Observe cómo debe especiﬁcar el nombre del control, separado por un punto (.) de la propiedad cuyos datos desea ver. Así, después de que se ejecuta la primera línea de código, la variable v contiene cualquier cosa que se encuentre en la propiedad de valor de este botón de control de número en particular. Recuerde que las propiedades Max y Min del botón evitan que los valores vayan debajo de 1 o arriba de 4. Luego todo lo que tiene que hacer es establecer la propiedad de acercamiento a 100 por el valor actual del botón de control de número. Esto da como resultado cuatro valores posibles de acercamiento: 100, 200, 300 y 400.

h*OEJDF Nota: Los números de página que hace referencia a ﬁguras aparecen seguidos por una “f ”.

2008, tasas de impuesto sobre la renta de Estados Unidos, 79f, 128–129

A Acceso rápido, barra de herramientas, 53–57 Aceptar, botones, 95 acercamiento, botones adición a hojas de cálculo, 146 controles del botón de alternar, 151–152 escritura de código para, 150–152 Acercar_Click, manejador de eventos, 149–150 Activate, eventos, 96 ActiveCell, comando, 19 ActiveCell.CurrentRegion. Select, comando, 109 ActiveX, botones de alternar, 151 ActiveX, controles, 147f, 149 ActiveX CommandButton, ícono, 146, 147f Add, método, 142 Alfabética, ﬁcha, 17

alineación horizontal, 24 almacenamiento asignación de macros a la barra de herramientas, 53–57 asignación de métodos abreviados de teclado a macros, 49 en el libro actual, 46 en el libro de macros personal, 44 en libros, 45 hacer que las macros estén disponibles, 47 módulos VBA, 46 protección de macros, 48 Alternar carpetas, botón, 17 Ancho de columna, cuadro de diálogo, 33–34 anidadas, macros If/Then, 130 anidamiento de bucles, 137 apóstrofos, 19, 36–37 Archivo, menú, 71–72 archivos habilitados para macros, 45–46 argumentos de función, 83f Argumentos de función, ventana, 81

argumentos, 18–19, 86–87 opcionales, 87 Ayuda, campo, 23

B barra de fórmulas, 28–29 barras de herramientas Acceso rápido, 54–57 cinta de opciones Programador, 2–3, 16 Vista, 2 Visual Basic, 23f Bloquear proyecto para visualización, casilla de veriﬁcación, 74–75 Bono, función, 82–83 booleanas, variables, 152–153 Borrar, botón, 40–41 botones, argumento, 90–91 botones adición a hojas de cálculo, 146–148 agrupados, 147–148 barra de herramientas, 54, 56 de alternar, 151–152 escritura de código para, 149–151

156

Macros con Excel 2007

bucles Do...Loop desplazamiento de condición de salida al final del, 140–141 estructura While... Wend, 141 revisión general, 139–140 For Each...Next, 141–144 For... Next anidamiento, 136–138 comando Step, 136 sin fin, 138 prueba de macros, 134–135 revisión general, 133–134 sin fin, 138 Button_Click, manejador de eventos, 150–151

C cadena, operador, 117–118 cálculos de impuestos ﬁscales corporativos, 78–82, 128–130 de tasa de interés, 69–70 ﬁnancieros, comandos, 69–70 cambio de nombre de módulos, 46–47 Cancelar, botones, 95 celdas, formato. Véase formato de celdas Cells, comando, 109 centrada, alineación, 24–26, 102–103

centrado de texto, 24–25 Centro de conﬁanza, ventana, 47–48 Click, manejador de eventos, 149–151 Código, grupo, 16 código copia y pegado, 38 ejemplos, 61–62 escritura para botones y controles, 149–151 para cuadros de entrada, 87–90 para macros, 63–64 familiarización con, 23–24 limpieza, 63–65 para formato de celdas, 24–26 recolección construcción de macros, 35–39 eliminación de macros antiguas, 39–40 módulos, 35–36 prueba de macros, 39–40 revisión general, 34–36 ventana, 17–18, 60–61, 88–89 visualización, 29–31 colecciones, 142–144 color, paleta, 107–108 columnas, 33, 39, 106 Coma, estilo, 104 Comandos disponibles en, menú desplegable, 54–56 comandos

búsqueda de argumentos, 87–88 cálculos ﬁnancieros, 69–71 comprensión, 65–66 editor de VB, 71–72 error, 70–72 escritura comprensión del código, 63–64 limpieza de código, 63–65 revisión general, 60–65 fecha y hora, 67–68 manipulación de texto, 65–68 matemáticos, 68–69 por nombre ActiveCell, 19–20 ActiveCell. CurrentRegion.Select, 109 Cells, 109 CurrentRegion, 110 DateDiff, 68–69 Debug.Print, 136–138 Dim, 89–90, 113–114, 121–122, 152 Dimension, 107–108 End Sub, 20, 36 Exit Sub, 70–71 Format, 67–68, 70–71 FormulaRlCl, 19–20 GoSub, 74 If...Then, 88–89 InputBox, 86–88, 129 InStr, 66–67 LCase, 67–68 Left, 67–68 Mid, 66–67

Índice

MsgBox, 68–69, 90 Offset, 21 Paste Special, 30–31 Range, 20, 109–110 Replace, 67–68 Round, 68–69 Select, 20–21 Selection, 110 Static, 152–153 Step, 136–138 Until, 140 Ver Macros, 54 While, 140 With...End With, 37–39 With Selection, 63–64, 102–103 comas, 86 CommandButton, control, 94–95 conexión con el código, 87 constantes integradas, 92 conteo hacia atrás, 136 contraseñas, 74–75 control de números, botón, 152–154 controles ajuste de propiedades, 148–150 escritura de código para, 149–151 copia código, 108 controles, 146 macros, 36, 47–48 cuadro de entrada escritura de macros completas, 87–88 escritura y prueba de código, 87–90

Cuadro de herramientas, 94 cuadros de mensaje, 90–92, 130–131 CurrentRegion, comando, 110

D DateDiff, comando, 68–69 Debug.Print, comando, 136–138 declaración formal de variables, 113–114 implícita, 113–114 Depuración, menú, 72 Descripción, campo, 4–5 desplazamientos, 125–126 Dim, comando, 89–90, 113–114, 121–122, 152 Dimension, comando, 107–108 dimensiones de una matriz, 121–122 Diseño de página, cinta de opciones, 32 división entera, 68–69 Do...Loop desplazamiento de la condición de salida al ﬁnal, 140–141 estructura While...Wend, 141 revisión general, 139–140 dos puntos, 74

E Edición, menú, 71–72 edición de macros

157

en el editor de VB Explorador de proyectos, 16–17 módulos, 16–17 ventana de código, 17–18 ventana Propiedades, 16–17 guardado de macros editadas, 22–23 editor de VB. Véase Visual Basic, editor Ejecutar, menú, 72 Else, operación, 124, 126–127 Elself, operación, 127–128 End Sub, comando, 20, 36 Error de sintaxis, mensaje, 113 escritura código para botones y controles, 149–151 para cuadros de entrada, 87–90 para macros, 63–64 comandos comprensión del código, 63 limpieza de código, 63–65 revisión general, 60–65 subrutinas, 75–77 etiquetas, 74–75 verticales, 61–64 Exit Sub, comando, 70–71 Explorador de objetos, 21–23 Explorador de proyectos, 16–17 Explorador de proyectos, ventana, 34–35, 60, 95–96

158

Macros con Excel 2007

expresiones, 114–115, 136–137 condicionales, 124–125 extensiones de archivo, 45–46

F fecha, macros, 8–11 fecha, opciones, 100–101 fecha y hora, comandos, 67–68 fechas estáticas, 9–10 For Each...Next, bucles, 141–144 For...Next, bucles anidados, 136–138 comando Step, 135–136 interminables, 137–139 formación celdas apariencia de los números, 104–105 cambio de formatos existentes, 100–101 comandos de VBA, 109–110 encabezados de columna, 101–104 esquema de color, 152–153 formación de macros, 107–110 revisión general, 10–12, 105–107 macros, 44–46, 77–79, 107–110 Formato, comando, 67, 70–71 Formato, menú, 72 formulario, controles, 148–149 FormulaRlCl, comando, 19–20

fórmulas, despliegue como valores prueba de macros, 29–30 revisión general, 28–31 visualización del código de macros, 29–31 fracciones, 135–136 funciones personalizadas ocultamiento de datos conﬁdenciales, 82–83 simpliﬁcación de cálculos complicados, 78–82

G GoSub, comando, 74 grabación de macros cinta de opciones Programador, 2–4 formación con macros, 10–12 guardado de macros en el libro actual, 13–14 en el Libro de macros personal, 13, 44–45, 60–61 en un nuevo libro, 13–14 lectura de macros NOMBRE1, 3–7 NOMBRE2, 6–7 NOMBRE3, 7–9 macros simples de datos, 8–11 revisión general, 31–34 Grabadora de macros, 12, 33–34, 101 Grabar macro, cuadro de diálogo campo Método abreviado, 49–51

grabación de macros de fecha en, 9–10 Guardar macro en, cuadro de lista, 60–61 revisión general, 4–5 guardado cambios, 26, 42 libros, 44–45 macros edición, 22–23 en el libro actual, 13–14 en el Libro de macros personal, 13, 44–45, 60–61 en un nuevo libro, 13–14 Guardar como, ventana, 13–14 Guardar macro en, campo, 4–5

H habilitación de macros, 47–48 hackeo, 136–137 hojas de cálculo adición de botones, 146–148 adición de botones de alternar, 151–153 botón de control de números, 152–154 cambio de la apariencia aspecto de números, 104–105 encabezados de columna, 101–104 esquema de color, 107–108 revisión general, 105–107 informe mensual, 10–11 propiedades de control de ajuste, 147–150

Índice

revisión general, 145–146 Horizontal, opción, 32–33, 38–39 HOY, fórmula, 8–11

I I, variable, 134 If...Then, comando, 88–89 If/Then/Else, macros de varios niveles, 128–130 llamadas a subrutinas, 75–76 macros If/Then anidadas, 130–131 simples, 124–126 operación Else, 126–127 operación Elself, 127–129 revisión general, 123–124 ImpCorp, función, 79–82 Imprimir, característica Líneas de cuadrícula, 32–33 inclusión en el código, 875 incrementos, 134 índice, números, 121–122 inﬁnitos, bucles, 138–139 Información rápida, característica, 87f, 88 Información rápida, opción, 22 informes mensuales, 10–11 Inmediato, ventana, 134–135 InputBox, comando, 75–77, 86–90, 129 Insertar función, cuadro de diálogo, 80–81 Insertar, ícono, 164, 147f InStr, comando, 66–67 interactivas, macros

argumentos, 86–87 cuadros de diálogo personalizados adición de elementos a cuadro de lista, 95–97 ofrecimiento al usuario de listas de opciones, 93–95 respuesta la selección del usuario, 96–97 cuadros de entrada escritura de macros completas, 88 escritura y prueba de código para, 88–90 cuadros de mensajes, 90–93 interrupción, modo, 72, 134 iteración, 134

L LCase, comando, 67 lectura de macros NOMBRE1 líneas de comando, 19–21 líneas de comentario, 19–20 revisión general, 18–21 NOMBRE2, 20–21 NOMBRE3, 20–22 Left, comando, 67 libro actual, 13–14, 45–47 Libro de macros personal, eliminación de macros, 41–42 guardado de macros, 13, 44–45, 60–61

159

libros cierre, 33–35 guardado, 44–46 guardado en nuevos, 13–14 habilitados para macros, 13–14 ocultos, 41–42 límite, condiciones, 153–154 limpieza de código, 62–64 líneas de comentario, 36–38 líneas guía, 32, 106 listas de argumentos, 68 Listltem, propiedad, 96–97 literales, 114–115

M Macro, botón, 5–6, 33–34 macro, código. Véase código macro, comandos. Véase comandos macro If/Then/Else de varios niveles, 128–130 manejador de eventos, 96, 149–151 manejadores de error, 70–71 manipulación de texto, comandos, 65–67 mapa del modelo de objetos, 143–144, 144f matrices comparación entre números y nombres, 120–122 reglas, 121–122 revisión general, 119–120 mayúsculas, cambio a, 112–113 Mensaje, argumento, 86–87 mensajes de error, 112–113

160

Macros con Excel 2007

Mid, comando, 66–67 Mod, operador, 116–117 Modiﬁcar, opción, 147–148 Modiﬁcar botón, ventana, 56–57 Modo Diseño, ícono, 146 módulos, 16–17, 35–36 MsgBox, comando, 68, 90–91

N Nombre de macro, campo, 4–5, 41 NOMBRE1, macro líneas de comando, 19–21 líneas de comentario, 19–20 prueba, 5–7 revisión general, 18–21 NOMBRE2, macro, 7, 20–21 NOMBRE3, macro, 8–9, 20–22 nombres de funciones, 79–80 de macros, 22 de módulos, 46–47 de subrutinas, 74–75 de variables, 112–113 en comparación con números, 120–122 números de elementos, 97 en comparación con nombres, 120–122 expresiones numéricas, 136–137

O objetos, 22, 24, 143–144 Offset, comando, 20–21

Opciones de Excel, ventana, 2–4, 54–57 Opciones de hoja, área, 32 Opciones de macro, cuadro de diálogo, 49–51 operaciones operadores aritméticos, 116–117 de cadenas, 117–118 de comparación, 115–117 de texto, 115–116 lógicos, 117–118 matemáticas, 68–69 revisión general, 115–116 orientación de la página, 32–33, 38–39

P página, orientación, 32–33, 38–39 paréntesis, 18–19, 79–81, 86, 118–119 Pegado especial, comando, 30–31 personalización barra de herramientas, 53–57, 57f cuadro de diálogo adición de elementos a cuadro de lista, 95–97 ofrecimiento de lista de opciones, 93–95 respuesta a selección de usuario, 96–97 fecha, 100–101 función ocultamiento de datos conﬁdenciales, 82–83

simplificación de cálculos complicados, 78–82 Personalizar barra de herramientas de acceso rápido, lista desplegable, 56–57 Por categorías, ficha, 17–18 precedencia de operadores, 118–119 predeterminadas, fechas, 100 Predeterminado, argumento, 86–87 procesadores de datos, 133 proceso de selección, 101–102 Programas, cinta de opciones, 16 Propiedades, ventana ajuste de propiedades de control, 147–150 apertura, 46–47 cambio de nombres de módulos en, 89–90 revisión general, 16–18, 60–61 Propiedades del proyecto, ventana, 48–49 protección de macros, 48–49 Protección, ficha, 48–49 punto y coma, 135–136

R Range, comando, 20–21, 109–110 recolección de código construcción de nuevas macros, 35–40 eliminación de macros

Índice

antiguas, 40–41 módulos, 35–36 prueba de macros, 40 revisión general, 34–41 Reemplazar, comando, 67 Referencia del programador de Excel, 24 referencias, 7, 124–125

T tasas de impuesto sobre la renta de corporaciones estadounidenses 2008, 79f, 128–129 teclas de método abreviado, 48–53 Título, argumento, 86–87

referencias absolutas, 7 Referencias relativas, característica, 7, 124–125 Round, comando, 68–69

S selección de fuente, 103, 106 Select, comando, 20–21 Select Case, instrucción, 79–80 Selection, comandos, 110

U Until, comando, 140 UsedRange, método, 142–143 UserForms adición a cuadro de lista, 95–97 ofrecimiento de listas de opciones, 93–95 respuesta a la selección de usuario, 96–97

Sheets, colección, 142–143 sistema de ayuda, 22–26, 61–62, 65–66 Static, comando, 151–153 Step, comando, 135–136, 137–138 Sub, líneas, 18–19, 34–36 subrutinas asignación de nombres a, 74–75 ejecución de macros como, 76–79 escritura, 75–77 llamado a, 75–76

creación, 113–115 de conteo en bucles, 134 matrices comparación entre números y nombres, 120–122 reglas, 121–122 revisión general, 119–120 operadores aritméticos, 116–117 de cadena, 117–118 de comparación, 116–117 lógicos, 117–118 precedencia de, 118–119 revisión general, 88–89, 112–115 variantes, 113–114 VBA, comandos, 109–110 Ver, menú, 71 Ver código, botón, 16–17, 96

sensibilidad a mayúsculas y minúsculas, 112–113

161

V valor de variable, 112–113 valores, despliegue de fórmulas como prueba de macros, 29–30 revisión general, 28–31 visualización del código de la macro, 29–31 valores devueltos, 91f value, propiedad, 152–153 variables asignación de nombres, 112–113 combinación en expresiones, 114–116

Ver macros, comando, 55 Ver objeto, botón, 16–17, 96 Vista, barra de herramientas, 2 Visual Basic comandos de macros, 64–65 despliegue de fórmulas como valores prueba de macros, 29–30 revisión general, 28–30 visualización del código de la macro, 29–31 funciones personalizadas de ocultamiento de datos conﬁdenciales, 82–83

162

Macros con Excel 2007

simpliﬁcación de cálculos complicados, 78–82 grabación de macros, 31–34 recolección del código de la macro apertura de nuevos módulos, 35–36 construcción de macros, 35–40 eliminación de macros antiguas, 40–41 organización de ventanas del módulo, 35–36 revisión general, 34–41 prueba de macros, 40 subrutinas

asignación de nombres, 74–75

visualización del código de la macro, 29–31

ejecución de macros

como, 76–79 escritura, 75–77 llamado a, 75–76 Visual Basic (VB), editor Cuadro de herramientas, 94 Explorador de proyectos, 16–17 módulos, 16–17, 46–47 revisión general, 71–72 ventana de código, 17–18 ventana Propiedades, 16–18, 147–150, 153–154 ventanas en, 60–61

While, comando, 140 While...Wend, estructura, 141 With Selection, comando, 62–63, 102–104 With, estructuras, 62–63 With...End With, comando, 37–39

X XLSM, extensiones de archivo, 45–46