65
CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL OCTUBRE - 2010 920-100920-INICIACION_VBA_EXCEL-ED1.DOC 1 HOJA DE CONTROL DE CALIDAD DOCUMENTO CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL OCTUBRE 2010 CÓDIGO 920-100920-INICIACION_VBA_EXCEL-ED1.DOC FIRMA GAS (ST) AUTORES FECHA 07/09/2010

Iniciacion VBA Excel Ed1

  • Upload
    marc-fc

  • View
    28

  • Download
    6

Embed Size (px)

DESCRIPTION

VBA Excel in spanish

Citation preview

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

1

HOJA DE CONTROL DE CALIDAD

DOCUMENTO CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL OCTUBRE 2010

CÓDIGO 920-100920-INICIACION_VBA_EXCEL-ED1.DOC

FIRMA GAS (ST) AUTORES

FECHA 07/09/2010

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

2

ÍNDICE

1. CONTROLES DE FORMULARIOS.................................................................................................................................. 5 1.1. INTRODUCCIÓN ................................................................................................................................................... 5 1.2. ETIQUETA.............................................................................................................................................................. 6 1.3. CUADRO DE EDICIÓN ......................................................................................................................................... 6 1.4. CUADRO DE GRUPO............................................................................................................................................ 6 1.5. BOTÓN.................................................................................................................................................................... 6 1.6. CASILLA DE VERIFICACIÓN.............................................................................................................................. 6 1.7. BOTÓN DE OPCIÓN.............................................................................................................................................. 7 1.8. CUADRO DE LISTA .............................................................................................................................................. 7 1.9. CUADRO COMBINADO ....................................................................................................................................... 8 1.10. CUADRO COMBINADO DE LISTA..................................................................................................................... 9 1.11. CUADRO COMBINADO DESPLEGABLE........................................................................................................... 9 1.12. BARRA DE DESPLAZAMIENTO......................................................................................................................... 9 1.13. CONTROL DE GIRO............................................................................................................................................ 10

2. MACROS.......................................................................................................................................................................... 11 2.1. CONTROLES DE MACRO .................................................................................................................................. 11 2.2. GRABAR UNA MACRO...................................................................................................................................... 12 2.3. EJECUTAR UNA MACRO................................................................................................................................... 12 2.4. ASIGNAR UNA MACRO..................................................................................................................................... 13

2.4.1. Asignar una macro a un botón de la barra de herramientas .......................................................................... 13 2.4.2. Asignar una macro a un botón de control..................................................................................................... 16

3. EL EDITOR DE VBA....................................................................................................................................................... 18 3.1. ACTIVACIÓN DEL EDITOR DE VBA ............................................................................................................... 18 3.2. VENTANAS DEL EDITOR DE VBA .................................................................................................................. 18

3.2.1. Ventana del explorador de proyectos ........................................................................................................... 19 3.2.2. Ventana de código........................................................................................................................................ 19 3.2.3. Ventana inmediato ....................................................................................................................................... 19 3.2.4. Ventana locales ............................................................................................................................................ 19 3.2.5. Ventana inspecciones ................................................................................................................................... 19 3.2.6. Ventana de propiedades ............................................................................................................................... 20 3.2.7. Cuadro de lista de objetos ............................................................................................................................ 20 3.2.8. Cuadro de procedimientos............................................................................................................................ 20

3.3. TRABAJAR CON EL EXPLORADOR DE PROYECTOS.................................................................................. 20 3.3.1. Añadir un módulo VBA ............................................................................................................................... 20 3.3.2. Eliminar un módulo VBA ............................................................................................................................ 21 3.3.3. Importar y exportar objetos .......................................................................................................................... 21 3.3.4. Introducir código VBA................................................................................................................................. 22

4. PROGRAMACIÓN BÁSICA CON VBA ........................................................................................................................ 26

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

3

4.1. VARIABLES, TIPOS DE DATOS Y CONSTANTES ......................................................................................... 26

4.1.1. Definir tipos de datos ................................................................................................................................... 26 4.1.2. Ámbito de las variables ................................................................................................................................ 27 4.1.3. Variables locales .......................................................................................................................................... 27 4.1.4. Variables para todo el módulo...................................................................................................................... 28 4.1.5. Variables públicas ........................................................................................................................................ 28 4.1.6. Variables estáticas ........................................................................................................................................ 28 4.1.7. Trabajar con constantes ................................................................................................................................ 28 4.1.8. Trabajar con cadenas.................................................................................................................................... 29 4.1.9. Trabajar con fechas ...................................................................................................................................... 29 4.1.10. Instrucciones de asignación........................................................................................................................ 30

4.2. MATRICES ........................................................................................................................................................... 30 4.2.1. Declarar matrices ......................................................................................................................................... 30 4.2.2. Declarar matrices con varias dimensiones.................................................................................................... 31

4.3. OBJETOS Y COLECCIONES .............................................................................................................................. 31 4.3.1. La jerarquía de objetos ................................................................................................................................. 32 4.3.2. Colecciones .................................................................................................................................................. 32 4.3.3. Hacer referencia a objetos ............................................................................................................................ 32

4.4. TRABAJAR CON CELDAS ................................................................................................................................. 34 4.4.1. Seleccionar libros, hojas y celdas................................................................................................................. 34 4.4.2. Propiedad Range .......................................................................................................................................... 34 4.4.3. Propiedad Cells ............................................................................................................................................ 35 4.4.4. Propiedad Offset........................................................................................................................................... 36

4.5. FUNCIONES INTEGRADAS............................................................................................................................... 36 4.6. MANIPULAR OBJETOS Y COLECCIONES...................................................................................................... 37

4.6.1. Construcciones With – End With ................................................................................................................. 37 4.6.2. Construcciones For Each – Next .................................................................................................................. 37

4.7. CONTROL DE LA EJECUCIÓN.......................................................................................................................... 38 4.7.1. Instrucciones GoTo ...................................................................................................................................... 38 4.7.2. Construcciones If – Then ............................................................................................................................. 39 4.7.3. Construcciones Select Case.......................................................................................................................... 40 4.7.4. Bucles For – Next......................................................................................................................................... 41 4.7.5. Bucles Do While .......................................................................................................................................... 42 4.7.6. Bucles Do Until............................................................................................................................................ 43

5. FUNCIONES .................................................................................................................................................................... 44 5.1. PROCEDIMIENTOS DE FUNCIÓN .................................................................................................................... 44

5.1.1. Declarar una función .................................................................................................................................... 44 5.1.2. Ámbito de Function...................................................................................................................................... 45 5.1.3. Ejecutar procedimientos Function................................................................................................................ 45

5.2. ARGUMENTOS DE FUNCIÓN ........................................................................................................................... 45 6. TABLAS DINÁMICAS.................................................................................................................................................... 47

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

4

6.1. TABLAS DINÁMICAS EN EXCEL..................................................................................................................... 47

6.1.1. Crear una tabla dinámica.............................................................................................................................. 47 6.1.2. Aplicar filtros a la tabla................................................................................................................................ 50 6.1.3. Obtener promedios ....................................................................................................................................... 51 6.1.4. Tablas dinámicas en 3D ............................................................................................................................... 51 6.1.5. Actualizar datos y subtablas ......................................................................................................................... 52 6.1.6. Gráficos de una tabla dinámica .................................................................................................................... 52 6.1.7. Eliminar una tabla dinámica......................................................................................................................... 53

6.2. TABLAS DINÁMICAS CON VBA...................................................................................................................... 53 6.2.1. Crear una tabla dinámica.............................................................................................................................. 53

7. FORMULARIOS INTEGRADOS.................................................................................................................................... 56 7.1. CUADROS DE DIÁLOGO INTEGRADOS ......................................................................................................... 56 7.2. FUNCIÓN INPUTBOX DE VBA ......................................................................................................................... 56 7.3. FUNCIÓN INPUT BOX DE EXCEL.................................................................................................................... 57 7.4. FUNCIÓN MSGBOX DE VBA ............................................................................................................................ 58

8. GRÁFICOS....................................................................................................................................................................... 61 8.1. PARÁMETROS BÁSICOS ................................................................................................................................... 61 8.2. CREAR UNA HOJA DE GRÁFICO..................................................................................................................... 61 8.3. INCRUSTAR UN GRÁFICO EN UNA HOJA ..................................................................................................... 62 8.4. APLICAR LAS OPCIONES DEL ASISTENTE PARA GRÁFICOS EN UN GRÁFICO.................................... 63 8.5. AÑADIR UNA NUEVA SERIE DE DATOS A UN GRÁFICO .......................................................................... 64

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

5

1. CONTROLES DE FORMULARIOS

1.1. INTRODUCCIÓN

Como primera herramienta para automatizar y simplificar los trabajos se dispone de una serie de controles personalizables asociados a celdas en los que no suele hacer falta introducir código VBA para su uso.

Existen dos tipos diferentes de conjuntos de controles:

Controles de formulario

Controles ActiveX

Los controles de formulario son únicos para Excel mientras que los controles ActiveX son propios de formularios de Visual Basic.

Su localización se encuentra en la Barra de Herramientas llamada “Formularios”.

Figura 1.1. Barra de herramientas “Formularios”.

Para situarlos en la hoja de cálculo se debe hacer clic sobre uno de ellos y a continuación dibujarlo en la hoja de cálculo como si fuera una forma predeterminada del menú “Dibujo”.

Para establecer las propiedades para un control existente, se debe hacer clic con el botón secundario en el control, haciendo clic en Formato de control y eligiendo la ficha Control.

Los rótulos y los botones no tienen propiedades.

Los controles que se analizan son los siguientes:

Etiqueta

Cuadro de edición

Cuadro de grupo

Botón

Casilla de verificación

Botón de opción

Cuadro de lista

Cuadro combinado

Cuadro combinado de lista

Cuadro combinado desplegable

Barra de desplazamiento

Control de giro

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

6

1.2. ETIQUETA

Es un texto que proporciona información sobre un control, de la hoja de cálculo o de un formulario.

1.3. CUADRO DE EDICIÓN

No está disponible en libros de Microsoft Excel. Este control se proporciona para que se pueda trabajar con las hojas de diálogo de Excel versión 5.0.

1.4. CUADRO DE GRUPO

Agrupa los controles relacionados, como los botones de opción o las casillas de verificación. Es necesario para poder activar los botones de opción o las casillas de verificación.

1.5. BOTÓN

Ejecuta una macro cuando se hace clic en él.

1.6. CASILLA DE VERIFICACIÓN

Activa o desactiva una opción. Puede activarse más de una casilla de verificación a la vez en una hoja o en un grupo.

Propiedades principales de la casilla de verificación:

Valor. Determina el estado de la casilla de verificación; es decir; si está activada (Activado), desactivada (Sin activar) o ninguna de las dos (Mixto).

Vincular con la celda. Una celda que devuelve el estado de la casilla de verificación. Si la casilla de verificación está activada, la celda del cuadro Vincular con la celda contiene VERDADERO. Si la casilla de verificación está desactivada, la celda contiene FALSO. Si la casilla de verificación es mixta, la celda contiene #N/A. Si la celda está vacía, Excel interpreta que el estado de la casilla de verificación es FALSO.

Figura 1.2. Formato de control de la casilla de verificación.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

7

1.7. BOTÓN DE OPCIÓN

Selecciona una de las opciones de un grupo contenido en un cuadro de grupo. Permite escoger una sola de todas las posibilidades incluidas en el cuadro de grupo.

Propiedades principales del botón de opción:

Valor. Determina el estado inicial del botón de opción; es decir; si está activado (Activado), desactivado (Sin activar).

Vincular con la celda. Devuelve el número del botón de opción seleccionado en el grupo de opciones (el primer botón de opción es el número 1). Se debe de utilizar la misma celda Vincular con la celda para todas las opciones de un grupo. Después se podrá utilizar el número devuelto en una fórmula o macro para responder a la opción seleccionada.

Figura 1.3. Formato de control del botón de opción.

1.8. CUADRO DE LISTA

Muestra una lista de elementos.

Propiedades principales del cuadro de lista:

Rango de entrada. Hace referencia al rango que contiene los valores que se mostrarán en el cuadro de lista.

Vincular con la celda. Devuelve el número del elemento seleccionado en el cuadro de lista (el primer elemento de la lista es 1). Este número puede utilizarse en una fórmula o macro para que devuelva el elemento real del rango de entrada.

Tipo de selección. Especifica el modo en que pueden seleccionarse los elementos de la lista. Si se define el tipo de selección como Múltiple o Extendida, la celda especificada en el cuadro Vincular con la celda no se tendrá en cuenta. La selección múltiple o extendida no se puede vincular con una celda, por lo que deberá usarse código VBA para poder usar la opción.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

8

Figura 1.4. Formato de control del cuadro de lista.

1.9. CUADRO COMBINADO

Es un cuadro con una lista desplegable. El elemento que se seleccione en el cuadro de lista aparecerá en el cuadro de texto.

Propiedades principales del cuadro combinado:

Rango de entrada. Hace referencia al rango que contiene los valores que se mostrarán en la lista desplegable.

Vincular con la celda. Devuelve el número del elemento seleccionado en el cuadro combinado (el primer elemento de la lista es 1). Este número puede utilizarse en una fórmula o macro para que devuelva el elemento real del rango de entrada.

Líneas de unión verticales. Especifica el número de líneas que aparecerá en la lista desplegable.

Figura 1.5. Formato de control del cuadro combinado.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

9

1.10. CUADRO COMBINADO DE LISTA

No disponible en libros de Microsoft Excel. Este control se proporciona para que pueda trabajar con las hojas de diálogo de Excel versión 5.0.

1.11. CUADRO COMBINADO DESPLEGABLE

No disponible en libros de Microsoft Excel. Este control se proporciona para que pueda trabajar con las hojas de diálogo de Excel versión 5.0.

1.12. BARRA DE DESPLAZAMIENTO

Sirve para desplazarse a través de un rango de valores cuando se hace clic en las flechas de desplazamiento o cuando se arrastra el cuadro de desplazamiento. Puede moverse a través de una página de valores haciendo clic entre el cuadro de desplazamiento y una flecha de desplazamiento.

Propiedades principales de la barra de desplazamiento:

Valor actual. La posición relativa del cuadro de desplazamiento dentro de la barra de desplazamiento.

Valor mínimo. La posición del cuadro de desplazamiento más próxima a la parte superior de una barra de desplazamiento vertical o al extremo izquierdo de una barra de desplazamiento horizontal.

Valor máximo. La posición del cuadro de desplazamiento más lejana a la parte superior de una barra de desplazamiento vertical o al extremo derecho de una barra de desplazamiento horizontal.

Incremento. El tamaño del espacio que se desplazará el cuadro de desplazamiento cuando se haga clic en la flecha situada en cualquier extremo de la barra de desplazamiento.

Cambio de página. El tamaño del espacio que se desplazará el cuadro de desplazamiento cuando se haga clic entre el cuadro de desplazamiento y una de las flechas de desplazamiento.

Vincular con la celda. Devuelve la posición actual del cuadro de desplazamiento. Este número puede utilizarse en una fórmula o macro para responder a la posición del cuadro de desplazamiento.

Figura 1.6. Formato de control de la barra de desplazamiento.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

10

1.13. CONTROL DE GIRO

Aumenta o disminuye un valor. Para aumentar el valor, haga clic en la flecha arriba; para disminuir el valor, haga clic en la flecha abajo.

Propiedades principales del control de giro:

Valor actual. La posición relativa del control de giro dentro del rango de valores permitidos.

Valor mínimo. El valor más bajo permitido para el control giro.

Valor máximo. El valor más alto permitido para el control giro.

Incremento. La cantidad de aumento o disminución del control de giro cuando se hace clic en las flechas.

Vincular con la celda. Devuelve la posición actual del control de giro. Este número puede utilizarse en una fórmula o macro para que devuelva el valor real que el control de giro debe seleccionar.

Este control es análogo a la barra de desplazamiento sin la opción de cambio de página.

Figura 1.7. Formato de control del control de giro.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

11

2. MACROS

Una macro es una secuencia de instrucciones que automatizan algunos aspectos de Excel de forma que podamos trabajar de forma más eficiente y reduciendo la posibilidad de errores.

No es necesario ser un usuario experto para crear y usar Macros de VBA simples. Los usuarios casuales pueden poner en marcha la grabadora de macros y Excel grabará todas las acciones y las convertirá en código VBA. Cuando se ejecute la macro, Excel ejecutará las acciones otra vez.

2.1. CONTROLES DE MACRO

Los controles para la grabación y ejecución de las macros se encuentran en los siguientes sitios:

Menú Herramientas / Macros

Barra de Herramientas Visual Basic

Figura 2.1. Posición botones macros

Los botones que podemos encontrar son:

Macros: Permite ejecutar una macro.

Grabar una macro: Permite grabar una macro.

Seguridad: Permite adaptar la seguridad de Excel frente a las macros.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

12

Figura 2.2. Niveles de seguridad.

2.2. GRABAR UNA MACRO

Para grabar una macro se pulsa el botón “Grabar macro” y se abrirá la ventana de la Figura 2.3.

Figura 2.3. Ventana Botón “Grabar”

Una vez introducido el nombre de la macro se apreta el botón “Aceptar” y se empiezan a grabar las acciones hasta que se apreta el botón “Parar” de la ventana de la Figura 2.4.

Figura 2.4. Ventana para detener la grabación de la macro.

2.3. EJECUTAR UNA MACRO

Al accionar el botón “Macro” se abre la ventana de la Figura 2.5. y en ella aparece un listado de las macros grabadas en el libro o cargadas al abrir Excel. Una vez seleccionada se puede ejecutar completa, paso a paso, modificarla mediante VBA, crear una macro nueva o eliminar una existente.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

13

Para crear una macro nueva se escribe el nombre y se pulsa “Crear”, que se habrá activado al iniciar a escribir el nombre. De esta forma se accede al Editor de VBA para introducir el código de la macro.

Figura 2.5. Ventana Botón “Macros...”

2.4. ASIGNAR UNA MACRO

Las macros se pueden asignar a diversos elementos, éstos son:

Botones de la barra de Herramientas

Botones de control

2.4.1. Asignar una macro a un botón de la barra de herramientas

Para asignar una macro a un botón de la barra de herramientas, en primer lugar se abre el menú de la propia barra apretando el botón derecho del ratón para ir al campo “Personalizar” (Figura 2.6.)

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

14

Figura 2.6. Menú desplegable de la barra de Herramientas.

Se abrirá la ventana “Personalizar” donde marcaremos el menú “Macros” y arrastraremos el icono “Personalizar botón” a la posición que deseemos de la barra de herramientas.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

15

Figura 2.7. Menú Personalizar.

Con la ventana “Personalizar” abierta se pulsa sobre el icono con el botón derecho del ratón y aparecerá el menú que nos permitirá asignar la macro, cambiar el nombre, la imagen, etc. del botón.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

16

Figura 2.8. Menú Personalizar propio del icono.

Se abrirá la ventana de la Figura 2.5. y podremos escoger el nombre de la macro que asignaremos al icono.

2.4.2. Asignar una macro a un botón de control

Al dibujar el botón de control en la hoja de cálculo aparecerá automáticamente el cuadro de la Figura 2.5. en el cual se podrá asignar la macro a ejecutar.

Si este cuadro no aparece, podremos activar el menú de la Figura 2.9 con el botón derecho del ratón y acceder al cuadro para asignar la macro.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

17

Figura 2.9. Menú Personalizar

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

18

3. EL EDITOR DE VBA

Todo el trabajo con VBA se realiza con el editor de Visual Basic (VBE). Es una aplicación separada que trabaja perfectamente integrada en Excel. Esto quiere decir que Excel puede abrir el editor de Visual Basic, pero no puede ejecutarse por separado.

Los módulos de VBA se almacenan junto a los archivos del libro, pero no son visibles a menos que se active el editor de Visual Basic.

3.1. ACTIVACIÓN DEL EDITOR DE VBA

Cuando se trabaja con Excel se puede activar el editor de Visual Basic de las siguientes formas:

Pulsando Alt-F11

Seleccionando Herramientas > Macro > Editor de Visual Basic

Haciendo clic en el botón del editor de Visual Basic que se encuentra en la barra de herramientas de Visual Basic.

3.2. VENTANAS DEL EDITOR DE VBA

El editor de Visual Basic consta de diferentes partes. Las más importantes son las siguientes:

Ventana del explorador de proyectos.

Ventana de código.

Ventana inmediato.

Ventana locales.

Ventana inspecciones.

Ventana de propiedades.

Cuadro de lista de objetos

Cuadro de procedimientos.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

19

Figura 3.1. Ventana del editor de Visual Basic.

3.2.1. Ventana del explorador de proyectos

Muestra la lista de los proyectos abiertos y sus correspondientes módulos, objetos y formularios, usando iconos para representar cada tipo.

Utiliza la estructura tipo árbol del Explorador de Windows. El Editor de Visual Basic se refiere a cada entrada de la ventana como un nodo. Los nodos más arriba, indicados en negrita, representan los proyectos de VBA Excel actualmente abiertos.

Excel abre un nuevo proyecto VBA para cada libro que se abre en Excel.

3.2.2. Ventana de código

Muestra el código fuente de VBA del módulo seleccionado.

3.2.3. Ventana inmediato

Proporciona resultados inmediatos para instrucciones tecleadas directamente en la ventana.

3.2.4. Ventana locales

Muestra los valores de las variables locales durante la depuración del código.

3.2.5. Ventana inspecciones

Muestra las alertas producidas durante la depuración del código.

Explorador de proyectos

Ventana de propiedades

Ventana de código

Ventana immediato

Ventana inspecciones

Ventana locales

Lista de objetos

Lista de propiedades

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

20

3.2.6. Ventana de propiedades

Muestra las propiedades del objeto actualmente seleccionado.

Si se selecciona un módulo en el Explorador de Proyectos, la única propiedad que se puede visualizar es su nombre. Sin embargo, si se selecciona una hoja en concreto se puede ver y modificar sus propiedades.

Para cambiar las propiedades de un objeto basta con simplemente seleccionar su propiedad y realizar el cambio.

3.2.7. Cuadro de lista de objetos

Muestra una lista de objetos asociados al presente proyecto.

Esta carpeta contiene un nodo por cada hoja dentro del libro seleccionado. Cada nodo representa una hoja de cálculo o una hoja de gráfico.

Cuando se hace clic dos veces en un nodo en particular, se abre el correspondiente código del módulo.

Se pueden colocar procedimientos independientes dentro de una específica hoja de módulo, pero normalmente estos procedimientos se sitúan en los módulos estándar.

3.2.8. Cuadro de procedimientos

Muestra la lista de procedimientos asociados con el objeto seleccionado.

3.3. TRABAJAR CON EL EXPLORADOR DE PROYECTOS

Cuando se está trabajando con el editor de Visual Basic, cada libro de Excel y complemento que esté abierto en ese momento se considera un proyecto.

Cuando se activa el editor de Visual Basic, no se puede presuponer que el módulo del código que se está mostrando se corresponde con el objeto señalado en la ventana del explorador de proyectos. Para asegurarnos de que estamos trabajando con módulo de código correcto, se debe hacer siempre clic en el objeto en la ventana del explorador de proyectos.

3.3.1. Añadir un módulo VBA

Las subrutinas se pueden introducir de dos formas diferentes en un libro Excel, directamente en las hojas de cálculo o en módulos.

Las subrutinas introducidas en la hoja de cálculo se mantendrán en la hoja con el peligro de perderlas si eliminamos la hoja. Las subrutinas introducidas en módulos no se pierden al manipular las hojas.

Para crear un módulo se selecciona el nombre del proyecto en la ventana del explorador de proyectos y seleccionar Insertar > Módulo. También se puede hacer clic con el botón derecho del ratón en el nombre del proyecto y seleccionar Insertar > Módulo del menú emergente.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

21

Figura 3.2. Inserción de un nuevo módulo VBA.

Cuando se graba una macro, Excel automáticamente inserta un módulo VBA para que contenga el código grabado.

3.3.2. Eliminar un módulo VBA

Si se necesita eliminar un módulo VBA, se selecciona el nombre del módulo en la ventana del explorador de proyectos y seleccionar Archivo > Quitar módulo Nombre o se puede hacer clic con el botón derecho del ratón en el nombre del módulo y seleccionar Quitar Nombre en el menú emergente.

Figura 3.3. Eliminación de un módulo VBA.

3.3.3. Importar y exportar objetos

Exceptuando los que aparecen en el nodo Referencias, cada objeto de un proyecto puede guardarse en un archivo separado.

Guardar un objeto individual en un proyecto se realiza mediante Exportar.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

22

Para exportar un objeto, se selecciona de la ventana del explorador de proyectos y se escoge Archivo > Exportar archivo (o pulsando Control-E). Aparecerá un cuadro de diálogo que solicita un nombre de archivo. Si se exporta un UserForm, cualquier código asociado al mismo también se exportará.

Para importar un archivo a un proyecto, se selecciona el nombre del proyecto en la ventana del explorador de proyectos y se escoge Archivo > Importar archivo. Aparecerá un cuadro de diálogo que solicita un archivo. Sólo se puede importar un archivo que ha sido exportado utilizando Archivo > Exportar archivo.

Figura 3.4. Importar / Exportar proyectos.

Si se quiere copiar un módulo o un objeto UserForm a otro proyecto bastará con tener ambos abiertos y activado el explorador de proyectos y arrastrar el objeto de un proyecto a otro.

3.3.4. Introducir código VBA

Para introducir el código se puede optar por tres procedimientos:

Introducir el código manualmente.

Función de grabación de macros. Utilizar la función de grabación de macros de Excel para grabar acciones y convertirlas en código VBA.

Copiar y pegar. Copiar el código de otro módulo y pegarlo en el módulo en el que se está trabajando.

Para iniciar una Subrutina se debe escribir la instrucción Sub+espacio+nombre(). automáticamente Excel introduce la instrucción End Sub. Nuestro código se introducirá entre estas dos líneas.

Sub ShowValue()

Msgbox Worksheets(“Hoja1”).Range(“A1”).Value

End Sub

El método de escritura es el mismo que cualquier editor de textos salvo que a medida que se va introduciendo el código las instrucciones se visualizan en azul y los nombres, variables, etc. en negro.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

23

Una única instrucción puede ser todo lo larga que haga falta. Sin embargo, para facilitar su lectura, es aconsejable dividirla en dos o más líneas. Para ello se coloca un espacio seguido de un carácter de subrayado al final de la línea y después se pulsa Intro y se continúa la instrucción en la siguiente línea.

MsgBox “No se puede encontrar “ & UCase(SHORTCUTMENUFILE) _

& vbCrLf & vbCrLf & “El archivo debería encontrase en “ _

& ThisWorkbook.Path & vbCrLf & vbCrLf & _

“Puede que tenga a volver a instalar el programa”, vbCritical, APPNAME

Es posible personalizar la forma en que el Editor de VBA nos indica errores y cómo visualiza las instrucciones. Estas opciones se hallan en Herramientas > Opciones.

Figura 3.5. Opciones del Editor de VBA.

La comprobación de sintaxis automática determina si el editor de VBA hace aparecer o no un cuadro de diálogo cuando descubre un error de sintaxis mientras se introduce el código. Si no se activa se seguirán indicando los errores de sintaxis pero mostrándolos en un color diferente al del resto del código.

La opción Requerir declaración de variables insertará la siguiente instrucción al estar activada:

Option Explicit

Esta opción obliga a definir explícitamente cada variable que se utilice. Si no se realiza la declaración de variables, todas serán del tipo Variant, que si bien puede ser un tipo de variable flexible no es eficiente en términos de almacenamiento y rapidez.

Si la opción Lista de miembros automática está marcada, el editor de Visual Basic proporcionará ayuda mientras se está introduciendo código VBA. Estos elementos incluyen métodos y propiedades para los objetos introducidos.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

24

Figura 3.6. Ejemplo de lista de miembros automática.

Si la opción Información rápida automática está activada, el editor de Visual Basic mostrará mientras escribimos información sobre los argumentos disponibles para funciones, propiedades y métodos.

Figura 3.7. Ejemplo de información rápida automática.

Si la opción Sugerencias de datos automáticas está activada, el editor de Visual Basic mostrará el valor de la variable sobre la que está situado el cursor mientras se está depurando el código.

La opción Sangría automática determina si el editor de Visual Basic tabulará automáticamente cada nueva línea de código con la misma cantidad que la línea anterior. Las tabulaciones manuales deben realizarse mediante la tecla Tab, no debe usarse el espaciador.

La opción modificar texto mediante arrastrar y colocar permite copiar y mover texto arrastrándolo.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

25

Cuando la opción Separador de procedimientos está activada, se mostrarán barras de separación al final de cada procedimiento en la ventana de código. Se puede observar la barra en la Figura 3.7.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

26

4. PROGRAMACIÓN BÁSICA CON VBA

4.1. VARIABLES, TIPOS DE DATOS Y CONSTANTES

El principal propósito de VBA es manipular datos. Algunos datos se ubican en objetos, como rangos de hoja (valores introducidos en las celdas). Otros datos se guardan en las variables que se crean. Una variable es simplemente una posición de almacenamiento del ordenador con nombre.

Los valores se asignan a una variable mediante el signo “=”. Las reglas que tiene VBA para los nombres de las variables:

Se pueden usar caracteres del alfabeto, números y algunos caracteres de puntuación, pero el primer carácter debe ser una letra del alfabeto.

VBA no distingue mayúsculas de minúsculas. Se pueden mezclar en un mismo nombre.

No se pueden usar ni espacios ni puntos. Para hacer los nombres más legibles debe usarse el guión bajo “_”.

Los caracteres especiales usados en instrucciones como (#, $, %, &, ! ) no pueden incluirse en un nombre de variable.

Los nombres de variables pueden llegar hasta los 254 caracteres. Pero no se recomiendan nombres tan largos.

4.1.1. Definir tipos de datos

VBA puede automáticamente asignar un tipo de datos a las variables que no se declaran, pero este hecho hace que el cálculo se ralentice. Los tipos de datos integrados en VBA son:

Tipo de dato Bytes usados Matriz de valores

Byte 1 byte 0 a 255

Boolean 2 bytes Verdadero o Falso

Integer 2 bytes -32.768 a 32.768

Long 4 bytes -2.147.483.648 a 2.147.483.648

Single 4 bytes -3,402823E38 a -1,401298E-45 (para valores negativos); 1,401298E-45 a 3,402823E38 (para valores positivos)

Double 8 bytes -1,79769313486232E308 a -4,94065645841247E-324

(valores negativos); 4,94065645841247E-324 a 1,79769313486232E308 (valores positivos)

Currency 8 bytes -922.337.203.685.477,5808 a 922.337.203.685.477,5808

Decimal 14 bytes ± 79.228.162.514.264.337.593.543.950.335 sin punto

decimal; ± 7,9228162514264337593543950335 con 28 elementos a la derecha del punto decimal

Data 8 bytes Enero 1, 0100 a diciembre 31, 9999

Object 4 bytes Cualquier referencia a objetos

String (de longitud variable) 10 bytes + la longitud de la matriz. 0 a aproximadamente 2 billones

String (de longitud fija) La longitud de la matriz 1 a aproximadamente 65.400

Variant (con números) 16 bytes Cualquier valor numérico de hasta el rango de un tipo de datos doble

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

27

Tipo de dato Bytes usados Matriz de valores

Variant (con caracteres) 22 bytes + la longitud de la matriz 0 a aproximadamente 2 billones

Definido por el usuario Variable Varía según el elemento

Tabla 4.1 Tipos de datos.

Para los cálculos de hoja, Excel usa el tipo de datos Double que es una buena opción para procesar números en VBA cuando no se quiere perder precisión. Para cálculos de números enteros, podemos usar el tipo Integer si estamos seguros de que su valor no superará 32.767. En caso contrario usaremos el tipo Long. Cuando estemos manejando los números de una fila de una hoja de Excel, deberemos usar el tipo Long porque el número de filas de una hoja de trabajo supera el valor máximo admitido por los datos de tipo Integer.

4.1.2. Ámbito de las variables

El ámbito de una variable determina qué módulos y procedimientos pueden usar la variable. El ámbito de una variable puede ser alguno de los siguientes:

Ámbito Cómo se declara una variable con este ámbito

Un solo procedimiento Incluyendo una declaración Dim o Static dentro del procedimiento.

Un solo módulo Incluyendo una declaración Dim o Private antes del primer procedimiento de un módulo.

Todos los módulos Incluyendo una declaración Public antes del primer procedimiento de un módulo.

Tabla 4.2. Declaración de variables.

4.1.3. Variables locales

Una variable local es una variable declarada dentro de un procedimiento. Las variables locales sólo se pueden usar en los procedimientos en los que están declaradas. Cuando finaliza el procedimiento, la variable deja de existir y Excel libera la memoria que ocupaba.

La manera más común de declarar una variable local es insertar una instrucción Dim entre una instrucción Sub y una instrucción End Sub.

Sub Programa_1()

Dim x As Integer

Dim First As Long

Dim Second As Single

Dim Today As Date

Dim UserName As String * 20

‘ – [El código de procedimiento va aquí] –

End Sub

VBA no permite declarar un grupo de variables como de un tipo particular separando las variables con comas. Debe seguirse el ejemplo siguiente separando las declaraciones con comas:

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

28

Dim i As Integer, j As Integer, k As Integer

4.1.4. Variables para todo el módulo

A veces es necesario que una variable pueda usarse en todos los procedimientos de un módulo, en eses caso se declarará la variable antes del primer procedimiento del módulo (fuera de cualquier procedimiento o función).

Dim CurrentValue As Integer

Sub Macro_1()

‘ – [El código va aquí] –

End Sub

Sub Macro_2()

‘ – [El código va aquí] –

End Sub

4.1.5. Variables públicas

Para hacer que una variable se pueda usar en todos los módulos VBA de un proyecto, debemos declarar la variable en el nivel de módulo, usando la palabra Public en lugar de Dim.

Public CurrentValue As Integer

La palabra clave Public hace que la variable se pueda usar en cualquier procedimiento del proyecto, incluso en otros módulos dentro del proyecto. Se deberá insertar esta función antes del primer procedimiento de un módulo. Este tipo de instrucción también debe aparecer en el módulo estándar VBA, no en un código de módulo para una hoja o para un UserForm.

4.1.6. Variables estáticas

Las variables estáticas se declaran en el nivel del procedimiento y mantienen su valor cuando el procedimiento finaliza (a menos que el procedimiento se detenga debido a una instrucción End.

Las variables estáticas se declaran con la palabra clave Static.

Sub MiSub()

Static Contador As Integer

‘ – [El código va aquí] –

End Sub

4.1.7. Trabajar con constantes

A veces deberemos hacer referencia a un valor o cadena con nombre que no cambien nunca. Para esta situación se utilizarán constantes.

Las constantes se declaran con la instrucción Const.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

29

Const NumUno As Integer

Const Rate = .0725, Period = 12

Const NombreMacro As String = “Macros de cálculo”

Public Const AppName As String = “Aplicación de macros”

Al igual que las variables las constantes también tienen ámbito. Si queremos que una constante sólo se pueda usar dentro de un determinado proceso, deberemos declararla después de una instrucción Sub o Function para hacerla una constante local. Para que una constante pueda utilizarse en todos los procesos de un módulo, debemos declararla antes del primer procedimiento del módulo. Para que una constante pueda usarse en todos los módulos de un libro, debemos usar la palabra clave Public y declarar la constante antes del primer procedimiento del módulo.

Excel y VBA ofrecen muchas constantes predefinidas que se pueden usar sin declarar. Para conocer las variables se puede acceder a una lista de todas las constantes a través del examinador de objetos (F2).

4.1.8. Trabajar con cadenas

Como Excel, VBA puede manipular tanto números como texto (cadenas). Hay dos tipos de cadenas en VBA:

Cadenas de longitud fija: se declaran con un número específico de caracteres. El valor máximo es 65.535 caracteres.

Cadenas de longitud variable: pueden tener, en teoría, más de 2 mil millones de caracteres.

Cada carácter de una matriz necesita un byte para guardarlo y una pequeña cantidad extra de almacenamiento para el encabezado de cada cadena. Cuando se declara una variable de cadena mediante la instrucción Dim se puede especificar la longitud (una cadena de longitud fija) o podemos dejar que VBA lo controle de forma dinámica (una cadena de longitud variable).

Dim MyString As String * 50 (Longitud máxima de 50 caracteres)

Dim YourString As String (Longitud máxima no determinada)

4.1.9. Trabajar con fechas

Para poder trabajar con fechas de forma numérica hace falta almacenarla con el tipo de datos Date.

Una variable definida como fecha usa 8 bytes de almacenamiento y puede contener datos desde el 1 de enero del año 100 hasta el 31 de diciembre de 9.999. Sin embargo hay que tener en cuenta que el rango de fechas que manejar Excel es mucho más reducido, que empieza el 1 de enero de 1900.

La forma de expresar fechas es colocándolas entre almohadillas ( # ).

Dim Hoy As Date

Const FirstDay As Date = # 1/1/2001 #

Las constantes de datos siempre se definen usando el formato mes / día / año, incluso si nuestro equipo está configurado para mostrar las fechas en otro formato (por ejemplo día/mes/año).

Hay que tener en cuenta que Excel tiene un error de fecha, asume erróneamente que el año 1900 es bisiesto. VBA no tiene este error de fecha, por lo tanto el sistema de números de matriz de Excel no se corresponde exactamente con el sistema de números

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

30

de matriz de VBA.

4.1.10. Instrucciones de asignación

Una instrucción de asignación es una instrucción que realiza una evaluación matemática y asigna el resultado a una variable o a un objeto.

VBA usa el signo igual ( = ) como operador de asignación. Otros operadores matemáticos también se pueden utilizar en las operaciones:

Sumas ( + ).

Restas ( - ).

Multiplicaciones ( * ).

Divisiones ( / ).

Potencias ( ^ ).

Etc.

VBA también admite los operadores de comparación usados en fórmulas de Excel:

Igual ( = ).

Mayor que ( > ).

Menor que ( < ).

Mayor que o igual a ( >= ).

Menor que o igual a ( <= ).

No igual a ( <> ).

Además VBA ofrece operadores lógicos como pueden ser:

Operador Función

Not Realiza una negación lógica en una expresión.

And Realiza una conjunción lógica en dos expresiones.

Or Realiza una disfunción lógica en dos expresiones.

XoR Realiza una exclusión lógica en dos expresiones.

Eqv Realiza una equivalencia lógica en dos expresiones.

Imp Realiza una implicación lógica en dos expresiones.

4.2. MATRICES

Una matriz es un grupo de elementos del mismo tipo que tienen un nombre común. Hacemos referencia a un elemento específica de la matriz usando el nombre de la matriz y un número de índice.

4.2.1. Declarar matrices

Se declara una matriz con una instrucción Dim o Public, exactamente igual que a como se declara una variable normal.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

31

Existen dos formas de declarar matrices:

Indicando el número de elementos en la matriz. Dim Matrix_1( 100 ) As Integer

Indicando el rango de índices de la matriz Dim Martix_2(1 To 100) As Integer

Cuando se declara una matriz, al especificar solamente el número de índice más alto, VBA asume que el 0 es el número de índice más bajo. Por lo tanto el primer ejemplo tiene 101 elementos y el segundo 100 elementos.

Si se quisiera que VBA escogiera como 1 el índice más bajo para todas las matrices en las que se declare únicamente el valor más alto, se debería incluir la siguiente instrucción antes de cualquier procedimiento de módulo:

Option Base 1

Para asignar un valor a un elemento de la matriz se indica entre paréntesis el número del elemento y se indica el valor tras un igual:

Matrix_3 (9) = 2.71828

4.2.2. Declarar matrices con varias dimensiones

Las matrices de VBA pueden tener hasta 60 dimensiones. Para declarar una matriz de varias dimensiones separamos las dimensiones mediante comas dentro de la declaración.

Dim Matrix_3(1 To 10, 1 To 30, 1 To 25) As Integer

Para asignar un valor a un elemento de la matriz anterior se procede como en una dimensión pero con los índices necesarios:

Matrix_3 (9, 18, 2) = 3.14159

Una matriz dinámica no tiene un número de elementos predeterminado. Se declara con un espacio entre paréntesis.

Dim Matrix_Reload ( ) As Integer

Sin embargo, antes de poder usar una matriz dinámica en el código, debemos usar la instrucción ReDim para indicar a VBA cuántos elementos se encuentran en la matriz (o ReDim Preserve si lo que queremos es mantener los valores existentes en la matriz). Podemos usar ReDim cuantas veces sea necesario, cambiando el tamaño de la matriz tan a menudo como haga falta.

4.3. OBJETOS Y COLECCIONES

Cuando se trabaja con VBA hay que entender el concepto de objetos y modelos de objetos de Excel. Se pueden concebir en términos de jerarquía. En la parte superior de este modelo está el objeto Application (en este caso el propio Excel).

En la Ayuda de Excel el modelo de objetos completo de Excel está esquematizado.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

32

4.3.1. La jerarquía de objetos

El objeto Application (es decir, Excel) contiene otros objetos. Algunos ejemplos de objetos contenidos en el objeto Application:

Workbooks (Una colección de todos los objetos Workbook)

Windows (una colección de todos los objetos Window)

AddIns (una colección de todos los objetos AddIn)

Algunos objetos pueden contener otros objetos. Por ejemplo, la colección Workbook consta de todos los objetos Workbook abiertos y un objeto Workbook contiene otros objetos, algunos de los cuales son los siguientes:

Worksheet (una colección de objetos Worksheet)

Charts (una colección de objetos Chart)

Names (una colección de objetos Name)

Cada uno de estos objetos, a su vez, puede contener otros objetos. Una colección Worksheet consta de todos los objetos Worksheet de un Workbook. Un objeto Worksheet consta de muchos otros objetos, entre los que se incluyen los siguientes:

ChartObjects (una colección de objetos ChartObjects)

Range

PageSetUp

PivotTables (una colección de objetos PivotTable)

4.3.2. Colecciones

Una colección es un grupo de objetos de la misma clase y una colección es por sí misma un objeto.

Para hacer referencia a un único objeto de una colección, colocamos el nombre del objeto o el número de índice entre paréntesis después del nombre de la colección, como en este caso:

Worksheets (“Hoja1”)

Worksheets (1)

4.3.3. Hacer referencia a objetos

VBA manipula objetos. Excel proporciona más de 100 clases de objetos que puede manipular. Ejemplos de objetos incluyen un workbook, un worksheet, un range en un worksheet, un chart, y otros.

Los objects están organizados en una jerarquía y pueden actuar como contenedores de otros objetos. Por ejemplo, Excel por sí mismo es un objeto llamado Application, y contiene otros objetos, tales como objetos Workbook. El objeto Workbook puede contener otros objetos, tales como objetos Worksheet y objetos Chart. Un objeto Worksheet puede contener objetos tales como objetos Range, objetos PivotTable, y otros. La organización de estos objetos está referida a un modelo de objetos.

Objetos iguales forman una colección. Por ejemplo, la colección Worksheets consiste en todas las worksheets en un workbook en particular. Los objetos de la colección Chart consisten en todos los objetos Chart de un worksheet. Las colecciones son objetos en ellos mismos.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

33

Nos referimos a un objeto en el código VBA especificando su posición en la jerarquía de objetos, usando un punto como separador. Los nombres introducidos dentro de objetos deben indicarse entre comillas.

Por ejemplo, nos podemos referir a una libro llamado Book1.xls como Application.Workbooks(“Book1.xls”).

Esta expresión se refiere al libro Book1.xls de la colección Workbooks que recoge todos los libros abiertos en un instante. La colección Workbooks está contenida en el objeto Application (que es Excel). Extendiendo esto a otro nivel, nos referiremos a la hoja Sheet1 en el libro Book1 de la forma siguiente:

Application.Workbooks(“Book1.xls”).Worksheets(“Sheet1”)

Se puede llevar a otro nivel y referirse a una celda en concreto como sigue:

Application.Workbooks(“Book1.xls”).Worksheets(“Sheet1”).Range(“A1”)

Si se omiten referencias, Excel usa los objetos activos. Si Book1.xls es el libro activo, el procedimiento se puede simplificar de la siguiente forma:

Worksheets(“Sheet1”).Range(“A1”)

Si se conoce que la hoja Sheet1 es la hoja activa, se puede simplificar incluso más:

Range(“A1”)

Los objetos tienen propiedades. Una propiedad puede pensarse como un ajuste de un objeto. Por ejemplo, un objeto Range tiene propiedades, como Value y Address. Un objeto Chart tiene propiedades como HasTitle y Type. Podemos usar VBA para determinar propiedades de objetos y cambiarlas.

Nos referimos a las propiedades combinando el objeto con su propiedad, separándola mediante un punto.

Por ejemplo, nos podemos referir al valor de la celda A1en la hoja Sheet1 como sigue:

Worksheets(“Sheet1”).Range(“A1”).Value

Se pueden asignar valores a las variables. Para asignar el valor de la variable Interest a la celda A1 en la hoja Sheet1 se usa la siguiente instrucción:

Interest = Worksheets(“Sheet1”).Range(“A1”).Value

Los objetos tienen métodos. Un método es una acción que es realizada por el objeto. Por ejemplo, uno de los métodos para los objetos Range es ClearContents. Este método limpia el contenido de Range.

Los métodos se especifican combinando el objeto con el método, separado por un punto. Por ejemplo, para limpiar el contenido

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

34

de las celdas contenidas entre la A1 y la C12, se usa lo siguiente:

Worksheets(“Sheet1”).Range(“A1:C12”).ClearContents

4.4. TRABAJAR CON CELDAS

Un objeto Range está contenido en un objeto Worksheet y consta de una única celda o serie de celdas de una hoja simple. Para hacer referencia a objetos Range en el código VBA lo hacemos de tres formas:

Propiedad Range

Propiedad Cells

Propiedad Offset

4.4.1. Seleccionar libros, hojas y celdas

Para decirle a VBA que mantenga un libro, una hoja o una celda activa y poder referir las distintas acciones a ellos hemos de usar las siguientes instrucciones:

ActiveWorksheet

ActiveSheet

ActiveCell

La sintaxis de estas instrucciones es la siguiente:

ActiveWorksheet = [Nombre del libro entre comillas]

ActiveSheet = [Nombre de la hoja entre comillas]

ActiveCell = [Nombre de la celda entre comillas]

Otra forma de seleccionar una celda puede ser a través del comando Select. Por ejemplo:

Worksheets(“Sheet1”).Range(“A1”).Select

4.4.2. Propiedad Range

La propiedad Range devuelve un objeto Range. Esta propiedad tiene dos sintaxis:

object.Range(cell)

object.Range(cell1, cell2)

La propiedad Range se aplica a dos tipos de objetos: un objeto Worksheet o un objeto Range. En este caso cell1 y cell2 hacen referencia a marcadores de posición en términos que Excel reconocerá para identificar el rango (en primera instancia) y definir el rango (en segunda instancia).

El siguiente ejemplo pone el valor 1 en la celda A1 de la Hoja 1 del libro activo:

Worksheets(“Sheet1”).Range(“A1”).Value = 1

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

35

La propiedad Range también reconoce nombres definidos en libros. Por ejemplo si una celda se llama Gamma podemos introducir un valor en esa celda de la siguiente forma:

Worksheets(“Sheet1”).Range(“Gamma”).Value = 1

El siguiente ejemplo introduce el mismo valor en un rango de 20 celdas:

Worksheets(“Sheet1”).Range(“A10:B10”).Value = 3

También se puede escribir con la segunda sintaxis de la forma:

Worksheets(“Sheet1”).Range(“A10”, “B10”).Value = 3

Para introducir un valor en una intersección de celdas usamos el operador intersección que en VBA es el espacio. En el siguiente ejemplo introducimos el valor 3 a la celda C6:

Worksheets(“Sheet1”).Range(“C1:C10 A6:E6”).Value = 3

Para introducir el valor 3 en cinco celdas diferentes se puede usar la siguiente instrucción:

Worksheets(“Sheet1”).Range(“A1;A3;A5;A7;A9”).Value = 3

En el caso de que queramos trabajar con celdas activas aplicando la propiedad Range, la celda activa se transforma en el origen de coordenadas de la nueva instrucción Range. Es decir, en el siguiente ejemplo se asigna el valor 5 a la celda una posición a la derecha y una posición por debajo de la activa:

ActiveCell.Range(“B2”).Value = 3

VBA tiene como propiedad por defecto del objeto Range la propiedad Value, por lo tanto si se omite la escritura de .Value el mismo VBA asigna el valor introducido a esta propiedad.

Worksheets(“Sheet1”).Range(“A1”) = 1

4.4.3. Propiedad Cells

Otro modo de hacer referencia a un rango es la propiedad Cells. Esta propiedad tiene tres sintaxis:

object.Cells(rowIndex, columnIndex)

object.Cells(rowIndex)

object.Cells

Para introducir el valor 7 en la celda B4 (fila 4, columna 2) haríamos:

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

36

Worksheets(“Sheet1”).Cells(4, 2) = 7

Si se quiere hacer una entrada en una celda relativa a la celda activa se puede proceder del mismo modo que con la propiedad Range. Sitúa nuestro origen de coordenadas en la celda activa y nos movemos en referencia a ella:

ActiveCell.Cells(1,1) = 3

La segunda sintaxis del método Cells utiliza un solo argumento que es la posición absoluta de la celda en el conjunto de la hoja. Admite los valores comprendidos entre 1 y 16.777.216. Las celdas están numeradas empezando en A1 y continuando hacia la derecha y después hacia abajo en la siguiente fila (65.536 filas por 256 columnas). Por ejemplo, la celda A1 es la 1, la B1 es la 2 y la A2 la 257. En el siguiente ejemplo se introduce el valor 6 en la celda H3:

Worksheets(“Sheet1”).Cells(520) = 6

Esta sintaxis puede utilizarse con un objeto Range. En este caso, la celda que se devuelve es relativa al objeto Range al que se hace referencia. Por ejemplo, si el objeto Range es A1:D10 (40 celdas), la propiedad Cells puede tener un argumento de 1 a 40 y devolver una de las celdas en el objeto Range. En el siguiente ejemplo se introduce el valor 1500 en la celda A2 porque A2 es la quinta celda (contando desde la parte superior derecha y hacia abajo) en el rango al que se hace referencia:

Worksheets(“Sheet1”).Range(“A1:D10”).Cells(5) = 1500

Con la tercera sintaxis hacemos referencia a todas las celdas de la hoja.

4.4.4. Propiedad Offset

La propiedad Offset sólo se aplica a un objeto Range. Su sintaxis es la siguiente:

object.Offset (rowOffset, columnOffset)

La propiedad Offset admite dos argumentos que se corresponden a la posición relativa de la celda superior izquierda del objeto Range especificado.

ActiveCell.Offset(1,0).Value = 5

Para realizar movimientos a la izquierda y por encima de la celda activa podemos usar valores negativos de rowOffset y columnOffset.

4.5. FUNCIONES INTEGRADAS

VBA tiene una gran cantidad de funciones integradas que simplifican los cálculos y las operaciones. Para obtener una lista de de las funciones propias de VBA, mientras escribimos código debemos escribir VBA seguido de un punto (.) y se abrirá un menú de donde podremos escoger la función necesaria.

También es posible el uso de funciones propias de Excel como por ejemplo Mdeterm para calcular un determinante de una

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

37

matriz sin tener que programar el cálculo mediante código. Se debe de comprobar que VBA no tiene ninguna función propia para realizar la misma acción ya que sino deberemos usar la función de VBA.

4.6. MANIPULAR OBJETOS Y COLECCIONES

VBA ofrece dos importantes construcciones que pueden simplificar el trabajo con objetos y colecciones:

Construcciones With-End With

Construcciones For Each-Next

4.6.1. Construcciones With – End With

La construcción With – End With nos permite realizar varias operaciones en un solo objeto.

El siguiente código:

Sub ChangeFont1()

Selection.Font.Name = “Times New Roman”

Selection.Font.FontStyle = “Bold Italic”

Selection.Font.Size = 12

Selection.Font.Underline = xlUnderlineStyleSingle

Selection.Font.ColorIndex = 5

End Sub

se podría simplificar con una construcción With – End With

Sub ChangeFont2()

With Selection.Font

.Name = “Times New Roman”

.FontStyle = “Bold Italic”

.Size = 12

.Underline = xlUnderlineStyleSingle

.ColorIndex = 5

End With

End Sub

4.6.2. Construcciones For Each – Next

En el caso de que queramos realizar alguna acción con todos los objetos de una colección o queremos evaluar todos los objetos de una colección y realizar algunas acciones si se cumplen ciertas condiciones se puede utilizar esta instrucción.

La sintaxis de la construcción es:

For Each [elemento] In [grupo]

[instrucciones]

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

38

[Exit For]

[instrucciones]

Next [elemento]

Un ejemplo concreto es el siguiente:

Sub Macro1( )

Dim MyArray(5) As Double

For i = 0 to 5

MyArray(i) = Rnd

Next i

For Each n In MyArray

Debug.Print n

Next n

End Sub

4.7. CONTROL DE LA EJECUCIÓN

Algunos procedimientos de VBA comienzan en la parte superior y progresan línea a línea hacia abajo. Sin embargo, muchas veces necesitaremos controlar el flujo de las rutinas omitiendo algunas instrucciones, ejecutando algunas instrucciones varias veces y comprobando las condiciones para determinar qué hará la rutina a continuación. Otros modos de controlar la ejecución de los procedimientos VBA son:

Instrucciones Go To.

Construcciones If – Then.

Bucles For – Next.

Bucles Do While.

Bucles Do Until.

4.7.1. Instrucciones GoTo

Esta instrucción simplemente transfiere la ejecución de un programa a una nueva instrucción, que debe estar precedida por una etiqueta (una cadena de texto seguida por dos puntos o un número sin dos puntos).

Los procedimientos VBA pueden contener cualquier número de etiquetas y la instrucción GoTo no puede bifurcarse fuera de un procedimiento. Este procedimiento está pensado para el control de errores, si bien se puede usar de muchas formas.

Sub GoTo_Demo ( )

UserName = InputBox(“Entre su nombre:”)

If UserName <> “Samuel” Then GoTo WrongName

MsgBox “Bienvenido Samuel”

‘ – [Más código aquí] –

Exit Sub

WrongName:

MsgBox “Lo siento, usuario no autorizado”

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

39

End Sub

4.7.2. Construcciones If – Then

Esta instrucción es una forma de proporcionar capacidad de tomar decisiones a las aplicaciones.

Existen dos sintaxis de la construcción. La primera implica la escritura de la construcción en una sola línea y la segunda en varias líneas.

Construcción If – Then en una sola línea:

If [condición] Then [instrucciones_si_se_cumple] Else [instrucciones_si_no_se_cumple]

Construcción If – Then en varias líneas:

If [condición] Then

[instrucciones si_se_cumple_la_condición]

Else If [condición-n] Then

[instrucciones alternativas]

Else

[instrucciones predeterminadas]

End If

Para comprender la sintaxis se indica una subrutina escrita de las dos formas:

Sub Saludo_1( )

If Time < 0.5 Then MsgBox “Buenos días”

If Time >= 0.5 And Time < 0.75 Then MsgBox “Buenas tardes”

If Time >= 0.75 Then MsgBox “Good Evening”

End Sub

Que se puede reescribir de la siguiente forma:

Sub Saludo_2( )

If Time < 0.5 Then

MsgBox “Buenos días”

ElseIf Time >= 0.5 And Time < 0.75 Then

MsgBox “Buenas tardes”

ElseIf Time >= 0.75 Then

MsgBox “Good Evening”

End If

End Sub

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

40

Esta última construcción se podría introducir separando la instrucción ElseIf en dos partes, Else + If:

Sub Saludo_3( )

If Time < 0.5 Then

MsgBox “Buenos días”

Else

If Time >= 0.5 And Time < 0.75 Then

MsgBox “Buenas tardes”

Else

If Time >= 0.75 Then

MsgBox “Good Evening”

End If

End If

End If

End Sub

Existe una alternativa a la construcción If – Then que es la función IIf. Esta función tiene tres argumentos y funciona de forma casi igual que la función Si de Excel. Su sintaxis es:

IIf (expr , parte_verdad , parte_falso)

Donde:

expr: Expresión que deseamos evaluar.

parte_verdad: Valor o expresión devuelta si expr es verdadero

parte_falso: Valor o expresión devuelta si expr es falso

El siguiente ejemplo muestra el uso de la función:

IIf (Range(“A1”) = 0; “Cero”; “Mayor que cero”)

Es importante destacar que el tercer argumento se evalúa siempre, incluso si el segundo es verdadero, por lo que una construcción como la siguiente daría un mensaje de error:

IIf (n = 0; 0; 1/n)

4.7.3. Construcciones Select Case

La construcción Select Case es útil para escoger entre tres o más opciones. Esta construcción también funciona con dos opciones y es una alternativa válida a If – then – Else. Su sintaxis es:

Select Case [expresión a comprobar]

Case [lista_expresiones-n]

[instrucciones-n]

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

41

Case Else

[instrucciones predeterminadas]

End Select

Un ejemplo de la construcción podría ser:

Sub Saludo_3 ( )

Select Case Weekday (Now)

Case 1 Or 7

MsgBox “Es fin de semana”

Case Else

MsgBox “No es fin de semana”

End Select

End Sub

4.7.4. Bucles For – Next

Esta construcción se utiliza para repetir una serie de declaraciones para cada elemento en una matriz o cada objeto de una colección. Su sintaxis es:

For contador = inicio To final [Step valor_salto]

[instrucciones]

[Exit For]

[instrucciones]

Next [contador]

El contador debe declararse al principio junto con el resto de variables para que VBA no le asigne un valor Variant. De esta forma se gana en rapidez de cálculo. Los contadores han de ser positivos aunque el salto (Step) podría ser negativo para reducir el valor a medida que se ejecuta el bucle.

En el siguiente ejemplo sumamos la raíz cuadrada de los primeros cien números:

Sub SumaRaiz ( )

Dim Sum As Double

Dim i As Integer

Sum = 0

For i = 1 To 100

Sum = Sum + Sqr (Count)

Next i

MsgBox Sum

End Sub

Si hubiéramos deseado considerar únicamente los valores impares deberíamos haber escrito:

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

42

For Count = 1 To 100 Step 2

Los bucles For – Next se pueden anidar uno dentro de otro. La única condición es que cada uno se ha de finalizar con su correspondiente Next.

4.7.5. Bucles Do While

Un bucle Do While se ejecuta mientras se cumpla una determinada condición. Puede tener dos sintaxis ya que VBA nos permite incluir la condición While al principio o al final del bucle. La diferencia está en el momento en que se evalúa la condición. En la primera sintaxis, el contenido del bucle nunca se ejecuta. En la segunda el contenido del bucle se ejecuta siempre al menos una vez.

Sintaxis 1:

Do [While condition]

[instrucciones]

[exit Do]

[instrucciones]

Loop

Sintaxis 2:

Do

[instrucciones]

[exit Do]

[instrucciones]

Loop [While condition]

Ejemplos de estas construcciones son:

Sub DoWhile_1( )

Do While Not IsEmpty(ActiveCell)

ActiveCell.Value = 0

ActiveCell.Offset (1,0).Select

Loop

End Sub

Sub DoWhile_2( )

Do

ActiveCell.Value = 0

ActiveCell.Offset (1,0).Select

Loop While Not IsEmpty(ActiveCell)

End Sub

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

43

4.7.6. Bucles Do Until

La estructura del bucle Do Until es muy parecida a la del bucle Do While. La diferencia se aprecia solamente cuando se comprueba la condición. En un bucle Do While, el bucle se ejecuta mientras se cumpla la condición. En el bucle Do Until, el bucle se ejecuta hasta que se cumple la condición.

Esta estructura también tiene dos sintaxis:

Do [until condición]

[instrucciones]

[Exit Do]

[instrucciones]

Loop

Do

[instrucciones]

[Exit Do]

[instrucciones]

Loop [until condición]

Los ejemplos anteriores se escribirían de la siguiente forma:

Sub DoUntil_1( )

Do Until ActiveCell.Value = 0

ActiveCell.Value = 0

ActiveCell.Offset (1,0).Select

Loop

End Sub

Sub DoUntil_2( )

Do

ActiveCell.Value = 0

ActiveCell.Offset (1,0).Select

Loop Until ActiveCell.Value = 0

End Sub

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

44

5. FUNCIONES

Los procedimientos Function se pueden usar en dos situaciones:

Como parte de una expresión en un procedimiento VBA.

En las fórmulas que se crean en una Hoja.

5.1. PROCEDIMIENTOS DE FUNCIÓN

5.1.1. Declarar una función

La sintaxis para declarar una función es la siguiente:

[Public | Private] [Static] Function nombre ([Lista de argumentos]) [As tipo]

[instrucciones]

[nombre = expresión]

[Exit Function]

[instrucciones]

[nombre = expresión]

End Function

Donde:

Public (Opcional). Indica que todos los demás procedimientos de todos los demás módulos de todos los proyectos activos de VBA pueden acceder al procedimiento Function.

Private (Opcional). Indica que sólo otros procedimientos del mismo módulo que el procedimiento Function pueden acceder a él.

Static (Opcional). Indica que los valores de las variables declaradas en el procedimiento Function se mantienen entre una invocación y otra.

Function (Obligatorio). Es la palabra clave que indica el principio de un procedimiento que devuelve un valor u otros datos.

Nombre (Obligatorio). Representa cualquier nombre válido del procedimiento Function, que debe seguir las mismas reglas que un nombre de variable.

Lista argumentos (Opcional). Representa una lista de una o más variables que representan argumentos pasados al procedimiento Function. Los argumentos se encuentran entre paréntesis. Usamos una coma para separar dos argumentos.

Tipo (Opcional). Es el tipo de dato devuelto por el procedimiento Function.

Instrucciones (Opcional). Cualquier número válido de instrucciones VBA.

Exit Function (Opcional). Es una instrucción que hace que se salga inmediatamente del procedimiento Function antes de que finalice.

End Function (Obligatorio). Es una palabra clave que indica el final del procedimiento.

Esta forma de definir funciones no puede utilizar nombres de funciones integradas en Excel ya que sino Excel usará la predeterminada.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

45

5.1.2. Ámbito de Function

El ámbito de una función determina si otros procedimientos en otros módulos u hojas pueden llamarla. Se ha de tener en cuenta que:

Si no declaramos el ámbito de una función éste será público.

Las funciones declaradas como Private no aparecerán en el cuadro de diálogo Pegar función de Excel. Por lo tanto, no podrá utilizarse como una fórmula de hoja.

Si el código VBA necesita llamar a una función que ha sido definida en otro libro, se deberá establecer una referencia al otro libro usando el comando del editor de VB Herramientas > Referencias. Otra opción es insertar la referencia al archivo antes de la Function.

Los procedimientos Function no aparecen en el cuadro de diálogo de Macros.

Los procedimientos Function no se pueden ejecutar mediante el comando Ejecutar del Editor de Visual Basic.

5.1.3. Ejecutar procedimientos Function

Los procedimientos function sólo se pueden ejecutar de dos formas:

Llamándolos desde otro procedimiento.

Usándolos en una fórmula de una hoja.

Podemos invocar funciones personalizadas desde un procedimiento, de la misma forma que invocamos funciones integradas. Por ejemplo, tras definir una función llamada Integral, podemos introducir una instrucción como la que sigue:

Area = Integral (Matriz)

Esta instrucción ejecuta la función Integral con Matriz como argumento, devuelve el resultado de la función y lo asigna a la variable Area.

También podemos usar el método Run del objeto Application como se muestra a continuación:

Area = Application.Run (“Integral”, “Matriz”)

El primer argumento para el método Run es el nombre de la función. Los siguientes argumentos representan los argumentos para la función. Los argumentos para el método Run pueden ser cadenas literales, números o variables.

Usar funciones personalizadas en una fórmula de hoja es similar a usar las funciones integradas, excepto que debemos asegurarnos de que Excel puede localizar el procedimiento Function.

En el cuadro Insertar función de Excel, las funciones personalizadas aparecen por defecto bajo la categoría Definidas por el Usuario.

5.2. ARGUMENTOS DE FUNCIÓN

Respecto los argumentos de una Function se ha de tener en cuenta lo siguiente:

Los argumentos pueden ser variables (incluyendo matrices), constantes, literales o expresiones.

Algunas funciones no tienen argumentos.

Algunas funciones tienen un número fijo de argumentos necesarios (entre 1 y 60).

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

46

Algunas funciones tienen una combinación de argumentos necesarios y opcionales.

En las funciones usadas en Excel separaremos los argumentos por un punto y coma ( ; ) a diferencia de como lo hacemos en VBA.

= Integral2 (A1 ; B1)

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

47

6. TABLAS DINÁMICAS

Una tabla dinámica en Excel permite hacer resúmenes de una Base de Datos, utilizándose para, promediar, o totalizar datos.

6.1. TABLAS DINÁMICAS EN EXCEL

6.1.1. Crear una tabla dinámica

Excel incluye un asistente-guía que facilita la creación de Tablas Dinámicas. Para su utilización se debe recurrir a Menú- Datos- Informe de Tablas y gráficos dinámicos.

La creación de una tabla dinámica se realiza a través del asistente y en varios pasos.

Con el cursor en cualquier celda de la tabla de datos, abrir la opción Menú – Datos – Informe de tablas y gráficos dinámicos. Se accederá al asistente.

Figura 6.1. Asistente para tablas y gráficos dinámicos.

En el paso 1 se seleccionan los siguientes campos:

Lista o base de datos de Microsoft Excel

Tabla dinámica

En el paso 2 se introduce el rango de celdas en los que están los datos.

Figura 6.2. Selección de rango para la tabla dinámica.

En el paso 3 aparecerá la ventana de la figura 6.2.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

48

Figura 6.3. Paso 3 del asistente para tablas dinámicas.

Se abre la ventana “Diseño...”:

Figura 6.4. Ventana Diseño del asistente para tablas dinámicas.

Se ha de montar la tabla desplazando las columnas de la derecha a las zonas que se deseen, Página, Fila, Columna o Datos. Dentro de datos pondremos los títulos de los que deseemos sumar o hacer promedios. En este caso colocaremos:

Semana en Columna

Vehículos en Fila

Cantidad en Datos.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

49

Figura 6.5. Montaje de la tabla dinámica.

Con el botón “Opciones...” podemos controlar los parámetros indicados en la figura 6.6.

Figura 6.6. Opciones de la tabla dinámica.

La tabla obtenida es la siguiente:

Tabla 6.1. Tabla dinámica.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

50

6.1.2. Aplicar filtros a la tabla

Esta tabla se puede modificar con las barras de herramientas que aparecen al seleccionar una celda de la tabla. Figura 6.7.

Figura 6.7. Barras de Herramientas de la tabla dinámica.

Con la Lista de campos de tabla dinámica podremos añadir campos a Filas, Columnas, Datos o Página. Una vez añadidos no se podrán quitar de la tabla, se deberá hacer una tabla nueva.

Al pulsar dos veces sobre una celda de la tabla se obtiene en una hoja nueva un resumen de ese valor. Por ejemplo para la celda intersección de Furgoneta y Semana 3 se obtiene:

Tabla 6.2. Detalle de operaciones en una celda de la tabla dinámica.

Pulsando sobre los menús desplegables se pueden seleccionar las filas o columnas que deseemos ver en la tabla. Se indica en la Figura 6.8.

Mes Semana Vehículo CantidadMarzo 3 Furgoneta 82961,77Febrero 3 Furgoneta 73908,44Enero 3 Furgoneta 2364,29

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

51

Figura 6.8. Opciones del menú Semana.

6.1.3. Obtener promedios

Siguiendo con las opciones que permiten las Tablas Dinámicas, se puede obtener de la tabla sin filtrar el promedio de los campos de datos.

Se coloca el cursor sobre una celda de la tabla y se hace clic con el botón derecho del ratón, obteniéndose un menú en el que se ha de seleccionar Configuración de campo... . Aparecerá la siguiente ventana:

Figura 6.9. Propiedades de los campos.

En esta ventana podremos cambiar la configuración de número (moneda, científico, etc.) y qué tipo de resumen queremos en la tabla (suma, promedio, etc.).

6.1.4. Tablas dinámicas en 3D

Se pueden crear tablas con una variable en un nivel superior, el campo Página. Si en el ejemplo anterior situamos Mes en el campo Página podremos obtener resúmenes en función de cada mes con todas las variables definidas en Filas y Columnas.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

52

Tabla 6.3. Detalle de operaciones en una celda de la tabla dinámica.

Aparece en la parte superior de la tabla la Variable de Página, en este caso Mes.

6.1.5. Actualizar datos y subtablas

Si se producen cambios en las celdas de la tabla de base de datos se debe realizar una actualización de la Tabla Dinámica. Esta actualización se realiza de la siguiente manera:

Se selecciona una celda y se pulsa el botón derecho del ratón para abrir el menú siguiente:

Figura 6.9. Propiedades de los campos.

Escogemos la opción Actualizar datos y la tabla se actualiza automáticamente.

6.1.6. Gráficos de una tabla dinámica Al hacer clic con el botón derecho del ratón se selecciona la opción Grafico Dinámico, lo que produce por defecto un grafico en columna. En este gráfico también podremos variar los elementos a ver en función de los campos que se hayan introducido en Página y Columna. El gráfico se crea en una hoja nueva.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

53

Figura 6.10. Propiedades de los campos.

Seleccionando Tipo de gráfico: Podemos elegir el tipo de gráfico.

6.1.7. Eliminar una tabla dinámica

Se elimina seleccionando la Tabla en su totalidad y apretando la tecla Suprimir (Supr)

6.2. TABLAS DINÁMICAS CON VBA

6.2.1. Crear una tabla dinámica

Para crear una tabla dinámica con VBA hemos de introducir dos parámetros principales, que son:

PivotCache (Memoria dinámica)

PivotTable (Tabla dinámica)

En primer lugar se declaran los dos parámetros:

Dim PTCache As PivotCache

Dim PT As PivotTable

Donde PTCache y PT son nombres a definir para el procedimiento.

A continuación definimos la memoria dinámica y la tabla dinámica:

Set PTCache = ActiveWorkbook.PivotCaches.Add (sourceType:=xlDataBase, _

SourceData:=”Sheet1!R1C1:R13C4”)

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

54

Set PT = PTCache.CreatePivotTable (TableDestination:=””, _

TableName:=”Tabla dinámica 1”)

En estas dos instrucciones se ha de tener en cuenta:

SourceData es el área de los datos de la tabla que actúa como Base de datos. Es el rango que se introduce en el paso 2 del asistente visto antes. La forma de indicarlo será cualquiera de las vistas anteriormente para indicar rangos de celdas.

TableDestination es la hoja en la que se situará la tabla. Si se deja “” es en la misma hoja. Si se desea ponerla en una hoja distinta se deberá indicar de la siguiente forma:

TableDestination:=Sheets(“Name_sheet”).Range(“B10”)

TableName es el nombre de la tabla.

Para añadir los campos se debe hacer en la instrucción PivotTable. Aprovechando que hemos definido PT como PivotTable se puede escribir de la siguiente forma:

With PT

.PivotFields(“Mes”).Orientation = xlPageField

.PivotFields(“Semana”).Orientation = xlColumnField

.PivotFields(“Vehículo”).Orientation = xlRowField

.PivotFields(“Cantidad”).Orientation = xlDataField

End With

Con esta instrucción se introducen los campos de forma análoga a como se hace con el asistente, con las siguientes consideraciones:

xlPageField es el campo correspondiente a Página.

xlColumnField es el campo correspondiente a Columna.

xlRowField es el campo correspondiente a Fila.

xlDataField es el campo correspondiente a Datos.

El código quedaría de esta forma:

Sub CrearPivotTable()

Dim PTCache As PivotCache

Dim PT As PivotTable

Set PTCAche = ActiveWorkbook.PivotCaches.Add (sourceType:=xlDataBase, _

SourceData:=”Sheet1!R1C1:R13C4”)

Set PT = PTCache.CreatePivotTable _

(TableDestination:= Sheets(“Name_sheet”).Range(“B10”), _

TableName:=”Tabla dinámica 1”)

With PT

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

55

.PivotFields(“Mes”).Orientation = xlPageField

.PivotFields(“Semana”).Orientation = xlColumnField

.PivotFields(“Vehículo”).Orientation = xlRowField

.PivotFields(“Cantidad”).Orientation = xlDataField

End With

End Sub

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

56

7. FORMULARIOS INTEGRADOS

Los cuadros de diálogo son los elementos más importantes de la interfaz de usuario de los programas Windows. En Excel se pueden crear cuadros de diálogo personalizados creando UserForms.

7.1. CUADROS DE DIÁLOGO INTEGRADOS

Los cuadros de diálogo se pueden crear partiendo de cero o se pueden aprovechar los ya integrados en Excel. Estos cuadros integrados son:

Un cuadro de entrada de datos.

Un cuadro de mensaje.

Un cuadro de diálogo para seleccionar un archivo para abrirlo.

Un cuadro de diálogo para especificar el nombre de archivo y su ubicación para guardar una operación.

Un cuadro de diálogo para especificar un directorio.

Un cuadro de diálogo para la entrada de datos.

7.2. FUNCIÓN INPUTBOX DE VBA

Es un cuadro de diálogo sencillo que permite al usuario introducir datos. Se puede introducir un texto, un número o incluso seleccionar un rango.

La sintaxis de esta función es:

InputBox (Mensaje [, título] [, predeterminado] [, xpos] [, ypos] _

[, archivo_ayuda, contexto])

Donde:

Mensaje. Obligatorio. El texto que se muestra en el cuadro de entrada.

Título. Opcional. El título de la ventana del cuadro de entrada.

Predeterminado. Opcional. El valor por defecto que se muestra en el cuadro de diálogo.

xpos, ypos. Opcional. Las coordenadas de la ventana desde la esquina superior izquierda de la pantalla.

Archivo_de_ayuda, contexto. Opcional. El archivo de ayuda y el tema de ayuda.

Esta función solicita al usuario alguna información. Esta información siempre es devuelta por la función como una cadena, por lo que será necesario convertir el resultado en un valor. Si la cadena devuelta es un número lo podemos transformar en un valor numérico mediante el comando de VBA Val.

Se puede añadir un título para el cuadro de diálogo, un valor predeterminado y especificar su posición en la pantalla. Si se especifica un tema de ayuda personalizado, el cuadro de introducción de datos incluirá un botón de ayuda.

Por ejemplo la siguiente línea crea el cuadro de diálogo de la figura 7.1.

Username = InputBox("Entre su nombre", "Identificación")

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

57

Figura 7.1. Ejemplo de InputBox.

7.3. FUNCIÓN INPUT BOX DE EXCEL

La utilización del método InputBox de Excel en lugar del de VBA tiene tres ventajas:

Podemos especificar el tipo de datos que devuelve.

El usuario puede especificar un rango de hoja arrastrándolo a la misma.

La validación de los datos introducidos se realiza automáticamente.

La sintaxis de este método es la siguiente:

Application.InputBox(Prompt, Title, Default, Left, Top, Helpfile, _

HelpContextId, Type)

Donde:

Prompt. Obligatorio. El texto que se muestra en el cuadro de introducción de datos.

Títle. Opcional. El título de la ventana del cuadro de introducción de datos.

Default. Opcional. El valor por defecto que devolverá la función si el usuario no introduce nada.

Left, Top. Opcional. Las coordenadas de pantalla de la parte superior izquierda de la ventana.

Helpfile, HelpContextId. Opcional. El archivo de ayuda y el tema de ayuda.

Type. Opcional. El código para el tipo de dato devuelto según tabla 7.1.

Código Significado

0 Una fórmula.

1 Un número.

2 Una cadena (texto).

4 Un valor lógico (Verdadero o Falso).

8 Una referencia a una celda, como un objeto de rango.

16 Un valor de error, como #N/A.

64 Una matriz de valores.

Tabla 7.1. Tipos de datos.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

58

Un ejemplo de la instrucción es:

Set myRange = Application.InputBox(Prompt:= "Sample", type:= 8)

El uso de Set hace que asignemos a myRange toda la expresión. Si no escribimos Set entonces se le asigna el valor introducido en el cuadro de diálogo como si fuera una variable.

Una ventaja de usar el método de Excel es que al realizar Excel automáticamente la validación de los datos, si se introduce un tipo de datos diferente al esperado aparece un mensaje informativo y permite al usuario volver a introducir los datos.

Para aceptar dos tipos diferentes de datos, en la propiedad tendremos que poner la suma de los, es decir, por ejemplo, type:=1+2.

7.4. FUNCIÓN MSGBOX DE VBA

La función MsgBox de VBA es un medio sencillo de mostrar un mensaje al usuario o de obtener una respuesta simple (como Aceptar o Cancelar).

La sintaxis es la siguiente:

MsgBox(Prompt[, Buttons][, Title][, Helpfile, Context])

Donde:

Prompt. Obligatorio. El texto que se muestra en el cuadro de mensaje.

Buttons. Opcional. Una expresión numérica que determina qué botones e iconos se muestran en el cuadro de mensaje.

Títle. Opcional. El título de la ventana del cuadro de mensaje.

Helpfile, Context. Opcional. El archivo de ayuda y el tema de ayuda.

Las constantes usadas para los botones de la función MsgBox se indican en la siguiente tabla:

Constante Valor Descripción

vbOKOnly 0 Sólo muestra el botón Aceptar.

vbOKCancel 1 Muestra los botones Aceptar y Cancelar.

vbAbortRetryIgnore 2 Muestra los botones Anular, Reintentar y Omitir.

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 consulta.

vbExclamation 48 Muestra el icono de mensaje de advertencia.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

59

Constante Valor Descripción

vbInformation 64 Muestra el mensaje de información.

vbDefaultButton1 0 El primer botón es el predeterminado.

vbDefaultButton2 256 El segundo botón es el predeterminado.

vbDefaultButton3 512 El tercer botón es el predeterminado.

vbDefaultButton4 768 El cuarto botón es el predeterminado.

vbSystemModal 4096 Se suspenden todas las aplicaciones hasta que el

usuario responda al mensaje (puede que no funcione en todos los casos).

Tabla 7.2. Tipos de datos.

Figura 7.2. Ejemplo de MsgBox.

Para usar una combinación de varios botones podemos “sumarlos” en la propiedad buttons como por ejemplo, vbYes + vbQuestion + vbDefaultButton2.

Se puede asignar el resultado de un MsgBox a una variable. Un ejemplo de asignación del resultado de un MsgBox a una variable se recoge a continuación:

Sub Respuesta()

Ans = MsgBox(“Continue?”, vbYesNo)

Select Case Ans

Case vbYes

... [código si Ans es Sí] ...

Case vbNo

... [código si Ans es No] ...

End Select

End Sub

Las constantes usadas para el valor devuelto por MsgBox se indican en la tabla 7.3.

Constante Valor Descripción

vbOK 1 Aceptar.

vbCancel 2 Cancelar.

vbAbort 3 Anular.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

60

Constante Valor Descripción

vbRetry 4 Reintentar.

vbIgnore 5 Omitir.

vbYes 6 Sí.

vbNo 7 No.

Tabla 7.3. Valores devueltos por MsgBox.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

61

8. GRÁFICOS

8.1. PARÁMETROS BÁSICOS

Excel mantiene todos los gráficos como objetos de gráfico dentro del Modelo de objetos de Excel.

Excel guarda cada hoja de gráfico como un objeto de gráfico, que son subordinados del correspondiente objeto Workbook. Cada objeto de gráfico tiene diferentes objetos subordinados que representan los diferentes elementos de un gráfico.

Los gráficos incrustados son los gráficos que se añaden a una hoja de cálculo existente en un punto determinado. En este caso el gráfico queda subordinado al objeto Worksheet. Todos los gráficos de una misma hoja de cálculo forman una colección ChartObject que contiene todos los gráficos de esa hoja.

Los principales objetos subordinados al objeto Chart son los siguientes:

Axes Collection. Incluye los siguientes objetos AxisTitle, Border, Gridlines y TickLabels.

ChartArea. Incluye los objetos Border, Font e Interior.

ChartGroups Collection. Colección de los objetos ChatrGroup que representan cada grupo de datos en un gráfico.

ChartObjects Collection. Colección de los objetos Chart en una hoja.

ChartTitle. Representa el título de un gráfico. Incluye los objetos Border, Characters, Font e Interior.

Corners. Representa las esquinas de un gráfico 3D.

DataTable. Representa la tabla de datos de un gráfico. Incluye un objeto Border.

Floor. Representa el suelo de un gráfico 3D. Incluye los objetos Border e Interior.

Legend. Representa la leyenda del gráfico. Incluye los objetos Font, Border, Interior y LegendEntries.

PageSetup. Contiene la información de configuración de la página incluyendo márgenes, medida del papel, etc.

SeriesCollection Collection. Contiene los objetos Series que representan los datos en el gráfico. Incluye los objetos Border, Points e Interior.

Shapes Collection. Colección de las formas dentro del gráfico.

Tab. Representa una lengüeta de un gráfico.

Walls. Representa las paredes de un gráfico 3D. Incluye los objetos Border e Interior.

Excel tiene 14 tipos de gráficos teniendo cada uno al menos dos subtipos de gráficos. Se selecciona un gráfico especificando un valor de la constante xlChartType para la propiedad ChartType.

8.2. CREAR UNA HOJA DE GRÁFICO

Cuando se crea un gráfico, VBA crea un nuevo objeto Chart que contiene todas las opciones correspondientes al gráfico. Además contiene diferentes objetos que representan las características del gráfico.

Como existe la posibilidad de crear una hoja de gráfico o incrustar un gráfico en una hoja, cuando se crea un gráfico en una nueva hoja se usa el objeto Chart directamente, cuando se incrusta en una hoja se utiliza la colección de objetos ChartObjects.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

62

Para crear una hoja de gráfico separada se usa el método Add con el objeto Chart. Con este método se pueden usar tres parámetros diferentes para especificar la localización del gráfico y el número de hojas a añadir. Utilizando el parámetro Before se especifica la hoja antes de la cual irá el gráfico. El parámetro After indica después de qué hoja se situará la hoja de gráfico. Finalmente si se desean crear múltiples hojas de gráfico se usa el parámetro Count para indicar el número de hojas a añadir.

El código a introducir empieza por declarar el nuevo gráfico:

Dim Nuevo_grafico As Chart

Se añade mediante la instrucción:

Set Nuevo_grafico = ThisWorkbook.Charts.Add()

Cambiamos el nombre a la hoja de gráfico con la instrucción:

Nuevo_grafico.Name = “Nuevo nombre de hoja”

En cualquier gráfico se debe especificar el rango de datos. Esto se realiza mediante la instrucción SetSourceData que necesita dos parámetros diferentes. Su sintaxis es:

Nuevo_grafico.SetSourceData :=Source, PlotBy

Donde:

Source. Range requerido. Es el rango que contiene los datos de origen.

PlotBy. Variant opcional. Especifica el modo de trazado de los datos. Puede ser una de las siguientes constantes XlRowCol: xlColumns o xlRows. Este parámetro le dirá a Excel si considerar los datos en columnas o filas.

Un ejemplo sería:

Charts(1).SetSourceData Source:=Sheets(1).Range("A1:A10"), PlotBy:=xlColumns

La indicación Charts(1) significa el primer gráfico dentro de Workbook.

Para especificar el tipo de gráfico se usa otra instrucción de la siguiente manera:

Nuevo_grafico.ChartType = xlColumnClustered

8.3. INCRUSTAR UN GRÁFICO EN UNA HOJA

Cuando se incrusta un gráfico en una hoja, se crea un objeto Chart dentro de un objeto Worksheet ya que se pueden situar diversos gráficos en la misma hoja. Por esta razón es necesario utilizar la colección ChartObjects para trabajar con esos gráficos.

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

63

Para añadir un gráfico a una hoja existente se debe usar el método Add con el objeto ChartObjects. El método Add tiene cuatro parámetros opcionales para ayudar a la situación y localización de los gráficos. Éstos son:

Left. Este parámetro se utiliza para especificar la localización del gráfico respecto el margen izquierdo de la columna A.

Top. Este parámetro se utiliza para especificar la localización del gráfico respecto el margen superior de la fila 1.

Width. Este parámetro se utiliza para especificar el ancho inicial del gráfico.

Height. Este parámetro se utiliza para especificar el alto inicial del gráfico.

El tipo de gráfico se especifica mediante la propiedad ChartType. Con esta propiedad se especifica el tipo de gráfico mediante la constante XlChartType.

El código a introducir empieza por declarar el nuevo gráfico:

Dim Nuevo_grafico As ChartObject

Se añade mediante la instrucción:

Set Nuevo_grafico = Sheets(“Sheet1”).ChartObjects.Add(Left:=50, Top:= 30, _

Width:=400, Height:=400)

En cualquier gráfico se debe especificar el rango de datos. Esto se realiza mediante la instrucción SetSourceData que necesita dos parámetros diferentes. Su sintaxis es:

Nuevo_grafico.Chart.SetSourceData := Source, PlotBy

Donde:

Source. Range requerido. Es el rango que contiene los datos de origen.

PlotBy. Variant opcional. Especifica el modo de trazado de los datos. Puede ser una de las siguientes constantes XlRowCol: xlColumns o xlRows. Este parámetro le dirá a Excel si considerar los datos en columnas o filas.

Un ejemplo sería:

Nuevo_grafico.Chart.SetSourceData Source:=Sheets(1).Range("A1:A10"), _

PlotBy:=xlColumns

Para especificar el tipo de gráfico se usa otra instrucción de la siguiente manera:

Nuevo_grafico.Chart.ChartType = xl3DColumn

8.4. APLICAR LAS OPCIONES DEL ASISTENTE PARA GRÁFICOS EN UN GRÁFICO

Para la construcción de un gráfico se pueden usar las propiedades del asistente para gráficos de Excel. En él se especificarán los

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

64

parámetros necesarios para crearlo, no siendo necesarios todos. Estos parámetros son:

Source. Variant opcional. Rango que contiene los datos de origen del nuevo gráfico. Si este argumento se omite, Microsoft Excel modificará la hoja de gráficos activa o el gráfico seleccionado en la hoja de cálculo activa.

Gallery. XlChartType opcional. Tipo de gráfico.

Format. Variant opcional. El número de opción de los autoformatos integrados. Puede ser un número del 1 al 10, dependiendo del tipo de la galería. Si este argumento se omite, Microsoft Excel elegirá un valor predeterminado basándose en el tipo de la galería y en el origen de los datos.

PlotBy. Variant opcional. Especifica si los datos de cada serie están en filas o en columnas. Puede ser una de las siguientes constantes XlRowCol: xlRows o xlColumns.

CategoryLabels. Variant opcional. Un número entero que especifica el número de filas o de columnas del rango de origen que contienen rótulos de categorías. Los valores permitidos van desde cero hasta uno menos que el número máximo de las categorías o series correspondientes.

SeriesLabels. Variant opcional. Un número entero que especifica el número de filas o de columnas del rango de origen que contienen rótulos de series. Los valores permitidos van desde cero hasta uno menos que el número máximo de las categorías o series correspondientes.

HasLegend. Variant opcional. True si se incluye una leyenda.

Title. Variant opcional. El texto del título del gráfico.

CategoryTitle. Variant opcional. El texto del título del eje de categorías.

ValueTitle. Variant opcional. El texto del título del eje.

ExtraTitle. Variant opcional. El título del eje de series en gráficos 3D o el segundo título del eje de valores en gráficos 2D.

Un ejemplo podría ser:

Charts("Chart1").ChartWizard Gallery:=xlLine, HasLegend:=True, _

CategoryTitle:="Year", ValueTitle:="Sales"

Los tipos de gráfico XlChartType son:

xlArea. Dibuja valores y colores individuales en un área para resaltar las series de datos.

xlBar. Muestra datos en columnas horizontales para ilustrar la relación entre datos.

xlColumn. Muestra datos en columnas horizontales para ilustrar la relación entre datos.

xlLine. Dibuja valores individuales en una línea continua para ilustrar las tendencias de los datos.

xlPie. Muestra la relación de cada dato con el resto en un todo completo.

xlRadar. Dibuja cada serie de datos en unos ejes separados.

xlXYScatter. Dibuja diferentes fuentes de datos mediante diferentes barras de tiempo.

xlCombination. Crea una combinación de gráficos.

xl3DLine. Dibuja relaciones entre datos en una línea continua con una representación en 3D.

xl3DPie. Muestra la relación de cada dato con el resto en un todo completo en 3D.

xl3DSurface. Dibuja los datos para crear un gráfico con forma topográfica en 3D.

xlDoughnut. Muestra las relaciones entre los valores de los datos en un círculo completo.

8.5. AÑADIR UNA NUEVA SERIE DE DATOS A UN GRÁFICO

Una vez se ha creado un gráfico, se puede redefinir el rango de datos que usa Excel para crear el gráfico. Para añadir una nueva

CURSO INICIACIÓN VISUAL BASIC FOR APPLICATIONS PARA EXCEL

OCTUBRE - 2010

920-100920-INICIACION_VBA_EXCEL-ED1.DOC

65

serie de datos al rango existente de un gráfico creado, se crea un nuevo objeto de Series y se añade a la colección SeriesCollection con el método Add.

La colección SeriesCollection representa todas las series de datos que Excel representa en un gráfico, siendo cada serie de datos un objeto Series diferente.

Con el método Add se pueden usar cinco parámetros distintos:

Source. Variant requerida. Los nuevos datos, ya sea como un objeto Range o como una matriz de puntos de datos.

Rowcol. Opcional. Especifica si los nuevos datos están en filas o columnas especificando la constante xlColumns o xlRows.

SeriesLabels. Variant opcional. No se tiene en cuenta si Source es una matriz. True si la primera fila o columna contiene el nombre de la serie de datos. False si la primera fila o columna contiene el primer punto de datos de la serie. Si este argumento se omite, Microsoft Excel intentará determinar la ubicación del nombre de la serie a partir del contenido de la primera fila o columna.

CategoryLabels. Variant opcional. No se tiene en cuenta si Source es una matriz. True si la primera fila o columna contiene el nombre de los rótulos de categorías. False si la primera fila o columna contiene el primer punto de datos de la serie. Si este argumento se omite, Microsoft Excel intentará determinar la ubicación del rótulo de categorías a partir del contenido de la primera fila o columna.

Replace. Variant opcional. Si CategoryLabels es True y Replace es True, las categorías especificadas reemplazarán a las existentes en la serie. Si Replace es False, no se reemplazarán las categorías ya existentes. El valor predeterminado es False.

La construcción a usar es:

Charts("Chart1").SeriesCollection.Add Source:=ActiveWorkbook. _

Worksheets("Sheet1").Range("B1:B10")

Worksheets("Sheet1").ChartObjects(1).Activate

ActiveChart.SeriesCollection.Add Source:=Worksheets("Sheet1").Range("B1:B10")