Programacion de Macros de Excel utilizando VBA, Ejercicios de Programación Funcional

¡Descarga Programacion de Macros de Excel utilizando VBA y más Ejercicios en PDF de Programación Funcional solo en Docsity! TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS TEMA 6 1º Grado en Ingeniería Química | Laura Briones Gil TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 1 Contenido 1. INTRODUCCIÓN A LAS MACROS DE EXCEL .......................................................................... 2 1.1 LA GRABADORA DE MACROS ........................................................................................ 3 1.2 SEGURIDAD DE MACROS ............................................................................................... 7 2. PRINCIPIOS BÁSICOS DE PROGRAMACIÓN EN VBA ............................................................ 8 2.1 PROGRAMACIÓN ORIENTADA A OBJETOS .................................................................... 8 2.2 EL EDITOR DE VISUAL BASIC ........................................................................................ 12 2.3 TIPOS DE ERRORES EN VBA ......................................................................................... 15 3. PROGRAMACIÓN EN VBA ................................................................................................... 17 3.1 VARIABLES ................................................................................................................... 17 3.2 ENTRADA DE DATOS Y EMISIÓN DE RESULTADOS ...................................................... 18 3.3 FUNCIONES .................................................................................................................. 21 3.4 ESTRUCTURAS DE CONTROL DE FLUJO ....................................................................... 25 4. FORMULARIOS Y CONTROLES ............................................................................................ 38 ANEXO: ........................................................................................................................................ 50 TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 4 Ejemplo 1: Grabar una macro sencilla. Una macro puede ser cualquier conjunto de comandos, desde cambiarle el color a una celda, hasta introducir una función y resolverla. En este ejemplo, se ha grabado un ejercicio de tipo Buscar Objetivo igual que uno de los que se hizo en el tema 2. Se ha cambiado el formato de la celda del título para combinar y centrar un conjunto de celdas, cambiar la fuente a negrita en blanco y el fondo a rojo, se ha escrito el título y los rótulos de las casillas, se ha recuadrado el conjunto de casillas y se ha utilizado el comando Buscar objetivo para resolver el problema. La macro se ha guardado con el nombre Y_Si en el libro personal de macros. El resultado al ejecutarla con el comando Macros es el esperado: Para ver el código exacto que contiene esta macro, en lugar de ejecutarla se puede abrir para modificar. Esto abre el Editor de Visual Basic: Podemos ver en detalle cada uno de los pasos que incluye el programa: TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 5 Sub Y_Si() Se le pide crear una macro con el nombre Y_Si ' Y_Si Macro ' Resuelve un problema de tipo Y si Esta es la descripción que se ha incluido al crearla Range("A1:D1").Select Selecciona las celdas A1 a D1 With Selection Con esas celdas .HorizontalAlignment = xlCenter centra el contenido tanto horizontalmente .VerticalAlignment = xlBottom como verticalmente .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With termina con las opciones de alineado Selection.Merge combina las celdas seleccionadas Range("A1:D1").Select selecciona la nueva celda combinada como celda objetivo ActiveCell.FormulaR1C1 = "Hallar el valor de x que hace y = x2+x+2 = 10" y escribe este texto Selection.Font.Bold = True Sobre la celda seleccionada hace que la fuente sea negrita With Selection.Font sobre esa celda .ThemeColor = xlThemeColorDark1 cambia el color .TintAndShade = 0 para que sea blanco End With termina con las opciones de la fuente With Selection.Interior para la celda seleccionada cambia el color del fondo .Pattern = xlSolid para que toda la celda esté rellena .PatternColorIndex = xlAutomatic de uno de los colores automáticos .ThemeColor = xlThemeColorAccent2 en concreto el rojo oscuro .TintAndShade = 0 sin sombras ni bordes de celda .PatternTintAndShade = 0 ni ningún patrón de relleno End With termina con el relleno de la celda Range("A3").Select para la celda A3 ActiveCell.FormulaR1C1 = "Valor objetivo (y)" escribe este texto Range("A5").Select para la celda A5 ActiveCell.FormulaR1C1 = "Variable independiente (x)" escribe este texto Columns("A:A").EntireColumn.AutoFit amplía el ancho de la columna A hasta ajustar el Esto son más opciones de alineado, como añadir una sangría o ajustar el texto a la celda, que por defecto entiende que no hay que hacer TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 6 texto Range("C3").Select a la celda C3 ActiveCell.FormulaR1C1 = "=R[2]C^2+R[2]C+2" le da el valor de la siguiente función Range("A1:D5").Select sobre este rango de celdas Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlMedium End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlMedium End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlMedium End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlMedium End With Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Range("C3").GoalSeek Goal:=10, ChangingCell:=Range("C5") para la celda C3 establece, con el buscador de objetivos, un valor buscado de 10, a base de modificar la celda C5, que es donde se ha referido la x al introducir la función End Sub fin de la macro crea un recuadro negro grueso alrededor a base de crear las líneas correspondientes, verticales y horizontales, arriba, abajo, a la derecha y a la izquierda de la selección TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 9 Pero estos no son los únicos objetos. Por ejemplo, cada Workbook contiene o puede contener, además, Charts (gráficos), Windows (ventanas), CommandBars (barras de herramientas), VBProject (proyectos de Visual Basic), etc. Cada Range, contiene Font, Interior, Border, Column, Row, Text, Cell, Formula, Value, etc. TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 10 TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 11 Tanto Workbook como Worksheet representan un libro o una hoja de cálculo en concreto, pudiendo existir muchos de estos elementos. Para diferenciarlos entre ellos hay que darles un nombre, del tipo Workbook(“Libro1.xlsm”) o Worksheet(“Hoja1”). Cuando los elementos son múltiples, se almacenan en colecciones que contienen todos los elementos de esa clase que están abiertos en ese momento. Así, Workbooks es la colección de todos los libros de Excel abiertos en ese momento y Worksheets es la colección de todas las hojas de cálculo de esos libros. De esta manera se puede hacer referencia a todos ellos con un único nombre en lugar de tener que nombrarlos uno a uno. Por otro lado, Sheets es la colección de hojas del libro activo (y no de todos los libros abiertos) y Sheet es una hoja del libro activo. Además, cuando se trata del elemento activo, no es necesario nombrarlo, basta con indicar que se trata del objeto activo de la forma ActiveWorkbook, ActiveSheet, ActiveCell o ThisWorkbook (el libro que contiene la macro que está siendo ejecutada). Propiedades y métodos: Las propiedades son las variables que describen los aspectos o características del objeto en el que están incluidas. Pueden ser permanentes o variables. El valor concreto de una propiedad se denomina estado. Por ejemplo, dentro del objeto celda, una de sus propiedades es color del fondo y uno de los posibles estados de esta propiedad es rojo. Esto se escribe de forma genérica como: NombreDelObjeto.Propiedad = estado. En el caso de la celda roja: Selection.Interior.Color = 255 en lugar de selection, que hace referencia a la celda en la que está el cursor, se puede llamar a una celda concreta, por ejemplo Range(“A5”). En este caso, la propiedad interior (fondo) contiene a su vez otra propiedad, Color, y el estado de dicha sub- propiedad es 255, que equivale al rojo brillante. ActiveSheet.Name = “Resultados” cambia el nombre de la hoja activa por Resultados. Los métodos son acciones que el objeto reconoce y sabe ejecutar. Es un procedimiento asociado a un objeto determinado y cuya ejecución sólo puede desencadenarse a través de un mensaje recibido por el objeto o uno de sus descendientes (objetos que dependen de otro objeto jerárquicamente superior). La forma de escribir un método es NombreDelObjeto.Método. Por ejemplo: Range(“A1”).Select selecciona y convierte en la celda activa la casilla A1. Lo mismo ocurre si se especifican las coordenadas de la celda del modo (fila, columna), en este caso Range (1, 1). Si fuera un rango, por ejemplo, de las celdas A1 a C5, el rango sería Range(Cells(1, 1), Cells(5, 3) o Range(“A1:C5”). Otras opciones son Columns(“A:C”), Rows(“1:5”), Range(“A:A”), Range(“3:3”). Sheets(“Hoja1”).Select selecciona la hoja 1 y la convierte en la hoja activa. Otros ejemplos: Range(“A1”).Select selecciona la celda A1 Selection.Copy copia su contenido TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 14 Excel trabaja con un modelo de objetos en el que cada objeto pertenece a un nivel jerárquico. En la parte superior de la jerarquía se encuentra el objeto Application, esto es, el propio programa Excel. Por debajo están ActiveWorkbook > ActiveSheet > Range. Ésta es sólo una de las ramas de la jerarquía. Para conocerlas todas basta con escribir Application. y elegir entre las opciones del cuadro de menú: Y así con cada objeto. De esta manera, para que una celda quede marcada en negrita basta con escribir Selection.Font.Bold = True, aunque podemos especificar toda la jerarquía de esa celda de la forma Application.ActiveWorkbook.ActiveSheet.Selection.Font.Bold = True. Cuando no es necesario declarar un objeto para que el código funcione, como en este caso Application, ActiveWorkbook y ActiveSheet, es porque se trata de objetos predeterminados, es decir, Excel entiende por defecto que la jerarquía de los objetos es la anterior, esto es, que la celda seleccionada está sin duda en la hoja activa, la hoja activa en el libro activo y el libro activo pertenece a Excel. Aun cuando el usuario puede necesitar declarar la ruta completa al objeto, no es necesario hacerlo para cada instrucción. Basta con utilizar el comando With al definir la jerarquía de objetos: Sub Probar_With() crea una macro con una única subrutina que se llama Probar_With With Application.ActiveWorkbook.ActiveSheet.Range(“A1”) la celda A1, dentro de la hoja activa, dentro del libro activo y dentro de la aplicación TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 15 . Value = "Aprender VBA" va a contener este texto . Font.Bold = True el texto va a estar en negrita . Font.ColorIndex = 2 las letras van a ser de color blanco . Interior.ColorIndex = 1 y el interior de la celda de color negro End With fin de las instrucciones para el objeto indicado End Sub fin de la subrutina 2.3 TIPOS DE ERRORES EN VBA Existen dos tipos de errores en VBA: errores de sintaxis y errores de tiempo de ejecución. Errores de sintaxis: Un error de sintaxis es el que se produce, bien porque se escribe mal una instrucción, bien porque se escribe una instrucción correcta en un lugar inadecuado. En el primer caso, el Editor avisa de un error al escribir Mud el lugar de Mod, que es el comando que corresponde al módulo: En el segundo caso, el Editor devuelve un error indicando que después del comando ValorBase as debe definirse dicho valor o decir que es una variable nueva, no pudiendo utilizarse ningún otro comando: TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 16 Errores en tiempos de ejecución: Estos errores son más complejos y ocurren cuando la macro intenta ejecutar una instrucción que no está permitida, de manera que Excel dejará de responder. Algunos de estos errores ocurren por: • Intentar realizar una operación prohibida en Excel, como dividir entre cero o sumar una cadena de texto. • Intentar utilizar una librería de código que no está accesible en ese momento. • Utilizar un bucle con una condición que nunca se cumple. • Tratar de asignar un valor que está fuera de los límites de la variable. Para evitar errores, el Editor permite depurar el código. Una manera sencilla es utilizar la opción Depuración
Paso a paso por instrucciones o simplemente pulsar F8. Esto hace que se inicie la ejecución de la primera línea. Si la línea está correcta, el fondo aparece en amarillo y no se muestra ningún mensaje de error. Para pasar a la siguiente línea hay que pulsar de nuevo F8, y así sucesivamente. Por ejemplo, al escribir valor en lugar de Value: Al pulsar depurar, el Editor nos devuelve a la línea que contiene el error para que lo corrijamos: TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 19 El programa no se sigue ejecutando hasta que el usuario pulsa Aceptar o Cancelar, que aparecen por defecto. El valor que se ha elegido por defecto, 120, aparece directamente en el recuadro a la espera de que el usuario escriba otro valor. Para que le programa muestre el resultado de las operaciones que ha realizado con los datos introducidos hay que crear un nuevo cuadro de diálogo con el comando MsgBox de la forma: MsgBox(mensaje, botones, título). Donde: • Mensaje: es el mensaje que va a recibir el usuario junto con los resultados. Puede incluir caracteres especiales, del tipo +Chr(13) (retorno de carro) o +Chr(10) (avance de línea). • Botones: se pueden incluir diferentes botones: Botones Comando abreviado Descripción VbOKOnly 0 Muestra solamente el botón Aceptar. VbOKCancel 1 Muestra los botones Aceptar y Cancelar VbAbortRetryIgnore 2 Muestra los botones Anular, Reintentar e Ignorar VbYesNoCancel 3 Muestra los botones Sí, No y Cancelar. VbYesNo 4 Muestra los botones Sí y No. VbRetryCancel 5 Muestra los botones Reintentar y Cancelar. VbCritical 16 Muestra el icono de mensaje crítico. VbQuestion 32 Muestra el icono de pregunta de advertencia. VbExclamation 48 Muestra el icono de mensaje de advertencia. VbInformation 64 Muestra el icono de mensaje de información • Título: rótulo de la ventana que se va a generar. Ejemplo 3: Crear cuadros de resultados con botones: a) Crear un cuadro llamado Repetir que pregunte al usuario si desea introducir nuevos datos y ofrezca dos botones, Sí y No. Sub SiNo() Respuesta = MsgBox("¿Desea introducir nuevos datos?", vbYesNo, "Repetir") End Sub TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 20 b) Crear un cuadro llamado Imposible Calcular que avise al usuario de que ha introducido un valor erróneo junto con un icono de mensaje crítico y los botones reintentar y cancelar: Sub Fallo() Aviso = MsgBox("Error: ha introducido un dato inválido", 5 + 16, "Imposible Calcular") End Sub c) Crear un cuadro llamado Advertencia que indique al usuario que faltan datos por introducir y que muestre un icono de advertencia: Sub Advertencia() Advertencia = MsgBox("Faltan datos por introducir", vbExclamation, "Advertencia") End Sub TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 21 d) Diseñar un programa que calcule el precio total de una compra a partir del número de unidades compradas, el precio unitario y el IVA, creando los correspondientes cuadros para introducir las variables y el cuadro con la respuesta final. Sub Precio_Compra() Dim Cantidad As Integer Dim Precio, IVA, Total As Single Precio = InputBox("Introduzca el precio unitario en €", "Precio") Cantidad = InputBox("Introduzca la cantidad que desea comprar", "Cantidad", 1) IVA = InputBox("Modifique el tipo impositivo si es necesario", "IVA", 0.21) Total = Cantidad * Precio + Cantidad * Precio * IVA Resultado = MsgBox("El precio total es:" + Chr(13) + Chr(10) + Chr(13) + Chr(10) & Total, 0, "Precio final") Después del mensaje, tiene que bajar dos líneas y recuperar la tabulación inicial hasta poner el resultado (esto es, dejar una línea vacía entre medias). 0 es que aparezca el botón de aceptar únicamente End Sub 3.3 FUNCIONES La expresión general de una función es la siguiente: Function Nombre(argumentos separados por comas) TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 24 El lenguaje VBA contiene una serie de funciones predeterminadas de carácter no matemático que pueden resultar útiles y que son las siguientes: FUNCIÓN DESCRIPCIÓN Abs Regresa el valor absoluto de un número Asc Obtiene el valor ASCII del primer carácter de una cadena de texto CBool Convierte una expresión a su valor booleano CByte Convierte una expresión al tipo de dato Byte CCur Convierte una expresión al tipo de dato moneda (Currency) CDate Convierte una expresión al tipo de dato fecha (Date) CDbl Convierte una expresión al tipo de dato doble (Double) CDec Convierte una expresión al tipo de dato decimal (Decimal) Choose Selecciona un valor de una lista de argumentos Chr Convierte un valor ANSI en valor de tipo texto CInt Convierte una expresión en un dato de tipo entero (Int) CLng Convierte una expresión en un dato de tipo largo (Long) CreateObject Crea un objeto de tipo OLE CStr Convierte una expresión en un dato de tipo texto (String) CurDir Regresa la ruta actual CVar Convierte una expresión en un dato de tipo variante (Var) Date Regresa la fecha actual del sistema DateAdd Agrega un intervalo de tiempo a una fecha especificada DateDiff Obtiene la diferencia entre una fecha y un intervalo de tiempo especificado DatePart Regresa una parte específica de una fecha DateSerial Convierte una fecha en un número serial DateValue Convierte una cadena de texto en una fecha Day Regresa el día del mes de una fecha Dir Regresa el nombre de un archivo o directorio que concuerde con un patrón EOF Regresa verdadero si se ha llegado al final de un archivo FileDateTime Regresa la fecha y hora de la última modificación de un archivo FileLen Regresa el número de bytes en un archivo FormatCurrency Regresa un número como un texto con formato de moneda FormatPercent Regresa un número como un texto con formato de porcentaje Hour Regresa la hora de un valor de tiempo IIf Regresa un de dos partes, dependiendo de la evaluación de una expresión InputBox Muestra un cuadro de diálogo que solicita la entrada del usuario InStr Regresa la posición de una cadena de texto dentro de otra cadena InStrRev Regresa la posición de una cadena de texto dentro de otra cadena pero empezando desde el final Int Regresa la parte entera de un número IsDate Regresa verdadero si la variable es una fecha IsEmpty Regresa verdadero si la variable está vacía IsError Regresa verdadero si la expresión es un valor de error IsNull Regresa verdadero si la expresión es un valor nulo IsNumeric Regresa verdadero si la variable es un valor numérico Join Regresa una cadena de texto creada al unir las cadenas contenidas en un vector o matriz TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 25 LCase Regresa una cadena convertida en minúsculas Left Regresa un número específico de caracteres a la izquierda de una cadena Len Regresa la longitud de una cadena (en caracteres) LTrim Remueve los espacios a la izquierda de una cadena Mid Extrae un número específico de caracteres de una cadena de texto Minute Regresa el minuto de una dato de tiempo Month Regresa el mes de una fecha MsgBox Despliega un cuadro de dialogo con un mensaje especificado Now Regresa la fecha y hora actual del sistema Replace Reemplaza una cadena de texto con otra Space regresa una cadena de texto con el número de espacios especificados Split Regresa un arreglo formado por cadenas de texto que formaban una sola cadena Str Regresa la representación en texto de un número Right Regresa un número especificado de caracteres a la derecha de una cadena de texto Rnd Regresa un número aleatorio entre 0 y 1 Round Redondea un número a una cantidad específica de decimales RTrim Remueve los espacios en blanco a la derecha de una cadena de texto Second Regresa los segundos de un dato de tiempo StrComp Compara dos cadenas de texto StrReverse Invierte el orden de los caracteres de una cadena Time Regresa el tiempo actual del sistema Timer Regresa el número de segundos desde la media noche TimeValue Convierte una cadena de texto a un número de serie de tiempo Trim Remueve los espacios en blanco al inicio y final de una cadena de texto TypeName Obtiene el nombre del tipo de dato de una variable Ucase Convierte una cadena de texto en mayúsculas Val Regresa el número contenido en una cadena de texto Weekday Regresa un número que representa un día de la semana WeekdayName Regresa el nombre de un día de la semana Year Obtiene el año de una fecha 3.4 ESTRUCTURAS DE CONTROL DE FLUJO Las estructuras de control de flujo permiten establecer condiciones para que una parte del código no se ejecute automáticamente, sino que haya que haya que cumplir alguna clase de condición previamente. Estructuras If-Then: La declaración If-Then permite validar una condición antes de seguir ejecutando el código. Se usa en ocasiones en las que, sólo si se cumple la condición, hay que ejecutar una acción determinada. La estructura es la siguiente: If condición Then TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 26 Instrucción a ejecutar si se cumple la condición Opción a) Else Instrucción a ejecutar si no se cumple Opción b) ElseIf3 siguiente condición Instrucción a ejecutar si no se cumple End If Ejemplo 4: Estructuras If-Then a) Crear un programa que calcule el logaritmo neperiano de un número dado por el usuario si éste es positivo. Si es negativo, que cree un cuadro de respuesta con un icono de alerta que advierta al usuario del error y le pida reintentarlo o cancelar. Sub SiEntonces() Número = InputBox("Introduzca un número", "Número") If Número > 0 Then Valor = Log(Número) Respuesta = MsgBox("El logaritmo neperiano es:" + Chr(13) + Chr(10) & Valor, 0, "Resultado") Else Respuesta = MsgBox("El número debe ser positivo", 5 + 16, "Resultado") End If End Sub 3 Si sólo hay dos opciones, se cumple o no se cumple, basta con Else, pero si hay varias condiciones habrá que usar ElseIf, es decir, si no se cumple la primera condición pero a su vez hay que valorar si se cumple la segunda. TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 29 Case 10 To 21.9 Solución = MsgBox("El tipo de crudo es: Pesado", 0, "Tipo de crudo") Case 22 To 29.9 Solución = MsgBox("El tipo de crudo es: Mediano", 0, "Tipo de crudo") Case 30 To 40 Solución = MsgBox("El tipo de crudo es: Liviano", 0, "Tipo de crudo") Case Else Solución = MsgBox("Imposible determinar el tipo de crudo", 5 + 16, "Error") End Select End Sub TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 30 b) Diseñar un programa que le pida al usuario el nombre de un elemento del grupo de los halógenos y le devuelva el peso atómico: Sub Halógenos() Dim Elemento As String Elemento = InputBox("Escriba el elemento que desea evaluar:", "Elemento") Select Case Elemento Case "Flúor" PA = MsgBox("18.9984 g/mol", 0, "Peso atómico") Case "Cloro" PA = MsgBox("35.453 g/mol", 0, "Peso atómico") Case "Bromo" PA = MsgBox("79.904 g/mol", 0, "Peso atómico") Case "Yodo" PA = MsgBox("126.9045 g/mol", 0, "Peso atómico") Case "Astato" PA = MsgBox("210 g/mol", 0, "Peso atómico") Case Else PA = MsgBox("El elemento introducido no es un halógeno", vbRetryCancel, "Error") End Select End Sub TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 31 Estructuras For-Next: Permiten ejecutar una instrucción una serie de veces mientras la variable toma los valores definidos dentro de un intervalo, es decir, es una estructura de bucle. Después, continúa con la siguiente instrucción. La estructura general es la siguiente: For variable = valor inicial To valor final Step Incremento Instrucción Next TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 34 Ejemplo 8: Estructuras Do While-Loop a) Diseñar un programa que calcule la temperatura media. Para ello debe pedir al usuario que declare el número de valores que va a introducir y cuáles son esos valores mediante sucesivos cuadros de entrada de datos. El resultado final debe aparece en un cuadro de mensaje. Sub DoWhileLoop() Dim Temp, Suma, Media As Single Dim i, n As Integer Suma = 0 n = InputBox("Introduzca el número de medidas", "Número de medidas") i = 1 Do While i <= n Temp = InputBox("Temperatura " & i, "Introduzca las temperaturas")7 Suma = Suma + Temp i = i + 1 Loop Media = (Suma / n) Resultado = MsgBox("Temperatura media = " & Media, 0, "Resultado") End Sub 7 ¡Ojo! No entiende los puntos como separadores decimales, ni siquiera al escribirlos con el teclado numérico, de manera que, por ejemplo 23.5 lo toma como 235. TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 35 Estructuras Do-Loop Until: Estas estructuras crean un bucle de instrucciones que sólo se termina cuando se cumple una condición, pero dicha condición no puede establecerse a priori, sino que es el resultado de alguna de las instrucciones del bucle. La estructura general es la siguiente: Do Instrucciones Loop Until la respuesta sea la condición requerida Ejemplo 9: Estructuras Do-Loop Until a) Diseñar un programa que calcule la suma y la media de un conjunto de números, que el usuario introduce en un cuadro de datos de uno en uno, hasta que el usuario pulsa la opción de no introducir más números: Sub DoLoopUntil() Dim valor, suma, media As Single Dim n As Integer suma = 0 n = 0 Do valor = InputBox("Introduzca el siguiente número", "Valores") suma = suma + valor n = n + 1 respuesta = MsgBox("¿Desea introducir otro número?", vbYesNo, "Siguiente") TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 36 Loop Until respuesta = vbNo media = suma / n Final = MsgBox("Números procesados = " & n & Chr(10) & "Suma = " & suma & Chr(10) & "Media = " & media, 0, “Resultado”) End Sub b) Diseñar un programa que pregunte al usuario qué temperatura desea como setpoint del reactor. El programa debe identificar como válidas las temperaturas entre 50 y 100ºC y, si el usuario introduce un valor fuer del rango, mostrar un mensaje de error. Sub TemperaturaConBucle() Dim Temp As Single Do Temp = InputBox("Introduzca la temperatura de operación en ºC", "Selección de la Temperatura") If Temp < 50 Or Temp > 100 Then Mensaje = MsgBox("La temperatura debe estar entre 50ºC y 100ºC", vbExclamation + 0, "Error") End If Loop Until Temp >= 50 And Temp <= 100 TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 39 deseado y arrastrando hasta hacer un recuadro. Los controles que se pueden insertar son los siguientes: Control Qué hace Etiqueta Almacena texto Cuadro de texto Permite introducir texto al usuario Cuadro Combinado Inserta una lista de elementos desplegable Cuadro de Lista Crea una lista de elementos Casilla de verificación Permite seleccionar uno o más elementos Botón de opción Permite seleccionar un solo elemento Botón de alternar Botón on/off Marco Crea un cuadro que contiene otros controles Botón de Comando Crea un botón que se puede presionar Barra de tabulaciones Crea fichas o pestañas Página múltiple Permite crear cuadros de diálogo con fichas Barra de desplazamiento Permite desplazarse en una lista y seleccionar Botón de número Permite desplazarse en una lista y seleccionar Imagen Inserta una imagen RefEdit Permite seleccionar un rango Los controles tienen propiedades específicas, pero también hay propiedades comunes a todos ellos, que son las siguientes: Control Qué hace Accelerator Selecciona una letra de entre las del título del control para crear un comando de ejecución rápido del tipo Alt+Letra. La letra seleccionada aparece subrayada en el título. AutoSize Si se selecciona True se autoajusta el tamaño del control. BackColor Color de fondo. BackStyle Estilo del fondo (opaco o transparente). Caption Texto que aparece en el control. Value Valor del control. Left and Top Indica la posición del control. TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 40 Width and Height Determina la anchura y altura del control. Visible Si es False, el control se oculta. Name Nombre del control. Cada control debe tener un nombre único. Picture Imagen para mostrar. Las propiedades del control pueden ser modificadas bien en el propio control, bien en el código o bien en el cuadro de propiedades del control. En este caso se ha insertado un cuadro de texto y se ha cambiado su nombre por Rótulo. Botón de comando: Este control permite insertar una determinada acción que se ejecutará al pulsar sobre el botón. Al insertar el control y hacer doble clic en él se abre el módulo donde se tiene que insertar el código a ejecutar. Aunque se puede insertar cualquier acción, lo habitual es que los formularios contengan, entre otras cosas, un botón de comando que permita ejecutar el TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 41 código, cerrar el formulario, o ambas cosas. Por ejemplo, se ha creado un botón de comando llamado Terminar, cuyo código es End (finaliza la tarea) y se ha elegido que la propiedad Default8 sea True para que al pulsar Enter se ejecute dicho código: Al ejecutar la macro: Pulsando el botón o presionando Enter, el formulario se cierra. 8 Si en lugar de la propiedad Default, se selecciona como True la propiedad Cancel, el código se ejecuta pulsando Esc. TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 44 Si se escribe un valor en la casilla y se pulsa Aceptar o Enter, el valor se escribe en la celda A1. Si se pulsa Cancelar o Esc, el formulario se cierra y se vuelve al Editor de VB. Cuadro combinado: Sirve para crear una lista desplegable de elementos de entre los que se pueda seleccionar uno o varios para realizar alguna acción con ellos. Los elementos se pueden introducir mediante peticiones por teclado o ser importados desde una tabla o rango de celdas en una hoja de cálculo. Por ejemplo, se puede crear un cuadro combinado con una lista de elementos que se van introduciendo a través de un cuadro de texto: El formulario contiene una etiqueta cuya caption es Introduzca un elemento. Después se ha añadido un cuadro de texto llamado Elementos donde el usuario tiene que escribir dichos elementos. Después se ha insertado un cuadro combinado con nombre Lista. De entre sus propiedades se ha elegido ListRows = 3, es decir, que muestre sólo 3 elementos a la vez y, si hay más elementos, aparezca una barra de desplazamiento. A continuación se ha añadido un botón de comando con el nombre OK y caption Aceptar. El código de ese botón incluye: TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 45 Private Sub OK_Click() al hacer click en el botón Lista.AddItem Elementos.Text el elemento escrito en el cuadro de texto Elementos se añade al cuadro combinado de nombre Lista Elementos.Text = "" después se borra el contenido del cuadro Elementos Elementos.SetFocus y se vuelve a poner el cursor en dicho cuadro para escribir el siguiente elemento End Sub Finalmente, se ha añadido un botón de comando con la caption Terminar y cuyo código es: Private Sub CommandButton1_Click() al hacer clic en el botón End termina de ejecutar la macro y cierra el formulario End Sub El resultado es: TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 46 Cuadro de lista: Permite insertar un conjunto de elementos que pueden ser seleccionados. Los elementos seleccionados se pueden mostrar en forma de lista en un cuadro de texto del formulario o en un rango de celdas de una hoja de Excel. Por ejemplo, se puede crear una lista de equipos de entre la que se puedan seleccionar varios elementos para confeccionar otra lista que aparezca en un cuadro de diálogo. Al presionar el botón Mostrar lista se hace visible la lista y al apretar Ok el elemento seleccionado se incorpora al recuadro: TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 49 Barra de desplazamiento y botón de número: Estos controles permiten dar un valor a una variable en función del desplazamiento que se haya realizado en dicho botón o del número de veces que se haya pulsado. Por ejemplo, se puede insertar una barra de desplazamiento que ajuste la temperatura de un reactor y un botón de número que seleccione el tiempo de reacción, mostrando los valores seleccionados en cuadros de texto: Se ha insertado una barra de desplazamiento llamada ScrollBar1 que puede tomar valores entre 0 y 250, escribiendo dicho valor en un cuadro de texto llamado Temperatura. Se ha insertado, además, un botón de número, llamado SpinButton1, cuyo valor se va a escribir en otro cuadro de texto llamado Tiempo. Finalmente, el botón salir cierra la aplicación. TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 50 ANEXO: Caracteres Ascii más habituales: Código vba Descripción Código vba Descripción 9 vbTab tabulación horizontal 46 . 10 vbLf salto de línea 47 / 11 vbVerticalTab tabulación vertical 58 : 12 página nueva 59 ; 13 vbCr retorno del carro 60 < 24 cancelar 61 = 27 escape 62 > 32 espacio 63 ? 33 ! 64 @ 34 " 91 [ 35 # 92 \ 36 $ 93 ] 37 % 94 ^ 38 & 95 _ 39 ' 96 ` 40 ( 123 { 41 ) 124 | 42 * 125 } 43 + 126 ~ 44 , 127 borrar 45 - TEMA 6 PROGRAMACIÓN DE MACROS DE EXCEL UTILIZANDO VISUAL BASIC FOR APPLICATIONS 51 Colores usando la propiedad ColorIndex: Colores usando la propiedad Color = RGB(R, G, B): RGB(0, 0, ): negro 0 RGB(255, 255, ): blanco 255 RGB(255, 0, ): rojo 0 RGB(0, 255, ): verde 0 RGB(0, 0, ): azul 255