macros en microsoft excel, Apuntes de Programación Funcional

¡Descarga macros en microsoft excel y más Apuntes en PDF de Programación Funcional solo en Docsity! Taller: EXCEL Evaluación de funciones y creación de macros con Excel Duración: 5hrs. Ing. Imelda Escamilla Bouchán [1] CONTENIDO Tema Página 1.1 Introducción 2 1.2 Objetivo 2 1.3 Evaluación de funciones 2 1.3.1 Funciones definidas por el usuario 2 1.3.2 Errores comunes 3 1.3.3 Evaluando una función con varios tipos de parámetro 4 1.3.3.1 Evaluación con argumentos variables 4 1.3.3.2 Evaluación con argumentos variables y/o constantes 5 1.3.3.3 Constituyendo rangos con un incremento fijo 7 1.4 Creación de macros empleando VBA 8 1.4.1 ¿Qué es una macro? 8 1.4.2 Objetos, propiedades y métodos 8 1.4.3 Conceptos útiles para trabajar con macros en Excel 9 1.4.4 Grabar una macro 10 1.4.4.1 Mi primera macro 10 1.4.4.1 Ejecutar una macro 13 Práctica 1. “Grabar macros” 14 1.4.5 Manipulación de macros 15 1.4.6 Editor de Visual Basic 16 Práctica 2. “Observando los códigos de una macro en Excel” 20 1.4.7 Cuadro de control 22 Práctica 3. “Formulario” 32 Bibliografía 33 [4] 6. Dependiendo de la forma en la que se encuentre configurado el sistema Windows, debe usarse punto o coma para separar la parte decimal de los números a evaluar. Para personalizarlo, se debe entrar al panel de control y en la Configuración regional se selecciona `Números'. En la primera cejilla, „Símbolo Decimal' se selecciona el punto o la coma, según sea el caso. Finalmente, se presiona el botón „Aplicar‟ y luego „Aceptar„. 7. Existe una circunstancia, que es comúnmente confundida con un error esta es cuando el sistema trabaja con poca precisión y se presentan valores numéricos no esperados. Por ejemplo, si el formato de una celda se ha definido para dos posiciones, entonces la operación +1.999+1 dará como resultado el valor de 2, que no es otra cosa que el resultado de la suma redondeado a dos decimales. El valor correcto se obtiene aumentando la precisión con el icono: También se puede cambiar la precisión en el menú „Formato-Celdas- Número-Posiciones decimales‟. Es importante recordar que estos cambios son solo en apariencia, pues, independientemente del número de dígitos que sean desplegados, Excel manipula números con una precisión de hasta 15 dígitos, si un número contiene más de 15 dígitos significativos, Excel los convertirá en ceros. 1.3.3 Evaluando una función con varios tipos de parámetros. Muchas fórmulas a evaluar tienen argumentos de distinto tipo, pues algunos argumentos varían (a veces con un incremento determinado), mientras que otros permanecen constantes. Por lo general estos argumentos son tomados de celdas específicas, por lo que es importante saber manejar distintos escenarios para la evaluación de una función o formula. 1.3.3.1 Evaluación con argumentos variables Continuando con el ejemplo que iniciamos en la sección 1.3.1, a partir de la celda B4 podemos continuar introduciendo valores, siempre en la columna B y con el cuidado de que los números ingresados no salgan del dominio de la función ( ) ( ) ( ) ( ) Que en este caso es el conjunto de los números reales positivos. Una vez hecho esto, se evalúa la función ( ) en la celda C3, como se hizo previamente. Posteriormente, seleccionamos esta misma celda y ubicamos el puntero del mouse en la esquina inferior derecha, arrastrándolo hasta la celda deseada. [5] Otra opción es hacer un doble clic en la esquina inferior derecha de la celda a copiar y esto realiza la copia automáticamente, Figura 1.2. Figura 1.2. Copia de una fórmula en un grupo de celdas 1.3.3.2 Evaluación con argumentos variables y/o constantes Es una práctica bastante común el tener que evaluar funciones o fórmulas que dependen de varios parámetros, algunos de los cuales se mantienen fijos mientras que otros son variables. Ejemplo1. El siguiente ejemplo describe una función con dos parámetros y una variable. La función ( ) con , describe el tamaño de una población en el momento t. Dónde: - k es una constante de proporcionalidad que se determina experimentalmente, dependiendo de la población particular que está siendo modelada, - P0 es la población inicial y - K es una constante llamada capacidad de contención o capacidad máxima que el medio es capaz de sostener. Si queremos evaluar P(t) para diferentes valores de tiempo t en días, seguimos los siguientes pasos: 1. El primer paso consiste en escribir encabezados en cada una de las columnas (o filas) donde vamos a escribir los datos que serán argumentos de la función. Para este ejemplo vamos a comenzar en la celda B3 con las siguientes etiquetas. [6] P0 K k t P(t) 2. A continuación escribimos los valores de los parámetros, empezando en la celda B4 100 1000 0.08 0 3. Ahora escribimos la fórmula de la función P(t) en la celda G4: Como se observa, el único argumento variable es t y nos interesa mantener a los demás argumentos constantes. Para mantener un valor constante, se le agrega el símbolo $ antes del número de fila, como por ejemplo C$4. Para este ejemplo, como se muestra en la Figura 1.3, los argumentos constantes son los que están en las celdas B4, C4 y D4, mientras que el valor de t en la celda E4, es variable. Figura 1.3 Evaluación con parámetros constantes y un parámetro con un incremento variable. 4. Por último, escribimos varios valores para t en la columna E, seleccionamos la celda F4 y arrastramos para evaluar P(t) en el resto de valores de t. Cabe mencionar que P(t) es la solución de la llamada ecuación logística ( ) [9] 1.4.3 Conceptos útiles para trabajar con macros en Excel - WorkSheet (Objeto hoja de cálculo) - Range (Objeto celda o rango de celdas). Un objeto Range está definido por una clase donde se definen sus propiedades, recordemos que una propiedad es una característica, modificable o no, de un objeto. Entre las propiedades de un objeto Range están Value, que contiene el valor de la celda, Column y Row que contienen respectivamente la fila y la columna de la celda, Font que contiene la fuente de los caracteres que muestra la celda, etc. Range, como objeto, también tiene métodos, recordemos que los métodos sirven llevar a cabo una acción sobre un objeto. Por ejemplo el método Activate, hace activa una celda determinada, Clear, borra el contenido de una celda o rango de celdas, Copy, copia el contenido de la celda o rango de celdas en el portapapeles. - Conjuntos. Una conjunto es una colección de objetos del mismo tipo . Por ejemplo, dentro de un libro de trabajo puede existir más de una hoja (WorkSheet), todas las hojas de un libro de trabajo forman un conjunto, el conjunto WorkSheets. Cada elemento individual de un conjunto se referencia por un índice, de esta forma, la primera, segunda y tercera hoja de un libro de trabajo, se referenciarán por WorkSheets(1), WorkSheets(2) y WorkSheets(3). - Objetos de Objetos. Es muy habitual que una propiedad de un objeto sea otro objeto. Siguiendo con el coche, una de las propiedades del coche es el motor, y el motor es un objeto con propiedades como caballos, número de válvulas, etc. y métodos, como aumentar_revoluciones, coger_combustible, mover_pistones, etc. En Excel, el objeto WorkSheets tiene la propiedad Range que es un objeto, Range tiene la propiedad Font que es también un objeto y Font tiene la propiedad Bold (negrita). Tenga esto muy presente ya que utilizaremos frecuentemente Propiedades de un objeto que serán también Objetos. Dicho de otra forma, hay propiedades que devuelven objetos, por ejemplo, la propiedad Range de un objeto WorkSheet devuelve un objeto de tipo Range. [10] - Programación Orientada a Objetos o Programación Basada en Objetos. Hay una sutil diferencia entre las definiciones del título. Programación orientada a Objetos, significa que el programador trabaja con objetos fabricados por él mismo, es decir, el programador es quien implementa las clases para luego crear objetos a partir de ellas. Lo que haremos nosotros, por el momento, será utilizar objetos ya definidos por la aplicación Excel (WorkSheets, Range,...) sin implementar ninguno de nuevo, por lo que en nuestro caso es más correcto hablar de programación basada en objetos. Observe que esta es una de las grandes ventajas de la OOP, utilizar objetos definidos por alguien sin tener que conocer nada sobre su implementación, sólo debemos conocer sus propiedades y métodos y utilizarlos de forma correcta. 1.4.4 Grabar una macro En Excel existen dos maneras de crear una macro, la primera es usando la herramienta para grabar macros que trae Excel y la segunda es empleando el lenguaje VBA. En esta sección explicaremos como usar el primer método. Cuando se graba una macro, Excel almacena información sobre cada paso dado cuando se ejecuta una serie de comandos. A continuación, se ejecuta la macro para que repita los comandos. Si se comete algún error mientras se graba la macro, también se graban las correcciones que se realicen, esto ocurre porque se graba todo lo que se hace. Luego Visual Basic almacena cada macro en un nuevo módulo adjunto a un libro. Recuerde que si comete algún error durante la grabación, no debe preocuparse, porque puede borrar la macro e intentarlo de nuevo. 1.4.4.1 Mi primera Macro. En este apartado crearás una macro y te explicaremos como acceder a ella y como parametrizarla. Activación de la barra de herramientas Antes de empezar es necesario colocar la barra de herramienta de Programador, que contendrá todas las herramientas necesarias para realzar todos los ejercicios y prácticas de este curso. Vamos a Inicio>Opciones Figura 1.6. [11] Figura 1.6 Menú opciones. Posteriormente en la ventana emergente que se despliega se busca Personalizar cinta de opciones y se selecciona en el menú del lado derecho la opción de Programador Figura 1.7. Figura 1.7. Menú opciones2. [14] Practica 1 “Grabar Macros” Objetivo: Que el participante tenga nociones claras del uso de las macros, empleando algunas de las herramientas que brinda el menú Programador de Excel. Instrucciones: Crear las siguientes macros, en el orden que se indica. 1) Macro “archivo” que se active con el método abreviado de su elección y que permita abrir un archivo. 2) Macro “selección” que seleccione las celdas A1:A10 de la primera hoja del libro abierto. 3) Macro “letra” que ponga tipo de letra arial 4) Macro “color” que ponga letra de color azul. 5) Macro “tamaño” que ponga letra de tamaño 14. 6) Macro “Todo” que una las macros anteriores. [15] 1.4.5 Manipulación de macros Tras grabar una macro, se puede ver el código de macro con el Editor de Visual Basic para corregir errores o modificar lo que hace la macro. Por ejemplo, si la macro de ajuste de texto también tiene que aplicar el formato de negrita al texto, se puede grabar otra macro para aplicar el formato de negrita a una celda y, a continuación, copiar las instrucciones de esa macro a la macro de ajuste de texto. El Editor de Visual Basic es un programa diseñado para que los usuarios principiantes puedan escribir y editar fácilmente código de macro, y proporciona mucha Ayuda en pantalla. No es preciso saber cómo se programa o se utiliza el lenguaje de Visual Basic para realizar cambios sencillos en las macros. El Editor de Visual Basic permite modificar macros, copiarlas de un módulo a otro, copiarlas entre diferentes libros, cambiar el nombre de los módulos que almacenan las macros o cambiar el nombre de las macros. Para acceder al código fuente de la macro, solamente es necesario seleccionar en el menú Programador > Macro > Modificar, seleccionando la macro de la cual queremos ver el código Figura 2.2 y mostrará una ventana como en la Figura 2.3 con el código generado al momento de grabar la macro. Figura 2.2 Manipulación de macros. [16] Figura 2.3 Editor de VBA. 1.4.6 Editor de Visual Basic El editor de Visual Basic es la aplicación que utilizaremos para construir las macros que interactuaran junto con los libros de trabajo. A continuación prepararemos un archivo en el que escribiremos las primeras instrucciones en Visual Basic. Preparar un archivo nuevo. Para entrar en el editor de Visual Basic, ejecute los siguientes pasos. 1.-En el menú Programador seleccione la opción Visual Basic Figura 2.4. Y se abrirá la ventana de la Figura 2.5. Figura 2.4 Acceso al editor VBA. Maximice la ventana para trabajar más cómodamente y procure tener activadas las ventanas Explorador de proyectos y la ventana Propiedades (Ver/Explorador de proyectos y Ver/Ventana propiedades). [19] Si desea poner "Hola" (o cualquier valor) en la casilla activa, puede utilizar la propiedad (objeto) Activecell de WorkSheets. Así para poner "Hola" en la casilla activa de la hoja activa seria Sub Primero ActiveSheet.ActiveCell.Value = "Hola" End Sub Para terminar con este primer ejemplo. WorkSheets están dentro del Objeto WorkBooks (libros de trabajo) y WorkBooks están dentro de Application. Application es el objeto superior, es el que representa la aplicación Excel. Así, el primer ejemplo, siguiendo toda la jerarquía de objetos quedaría de la forma siguiente. Sub Primero Application.WorkBooks(1).WorkSheets(1).Range("A1"). Value = "Hola" End Sub Insistiendo con la nomenclatura, Application casi nunca es necesario especificarlo, piense que todos los objetos penden de este, WorkBooks será necesario implementarlo si en las macros se trabaja con diferentes libros de trabajo (diferentes archivos), a partir de WorkSheets, es aconsejable incluirlo en el código, sobre todo si se quiere trabajar con diferentes hojas, verá, sin embargo, que en muchas ocasiones no se aplica. Ejecutar un procedimiento o función. Pruebe ejecutar el primer procedimiento de ejemplo. 1. Sitúe el cursor dentro del procedimiento. 2. Active opción de la barra de menús Ejecutar/ Ejecutar Sub Userform. También puede hacer clic sobre el botón o pulsar la tecla F5. [20] Practica 2 “Observando los códigos de una macro en Excel” Objetivo: El asistente interactuar con códigos generados por el editor VBA al momento de grabar una macro. Instrucciones: Gravar las siguientes macros, en el orden que se indica. 1) Generar una macro que escriba un nombre en una celda y lo ponga cursivas y observe el código. 2) Generar una macro que escriba un nombre en una celda y lo centre y observe el código. 3) Generar una macro que escriba un nombre en una celda y cambie el tamaño de letra a 18 y el tipo a Arial y observe el código. 4) Generar una macro que ejecute a todas las anteriores y observe el código. [21] Códigos más comunes Trasladarse a una Celda Range("A1").Select Escribir en una Celda Activecell.FormulaR1C1="Paty" Letra Negrita Selection.Font.Bold = True Letra Cursiva Selection.Font.Italic = True Letra Subrayada Selection.Font.Underline = xlUnderlineStyleSingle Centrar Texto With Selection .HorizontalAlignment = xlCenter End With Alinear a la izquierda With Selection .HorizontalAlignment = xlLeft End With Alinear a la Derecha With Selection .HorizontalAlignment = xlRight End With Tipo de Letra (Fuente) With Selection.Font .Name = "AGaramond" End With Tamaño de Letra (Tamaño de Fuente) With Selection.Font .Size = 15 End With Copiar Selection.Copy Pegar ActiveSheet.Paste [24] Una de las partes más fundamentales es “Modo Diseño” es la posibilidad de cambiar cualquier cosa del Control, por ejemplo tamaño, ubicación, etc. Y para poder probar su funcionamiento se debe salir de “Modo Diseño”. Otra parte fundamental es “Propiedades” , donde es posible manipular atributos fundamentales para que el control se adapte a sus necesidades. Por ejemplo, Max, Min, LinkedCell, Delay (velocidad con que cambian los números), Shadow (le agrega sombra al control), entre otras más. Ejemplo 2 Otro de los botones más útiles que tiene la Barra de herramientas de “Cuadro de Controles” es el “Cuadro Combinado”. Primera Sección: Escribir en la hoja3 los siguientes datos de manera consecutiva. -Televisor -Heladera -Licuadora -Monitor -Teclado Nombre a este rango de datos como “datos” (sin comillas) (Para nombrar un rango de datos marque los datos y escriba el nombre en el “Cuadro de nombres”) Segunda parte: Dibuje un “Cuadro combinado” por ejemplo: Seleccione propiedades Busque la propiedad: ListFillRange y escriba: “datos” (sin comillas). En la propiedad LinkedCell Escriba A1 (es la celda donde se mostrará el dato elegido). Salga del “Modo Diseño” y pruebe este ejemplo: [25] Podrá comprobar que en el “Cuadro combinado” se encuentran los datos ingresados en la hoja3 y que cuando elige uno, éste se muestra en la celda A1. Es necesario recordar que esto se aplica tanto a “Cuadro combinado” (arriba) o a “Cuadro de lista” (abajo) Ejemplo 3 Esta vez haremos un botón que cuando se presione pase a otra hoja del Excel. Por ejemplo se puede hacer un menú con varios botones que al presionarlos pasen a las distintas opciones. En la Hoja1 crear dos “botones de comando”. Por Ejemplo: Seleccione el primero botón y muestre las propiedades Cambie la Propiedad “Caption” por : “Ventas” Seleccione el segundo botón y muestre las propiedades Cambie la Propiedad “Caption” por: “Compras” [26] Seleccione el primer botón y haga clic en ver código En esta parte se abrirá el Editor de Visual Basic y debe escribir lo siguiente: Hoja2.activate Cierre el editor de Visual Basic y seleccione el segundo botón, haga clic en ver código y escriba: Hoja3.activate Creando formularios y programándolos Un formulario es una ventana que se programa por medio de controles, estos controles responden a eventos que son programados dentro del ambiente de VBA. En esta sección se muestra como crear un formulario y programar cada objeto: Ejemplo 4 1. Abrir el editor de Visual Basic. 2. Activar las opciones: Explorador de Proyectos Ventana Propiedades 3. Del Menú Insertar elegir la opción UserForm. En el Explorador de Proyecto se observara que se insertó el UserForm. 4. Elegir del Cuadro de Herramientas el Control Etiqueta y arrastrar al UserForm, dibujando en el Formulario UserForm1 la etiqueta Label1, después de un clic en la etiqueta dibujada y podrá modificar el nombre de adentro y pondremos Nombre. Si por error da doble clic en la etiqueta y lo manda a la pantalla de programación de la etiqueta, solo de doble clic en UserForm1 que se encuentra en el Explorador de Proyecto. [29] Empleando formulas Es de suma importancia saber aplicar formulas con macros de Excel, ya que la mayoría de las hojas de cálculos las involucran, por ejemplo los Inventarios, las Nóminas o cualquier otro tipo de hoja las llevan, es por eso que en la siguiente sección se muestra cómo conjuntar formulas con macros de Excel. Ejemplo 5. 1. Retomando los pasos del ejemplo anterior, generaremos un formulario con los siguientes campos: a. Nombre b. Edad c. Días vividos Y un botón de Resultado. Los datos que se preguntaran serán Nombre y Edad, los Días Vividos se generaran automáticamente cuando insertes la edad. A continuación se muestra como se deben de programar estos Controles: Programación de los Controles: Botón. Private Sub CommandButton1_Click() Selection.EntireRow.Insert TextBox1 = Empty TextBox2 = Empty TextBox3 = Empty TextBox1.SetFocus End Sub Nombre. Private Sub TextBox1_Change() Range("A9").Select ActiveCell.FormulaR1C1 = TextBox1 End Sub Edad. Private Sub TextBox2_Change() Range("B9").Select ActiveCell.FormulaR1C1 = TextBox2 Rem aquí se crea la Formula TextBox3 = Val(TextBox2) * 365 Rem El Textbox3 guardara el total de la multiplicación del Textbox2 por 365 Rem El Comando Val permite convertir un valor de Texto a un Valor Numérico Rem Esto se debe a que los Textbox no son Numéricos y debemos de Convertirlos End Sub [30] Dias vividos. Private Sub TextBox3_Change() Range("C9").Select ActiveCell.FormulaR1C1 = TextBox3 End Sub Al ejecutarse el formulario y se introduzca la edad se obtendrán el resultado de los días vividos en el Textbox3 y también en Excel. El comando Val es un comando de Visual Basic que te permite convertir un valor de texto a un valor numérico. Ejemplo 6. Generaremos otro ejemplo, Crea el Siguiente Formulario con los siguientes datos: • 5 Etiquetas • 5 Textbox • 1 Botón de Comando Los datos que se preguntaran serán Nombre, Días Trabajados, Pago por Día, Bonos y Sueldo Neto. Genera el siguiente código: Private Sub CommandButton1_Click() Selection.EntireRow.Insert TextBox1 = Empty TextBox2 = Empty TextBox3 = Empty TextBox1.SetFocus End Sub Private Sub TextBox1_Change() Range("A9").Select ActiveCell.FormulaR1C1 = TextBox1 End Sub [31] Private Sub TextBox2_Change() Range("B9").Select ActiveCell.FormulaR1C1 = TextBox2 End Sub Private Sub TextBox3_Change() Range("C9").Select ActiveCell.FormulaR1C1 = TextBox3 End Sub Private Sub TextBox4_Change() Range("D9").Select ActiveCell.FormulaR1C1 = TextBox4 Rem aquí se crea la formula TextBox5 = Val(TextBox2) * Val(TextBox3) + Val(TextBox4) Rem El TextBox5 guardara el total End Sub Private Sub TextBox5_Change() Range("E9").Select ActiveCell.FormulaR1C1 = TextBox5 End Sub Cuando se introduzca el Bonos automáticamente se generara el Sueldo Neto. Buscando información Se puede buscar información con un Textbox programándolo de la siguiente forma: Private Sub TextBox1_Change() Range("a9").Select ActiveCell.FormulaR1C1 = TextBox1 End Sub Private Sub CommandButton1_Click() Cells.Find(What:=TextBox1, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate End Sub