MUNDO DE EXCEL: enero 2011

Introducción a las macros personalizadas en Excel

Corresponde a: Microsoft Office Excel 2003

Corresponde a
Microsoft Office Excel 2003
Microsoft Excel 2002

Este artículo lo ha escrito Curtis Frye, autor de Microsoft Office Excel 2003 Programming Inside Out.

Muchas de las tareas que realiza en Excel, como introducir los datos de ventas de un día determinado o agregar una fórmula a una hoja de cálculo (hoja de cálculo: documento principal que se utiliza en Excel para almacenar y trabajar con datos. Consta de celdas que se organizan en filas y columnas. Una hoja de cálculo se almacena siempre en un libro.), sólo se realizan una vez. Otras tareas, como cambiar el formato de un rango de celdas, se repiten a menudo pero se pueden realizar de manera rápida gracias a las herramientas de Excel. Sin embargo, siempre hay una o dos tareas que se realizan con frecuencia y que son de lenta ejecución.

Por ejemplo, puede que desee resaltar las celdas más importantes de una hoja de cálculo cada vez que presente un informe a sus compañeros. En vez de tener que ejecutar una larga serie de pasos manualmente, puede crear una macro, o serie de acciones grabadas, que realice estos pasos automáticamente. O, si conoce el lenguaje de programación Microsoft Visual Basic® para Aplicaciones (VBA), puede escribir una macro personalizada nueva. En cualquier caso, una vez creada la macro podrá ejecutarla, editarla o eliminarla según le convenga.

Este artículo es una introducción a la programación orientada a objetos y al Editor de Visual Basic, entorno en el que puede escribir sus propias macros de Excel.
Uso del Editor de Visual Basic

El Editor de Visual Basic es una útil herramienta que le permite ampliar la eficacia y versatilidad de las macros más allá de lo posible mediante grabación. Para abrirlo, seleccione Macro en el menú Herramientas y haga clic en Editor de Visual Basic (o presione ALT+F11).

Para abrir una macro específica, seleccione Macro en el menú Herramientas y haga clic en Macros (o presione ALT+F8). En el cuadro de diálogo Macro, seleccione la macro que desea editar o ver y haga clic en Editar para abrir el Editor de Visual Basic.
Programación orientada a objetos en el Editor de Visual Basic

En el Editor de Visual Basic, puede utilizar la programación orientada a objetos para crear sus propios programas. Un programa es, en principio, un mero conjunto de instrucciones que un equipo ejecuta en un orden específico.

En los lenguajes de programación orientada a objetos, todos los aspectos del código informático se basan en los elementos del entorno. Esos "elementos" se representan como objetos, y todas las acciones y todos los datos están encapsulados en dichos objetos. En Excel, los objetos pueden ser libros, hojas de cálculo, rangos de celdas o archivos externos.

En general, hay cuatro aspectos de los objetos que se utilizan para desarrollar un programa: propiedades, métodos, eventos y colecciones.
Propiedades

Resumiendo, las propiedades son variables que describen algunos aspectos del objeto en el que están incluidas. Una propiedad de objeto común de Excel es Nombre, que encierra el valor identificativo que el usuario o Excel asigna a un libro, a una hoja de cálculo, a un rango de celdas o a otro objeto. Por ejemplo, si modifica el nombre de una hoja de cálculo (utilizando un código VBA o haciendo clic con el botón secundario en la ficha de la hoja de cálculo), se modifica el valor guardado en la propiedad Nombre.

Puede definir nuevos valores para algunas propiedades de hoja de cálculo (por ejemplo, el nombre) directamente en VBA. Para cambiar otras propiedades de hoja de cálculo, es posible que necesite grabar una acción en Excel (por ejemplo, los pasos requeridos para proteger un rango de celdas) o utilizar un método (tal y como se describe en la siguiente sección).

En VBA, se hace referencia a las propiedades en los programas utilizando la notación con puntos, donde se escribe primero el nombre del objeto y después el nombre de la propiedad, separando estos dos elementos mediante un punto. Por ejemplo, para modificar el nombre de una hoja de cálculo, se utiliza la propiedad hojadecálculo.nombre.
Métodos

Un método es una acción que un objeto "sabe" como ejecutar. Por ejemplo, probablemente sabe que la hoja de cálculo que se muestra en la ventana de Excel se conoce como hoja de cálculo activa. En lenguaje VBA de Excel, puede dirigirse a la hoja de cálculo que desea modificar llamando primero a su método Activar. Una vez ejecutado el método Activar, la hoja de cálculo a la que está asociado se coloca al frente del resto de las hojas de cálculo en la ventana de Excel, quedando disponible para su edición.

Al igual que ocurre con las propiedades, los métodos se llaman utilizando notación con puntos. Para volver a calcular todas las fórmulas de una hoja de cálculo, por ejemplo, hay que llamar al método hojadecálculo.calcular.
Eventos

Igual que una propiedad es un atributo cuantificable de un objeto y que un método es una acción que el objeto sabe ejecutar, un evento es una acción que el objeto "reconoce" que ha ocurrido. Por ejemplo, Excel 2003 reconoce los siguientes eventos (entre otros muchos):

* Se ha abierto o cerrado un libro.
* Se ha activado o desactivado una hoja de cálculo.
* Se ha guardado un libro.
* Se ha hecho clic en un gráfico.
* Se ha presionado una tecla (o una combinación de teclas).
* Se han escrito datos en una celda.
* Se han calculado de nuevo las fórmulas de una hoja de cálculo.
* Se ha seguido un hipervínculo.

Excel incluye varios controladores de eventos, o rutinas de código, que controlan acciones determinadas. Cuando ocurre una de dichas acciones, y ha comunicado a Excel qué desea que haga cuando ocurra el evento, Excel ejecuta el código del controlador de eventos. Por ejemplo, si después de crear un nuevo libro desea que Excel muestre todos los libros abiertos como un conjunto de ventanas en cascada, puede crear el siguiente controlador de eventos:

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
Application.Windows.Arrange xlArrangeStyleCascade
End Sub

No se preocupe si no está seguro de qué hace cada uno de los elementos de la rutina del controlador de eventos. Por ahora, concéntrese en la línea del medio del código, que comunica a Excel que organice las ventanas utilizando el estilo de cascada. Puede obtener el mismo resultado utilizando los comandos de menú de Excel (menú Ventana, comando Organizar, subcomando Cascada). Sin embargo, si se trata de una acción que desea que se ejecute cada vez que ocurra un evento determinado, puede utilizar el lenguaje VBA para que se ejecute automáticamente y así ahorrarse el trabajo.
Colecciones

El elemento final de la programación orientada a objetos con el que debe familiarizarse es la colección. Tal y como indica su nombre, una colección es un grupo de objetos del mismo tipo contenidos en otro objeto. Por ejemplo, un libro contiene una colección de una o varias hojas de cálculo. Si desea realizar un mismo cambio en todas las hojas de cálculo de un libro, puede programar el cambio automático en todas las hojas de cálculo de la colección.

Si ha creado programas antes, seguramente conozca el bucle For...Next, que permite repetir un grupo de instrucciones varias veces utilizando algo como la siguiente secuencia. (En este ejemplo se agrega la ruta de directorio del libro activo a la sección derecha del pie de página en las primeras tres hojas de cálculo.)

For i = 1 to 3
Worksheets(i).PageSetup.RightFooter = Path
Next i

El inconveniente de codificar (es decir, asignar un valor determinado a) el límite superior de este bucle For…Next es que será necesario cambiar el código cada vez que se agregue o elimine una hoja de cálculo. Esto no es grave si se tiene que hacer una o dos veces, pero si está administrando muchos códigos, probablemente olvide cambiarlo en algunos sitios. Esto generará errores que tendrá que solucionar. Lo que es peor, es posible que los errores pasen inadvertidos hasta que el responsable de calidad descubra que las 500 copias impresas del informe anual no tienen el formato correcto (y que cuando esto ocurra tenga apagado el móvil porque esté dando un paseo por la playa).

Puede utilizar un código para que le devuelva el número de hojas de cálculo que se incluyen en el libro, pero hay un modo más sencillo de hacer esto: utilizar un bucle For Each…Next. Los bucles For Each…Next buscan el número de objetos que hay en una colección, por ejemplo las hojas de cálculo de un libro, y pasan por todos ellos. Utilizando esta técnica, el código anterior se puede escribir de este modo:

For Each Wksht in Worksheets
Wksht.PageSetup.RightFooter = Path
Next Wksht

En vez de incrementar el valor en un bucle For…Next estándar, el bucle For Each…Next busca simplemente el siguiente elemento de la colección Worksheets (hojas de cálculo) y se detiene cuando no encuentra uno.
Editor de Visual Basic y sus ventanas

El Editor de Visual Basic muestra distinta información en distintas ventanas. Es importante conocer las siguientes ventanas cuando se está aprendiendo a escribir macros: la ventana Explorador del proyecto, la ventana Propiedades y la ventana Código.
Ventana Explorador del proyecto

Cuando se abre el Editor de Visual Basic directamente, se puede utilizar la ventana Explorador del proyecto para seleccionar la macro en la que se desea trabajar. El Explorador del proyecto muestra todos los proyectos (grupos de códigos) y las macros que contienen en vista de árbol, como la vista de árbol del Explorador de Windows®. La raíz u objetos de base de la estructura de árbol son los libros actuales en los que se está trabajando, y los demás libros y complementos que estén abiertos.

Cada uno de los elementos principales que se utilizan en los proyectos VBA se guarda en una carpeta independiente en cada proyecto. Estos elementos son:

* Objetos (descritos anteriormente en este artículo)
* Módulos, que contienen el código de macro asociado a la hoja de cálculo
* Módulos de clase, que son definiciones de los objetos definidos por usuario creados para el libro
* Formularios de usuario, que ofrecen una superficie visible en la que se pueden colocar controles gráficos, como botones, imágenes y áreas de texto

En el siguiente ejemplo, el libro actual está identificado como VBAProject. Las tres hojas de cálculo incluidas en el libro (Hoja1, Hoja2 y Hoja3), así como el libro completo (EsteLibro), aparecen en la carpeta Objetos de Microsoft Office Excel. Las macros programadas en VBA o grabadas aparecen en la carpeta Módulos. Todos los módulos de clase o formularios de usuario aparecen en la carpeta Módulos de clase o Formularios, respectivamente.

Ventana Explorador del proyecto, ventana Propiedades y ventana Código
Ventana Propiedades

Justo debajo de la ventana Explorador del proyecto, se encuentra la ventana Propiedades, que se utiliza para examinar y modificar las distintas propiedades asociadas al objeto seleccionado. La única propiedad que suele estar disponible para los módulos es el nombre. Las hojas de cálculo tienen propiedades adicionales que se pueden modificar, como StandardWidth y DisplayPageBreaks. La modificación de propiedades en la ventana Propiedades se suele realizar cuando se trabaja con formularios de usuario.

Nota El nombre que se muestra en el cuadro Nombre de la ventana Propiedades correspondiente a un objeto no tiene que coincidir necesariamente con el nombre del objeto en Excel. Puede que sean similares, pero probablemente sean distintos debido a las reglas de nombre predeterminadas que utiliza el Editor de Visual Basic para los objetos. (Por ejemplo, los nombres no pueden incluir espacios en blanco, deben comenzar con una letra, no pueden ser palabras reservadas y no pueden tener una longitud superior a 31 caracteres.) Incluso cuando los nombres son distintos, deben estar vinculados internamente para garantizar que se hace referencia al objeto adecuado.
Ventana Código

La ventana Código es la ventana de mayor tamaño del Editor de Visual Basic e incluye en su lado superior dos cuadros desplegables. El cuadro desplegable de la izquierda (cuadro Objeto) se utiliza para seleccionar el objeto en el que se quiere trabajar. Cuando se trabaja sólo con código, el cuadro muestra el objeto General predeterminado. El segundo cuadro desplegable (cuadro Procedimiento) se utiliza para seleccionar macros individuales del módulo actual. Según se agreguen o eliminen macros en el módulo, se agregarán y eliminarán en el cuadro Procedimiento.

Ventana Código

También puede utilizar las teclas de FLECHA ARRIBA o FLECHA ABAJO para desplazarse por las listas del código y buscar la macro que desea. Conforme se vaya desplazando por el código, el cuadro Procedimiento se irá actualizando para reflejar la macro actual.

La función =SI( )

La función =SI( ) es una de las más potentes que tiene Excel. Esta función comprueba si se cumple una condición. Si ésta se cumple, da como resultado VERDADERO. Si la condición no se cumple, da como resultado FALSO. Obseva la sintaxis:

=SI(Condición;Verdadero;Falso)

Esta es la forma más simple de representar esta función. Vamos a proponer un ejemplo:

Observa el gráfico.Vamos a colocar una función =SI( ) en la celda B4 (celda rosa del descuento).

Vamos a hacer que la factura del ejemplo anterior nos haga un descuento del 10% sólo en el caso de cobrar al contado. La fórmula se colocará en la celda B4 y será la siguiente:

=SI(C1="Contado";B3*10%;0;)

Esta fórmula mirará si en la casilla C1 (celda amarilla) existe la palabra Contado. En tal caso, ejecutará una fórmula (10% de descuento), en caso contrario, colocará simplemente un cero en la celda B4, es decir, no realizará ningún cálculo. Observa el resultado:

Vamos ha hacer otro ejemplo. Supongamos que tenemos las notas de un examen y tenemos que calificar con "Apto" o "No Apto". "Apto" si la nota es mayor a 5 y "No Apto" si es menor a 5.

Copia el cuadro e intenta solucionarlo tu mismo.