1. El documento está inicialmente dirigido a alumnos de ingeniería que con mínimos conocimientos de programación pueden entender cómo funciona el VBA de Excel. No se pretende enseñar a programar, sólo a utilizar el entorno y a sacar partido al mínimo conocimiento en programación que tienen mis alumnos de ingeniería. Lo que se pretende con este manual es presentar de una manera práctica, diferentes utilidades, funciones, sentencias…, en el Editor de Visual Basic, y que con posterioridad serán útiles para el desarrollo del ejercicio concreto de que consta la práctica.
2. en el ejemplo siguiente se observa, como se deberían de utilizar los anidamientos consecutivos de If… Then, ElseIf… Then y Else. Se observa aquí, cómo se utilizaría el End If, siempre en el caso del anidamiento de condiciones, y no en el caso de escribir la condición If (general) en una sola línea. If b > 0 And c > 0 Then Hoja1.Cells(i, 8) = a Else If b > 0 And c < 0 Then Hoja1.Cells(i, 8) = a + 360 Else Hoja1.Cells(i, 8) = a + 180 End If End If
3. Condición If…, etc. (diversas posibilidades): If … Then … / If … Then … Else … / If … Then … ElseIf … Then … ¿Cuándo poner el EndIf?, ¿cuándo no?, ¿cuándo se deberían usar los “:” (dos puntos)? Las instrucciones If...Then...Else se pueden presentar en varios formatos, con unas características determinadas. Normalmente, se presentan anidadas en tantos niveles como sea necesario. Esto, sin embargo, puede hacer menos legible el código, por lo que es aconsejable utilizar una instrucción Select Case en vez de recurrir a múltiples niveles de instrucciones If...Then...Else anidadas (únicamente en caso de que el excesivo número de anidamientos pudiera dar problemas en la legibilidad del programa, o errores en la depuración de éste).
4. Una vez presentados y explicados los ejemplos anteriores, esperamos que sirvan de ayuda real a la realización de los problemas concretos. También, y porque no, esperamos que este pequeño manual pueda llegar a servir como herramienta de inicio de otros posibles futuros trabajos encaminados en esta materia.
5. OBTENCIÓN DE NÚMEROS PRIMOS Una vez visto todo lo realizado previamente, se va a pasar a describir estos nuevos ejemplos. En ellos, vamos a mostrar cómo hacer dos programas, el primero para saber si un número es primo, y el segundo, para obtener listas de números primos. Para esto, en el primer programa, mostraremos qué funciones se deben utilizar para declarar menús de trabajo, y cómo trabajar con ellas, además de cómo llamar a otras funciones sin utilizar la función que se había visto previamente para este propósito (función call). En el segundo programa, veremos de qué forma se podrán declarar listas de números primos, en un número indicado previamente por nosotros mismos.
6. 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
7. 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), sólo 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.
8. 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 20 de febrero de 2014 .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.
9. 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?
10. Vamos a crear un botón, que al hacer clic sobre él, muestre en la celda A1 la expresión “HOLA”
11. 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).
12. 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.
13. With Estructura de control, que permite ejecutar una serie de instrucciones sin necesidad de recalificar un objeto, es decir, sobre el mismo objeto cada vez; entendiendo por objeto toda combinación de código y datos que se pueden tratar como una unidad, por ejemplo, un control, un formulario o un componente de una aplicación. Cada objeto se define por una clase.
14. For... To... Next Mediante la palabra clave Step, se puede aumentar o disminuir la variable contador en el valor que se desee (For j = 2 To 10 Step 2). Se pueden anidar bucles For...Next, colocando un bucle For...Next dentro de otro. Para ello, hay que proporcionar a cada bucle un nombre de variable único como su contador. La siguiente construcción es correcta: For i = 1 To 10 For j = 1 To 10 20 de febrero de 2014 Next j Next i
15. Jugando con las series de Fibonacci. En este caso, vamos a desarrollar código que cumplirá las siguientes características: Utilización de una serie de Fibonacci de números aleatorios. 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). 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).
16. 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 ésta, 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
17. 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).
18. 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).
19. 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.
20. 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.
21. Como se puede comprobar, al trabajar con variables Booleanas, se devuelve o recibe un True o un False, que en función de la definición de la condición if del siguiente programa (el definido por el botón), se dará como resultado lo correspondiente al “si” (if) o al “sino” (else). Ahora se ve cómo quedaría en la pantalla de la hoja de Excel lo expuesto previamente. Se ve, en la página siguiente, como al hacer clic sobre el botón, aparecería el menú pidiendo un número, y acto seguido se diría si éste es primo o no.
22. Sentencias y funciones habituales Antes de empezar con el anexo queremos incorporar una nota. Por un motivo que desconocemos Excel ha empeorado su comportamiento desde algunas versiones hacia aquí. Macros que funcionaban estupendamente se han convertido en muy lentas. Tras una indagación en la web parece que si se pega esto al principio de las aplicaciones mejora el funcionamiento.
23. 1. Select Case En este caso, esta instrucción será más útil que la Condición If..., cuando se ejecute uno de varios grupos de instrucciones, dependiendo del valor de una expresión condición a cumplir
24. Do While... Loop Siguiendo lo explicado en el punto inicial, otro error común sería el no introducir la línea de acumulación del contador (por ejemplo: i = i + 1), con lo que el bucle entraría cada vez en el cálculo, quedándose colgado en este punto.
25. Ejemplo de utilización de esta función (hay que fijarse en la inicialización previa de la variable contador i): i = 5 Do While Hoja1.Cells(i, 2) <> "" b = Hoja1.Cells(i, 5) c = Hoja1.Cells(i, 6) d = (b ^ 2) + (c ^ 2) a = Sqr(d) Hoja1.Cells(i, 7) = a i = i + 1 Loop
26. Un ejemplo concreto podría ser el siguiente: For i = 1 To 15 x = Hoja1.Cells(i, 1) If x <> 0 Then For j = 2 To x + 1 Hoja1.Cells(i, j).Select With Selection.Interior .ColorIndex = x + 2 .Pattern = xlSolid End With Next j End If Next i
27. Posteriormente, se le daría dicho valor a otra variable, que estaría encargada de ir dándole dichos valores a las celdas correspondientes de la Hoja de cálculo anterior. i = 5 Do While Hoja1.Cells(i, 2) <> "" b = Hoja1.Cells(i, 5) c = Hoja1.Cells(i, 6) d = (b ^ 2) + (c ^ 2) a = Sqr(d) Hoja1.Cells(i, 7) = a i = i + 1 Loop
28. Cambiar criterios de ordenación: Aquí se puede ver cómo se podrían definir criterios de ordenación (ascendente o descendente), en función de la necesidad del programador, y respecto a una columna o rango predefinida. Application.AddCustomList ListArray:=Range("J2:J21") numlista = Application.CustomListCount Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=numlista + 1, MatchCase:=False, Orientation:=xlTopToBottom,_ DataOption1:=xlSortNormal Application.DeleteCustomList ListNum:=numlista
29. Para Ordenar Básicamente, se busca lo mismo que cuando se hablaba del cambio de criterios de ordenación. Range ("D18:F23").Select Selection.Sort Key1:=Range("D19"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal
30. Quitar el signo de los números convertidos en string: Estas líneas de código sirven para poder tomar números positivos siempre, aunque se introdujeran negativos (por error o cálculo), a través de un menú, cálculo,… El resultado sería semejante a la utilización de la función del Editor de Visual Basic, Abs (numero) (que devuelve el valor absoluto de todo número introducido entre paréntesis. nombre = Str(i) nombre = Right(nombre, Len(nombre) - 1)
31. Referencias que corresponderían con los valores de la celda correspondiente de la Hoja de Cálculo con la que se está trabajando. DESREF (C11; 0; SI (C6>$C$3;-$C$3;-C6); 1; 1)
32. Zoom de la ventana: Zoom, agrandará o empequeñecerá la presentación del formulario preseleccionado, en la ventana activa de trabajo. ActiveWindow.Zoom = 25
33. Para cancelar el botón: Bastaría con introducir la orden siguiente: End
34. Crear Rango: Aquí se crearía un rango, sin tener que seleccionarlo previamente en la página de trabajo de la Hoja de Cálculo, desde la celda B5. rango = Str(nuevoprod - 1) rango = "B5:D" + Right(rango, Len(rango) - 1)
35. Entero y Logaritmo: Con la sintaxis siguiente, se transforman números reales logarítmicos (obtenidos mediante la función logaritmo Log), en un número entero; para utilizarlo, por ejemplo, en el caso de disponer de poca memoria para una variable, o por necesidad de trabajar con números pequeños. aux = Int(Log(x) / Log(2))
36. Temporizador: Función encargada de dar un intervalo de tiempo, previamente a la obtención de un resultado, o por otra razón necesitada por el programador. Dim ppio As Single ppio = Timer Do While ppio + 10 > Timer Loop 6.11. Funciones: Definición de funciones (con la forma que se requiera, sea Integer para entero, o de cualquier otro tipo), dentro del programa, en el Editor de Visual Basic, con la intención de tenerlas
37. Ángulo (calculado a partir de las coordenadas x e y de los puntos en cuestión) α=Arctan (x/y): Se considera el mismo proceso anterior, pero en este caso, y para poder presentar los valores del ángulo correspondiente en grados entre 0 o y 360o , puesto que Excel sólo los presenta en valores entre 90o y -90o , se utiliza la estructura condicional que se puede observar en el programa. i = 5 Do While Hoja1.Cells(i, 2) <> "" b = Hoja1.Cells(i, 5) c = Hoja1.Cells(i, 6) d = c/b a = (180 / PI) * Atn(d) If b > 0 And c > 0 Then Hoja1.Cells(i, 8) = a Else
38. Do... Loop Until Esta estructura de control se puede usar para ejecutar un bloque de instrucciones un número indefinido de veces. Las instrucciones se repiten hasta que una condición llegue a ser True. 20 de febrero de 2014 26 Un ejemplo podría ser el siguiente: Sub ComPrimeroUntil () contador = 0 miNum = 20 Do Until miNum = 10 miNum = miNum – 1 contador = contador + 1 Loop MsgBox "El bucle se ha repetido " &contador& " veces." End Sub
39. Bucles: For... To ... Next/Do While... Loop/Do Loop… Until (Utilización y posibles problemas): Las estructuras de bucle también son conocidas por el nombre de estructuras de control. Permitiendo la repetición de determinadas acciones. Uno de los errores más comunes que se producen en la utilización de bucles de este tipo, es la no inicialización de las variables utilizadas como contadores de iteraciones. Así que habrá que prestar una atención especial en este punto. Una opción para evitar este posible error, sería la definición al principio del programa, como primera línea de código de éste, el ya comentado Option Explicit.
40. Borrar el carácter de la derecha: ¿Cómo borrar el carácter situado más a la derecha en una celda? Por ejemplo, si el texto se encuentra en B10 =IZQUIERDA(B10;LARGO(B10)-1) 6.46. Comprobar la presencia de una cadena de caracteres: ¿Cómo verificar que una cadena de caracteres se encuentre en una celda o en una variable? =CONTAR.SI(A1;"*texto*")=1 Reenvía TRUE si la cadena de caracteres (texto) se encuentra en la celda A1.
41. Acumulación de “HOLA”’s 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.
42. 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).
43. Es una combinación de un entorno de programación integrado denominado Editor de Visual Basic y del lenguaje de programación Visual Basic, permitiendo diseñar y desarrollar con facilidad programas en Visual Basic. El término “para aplicaciones” hace referencia al hecho de que el lenguaje de programación y las herramientas de desarrollo están integrados con las aplicaciones del Microsoft Office (en este caso, el Microsoft Excel), de forma que se puedan desarrollar nuevas funcionalidades y soluciones a medida, con el uso de estas aplicaciones. El Editor de Visual Basic contiene todas las herramientas de programación necesarias para escribir código en Visual Basic y crear soluciones personalizadas
