Subsiguientemente, estos son los macros de Visual Basic para Excel desglosados en diferentes aspectos.
Creación de un botón en Excel con macros
Vamos a crear un botón, que al hacer clic sobre él, muestre en la celda A1 la expresión “HOLA”.
Para ello, en primer lugar, se instalará en el documento de Microsoft Excel, el menú Programador (Menú Archivo -> Opciones -> Personalizar cinta de opciones y se selecciona la casilla Programador).
Visual Basic
Una vez hecho esto, aparecerá la pestaña Desarrollador desde la que se pueden añadir los botones dentro de la pestaña.
En él se tomará el icono que representa a un botón, desplegándose en la Hoja1, por ejemplo, del documento Excel. De los dos botones que hay (tanto en formularios como en ActiveX), se seleccionará el de Controles de ActiveX. Esto ya que, de este modo, se podrá cambiar el color y otras opciones del propio botón.
Una vez hecho esto, se pulsará dos veces sobre dicho botón para acceder así al Editor de Visual Basic, con el que se realizará el pequeño programa requerido, tal y como sigue:
Acumulación de “HOLA”’ en la misma celda
Ahora vamos a cambiar el programa anterior, cambiando una de las líneas de programa, para hacer que cada vez que se haga un clic en el botón, se acumule un nuevo “HOLA” (igual que podría ser cualquier otro valor numérico o cadena de caracteres) al anterior. De esta forma, se identificará el contenido de la primera celda como un contador, acumulándose, en cada clic sobre el botón, una nueva cadena de texto en dicha celda contador.
Visual Basic
Acumulación de texto en varias diagonales sucesivas
Continuando el ejemplo anterior, vamos a definir una lista en varias diagonales, en las que se mostrará el texto previamente definido (“BIENVENIDO”). En la nueva versión del programa anterior, se podrá observar cómo utilizar la función “condición” (representada por la función if) y el bucle (mediante la aplicación de la función for, entre otras opciones).
Así, para hacer que la palabra “BIENVENIDO” aparezca colocada siguiendo varias diagonales un número determinado de veces. Se definen, inicialmente, dos variables contador como enteros (función Dim… As Integer), y que representan además los índices de las celdas de la Hoja de Cálculo (filas y columnas).
Se define el texto en la primera celda. Seguidamente, se define la condición de que la suma de los índices de celda (variables contadores) sean números pares, con la utilización de la función mod (función resto, dividiendo el número requerido por dos, si el resto es 0, el número es par), así se tendrían definidas las diferentes diagonales. Esta “condición” estaría colocada dentro de un doble bucle for (bucle anidado), en el que el valor de cada nueva celda de la diagonal, tendrá el mismo valor que la anterior.
Macros de Visual Basic en Excel:
Jugando con las Series de Fibonacci
En este caso, vamos a desarrollar código que cumplirá las siguientes características:
- Se tomarán exclusivamente la cifra de unidades de los números de la serie anterior
- Se ordenarán estos valores de mayor a menor (para poder trabajar con ellos)
- Utilización de una serie de Fibonacci de números aleatorios
- Se mostrará cómo realizar el diagrama de barras correspondiente a la serie anterior (cada barra con el tamaño y el color correspondiente al número de la serie).
Y en él, se utilizarán además las funciones y opciones del Editor de Visual Basic / Microsoft Office siguientes:
- Cambio de nombre de un botón
- Utilización y grabación de macros
- Utilización de la función Call para llamar a una función definida en otro lugar
- Cambio de color.
La serie de Fibonacci cumple que cada elemento de la serie es el resultado de la suma de los dos precedentes, es decir:
an+2 = an+1 + an
Así, se introducirá la fórmula anterior mediante la utilización de un bucle Do While…Loop (una de las opciones posibles), previa definición de los dos valores iniciales. De esta forma, se van a definir estos valores iniciales como aleatorios; para ello, se va a utilizar la función de generación de números aleatorios RND (tal y como se ve en el programa). Se evitan números excesivamente grandes o en coma flotante, tomando la variable como INT para evitar la aparición de decimales.
Además, se ve cómo se utiliza la función With, para definir la selección de color. Este código se ha tomado del de la macro grabada a partir del cambio de color de una celda cualquiera (mediante la utilización de la opción del menú Cambio de color).
Macros en Excel
Pero ¿qué es una macro? Y ¿cómo se graba una macro?
En primer lugar, se debería considerar que una macro es un pequeño programa ejecutable desde la Hoja de Cálculo, y que realiza funciones repetitivas o comunes en la normal ejecución de la actividad con la herramienta de cálculo. Así, y en el caso particular de grabar una macro para poder cambiar de color una serie de celdas de la Hoja de Cálculo, se procede de la siguiente forma.
En el menú se toma la opción Desarrollador y, en esta, Grabar macro. Acto seguido, se realiza la acción a grabar en la macro. En este caso, cambiar de color el color de una columna de la hoja de cálculo.
Abriendo la opción de Visual Basic, la macro grabada quedaría reflejada de la siguiente manera:
En el paso anterior se ve, en el código definido por la macro, la opción Range. Esto define el rango de aplicabilidad de la opción escogida con el código, en ese caso el cambio de color de las celdas A1 hasta la A10.
Además, se le puede cambiar el nombre al botón para que deje de “llamarse” CommandButton1 y así poder ponerle el nombre deseado y cambiar otras propiedades como el color del botón.
Pero ¿cómo se consigue cambiar el nombre al botón?
Para ello, se selecciona el Modo Diseño del cuadro de controles de la pestaña Desarrollador, una vez ahí, se haría clic con el botón derecho del ratón, sobre el botón al que se le quiere cambiar el nombre. Acto seguido, se selecciona la opción Propiedades y dentro de estas se cambia la opción Caption.
Una vez mostradas las acciones anteriores, se va a pasar a definir el ejemplo concreto. Así, y como ya habíamos dicho, vamos a definir el código de programa necesario para por un lado generar la serie de Fibonacci de términos aleatorios. (Y, por el otro, tomar de los valores de la serie anterior exclusivamente las cifras correspondientes a las unidades).
Aquí pueden observarse dos bloques diferenciados de programa, cada uno para un botón diferente (que se pueden ver en la transparencia siguiente). En el primero se crea una serie de Fibonacci, tal y como ya se ha explicado, y acto seguido, se reduce cada uno de los números de dicha serie a su cifra de unidades. Esta sería el resto obtenido de dividir dicho número de la serie original, por 10.
Esto se consigue con la utilización de la función mod. Todo ello dentro de su correspondiente bucle para ir tomando todos los valores de la serie.
Se ve el resultado obtenido
Primero, haciendo clic en el primer botón, se obtendría la serie, y seguidamente, haciendo clic sobre el segundo botón, se obtiene la cifra correspondiente a la cifra de unidades de la serie de Fibonacci anterior.
Ahora, se deberá definir una función que tome una serie de números y los ordene de mayor a menor. Esto se haría mediante la grabación de una macro llamada ordenar, en la que se graba la acción de Ordenar (función perteneciente al menú datos de la barra de menú) de mayor a menor los valores de la primera columna, se obtiene el código de programa necesario para implementar un tercer botón, por ejemplo (código que se ve abajo).
Clicando en el tercer botón se obtiene la serie numérica resultante de ordenar la serie de cifras unidad de la serie de Fibonacci (de la transparencia anterior). Si la macro se hubiera grabado en sentido descendente (del número 9 al 1), solo habría que grabar la macro cambiando el orden por descendente, o bien modificar el código de manera que apareciera la palabra Descending en lugar de Ascending.
Este sería el último del conjunto de programas individuales (definidos mediante botones), con el se conseguiría el objetivo buscado. En la página siguiente se muestra el código del diagrama de barras correspondiente a los valores de la serie anterior.
Este código muestra, después de un corto programa para borrar el diagrama que pueda existir con anterioridad (obtenido a partir del código de la macro grabada durante el borrado de un diagrama con las dimensiones requeridas, dándole al color el valor “sin relleno”), cómo hacer el diagrama de barras.
Definición del uso de las celdas
Primeramente, se define que el número de celdas a colorear (barras del diagrama), sea igual al número de la serie en cada fila. Después, se define una condición para evitar colorear una celda de la primera columna, cuando se tuviera un cero. Además, se define que el color corresponda al número presente en cada celda, pero evitando el negro (correspondiente al 0), y el blanco (correspondiente al 1).
Tras lo definido anteriormente, y haciendo clic sobre el cuarto botón, se obtendría el diagrama de Gantt correspondiente a la serie previamente calculada, cambiando cada vez que se ejecutara todo el proceso completo:
Botón 1 -> Botón 2 -> Botón 3 -> Botón 4
Una vez realizado lo anterior, vamos a mostrar como emplear la función de Visual Basic, Call. Con esta función lo que pretendemos, es poder hacer llamadas desde dentro de un programa a otro que puede ser utilizado varias veces, y de esta forma, evitaríamos tener que definir el programa correspondiente cada vez.
En este caso, vemos cómo, una vez definida la serie de Fibonacci (de la misma forma que ya se ha visto previamente en varias ocasiones, siguiendo el mismo ejemplo), se introducen tres llamadas a otras tantas funciones independientes previamente definidas (como se ha visto en las transparencias precedentes) mediante la función call.
Cálculo final
Así, una vez calculada mediante el bucle Do While la serie de Fibonacci, se llamaría inicialmente a la función Reducir. Esta, como ya se ha visto, tomaría el resultado anterior, “reduciéndolo” a la cifra de unidades correspondiente a cada uno de los elementos de la serie anterior.
Se vería de la misma forma que se veía en un punto anterior, como con la utilización de la función resto mod, entre 10, conseguimos tomar o “reducir” la cifra correspondiente a las unidades de los elementos de la serie de Fibonacci previamente calculada.
A continuación, se llama a la función Ordenar, que realizará la ordenación de los elementos de la serie numérica previamente calculada, de mayor a menor (siendo este código obtenido, como ya se había explicado, a partir de la grabación de una macro utilizando la función ordenar del menú). Tomando como rango de elementos a ordenar la primera columna (A) desde la celda 1 a la 15, en este caso.
La última llamada realizada desde la función principal, sería la realizada a la función encargada de definir el diagrama de barras, en tamaño y en color, además de definir otra subfunción que se encargaría de borrar el diagrama anterior cada vez que se hiciera clic en el botón para obtener una nueva serie y un nuevo diagrama de barras.
Conclusión
La última llamada realizada desde la función principal, sería la realizada a la función encargada de definir el diagrama de barras, en tamaño y en color, además de definir otra subfunción que se encargaría de borrar el diagrama anterior, cada vez que se hiciera clic en el botón para obtener una nueva serie y un nuevo diagrama de barras.
Como ya se ha explicado antes, se definiría una función encargada de tomar el valor de cada uno de los elementos de la serie en la columna A, luego, y mientras ésta fuera diferente de 0, se entraría en el bucle, en el se definiría el tamaño y el color de la barra en función del número de la serie en cada posición.