Diapositivas Vba Para Excel 3i2p50

Excel VBA con New Horizons Madrid

Javier Madrigal Rodríguez M ID 3671858 – MCT desde 2006 MCITP Sharepoint 2007 y 2010 Exchange 2010 MCTS Windows 7, Configuration MCAD en Microsoft .NET N ID 10033139 – CNI desde 2007 NCLA 10 Certified Novell Engineer Certified Linux Professional

Excel VBA

Con Javier Madrigal Rodríguez

Temario • 1.- Introducción • 2.- Lenguaje VBScript • 3.- Modelo de Objetos de Excel

1.- Introducción  1.1 Definición de VBA  1.2 Pestaña “Programador”  1.3 Grabadora de Macros  1.4 Entorno de Programación  1.5 Guardado de Libros Excel con Macros

1.1. Definición de VBA • VBA (Visual Basic for Applications) es el sistema de programación basado en lenguaje Visual Basic Script, que junto a un modelo de objetos (en este curso Excel) nos permite ampliar la capacidad y automatización de dicho programa. • VBA = Visual Basic Script (vbscript) + Modelo de Objetos • VBA para Excel = vbscript + Modelo de Objetos de Excel

1.2 Pestaña “Programador” • La pestaña “Programador” agrupa todos los elementos necesarios para programar Macros y demás elementos VBA. • Por defecto no aparece, hay que configurar que se vea para poder utilizarla. • En el menú “Archivo” tenemos la configuración de “Cinta de Opciones”.

1.2 Pestaña “Programador”

1.2 Pestaña “Programador”

1.2 Pestaña “Programador”

1. Permite grabar nuevas macros de forma automática. 2. Permite ver, ejecutar y configurar macros ya creadas. 3. Abre el entorno de programación 4. (Opcional) Activa las referencias relativas a la hora de grabar macros ANTES de utilizar la grabadora.

1.3 Grabadora de Macros • Se utiliza para ejecutar acciones mientras el propio Excel, crea la Macro de forma automática. • Se enciende con el botón de la pestaña “Programador”. • Se apaga con el icono que aparece en puesto del anterior cuando ya está grabando.

1.3 Grabadora de Macros

1.3 Grabadora de Macros Demostración: Grabar una macro que cambia el formato a ciertas celdas, incluido el contenido de las mismas. Ejecución de la macro en distintas hojas

1.4 Entorno de Programación Explorador de proyectos

Título de la Ventana De código Ventana de Código

Ventana Propiedades

1.4 Entorno de Programación

Examinador de Objetos

Ejecutar, pausar y parar la macro activa

Cambiar a Excel Macro Activa (tiene el cursor)

1.4 Entorno de Programación Demostración: Utilizar el entorno de programación para manejar módulos y objetos de Excel. Manejar macros y ejecutarlas y pararlas. Grabar un libro Excel con soporte para Macros.

1.5 Guardado de Libros Excel con Macros Los Libros Excel que tienen macros, además tienen que guardarse de una forma especial: como “LIBRO HABILITADO PARA MACROS”, con la extensión .xlsm. Esto hace que estos libros nos avisen cuando se abren, que tienen macros y por seguridad nos pregunta si habilitarlas o no. También hay opciones de seguridad para definir “Ubicaciones de Confianza” para que no pregunte con los libros que están ahí guardados. Además el libro Excel que contiene macros tiene un icono distinto al de los libros Excel sin macros.

1.5 Guardado de Libros Excel con Macros Demostración: Guardar un libro Excel habilitado para macros y cambiar las opciones de seguridad para que no pregunte si habilitarlas para los libros en ubicaciones de confianza.

2.- Lenguaje VBScript  2.1 Consideraciones Generales  2.2 Funciones Interactivas (msgbox e inputbox)  2.3 Variables, Constantes y Operadores  2.4 Funciones de Conversión y otras  2.5 Condicionales (if y select case)  2.6 Bucles (for y while)  2.7 Control de Errores

2.1 Consideraciones Generales El lenguaje VBScript es un lenguaje que tiene las siguientes características generales: • • • •

No diferencia mayúsculas y minúsculas salvo en comparación de literales. No tiene en cuenta las líneas en blanco. Se pueden comentar líneas con el carácter ‘ (apóstrofe) Cuando utilizamos funciones sin recoger el resultado NO se deben utilizar paréntesis; cuando sí recogemos el resultado SI se deben utilizar.

2.2 Funciones Interactivas Una función interactiva es la que nos permite interactuar con el del libro Excel. Las dos funciones interactivas más características son las correspondientes a la entrada/salida de datos. MSGBOX -> Nos permite mostrar información al del libro. INPUTBOX -> Nos permite preguntar un dato al del libro y obtener una respuesta. Por Ejemplo: Msgbox (“hola ”) Inputbox(“dime tu edad:”)

2.2 Funciones Interactivas MSGBOX MsgBox mensaje, [botones+aspecto (vbmsgboxstyle)], [Titulo] Msgbox “Hola que tal” -> Muestra un mensaje simple

Msgbox “Hola que tal”, vbOkOnly+vbInformation, “Saludo”

Msgbox “¿Eres de Madrid?”, vbYesNo+vbQuestion, “Pregunta”

2.2 Funciones Interactivas MSGBOX Msgbox “Error grave”, vbOkOnly+vbCritical, “error”

Cuando mostramos más de un botón y queremos recoger la respuesta del , todos los datos deben ir entre paréntesis y recoger el resultado en una variable. Ej. Resultado=Msgbox (“¿Eres de Madrid?”,vbYesNo+vbQuestion,”Pregunta”)

2.2 Funciones Interactivas INPUTBOX Variable=Inputbox(mensaje, [Titulo], [Valor por defecto],[Coord. Izq.],[Coord. Sup])

Edad=inputbox(“dime tu edad: “,”Pregunta”,18)

2.2 Funciones Interactivas Demostración: Utilización de función MSGBOX e INPUTBOX en una macro de Excel

2.3 Variables, Constantes y Operadores DEFINICIONES Variables: Las variables son zonas de memoria con un nombre que nos inventamos, que almacenan información. Como su nombre indica, a la misma zona de memoria le podemos “variar” el dato que almacena. Constantes: Las constantes son lo mismo que las variables pero una vez que se les ha dado el primer dato a guardar, ya no se puede modificar; tienen un valor “constante”. Operadores: Los operadores nos permiten establecer distintos tipos de comparaciones entre 2 ó más expresiones o variables.

2.3 Variables, Constantes y Operadores VARIABLES Sintaxis: Dim <nombre> AS Los nombres de las variables tienen que empezar por una letra y pueden contener letras, números y guión bajo hasta un máximo de 255 caracteres. Además debe ser única en el ámbito que se declara. Los tipos indican qué tipo de dato van a guardar y el alcance (cómo de grande es) – Ver tabla en siguiente diapositiva. Si no se define tipo, se asume tipo “Variant” que implica que puede guardar cualquier cosa pero gasta más memoria. Por ejemplo: Dim nombre AS string Dim edad AS Byte Dim salario as Double

2.3 Variables, Constantes y Operadores VARIABLES

2.3 Variables, Constantes y Operadores CONSTANTES Existen dos tipos de constantes: implícitas y explícitas. Las implícitas son las que ya existen en el propio lenguaje y las explícitas las que nosotros podemos definir. Por ejemplo, constantes implícitas son las que utilizamos con msgbox (vbOkOnly, vbCritical, etc…). Las constantes explícitas las definimos nosotros con la sintaxis: Const <nombre> = Por ejemplo: Const PI=3.14

2.3 Variables, Constantes y Operadores OPERADORES Existen de distintos tipos. Ver tablas anexas.

2.3 Variables, Constantes y Operadores Demostración Definición de variables, constantes y su uso con distintos tipos de operadores. - Operadores aritméticos - Operadores de comparación - Operador para concatenar cadenas de texto

2.4 Funciones de Conversión y otras Una función es un elemento implícito del lenguaje que a través de un nombre, es capaz de coger unos datos, operar con ellos y devolver un resultado. Por ejemplo, si existiera, la función suma podría sumar números: suma(4,6) El resultado sería 10 Entre todas las que existen implícitas con el lenguaje VBScript, empezamos por las de Conversión (aplicable a variables con operadores) y luego haremos una clasificación de otras. Para poder ver un listado completo y obtener ayuda, podemos utilizar el “Examinador de Objetos”

2.4 Funciones de Conversión y otras Funciones de Conversión Nos permiten convertir el tipo de dato de una variable para poder utilizar los operadores que nos interesen. Aunque existen conversiones implícitas (automáticas) lo más correcto es utilizarlas nosotros según necesitemos en la programación.(ver tabla en la siguiente diapositiva) cInt – Convierte a tipo Integer

Ej. Cint(34.28) devuelve 34 Ej. Cint(“81”) devuelve 81

cStr – Convierte a tipo String

Ej. Cstr(54) devuelve “54”

cDbl – Convierte a tipo Double

Ej. Cdbl(“62”) devuelve 62.00 Ej. Cdbl(25) devuelve 25.00

2.4 Funciones de Conversión y otras Funciones de Conversión (consultar el “Examinador de Objetos”)

2.4 Funciones de Conversión y otras Funciones de Fecha / Hora

2.4 Funciones de Conversión y otras Funciones Matemáticas

2.4 Funciones de Conversión y otras Funciones de Cadena (de texto)

2.4 Funciones de Conversión y otras Funciones de Identificación

2.4 Funciones de Conversión y otras Demostración Utilización de funciones de conversión y otras en macros VBA de Excel.

2.5 Condicionales (if y select case) Las estructuras condicionales sirven para tomar decisiones en tiempo de ejecución. Utilizadas junto a los operadores de comparación, nos permiten ejecutar un código u otro en función de una condición. Existen dos estructuras condicionales que podemos utilizar: “IF” y “SELECT CASE”. Aunque las dos servirían para cualquier situación, se suele utilizar “IF” cuando tenemos condiciones sencillas o evaluamos distintos aspectos como condición y “SELECT CASE” cuando queremos hacer varias comparaciones sobre el mismo aspecto.

2.5 Condicionales (if y select case) SINTAXIS DE “IF” If then [código a ejecutar si se cumple la condición] Else [código a ejecutar si no se cumple la condición] End if Además los “condicionales” se pueden anidar, es decir, utilizar unos dentro de otros. Por ejemplo: If color=“rojo” then msgbox “el color es rojo” Else if color=“azul” then msgbox “el color es azul” else msgbox “no es ni rojo ni azul” end if End if

2.5 Condicionales (if y select case) SINTAXIS DE “SELECT CASE” Select Case ( ) case : [código a ejecutar] case : [código a ejecutar] case else: [código a ejecutar si no se cumple ningún caso anterior] End Select

Por ejemplo: Select Case (color) case “rojo”: msgbox “es rojo” case “azul”: msgbox “es azul” case else: msgbox “no es ni rojo ni azul” End Select

2.5 Condicionales (if y select case) Demostración Utilización de funciones de estructuras condicionales if simple, if en bloque anidado y select case.

2.6 Bucles (for y while) Los bucles nos permiten ejecutar tareas de forma repetitiva. Por ejemplo, si quiero cambiar el borde de color a 300 celdas, en vez de ir una por una puedo hacer un bucle para que lo haga él en todas las celdas, de forma repetitiva. Los bucles pueden ser definidos (for) es decir, que se ejecutan un número de veces concreto o indefinidos (while) es decir, que repiten tareas “mientras” se cumpla una condición pero no sabemos si se va a repetir 2, 6 o 23 veces. Existen más tipos de bucles pero estos dos son los básicos y necesarios para entender este aspecto de la programación.

2.6 Bucles (for y while) Sintaxis de FOR For = TO [STEP ± ] …. Next

Por Ejemplo: for x=1 to 10 msgbox “hola” next x

‘ este bucle dice hola 10 veces

for x=4 to 22 step +2 msgbox “hola” next x

‘ este bucle dice hola 10 veces

for x=10 to 1 step -1 msgbox “hola” next x

‘ este bucle dice hola 10 veces

2.6 Bucles (for y while) Sintaxis de WHILE While ( ) …. Wend

Por Ejemplo: dim color as string color=inputbox(“dime un color”) while (color<>”Rojo”) msgbox “el color no es rojo” color=inputbox(“dime un color”) wend NOTA: El dato que sirva de condición, debe modificarse dentro del bucle, si no sería un bucle infinito. Si eso pasara, la forma de cortar el bucle infinito es CTRL + Pause

2.6 Bucles (for y while) Demostración Ejemplos con bucles for y while para repeticiones con un número definido de veces o basado en condición.

2.7 Control de Errores Cuando la ejecución de una macro produce algún error, muestra un mensaje de error en pantalla que el del libro Excel quizá no sepa manejar. Para evitar esa situación, podemos hacer control de errores con la estructura: ON ERROR GOTO <ETIQUETA> que nos llevará a la etiqueta definida, donde podremos tomar decisiones de si continuar con la macro a pesar del fallo o terminar la ejecución de la misma. Para acotar el bloque de control de errores utilizaremos la expresión “Exit Sub” al final de la macro, antes del bloque de errores. Para indicar que vuelva a intentar ejecutar la línea que ha dado error porque hayamos arreglado el error utilizaremos “resume” y si queremos saltarnos la línea de error pero continuar “resume next”. Podemos acceder al error con err.number y err.description

2.7 Control de Errores Ejemplo: Sub macro1() on error goto mis_errores dim numero as integer numero=inputbox(“dime un numero:”) msgbox numero+45 exit sub mis_errores: if err.number= then resume next else exit sub end if End sub

2.7 Control de Errores Demostración Macro con control de errores

3.- Modelo de Objetos de Excel  3.1 Introducción  3.2 Manejo de Celdas  3.3 Manejo de Hojas  3.4 Manejo de Libros

3.1 Introducción El modelo de objetos de Excel es muy amplio con más de 200 objetos distintos para controlar todos los aspectos de la aplicación. El manejo de objetos cotidiamos atiendo a este esquema: Application Workbooks workbook worksheets worksheet cells range Cada objeto además define un tipo de variable por si queremos guardar un objeto en una variable. Utilizaremos la palabra clave SET. Dim mihoja as worksheet Set mihoja=worksheets(1)

3.1 Introducción Los objetos que acaban en s (están en plural) son COLECCIONES. Una colección es un array de objetos del mismo tipo, es decir, que en la misma variable puedo almacenar más de un objeto. worksheets 1

Podemos acceder a los objetos por índice o por nombre del objeto.

2

Las colecciones propias de Excel se generan automaticamente, nosotros sólo las utilizamos

3

Worksheets(3)

4

3.1 Introducción Para utilizar los objetos utilizamos sus propiedades y métos. Una propiedad nos permite acceder o cambiar un dato del objeto. Por ejemplo, de una celda, tenemos las propiedades de borde, tipo de letra, tamaño, color, grosor de borde, etc… Un Método nos permite cambiar un aspecto del objeto. Por ejemplo sobre una hoja de Excel, nos permite copiar la hoja, moverla, borrarla, etc… Para ver todas las propiedades y métodos de un objeto, podemos utilizar el “Examinador de Objetos”.

3.2 Manejo de Celdas Para acceder a las celdas, utilizamos indistintamente el objeto Cells o el objeto Range. La única diferencia es la forma de referenciar la celda. Range(“B4”)  Celda B4 Cells(4,2)  Celda B4 (fila 4, columna 2) A través de sus propiedades y métodos, accedemos a sus elementos. Por ejemplo: Range(“C2”).FormulaR1C1=“hola”  Escribe dentro de la celda Range(“D4”).Clear  Limpia el contenido de la celda

3.2 Manejo de Celdas Demostración - Ejemplos de manejo de propiedades y métodos de las celdas. - Integración del modelo de objetos (celdas) con el lenguaje VBScript.

3.3 Manejo de Hojas Para acceder a las hojas utilizamos la colección Worksheets. Podemos acceder directamente a la hoja por posición o nombre, o también podemos “extraer” una hoja en una variable independiente de tipo Worksheet. Worksheets(2).Name=“mi hoja” Worksheets(“Hoja3”).Name=“mihoja3” Msgbox Worksheets(1).Name Dim lahoja as worksheet Set lahoja=worksheets(2) Msgbox lahoja.name

3.3 Manejo de Hojas Demostración - Ejemplos de manejo de propiedades y métodos de las hojas. - Integración del modelo de objetos (hojas) con el lenguaje VBScript.

3.4 Manejo de Libros Para acceder a los libros utilizamos la colección Workbooks. Podemos acceder directamente al libro por posición o nombre, o también podemos “extraer” un libro en una variable independiente de tipo Workbook. Workbooks(2).Name=“mi libro.xlsx” Workbooks(“Libro1”).Name=“milibro2” Msgbox workbooks(1).Name Dim ellibro as workbook Set ellibro=workbooks(2) Msgbox ellibro.name

3.4 Manejo de Libros Demostración - Ejemplos de manejo de propiedades y métodos de los libros. - Integración del modelo de objetos (libros) con el lenguaje VBScript.