Sé diferente, intégrate…
Fecha: 23 / 07 / 2013
Autor: ORLANDO GUTIERREZ
Mm011
Manual CURSO EXCEL 2013 y
INDICE
Pag. 3
Lección 1 Introducción a VBAPag. 5
Lección 2 Módulos en VBAPag. 6
Lección 3 Clases en VBAPag. 10
Lección 4 MacrosPag. 13
Lección 5 Modelo de objetos de Excel: Colecciones, Objetos, Propiedades, Métodos, EnumeracionesPag. 18
Lección 6 Objeto ExcelPag. 19
Lección 7 LibrosPag. 20
Lección 8 HojasPag. 20
Lección 9 Celdas y RangosPag. 22
Lección 10 Manejo de gráficosPag. 24
Lección 11 Colecciones para el manejo de gráficosPag. 27
Lección 12 Objetos para el manejo de gráficosPag. 28
Lección 13 Propiedades para el manejo de GráficosPag. 32
Lección 14 Métodos para el Manejo de GráficosPag. 33
Lección 15 Controles, Cuadros de Diálogo y FormulariosPag. 34
Lección 16 Crear FormulariosPag. 35
Lección 17 Agregar Controles a FormulariosPag. 36
Lección 18 Programar eventos de controles en FormulariosPag. 39
Lección 19 Novedades en Aplicaciones para Office en Excel 2013 :Nuevas funciones de hoja de cálculo, Análisis Rápido, Disponibilidad de nuevos orígenes de datos, Uso de los modelo OM, Creación de gráficos dinámicos y tablas dinámicas de forma independiente, Interfaz de un único documento, Las animaciones de encendido y apagado mediante programación, Escala de tiempoLECCION 1 Introducción a VBA
A la hora de programar en Visual Basic para Aplicaciones VBA , Visual Basic for Applications en inglés, se dispone de un ambiente de programación bastante completo: en Excel, Word y Access se dispone del Editor de Visual Basic.
VBA es un subconjunto del lenguaje de Programación Visual Basic y por lo tanto mantiene el conjunto de instrucciones básicas: secuenciales, condicionales y repetitivas.
En la siguiente figura se muestra un resumen de estas instrucciones
El valor agregado de VBA es que ofrece una librería de objetos para cada una de las herramientas de Office: Excel, Word y Access. En las aplicaciones modernas desarrolladas con Microsoft Visual Studio .Net; esta funcionalidad la ofrecen a través de las características de Interop.
Editor de Visual Basic
El Editor de Visual Basic, VBE por sus siglas en inglés, es un programa independiente a Excel pero fuertemente relacionado a él porque es el programa que permite escribir código VBA que estará asociado a las macros. Existen al menos dos alternativas para abrir este editor, la primera de ellas es a través del botón Visual Basic de la ficha Programador.
El segundo método para abrir este programa es, el más sencillo y rápido y que es a través del atajo de teclado: ALT + F11. El Editor de Visual Basic contiene varias ventanas y barras de herramientas.
En la parte izquierda se muestra el Explorador de proyectos el cual muestra el proyecto VBA creado para el libro actual y además muestra las hojas pertenecientes a ese libro de Excel. Si por alguna razón no se puedes visualizar este módulo se puede habilitar en la opción de menú Ver y seleccionando la opción Explorador de proyectos.
El Explorador de proyectos también ayuda a crear o abrir módulos de código que se serán de gran utilidad para reutilizar todas las funciones de código VBA.
Dentro del Editor de Visual Basic se observa una ventana llamada Inmediato que está en la parte inferior. Esta ventana es de mucha ayuda al momento de escribir código VBA porque permite introducir instrucciones y observar el resultado inmediato. Además, desde el código VBA se pueden imprimir mensajes hacia la
ventana Inmediato con el comando Debug.Print de manera de depurar el código. Si no se puede observar esta
ventana se puede mostrarla también desde el menú Ver. El área más grande en blanco es donde se escribe el código VBA.
LECCION 2 Módulos en VBA
Un módulo se utiliza para recopilar bajo un nombre un conjunto de declaraciones, instrucciones, procedimientos y funciones.
Al crearse un módulo desde VBA se pueden agegar variables (declaraciones) y código Sub y Functions. Ejemplo se crea un módulo saludo.vb
Y se escribe el siguiente código
Sub saludo() ‘Subrutina, saludo del modulo
Dim saludo As String ‘ declaración de variable local
saludo = MsgBox(“Hola Mundo”, vbCritical, “Mi saludo”) ‘instrucción End Sub
Se Inicia VBA y Menú>Insertar>Módulo.
A continuación se resumen la sintaxis para las instrucciones en VBA.
LECCION 3 Clases en VBA
Aparte de los módulos para código que tiene Excel VBA, también existen Módulos de clase o Class Modules, en estos módulos se puede crear clases personalizadas con las cuales se puede representar en cierta forma objetos, aunque Excel VBA no es un lenguaje orientado a objetos, se puede aprovechar algunas ventajas que da el uso de clases.
Un módulo de clase en VBA puede definirse como una plantilla donde crear objetos.
Definirá todas las características que requiere el objeto final. Por ejemplo, se puede tener una clase llamada clsEmpleados en donde se almacenan todas las características con respecto a un empleado. Como propiedades se pueden definir: nombre, apellidos, domicilio, edad, casado, sueldo, fecha de ingreso, antigüedad, etc. Como métodos se podría tener: ActualizarDatos , GuardarCambios, ValidarDatos.
Un módulo de clase:
Es parecido a los tipos de datos definidos por el usuario UDT.
Cada módulo define un tipo de objeto.
En ejecución se debe crear una instancia (o objeto) de la clase.
No tiene interfaz de usuario.Los módulos de clase no presentan una interfaz gráfica al desarrollador tal y como lo hacen los formularios. En los formularios se puedes colocar visualmente un botón, una lista, un texto, etc. Los módulos de clase solo contienen código definiendo las características del objeto.
Se pueden crear múltiples instancias en ejecución.De acuerdo a la configuración de las propiedades de la clase se pueden crear varias instancias u objetos de una misma clase. Esto es, la propiedad Instancing de un módulo de clase indica cómo se trataran los objetos creados utilizando un módulo de clase determinado. Se tiene la opción de que sea una clase Privada lo cual significa que no se puede instanciar directamente por otro programa.
Proporciona métodos y propiedades.Eventos de un módulo de clase: Initialize y Terminate Los módulos de clase presentan dos eventos:
Initialize: ocurre cuando se crea una instancia de una clase. Se usa, generalmente para inicializar cualquier dato usado por la instancia de una clase en el código.
Terminate: ocurre cuando toda referencia a una instancia de una clase son removidas de memoria alestablecer todas las variables que la refieren a un objeto a Nothing o cuando la última referencia al el objeto se encuentra fuera de alcance. Usado generalmente para limpiar la memoria de objetos creados dentro de esta instancia y generar un error si hay alguna anomalía o guardar alguna información del objeto, etc.
Diferencias con los módulos estándar
Deben ser creadas explícitamente (crear instancia) antes de usarlas.No se puede utilizar el método o la propiedad de una clase si no se tiene una instancia de la misma o una referencia al componente que la contiene si la clase está configurada como Instancing = PublicNoCreatable.
Se Pueden crear múltiples instancias de la clase.
Ejemplo:
1. Iniciar VBA y en Menú>Insertar>Módulo de clase.
2. Darle un nombre por ejemplo ObAplicacion
3. Insertar el siguiente código donde se crea un objeto Aplicacion:
Public WithEvents MiAplicacion as Application
Si se pulsa ahora F2 y se despliega la lista de objetos, encontrará el objeto recién creado. A continuación se crean dos métodos para el objeto recién creado. El primero se encargará de insertar una nueva hoja en el libro:
Private Sub MiAplicacion_NuevaHoja(ByVal wb As Workbook, ByVal sh As Object) Dim nombrehoja As String
nombrehoja = InputBox(“Introduzca un nombre para la hoja”) ActiveSheet.Name = nombrehoja
ActiveSheet.Move After:=Sheets(Sheets.Count)
End Sub
Con este código se crea un nuevo libro:
Private Sub MiAplicacion_NuevoLibro(ByVal wb As Workbook) Dim numhojas, numactual, diferencia As Integer
‘por cada nuevo libro se solicita al usuario el número de hojas ‘caso necesario se agregan o eliminan hojas
Do
numhojas = Application.InputBox(“¿Cuántas hojas va a necesitar?”, Type:=1) Loop While numhojas = False
numactual = Sheets.Count
diferencia = numactual – numhojas
‘se eliminan las hojas de sobra y se desactivan las alertas Do While diferencia > 0 Application.DisplayAlerts = False Sheets.Item(diferencia).Select ActiveWindow.SelectedSheets.Delete diferencia = diferencia – 1 Loop Application.EnableEvents = True Application.DisplayAlerts = True End Sub
Por último se va a conectar el módulo de clase con el objeto Aplicación:
Option Explicit
Dim app As New ObjAplicacion Sub Inicializa_MiAplicacion() Set app.MiAplicacion = Application End Sub
Otro ejemplo conteniendo propiedades
Para ilustrar sobre el uso de clases, se creará la clase Rectángulo. En aspectos generales una clase tiene un constructor, variables privadas, métodos de lectura/escritura de las variables privadas, propiedades y métodos en sí. En un módulo de clase no existen constructores, más si existe el evento Initialize de la clase, una variable privada se crea con la sentencia private al definir una variable, las propiedades de lectura/escritura se realizan con las palabras clave Get y Let, las propiedades con Property y los métodos como otros Sub o Function.
Aquí se muestra el código para la clase Rectangulo
'variables privadas Private pnombre As String Private plargo As Double Private pancho As Double 'Constructora de la Clase Private Sub Class_Initialize() plargo = 6
pancho = 4 End Sub
'Acceso variables publicas
Public Property Let nombre(p As String) pnombre = p
End Property
Public Property Get nombre() As String nombre = pnombre
End Property
Public Property Let largo(x As Double) plargo = x
End Property
Public Property Get largo() As Double largo = plargo
End Property
Public Property Let ancho(x As Double) pancho = x
End Property
Public Property Get ancho() As Double ancho = pancho
End Property 'Propiedades
Property Get Area() As Double Area = plargo * pancho End Property
Property Get Perimetro() As Double Perimetro = 2 * (plargo + pancho) End Property
'Metodos
Public Sub Resize(f As Double) plargo = plargo * (1 + f) pancho = pancho * (1 + f) End Sub
En el código, en primer lugar se han declarado las variables privadas (pnombre, pancho, plargo), luego se ha declarado el evento inicialización de una clase (Class_Initialize), que en caso no se declare los parámetros para la clase Rectangulo (largo, ancho) automáticamente toma los valores que indica Initialize.
Luego se han declarado las propiedades de acceso lectura/escritura (Get/Let) a cada una de esas variables. Luego se han declarado las propiedades Area y Perimetro que se calculan en función de los parámetros plargo y
pancho. Finalmente se creó el método Resize, que reajusta el tamaño de plargo y pancho en un porcentaje f.
Para el siguiente fragmento de código, se requiere una hoja de Excel conteniendo la siguiente información:
Luego en un módulo estándar se agrega el siguiente sub.
Sub readRectangulos() Dim rg As Range
Dim oRec1 As Rectangulo Dim oRec2 As Rectangulo Set rg = Range("B3") Set oRec1 = New Rectangulo 'Se define los datos de oRec1 oRec1.largo = rg.Offset(0, 1).Value oRec1.ancho = rg.Offset(0, 2).Value rg.Offset(0, 3).Value = oRec1.Area rg.Offset(0, 4).Value = oRec1.Perimetro 'No se define los datos de oRec2 Set rg = Range("B4")
Set oRec2 = New Rectangulo rg.Offset(0, 1).Value = oRec2.largo rg.Offset(0, 2).Value = oRec2.ancho
rg.Offset(0, 3).Value = oRec2.Area rg.Offset(0, 4).Value = oRec2.Perimetro 'Aplicando Resize a oRec1
oRec1.Resize (0.25) Set rg = Range("B5")
rg.Offset(0, 1).Value = oRec1.largo rg.Offset(0, 2).Value = oRec1.ancho rg.Offset(0, 3).Value = oRec1.Area rg.Offset(0, 4).Value = oRec1.Perimetro 'Libera memoria
Set oRec1 = Nothing Set oRec2 = Nothing End Sub
Se declaran dos objetos Rectangulo oRec1 y oRec2, el primero toma datos de C3 y D3 de la hoja activa, mientras
que oRec2 no toma ningún valor, mantiene los valores que se le dio en Class_Initialize para plargo y pancho. Se escriben estos valores en la hoja, luego se modifica oRec1 con el método Resize y se inscriben los valores en la hoja.
LECCION 4 Macros
Las macros de Excel permiten automatizar tareas realizadas cotidianamente de manera de ser más eficientes en el trabajo. Una macro no es más que una serie de comandos o instrucciones que permanecen almacenados dentro de Excel y que se pueden ejecutar cuando sea necesario y cuantas veces se requiera.
Las macros se escriben en un lenguaje de programación que es conocido como VBA. Este lenguaje permite acceder a prácticamente todas las funcionalidades de Excel y con ello también ampliar la funcionalidad del programa. Excel provee de una herramienta especial que permite crear una macro sin necesidad de conocer los detalles del lenguaje de programación, pero no es el caso ideal para los desarrolladores profesionales.
Las macros se crean con el Editor de Visual Basic el cual permitirá introducir el código con las instrucciones que serán ejecutadas por la macro.
Existe otro método que es utilizar la Grabadora de macros la cual irá grabando todas las acciones realizadas en Excel hasta que se detenga la grabación. Una vez grabada la macro podremos “reproducir” de nuevo las acciones con tan solo un clic. La utilidad de la grabadora es visualizar el código VBA generado de manera automática el cual puede ser modificado en las aplicaciones desarrolladas.
El código de la grabadora de macros
Una manera muy interesante de descubrir y aprender más sobre código VBA es analizar el código generado por
la Grabadora de macros. En el siguiente ejemplo se grabará una macro muy sencilla que solamente cambie el color de la fuente de la celda actual.
Para comenzar se debe ir a la pestaña Programador y pulsar el comando Grabar macro lo cual mostrará el
Pulsar el botón Aceptar y se comenzarán a grabar todas las acciones. Para la macro a grabar se ejecutará lo siguiente: ir a la pestaña Inicio y pulsar el comando Color de fuente y seleccionar el color rojo para la celda activa.
Una vez hecho esto se detiene la grabación de la macro y una alternativa para hacerlo es pulsar el icono que se muestra en la barra de estado.
Una vez generada la macro, se pulsa el botón Macros que se encuentra en el grupo Código de la ficha
Programador. Se mostrará el cuadro de diálogo Macro que enlista todas las macros creadas.
Seleccionar la macro recién creada y pulsar el botón Modificar. Esto abrirá el Editor de Visual Basic y mostrará el código generado para la macro.
Observando este código se aprenden varias cosas. Para empezar se observa que el objeto Selection tiene una propiedad llamada Font que es la que hace referencia a la fuente de la celda o rango seleccionado. A su vez, la propiedad Font tiene otra propiedad llamada Color que es precisamente la que define el color rojo de nuestra celda.
Aunque este ha sido un ejemplo muy sencillo, cuando se tenga duda sobre qué objetos utilizar al programar en
LECCION 5 Modelo de objetos de Excel: Colecciones, Objetos, Propiedades, Métodos, Enumeraciones
Elementos
El modelo de objetos de Excel se compone de objetos y colecciones de objetos. Los objetos, como cualquier objeto de Visual Basic se componen de Propiedades, Métodos. Para algunos casos se definen enumeraciones agrupando conjuntos de constantes.
Los objetos en Excel (VBA) son elementos dentro de la hoja de excel. Una celda es un objeto, una hoja es un objeto, un libro es un objeto y de esta manera existen muchos más objetos en Excel. A esto lo conocemos como el modelo de objetos de Excel.
Cada uno de los objetos de Excel tiene propiedades y métodos. Las propiedades son las características del objeto y los métodos son las acciones que el objeto puede ejecutar.
Propiedades de un objeto
Si una persona fuera un objeto de Excel sus propiedades serían el color de sus ojos, el color de su cabello, su estatura, su peso. De la misma manera, un objeto de Excel tiene propiedades por ejemplos, una celda (Range) tiene las propiedades valor (Value) y dirección (Address) entre muchas otras. Estas propiedades describen mejor al objeto.
Métodos de un objeto
Siguiendo con el ejemplo de una persona, si fuera un objeto de Excel sus métodos serían correr, caminar, hablar, dormir. Los métodos son las actividades o acciones que el objeto puede ejecutar. Los objetos de Excel se
comportan de la misma manera que el ejemplo de una persona. Una celda (Range) tiene los métodos activar
(Activate), calcular (Calculate), borrar (Clear) entre muchos más.
Utilizando las propiedades y los métodos
Para acceder a las propiedades y métodos de un objeto se hace a través de una nomenclatura especial. Justo
después del nombre del objeto colocamos un punto seguido del nombre de la propiedad o del método. En este ejemplo se hace uso de la propiedad Value para la celda A1:
Range("A1").Value = "Hola"
De esta manera se asigna una cadena de texto al valor de la celda A1. Ahora bien, si se quiere borrar el valor que se acaba de colocar en la celda se puede utilizar el método Clear de la siguiente manera:
Ver todas las propiedades y métodos
Los objetos tienen muchas propiedades y métodos y a veces es difícil pensar que se conocerán todos por completo.
Sin embargo, el Editor de Visual Basic es de gran ayuda porque justamente al momento de escribir el código
proporciona la lista completa de propiedades y métodos para un objeto.
Esto sucede al momento de introducir el punto después del nombre del objeto. Se puede distinguir entre las propiedades y métodos porque tienen iconos diferentes. En la imagen de arriba los métodos son los que tienen el icono de color verde.
Jerarquía Principal.
Nivel 1. Un objeto Application (representando al Excel) contiene una colección de libros (.xls), objeto Workbooks
Nivel 2. Un objeto Workbook (libro) contiene una colección de Pestañas, objeto Worksheet
Nivel 3. Las celdas de una Pestaña se representan en el objeto Range, el cual tiene dos dimensiones; celda inicial y celda final
Objeto Workbook
Objeto Range
Navegando el Modelo de Objetos
Excel tiene un modelo de objetos el cual es una jerarquía de todos los objetos que se pueden utilizar desde el lenguaje VBA. En la parte superior de la jerarquía se encuentra el objeto Application y todos los demás objetos estarán por debajo de él.
Acceder a objetos inferiores
Para tener acceso a los objetos que están por debajo del objeto Application se puede utilizar el punto. El punto ayuda a navegar por la jerarquía hacia un nivel inferior. Observar lo que se muestra en el Editor de Visual Basic al colocar un punto después del objeto Application:
Por ejemplo, si se desea poner en negrillas el texto de la celda A1 se debe llegar al objeto Range el cual dará acceso a modificar la propiedad Bold de la siguiente manera:
Aunque esta línea de código puede tomar un poco de tiempo en escribirla, pero describe por completo la jerarquía de los objetos.
Objetos predeterminados
Existe una funcionalidad intrínseca de VBA conocida como objetos predeterminados la cual permite omitir la escritura de algunos objetos y aun así tener un código funcional. Por ejemplo, en la instrucción mostrada previamente se puede omitir el objeto Application y tener el código funcionando correctamente:
Inclusive se pueden omitir los objetos ActiveWorkbook y ActiveSheet sabiendo que el código se ejecutará siempre sobre el libro activo y la hoja que esté activa al momento de la ejecución:
Referencias completas a objetos
Se puede utilizar las referencias completas a los objetos, es decir, especificar toda la ruta completa hasta llegar al objeto deseado. Una razón para hacer esto es porque da una claridad absoluta sobre la ubicación exacta de cada objeto lo cual ayudará a evitar cualquier mala interpretación del código.
Si se decides no hacer uso de los objetos predeterminados sino que se desea utilizar las referencias completas hacia cada objeto aún hay una manera de ahorrar algunas líneas de código. Suponga las siguientes instrucciones en VBA:
En ambos casos el resultado será el mismo y en el último se ahorran algunos caracteres dejando el código VBA claro y legible.
LECCION 6 Objeto Excel
El objeto Application
Cuando se escribe código con VBA se trabajamos con múltiples objetos que pueden ejecutar instrucciones adecuadamente, pero el objeto Application está en el nivel más alto de la jerarquía del modelo de objetos de Excel.
El objeto Application simboliza a Excel mismo y da acceso a opciones y configuraciones a nivel de la aplicación. Muchas de las opciones a modificar con el objeto Application son las mismas que se encuentran en la pestaña Archivo dentro del cuadro de diálogo Opciones de Excel.
Ya que el objeto Application es el objeto principal dentro de VBA todos los demás objetos derivan de él. Es por ello que se encontrarán frecuentemente instrucciones que comienzan especificando el objeto Application:
Application.ActiveSheet.Name = "Reporte de Ventas"
Sin embargo, VBA permite, en la mayoría de los casos, omitir la escritura del objeto Application ya que supone que todos los demás objetos provienen de él. De esta manera la siguiente instrucción también es válida.
ActiveSheet.Name = "Reporte de Ventas"
Colecciones del objeto Application
El objeto Application tiene algunas colecciones que son de mucha utilidad como Sheets, Columns y Rows. La colección Sheets permite acceder a todas las hojas de un libro:
Application.Sheets.Count
Es muy importante mencionar que la colección Sheets se referirá al libro de Excel que se encuentre activo en el momento de ejecutar esta instrucción. Las colecciones Columns y Rows permitirán acceder a las columnas y filas de la hoja activa.
Application.Columns(5).Select Application.Rows(5).Select
Propiedades del objeto Application
El objeto Application tiene muchas propiedades como para mencionarles todas en este manual, pero algunas de las más importantes son las siguientes:
ActiveWorkbook. Devuelve un objeto Workbook que representa el libro de Excel activo.
ActiveSheet. Regresa un objeto Worksheet que representa a la hoja que esté actualmente seleccionada (activa).
ActiveCell. Devuelve un objeto Range que representa la celda activa dentro de la hoja activa en el libro de Excel activo.
ThisWorkbook. Esta propiedad devolverá un objeto Workbook que representará el libro que contiene la macro que está siendo ejecutada.Métodos del objeto Application
Uno de los métodos más utilizados del objeto Application es el método InputBox mostrando un cuadro de diálogo
Impresiones = Application.InputBox(Prompt:="Número de impresiones:", _ Title:="Imprimir", Default:=1, Type:=1)
Esta instrucción hará que Excel muestre un cuadro de diálogo pidiendo al usuario ingresar el número de impresiones que desea realizar. El número ingresado por el usuario se guardará en la variable Impresiones.
LECCION 7 Libros
Un objeto en VBA puede contener otro objeto y ese objeto a su vez puede contener otro objeto y así
sucesivamente. La raíz de todos los objetos en VBA se encuentra en el objeto Application el cual a su vez contiene las colecciones de objetos Workbooks.
El objeto Workbook representa un libro de Excel
Un libro de Excel puede tener más de una hoja lo cual significa que un objeto Workbook puede contener más de un objeto Worksheet.
Como no hay límite en el número de hojas que puede tener un libro, se volvería complicado organizar esta relación entre los objetos Workbook y Worksheet y por esta razón se crearon las colecciones de objetos. De esta manera un
objeto Workbook tiene asociada una colección de objetos Worksheets la cual contiene los objetos Worksheet
que representan las hojas de ese libro de Excel.
De la misma manera, el objeto Application no tiene asignados directamente todos los libros de Excel sino que tiene una colección de objetos Workbooks la cual incluirá todos los objetos Workbook de los libros de Excel que se abran en el código VBA.
Abrir un libro de Excel
Para abrir un libro de Excel en VBA se puede utilizar el método Open del objeto Workbooks de la siguiente manera:
Application.Workbooks.Open Filename:="C:Libro1.xlsx"
Esta instrucción abrirá el archivo ubicado en “C:Libro1.xlsx” y lo agregará a la colección de objetos Workbooks. De esta manera se pueden abrir tantos archivos como sean necesarios y para cada uno de ellos se creará un objeto Workbook el cual será almacenado dentro de Workbooks.
Hacer referencia a un libro en VBA
Una vez abiertos los archivos requeridos se puede hacer referencia a cada uno de ellos a través de la colección de objetos Workbooks de la siguiente manera:
Application.Workbooks(1).Activate
El número dentro de los paréntesis indica el índice del objeto Workbook dentro de la colección de objetos Workbooks. De manera predeterminada el índice 1 será para el libro de Excel que contiene el código VBA y a partir de ahí la numeración será de acuerdo al orden en se hayan abierto otros archivos.
Si se conoce el nombre del libro se puede utilizar en lugar del índice y tener una instrucción como la siguiente:
Application.Workbooks("Libro1.xlsx").Activate
La colección de objetos Workbooks permitirá acceder a todos los libros abiertos dentro de la aplicación VBA.
LECCION 8 Hojas
Un objeto en VBA puede contener otro objeto y ese objeto a su vez puede contener otro objeto y así sucesivamente. La raíz de todos los objetos en VBA se encuentra en el objeto Application el cual a su vez contiene las colecciones de objetos Worksheets.
El objeto Worksheet representa una hoja de un libro de Excel. Acceder las hojas de un libro
Se puede acceder las hojas de cualquier libro a través de su colección de objetos Worksheets. Esta colección
también puede ser accedida por el índice de cada una de las hojas del libro: Application.Workbooks(1).Worksheets(1).Range("A1").Value = "Hola"
Esta instrucción accede a la hoja con el índice 1 y coloca el valor “Hola Mundo” en la celda A1. También se puede acceder a una hoja a través de su nombre en caso de conocerlo:
Application.Workbooks(1).Worksheets("Hoja1").Range("A1").Value = "Hola" Agregar una nueva hoja
A través de la colección de objetos Worksheets se pueden crear nuevas hojas en un libro. Observar la siguiente
instrucción: Worksheets.Add
Observar que no se ha iniciado la instrucción anterior con el objeto Application, ni tampoco está precedida por el objeto Workbooks. Esta es una sintaxis aceptable dentro de VBA e indica que se agregará una nueva hoja al libro que esté activo en ese momento. Este es un método abreviado que se puede utilizar si se está seguro de que el libro activo es el libro dondse se agregará una nueva hoja. De lo contrario, se puede especificar tota la ruta completa:
Application.Workbooks("Libro1.xlsx").Worksheets.Add
LECCION 9 Celdas y Rangos
Objeto Range
Representa una celda, una fila, una columna, una selección de celdas que contienen uno o más bloques contiguos de celdas o un rango 3D.
Usar Range(arg), donde arg asigna un nombre al rango, para obtener un objeto Range que represente una sola celda o un rango de celdas. En el ejemplo siguiente se coloca el valor de la celda A1 en la celda A5.
Worksheets("Sheet1").Range("A5").Value = _ Worksheets("Sheet1").Range("A1").Value
En el ejemplo siguiente se rellena el rango A1:H8 con números aleatorios y se establece la fórmula de cada celda del rango. La propiedad Range, si se emplea sin un calificador de objeto (un objeto colocado a la izquierda del punto), devuelve un rango de la hoja activa. Si la hoja activa no es una hoja de cálculo, este método no se llevará a cabo con éxito. Usar el método Activate para activar una hoja de cálculo antes de usar la propiedad Range sin un calificador de objeto explícito.
Worksheets("Sheet1").Activate
Range("A1:H8").Formula = "=Rand()" 'Range is on the active sheet
En el ejemplo siguiente se borra el contenido del rango denominado Criteria.
Nota: Si se usa un argumento de texto para la dirección del rango, se debe especificar la dirección en notación de estilo A1 (no se puede usar la notación de estilo R1C1).
Worksheets(1).Range("Criteria").ClearContents
Usar Cells(fila, columna), donde fila es el índice de la fila y columna es el índice de la columna, para obtener una sola celda. En el siguiente ejemplo se establece en 24 el valor de la celda A1.
Worksheets(1).Cells(1, 1).Value = 24
En el siguiente ejemplo se establece la fórmula de la celda A2.
ActiveSheet.Cells(2, 1).Formula = "=Sum(B1:B5)"
Aunque también se puede usar Range("A1") para devolver la celda A1, es posible que haya veces cuando la propiedad Cells es más conveniente, ya que puede usar una variable de la fila o columna. En el ejemplo siguiente se crea los encabezados de columna y fila de Sheet1. Tener en cuenta que después de activar la hoja de cálculo, la propiedad de las celdas se puede usar sin una declaración de hoja explícitas (devuelve una celda de la hoja activa).
Nota: Aunque podría usar funciones de cadena de Visual Basic para modificar las referencias de estilo A1, es más sencillo (y una mejor práctica de programación) usar la notación Cells(1, 1) .
Sub SetUpTable()
Worksheets("Sheet1").Activate For TheYear = 1 To 5
Cells(1, TheYear + 1).Value = 1990 + TheYear Next TheYear
For TheQuarter = 1 To 4
Cells(TheQuarter + 1, 1).Value = "Q" & TheQuarter Next TheQuarter
End Sub
Usar expresión.Cells(fila, columna), donde expresión es una expresión que devuelve un objeto Range, y fila y columna son relativos a la esquina superior izquierda del rango, para obtener una parte del rango. En el siguiente ejemplo se establece la fórmula para la celda C5.
Worksheets(1).Range("C5:C10").Cells(1, 1).Formula = "=Rand()"
Usar Range(celda1, celda2), donde celda1 y celda2 son objetos Range que especifican la primera y la última celda, para obtener un objeto Range. En el siguiente ejemplo se establece el estilo de línea de los bordes de las celdas A1:J10.
Nota: Tener en cuenta que el período delante de cada aparición de la propiedad Cells. El período es necesario si el resultado de la instrucción con anterior es que se aplicará a la propiedad Cells — en este caso, para indicar que son las celdas de la hoja uno (sin el período, la propiedad Cells devolvería las celdas de la hoja activa).
With Worksheets(1) .Range(.Cells(1, 1), _
.Cells(10, 10)).Borders.LineStyle = xlThick End With
Usar Offset(fila, columna), donde fila y columna son la fila y columna, desplaza, para devolver un intervalo con un desplazamiento especificado al intervalo de otra. En el ejemplo siguiente se selecciona la celda situada tres filas hacia abajo desde y una columna a la derecha de la celda en la esquina superior izquierda de la selección actual. No puede seleccionar una celda que no está en la hoja activa, por lo que debe activar la hoja de cálculo.
Worksheets("Sheet1").Activate
'Can't select unless the sheet is active Selection.Offset(3, 1).Range("A1").Select
Usar Union(rango1, rango2, ...) para devolver rangos de varias áreas, es decir, rangos compuestos por dos o más bloques contiguos de celdas. En el siguiente ejemplo se crea un objeto definido como la unión de los rangos A1:B2 y C3:D4 y, a continuación, se selecciona el rango definido.
Dim r1 As Range, r2 As Range, myMultiAreaRange As Range Worksheets("sheet1").Activate
Set r1 = Range("A1:B2") Set r2 = Range("C3:D4")
Set myMultiAreaRange = Union(r1, r2) myMultiAreaRange.Select
Si se trabaja con selecciones que contienen más de un área, la propiedad Areases útil. Divide una selección de varias áreas en objetos de rango individuales y, a continuación, devuelve los objetos como una colección. Se puede usar la propiedad Count en la colección devuelta para comprobar una selección que contiene más de un área, tal como se muestra en el siguiente ejemplo.
Sub NoMultiAreaSelection()
NumberOfSelectedAreas = Selection.Areas.Count If NumberOfSelectedAreas > 1 Then
MsgBox "You cannot carry out this command " & _ "on multi-area selections"
End If End Sub
LECCION 10 Manejo de gráficos
A continuación se muestra un ejemplo de cómo crear gráficos en Excel con VBA. En las siguientes lecciones se explicarán los elementos por separado.
Crear gráficos personalizados puede ser una tarea práctica y sencilla si se conoce como utilizar el objeto Chart y
ChartObject, al igual que sus métodos y propiedades.
Inicialmente en Excel solo se podían crear hojas de gráficos (Chart), es decir un gráfico se situaba en toda una hoja algo similar a lo que sucede cuando se presiona la tecla F11 sobre un listado de datos, inmediatamente se crea una hoja de grafico de los datos con el tipo de grafico por defecto. Después Excel incorporo la posibilidad de tener objetos de gráficos dentro de una hoja existente (ChartObject).
En esta lección se muestra como crear un gráfico, manipular el área de gráfico, el área de trazado y las series de un objeto grafico (ChartObject), al igual que algunas de sus propiedades. También se verá cómo crear un gráfico circular con un subgráfico de barras que agrupe porcentajes menores a un valor establecido y por último como exportar un gráfico como una imagen.
La ventaja de crear gráficos y exportarlos como imagen es que se puede utilizar dichas imágenes exportadas para poderlas cargas en un formulario, poderlas visualizar en páginas web y otras posibilidades más…
Option Explicit
‘Creacion de un grafico desde VBA Sub Crear_Grafico()
Dim ws As Worksheet Dim migrafico As ChartObject Set ws = ActiveSheet
With ActiveChart ‘en la hoja de gafico activa…
.SetSourceData ws.Range(“A1″).CurrentRegion ‘establecer el origen de los datos a graficar
.Location xlLocationAsObject, ws.Name ‘mover a la hoja1, con lo cual pasa de objeto Chart a ChartObject End With
Set migrafico = ws.ChartObjects(1) ‘se crea una referencia With migrafico
.Width = 300 ‘ancho .Height = 150 ‘alto
.Left = 180 ‘distancia con la parte izquierda de la hoja .Top = 10 ‘distancia con la parte superior de la hoja
.Chart.Legend.Position = xlLegendPositionBottom ‘poner leyenda en la parte inferior del grafico End With
End Sub
‘Modifica el area de grafico (ChartArea) del grafico activo Sub Area_Grafico()
Dim ws As Worksheet Dim grafico As Chart Dim area As ChartArea
Set ws = ActiveSheet ‘hoja activa
Set grafico = ws.ChartObjects(1).Chart ‘grafico 1 Set area = grafico.ChartArea ‘area de grafico With area .Shadow = True .Select .Border.ColorIndex = 3 .Border.Weight = xlMedium .Interior.ColorIndex = 34 .AutoScaleFont = False .Font.Size = 14 End With End Sub
‘Modifica el area de trazado (PlotArea) del grafico activo Sub Area_Trazado()
Dim ws As Worksheet Dim grafico As Chart Dim trazado As PlotArea Set ws = ActiveSheet
Set grafico = ws.ChartObjects(1).Chart Set trazado = grafico.PlotArea
With trazado .Top = 20 .Left = 35 .Height = 100 .Width = 200 End With End Sub
‘Modificar las series del grafico Sub Series()
Dim ws As Worksheet Dim grafico As Chart Dim serie As Series Set ws = ActiveSheet
Set grafico = ws.ChartObjects(1).Chart Set serie = grafico.SeriesCollection(“Xdata”)
With serie .ChartType = xlLine .Border.Weight = xlThick .MarkerStyle = xlMarkerStyleCircle .MarkerBackgroundColorIndex = xlColorIndexAutomatic .MarkerForegroundColorIndex = xlColorIndexAutomatic .MarkerSize = 10 End With End Sub
‘Crea un grafico circular con subgrafico de barras (agrupa todos los porcentajes < 5%) Sub Grafico_Circular()
Dim ws As Worksheet Dim rng As Range Dim grafico As Chart Dim grupo As ChartGroup
Set ws = ThisWorkbook.Sheets(“Hoja2″)
Set rng = ws.Range(“A1″).CurrentRegion Set grafico = Charts.Add
With grafico
.ChartType = xlBarOfPie ‘tipode grafico .SetSourceData rng ‘fuente de datos
.ApplyDataLabels xlDataLabelsShowPercent ‘mostrar etiquetas de porcentaje Set grupo = .ChartGroups(1)
With grupo
.SplitType = xlSplitByPercentValue .SplitValue = 5 ‘menor que 5% combinado
.GapWidth = 200 ‘espacio entre el grafico circular y las barras .SecondPlotSize = 55 ‘% relativo al tamaño del circulo End With
.Location xlLocationAsObject, ws.Name ‘establecer como objeto de grafico End With
End Sub
‘Exportar un grafico como imagen Sub Exporta_Grafico()
Dim ws As Worksheet Dim grafico As Chart
Set ws = ThisWorkbook.Sheets(“Hoja2″)
Set grafico = ws.ChartObjects(1).Chart
grafico.Export “C:\grafico.gif”, filtername:=”GIF” ‘se exporta el grafico a la unidad C como imagen gif End Sub
LECCION 11 Colecciones para el manejo de gráficos
Conjunto Axes
Usar el método Axes para devolver el conjunto Axes. El siguiente ejemplo muestra el número de ejes del gráfico. With myChart
MsgBox .Axes.Count End With
Utilizar Axes(type, group), donde type es el tipo de eje y group es el grupo de ejes, para devolver un solo objeto Axis.
Type puede ser una de las siguientes opciones
XlAxisType puede ser una de estas constantes XlAxisType.
xlCategory xlSeries xlValue
Grupo puede ser cualquiera de las siguientes constantes XlAxisGroup : xlPrimary o xlSecondary. El siguiente ejemplo establece el texto del título del eje de categorías.
With myChart.Axes(xlCategory) .HasTitle = True
.AxisTitle.Caption = "1994" End With
Conjunto ChartGroups
Una colección de todos los objetos ChartGroup del gráfico especificado. Cada objeto ChartGroup representa una o varias series trazadas con el mismo formato de un gráfico. Un gráfico contiene uno o varios grupos de gráficos, cada grupo de gráficos contiene una o varias series y cada serie contiene uno o varios puntos. Por ejemplo, un único gráfico puede contener un grupo de gráficos de línea, que contiene todas las series trazadas con el formato de gráfico de línea y en un grupo de gráficos de barras, que contiene todas las series trazadas con el formato de un gráfico de barras.
Usar el método ChartGroups para devolver el conjunto ChartGroups. El siguiente ejemplo muestra el número de
grupos de gráficos en el gráfico. MsgBox myChart.ChartGroups.Count
Para devolver un solo objeto ChartGroup, utilizar ChartGroups(index), donde index es el número de índice del grupo de gráficos. El siguiente ejemplo agrega líneas de unión al grupo de gráficos uno del gráfico.
myChart.ChartGroups(1).HasDropLines = True
Dado que el número de índice de un grupo de gráficos determinado puede cambiar si se modifica el formato de gráfico del grupo, puede ser conveniente utilizar uno de los métodos abreviados para grupos de gráficos para
devolver un grupo de gráficos determinado. El método PieGroups devuelve el conjunto de grupos de gráficos
circulares del gráfico especificado, el método LineGroups devuelve el conjunto de grupos de gráficos de líneas, etc. Cada uno de estos métodos se puede utilizar con un número de índice para devolver un
objeto ChartGroup individual, o sin número de índice para devolver un conjunto ChartGroups. Existen los siguientes métodos para grupos de gráficos:
AreaGroups
BarGroups
ColumnGroups
DoughnutGroups
LineGroups
PieGroups Conjunto DataLabelsUna colección de todos los objetos DataLabel para la serie especificada. Cada objeto DataLabel representa un rótulo de datos para una línea de tendencia o un punto. Para obtener una serie sin puntos definidos (por ejemplo, una serie de área), el conjunto DataLabels contiene un único rótulo de datos.
Usar el método DataLabels para devolver el conjunto DataLabels. El siguiente ejemplo establece el formato numérico de los rótulos de datos de la serie uno del gráfico.
.HasDataLabels = True
.DataLabels.NumberFormat = "##.##" End With
Para devolver un solo objeto DataLabel, utilizar DataLabels(index), donde index es el número de índice del rótulo de datos. El siguiente ejemplo establece el formato numérico del quinto rótulo de datos de la serie uno del gráfico. myChart.SeriesCollection(1).DataLabels(5).NumberFormat = "0.000"
HiLoLines
Representa las líneas de máximos y mínimos del grupo de gráficos especificado. Las líneas de máximos y mínimos conectan el punto máximo con el mínimo en todas las categorías del grupo de gráficos. Sólo pueden tener líneas de máximos y mínimos los grupos de líneas bidimensionales. Este objeto no es un conjunto. Ningún objeto representa una línea de máximo y mínimo individual; este tipo de líneas se activan o se desactivan para todos los puntos de un grupo de gráficos.
Usar la propiedad HiLoLines para devolver el objeto HiLoLines. El siguiente ejemplo asigna el color azul a las líneas de máximos y mínimos del grupo de gráficos uno.
myChart.ChartGroups(1).HiLoLines.Border.Color = RGB(0, 0, 255) Conjunto LegendEntries
Una colección de todos los objetos de LegendEntry de la leyenda del gráfico especificado. Cada entrada de leyenda consta de dos partes: el texto de la entrada, que es el nombre de la serie o línea de tendencia asociada con la entrada; y el marcador de entrada, que vincula visualmente la entrada de leyenda con su serie o línea de tendencia en el gráfico. Las propiedades de formato para el marcador de entrada y su serie o línea de tendencia se encuentran en el objeto LegendKey.
Usar el método LegendEntries para devolver el conjunto LegendEntries. El siguiente ejemplo ejecuta un ciclo en el conjunto de elementos de leyenda del gráfico y cambia a azul el color de la fuente.
With myChart.Legend
For i = 1 To .LegendEntries.Count .LegendEntries(i).Font.ColorIndex = 5 Next
End With
Para devolver un solo objeto LegendEntry, utilizar LegendEntries(index), donde index es el número de índice del elemento de leyenda. No es posible devolver entradas de leyenda por nombre.
El número de índice representa la posición de la entrada de leyenda en la leyenda. LegendEntries(1)está en la parte superior de la leyenda; LegendEntries(LegendEntries.Count)está en la parte inferior.
En el ejemplo siguiente se cambia el estilo de fuente en cursiva el texto de la entrada de leyenda en la parte superior de la leyenda (generalmente es la leyenda para la serie uno) en myChart.
myChart.Legend.LegendEntries(1).Font.Italic = True Conjunto Points
Una colección de todos los objetos Point en la serie especificada de un gráfico.
Usar el método Points para devolver el conjunto Points. En el siguiente ejemplo se agrega un rótulo de datos al último punto de la serie uno del gráfico.
Dim pts As Points
Set pts = myChart.SeriesCollection(1).Points pts(pts.Count).ApplyDataLabels Type:=xlShowValue
Usar puntos(índice), donde index es el número de índice del punto, para devolver un solo objeto de punto. Puntos se numeran de izquierda a derecha de la serie. Points(1) es el punto del extremo izquierdo y es el punto del extremo izquierdo y Points(Points.Count) es el punto más a la derecha. En el ejemplo siguiente se establece el estilo del marcador para el tercer punto de la serie uno del gráfico. La serie especificada debe ser una línea de 2D, dispersión o serie de radiales.
myChart.SeriesCollection(1).Points(3).MarkerStyle = xlDiamond
Conjunto SeriesCollection
Usar el método SeriesCollection para devolver el conjunto SeriesCollection. En el siguiente ejemplo se ajusta el color interior de cada serie del conjunto:
For X = 1 To myChart.SeriesCollection.Count With myChart.SeriesCollection(X)
.Interior.Color = RGB(X * 75, 50, X * 50) End With
Next X
Para devolver un solo objeto Series, utilizar SeriesCollection(index), donde index es el nombre o número de índice de la serie. En el siguiente ejemplo se establece en rojo el color interior de la serie uno del gráfico.
myChart.SeriesCollection(1).Interior.Color = RGB(255, 0, 0) Conjunto TrendLines
Una colección de todos los objetos Trendline para la serie especificada. Cada objeto de la línea de
tendencia representa una línea de tendencia de un gráfico. Una línea de tendencia muestra la tendencia, o la dirección de los datos en una serie.
Utilizar el método Trendlines para devolver el conjunto Trendlines. En el siguiente ejemplo se muestra el número de líneas de tendencia de la serie uno del gráfico.
MsgBox myChart.SeriesCollection(1).Trendlines.Count
Usar el método Add para crear una nueva línea de tendencia y agregarla a la serie. En el ejemplo siguiente se agrega una línea de tendencia lineal a la serie uno del gráfico.
With myChart.SeriesCollection(1).Trendlines .Add Type:=xlLinear, Name:="Linear Trend" End With
Para devolver un solo objeto TrendLine, utilizar Trendlines(index), donde index es el número de índice de la línea de tendencia. En el siguiente ejemplo se cambia el tipo de línea de tendencia de la serie uno del gráfico. Si la serie no tiene línea de tendencia, este ejemplo dará error.
myChart.SeriesCollection(1).Trendlines(1).Type = xlMovingAvg
El número de índice representa el orden en que se agregan líneas de tendencia a la serie. Trendlines(1) es la primera línea de tendencia agregado a la serie y se agrega de la primera línea de tendencia a la serie y Trendlines(Trendlines.Count) es el último agregado.
LECCION 12 Objetos para el manejo de gráficos
Objeto Chart
Representa el gráfico Microsoft Graph especificado.
Utilizar la propiedad Chart para devolver el objeto Chart. La mayoría de las veces, se creará una referencia a un gráfico de Microsoft Graph y después se utilizará la referencia en el código.
Objeto PlotArea
Representa el área de trazado del gráfico especificado. Es el área en que se trazan los datos de un gráfico. El área de trazado de un gráfico 2D contiene los marcadores de datos, rótulos de datos, líneas de división y elementos opcionales del gráfico situados en el área de gráfico. El área de trazado de un gráfico 3D contiene los elementos anteriores más los planos inferior y laterales, ejes, títulos de los ejes y rótulos de las marcas de graduación del gráfico.
El área de trazado está rodeado por el área del gráfico. El área del gráfico en un gráfico 2D contiene los ejes, el título del gráfico, los títulos de los ejes y la leyenda. El área del gráfico en un gráfico 3D contiene el título del gráfico y la leyenda.
Utilizar la propiedad PlotArea para devolver un objeto PlotArea. El siguiente ejemplo coloca un borde discontinuo alrededor del área de gráfico y un borde punteado alrededor del área de trazado.
With myChart
.ChartArea.Border.LineStyle = xlDash .PlotArea.Border.LineStyle = xlDot End With
Objeto Series
Representa una serie del gráfico especificado. Objeto Series es un miembro de la colección SeriesCollection Para devolver un solo objeto Series, utilizar SeriesCollection(index), donde index es el nombre o número de índice de la serie. En el siguiente ejemplo se establece el color interior de la serie uno del gráfico.
myChart.SeriesCollection(1).Interior.Color = RGB(255, 0, 0)
El número de índice de la serie indica el orden en que las series se agregan al gráfico. SeriesCollection(1) es la primera serie que se agregan a la tabla, se agrega de la primera serie al gráfico
y SeriesCollection(SeriesCollection.Count) es el último agregado.
Objeto SeriesLine
Representa las líneas de serie del grupo de gráficos especificado. Las líneas de serie conectan los valores de datos de cada serie. Sólo pueden tener líneas de serie los grupos de gráficos de columnas o barras 2D apiladas. Este objeto no es un conjunto. Ningún objeto representa una línea de serie individual; las líneas de serie se activan o se desactivan para todos los puntos de un grupo de gráficos.
Utilizar la propiedad SeriesLines para devolver el objeto SeriesLines. En el siguiente ejemplo se agregan líneas de serie al grupo de gráficos uno del gráfico. El gráfico debe ser 2D de barras o columnas apiladas.
With myChart.ChartGroups(1) .HasSeriesLines = True
.SeriesLines.Border.Color = RGB(0, 0, 255) End With
Objeto Point
Representa un único punto de una serie del gráfico especificado. El objeto de punto es un miembro de la colección Points, que contiene todos los puntos de la serie especificada.
Usar puntos(índice), donde index es el número de índice del punto, para devolver un solo objeto
de punto . Puntos se numeran de izquierda a derecha de la serie. Points(1) es el punto del extremo izquierdo y es el punto del extremo izquierdo y Points(Points.Count) es el punto más a la derecha. En el ejemplo siguiente se establece el estilo del marcador para el tercer punto de la serie uno. Para que este ejemplo funcione, la serie uno debe ser una línea de 2D, dispersión o serie de radiales.
myChart.SeriesCollection(1).Points(3).MarkerStyle = xlDiamond
Objeto ChartTitle
Representa el título del gráfico especificado.
Usar la propiedad ChartTitle para devolver el objeto ChartTitle. El siguiente ejemplo agrega un título al gráfico.
With myChart .HasTitle = True
.ChartTitle.Text = "February Sales" End With
LECCION 13 Propiedades para el manejo de Gráficos
Propiedad ChartType
Devuelve o establece el tipo de gráfico. XlChartType de lectura y escritura. XlChartType puede ser una de estas constantes XlChartType.
xl3DArea. Área 3D
xl3DAreaStacked. Área 3D apilada
xl3DAreaStacked100. Área 3D apilada
xl3DBarStacked. Barras 3D apiladas
xl3DBarStacked100. Barras 3D 100% apiladas
xl3DColumn. Columnas 3D
xl3DColumnClustered. Columnas 3D agrupadas
xl3DColumnStacked. Columnas 3D apiladas
xl3DColumnStacked100. Columnas 3D 100% apiladas
xl3DLine. Líneas 3D
xl3DPie. Circular 3D
xl3DPieExploded. Circular 3D seccionado
xlArea. Área
xlAreaStacked. Área apilada
xlAreaStacked100. Área apilada 100%
xlBarClustered. Barra agrupada
xlBarOfPie. Circular con subgráfico de barras
xlBarStacked. Barra apilada
xlBarStacked100. Barra apilada 100%
xlBubble. Burbuja
xlBubble3DEffect. Burbujas con efectos 3D
xlColumnClustered. Columna agrupada
xlColumnStacked. Columna apilada
xlColumnStacked100. Columna apilada 100%
xlConeBarClustered. Barra cónica agrupada
xlConeBarStacked. Barra cónica apilada
xlConeBarStacked100. Barra cónica apilada 100%
xlConeCol. Columna 3D cónica
xlConeColStacked. Columna cónica apilada
xlConeColStacked100. Columna cónica apilada 100%
xlCylinderBarStacked. Barra cilíndrica apilada
xlCylinderCol. Columna 3D cilíndrica
xlCylinderColStacked. Columna cilíndrica apilada
xlCylinderBarClustered. Barra cilíndrica agrupada
xlCylinderBarStacked100. Barra cilíndrica apilada 100%
xlCylinderColClustered. Columna cilíndrica agrupada
xlCylinderColStacked100. Columna cilíndrica apilada 100%
xlDoughnut. Anillos
xlDoughnutExploded. Anillos seccionado
xlLineMarkers. Líneas con marcadores
xlLineMarkersStacked100. Línea apilada 100% con marcadores
xlLineStacked100. Línea apilada 100%
xlLine. Línea
xlLineMarkersStacked. Líneas apilada con marcadores
xlLineStacked. Línea apilada
xlPie. Circular
xlPieExploded. Circular seccionado
xlPieOfPie. Circular con subgráfico circular
xlPyramidBarClustered. Barra piramidal agrupada
xlPyramidBarStacked. Barra piramidal apilada
xlPyramidBarStacked100. Barra piramidal apilada 100%
xlPyramidCol. Columna piramidal 3D
xlPyramidColStacked. Columna piramidal apilada
xlPyramidColStacked100. Columna piramidal apilada 100%
xlRadar. Radial
xlRadarFilled. Radial relleno
xlRadarMarkers. Radial con marcadores de datos
xlStockHLC. Máximos, mínimos, cierre
xlStockOHLC. Apertura, máximos, mínimos, cierre
xlStockVHLC. Volumen, máximos, mínimos, cierre
xlStockVOHLC. Volumen, apertura, máximos, mínimos, cierre
xlSurface. Superficie 3D
xlSurfaceTopView. Superficie (vista superior)
xlSurfaceTopViewWireframe. Superficie (estructura metálica vista superior)
xlSurfaceWireframe. Superficie 3-D (estructura metálica)
xlXYScatter. Dispersión
xlXYScatterLines. Dispersión con líneas
xlXYScatterLinesNoMarkers. Dispersión con líneas y sin marcadores de datos
xlXYScatterSmooth. Dispersión con líneas suavizadas
xlXYScatterSmoothNoMarkers. Dispersión con líneas suavizadas y sin marcadores de datos
expression.ChartType
expresión Requerida. Expresión que devuelve uno de los objetos de la lista Aplicar a.
En este ejemplo se establece el tamaño de la burbuja del grupo de gráficos uno en un 200% del tamaño predeterminado si el gráfico es una burbuja 2D.
With myChart
If .ChartType = xlBubble Then .ChartGroups(1).BubbleScale = 200 End If
End With
Propiedad Font
Devuelve una fuente de objeto que representa la fuente del objeto especificado. Objeto de fuente de
lectura/escritura solo para el objeto de la hoja de datos, para todos los demás objetos, objeto Font de sólo lectura.
expression.Font
expresión Requerida. Una expresión que devuelve uno de los objetos arriba mencionados. En este ejemplo se establece como cursiva y negrita de 14 puntos la fuente del título del gráfico.
.Size = 14 .Bold = True .Italic = True End With Propiedad ScaleType
Devuelve o establece el tipo de escala del eje de valor. Se aplica sólo para el eje de valores. XlScaleType de lectura y escritura.
XlScaleType puede ser una de estas constantes XlScaleType.
xlScaleLinear xlScaleLogarithmic
expression.ScaleType
expresión Requerida. Expresión que devuelve uno de los objetos de la lista Aplicar a.
LECCION 14 Métodos para el Manejo de Gráficos
Método Chart
Devuelve un objeto Chart que representa el gráfico de Microsoft Graph. Método SeriesCollection
Devuelve un objeto que representa una sola serie o un conjunto de series del gráfico o grupo de gráficos.
expression.SeriesCollection(Index)
expresión Requerida. Expresión que devuelve uno de los objetos de la lista Aplicar a.
Index Variant opcional.El nombre o número de la serie. En este ejemplo se activan las rótulos de datos en la serie uno.
myChart.SeriesCollection(1).HasDataLabels = True Método DataLabels
Devuelve un objeto que representa un único rótulo de datos o un conjunto de rótulos de datos para la serie.
expression.DataLabels(Index)
expresión Requerida. Expresión que devuelve uno de los objetos de la lista Aplicar a.
Index Variant opcional.El número del rótulo de datos.
En este ejemplo se configuran los rótulos de datos de la serie uno para que muestren la leyenda, suponiendo que sus valores estén visibles cuando se ejecute el ejemplo.
With myChart.SeriesCollection(1) .HasDataLabels = True With .DataLabels .ShowLegendKey = True .Type = xlValue End With End With
LECCION 15 Controles, Cuadros de Diálogo y Formularios
Los formularios en VBA no son más que un cuadro de diálogo de Excel donde se puede colocar controles que permitirán a solicitar información del usuario. Se puede colocar cajas de texto, etiquetas, cuadros combinados, botones de comando, etc.
Los formularios de Excel son creados desde el Editor de Visual Basic. El editor de los formularios se muestra a continuación:
LECCION 16 Crear Formularios
Un formulario en VBA es un cuadro de diálogo donde se agregan diversos tipos de controles como botones de comando, cajas de texto, casillas de verificación, y otros controles más que serán de utilidad para obtener información de un usuario ya sea para procesarla y devolver algún resultado o simplemente almacenarla. Los formularios permiten crear aplicaciones en Excel para extender el uso y funcionalidad de la herramienta y aunque su uso está fuertemente relacionado con los usuarios avanzados de Excel, no es tan complicado utilizarlos. Para agregar un nuevo formulario en la Pestaña Programador, seleccionar Visual Basic y se mostrará el editor de VBA . Se realiza clic derecho sobre VBA Project y se selecciona la opción Insertar > UserForm:
De inmediato el panel principal mostrará el formulario recién creado así como un pequeño cuadro de diálogo con el título Cuadro de herramientas el cual contiene los controles que se pueden agregar al formulario.
El Cuadro de herramientas tiene un botón para cada tipo de control como etiqueta, cuadro de texto, cuadro
combinado, cuadro de lista, casilla de verificación, botón de opción, etc. Para agregar un control al formulario es necesario hacer clic en el botón correspondiente dentro del Cuadro de herramientas y posteriormente hacer clic dentro del formulario. Otra manera de agregar un control al formulario es pulsar el botón del control y arrastrarlo sobre el formulario.
LECCION 17 Agregar Controles a Formularios
Como ejemplo se agregará una etiqueta, un cuadro de texto, un cuadro de lista y un botón de comando de manera que el formulario quede de la siguiente manera:
Para cambiar el texto de la etiqueta y del botón de comando se debe modificar la propiedad llamada Caption y eso se realiza desde la ventana Propiedades. Si esta ventana no se muestra actualmente en el Editor de Visual Basic se puede seleccionar desde el menú Ver o también se puede hacer clic derecho sobre alguno de los controles y seleccionar la opción Propiedades lo cual mostrará la ventana:
Al lado derecho de la propiedad se ingresa el valor de dicha propiedad para el control seleccionado. En la imagen anterior se están viendo las propiedades de la etiqueta llamada Label1 y por ejemplo se coloca el
texto Temperatura. Para hacer el cambio de esta propiedad para el botón de comando solo se debes seleccionar de la lista desplegable que se encuentra en la parte superior de la misma ventana Propiedades. Al terminar el cambio de la propiedad Caption se tendrá el formulario de la siguiente manera:
En la imagen anterior se puedes notar que también se cambió en el título del formulario y se puedes hacer modificando también la propiedad Caption del formulario mismo.
LECCION 18 Programar eventos de controles en Formularios
Agregar opciones al cuadro de lista
Un último cambio que se hará a los controles es agregar los valores al cuadro de lista. Para el ejemplo solo se necesitan los valores Celsius y Fahrenheit pero no existe una propiedad del control que permita hacerlo sino que se debe utilizar código VBA. Para especificar estas opciones se debe hacer doble clic sobre el formulario y seleccionar el código para el procedimiento Initialize donde colocaremos las instrucciones necesarias para agregar las opciones. El procedimiento Initialice captura el evento de arranque del formulario cuando este se muestra:
La instrucción AddItem es la que permite agregar un nuevo elemento al cuadro de lista y la instrucción ListIndex permite indicar el valor que estará seleccionado al cargarse el formulario y que será la opción Celsius ya que es la primera opción de la lista y por lo tanto tiene asociado el índice cero.
Código del botón de comando
Finalmente en el ejemplo se agrega código VBA al botón de comando que será el encargado de hacer la conversión, solo se realiza doble clic sobre el botón para mostrar la ventana de código asociada. El código que se coloca en el procedimiento Click es el siguiente:
Private Sub CommandButton1_Click()
'Validar que se ha especificado una temperatura If TextBox1.Value = "" Then
MsgBox "Debe especificar una temperatura" Exit Sub
End If
'Si es Celsius convertir a Fahrenheit If ComboBox1.ListIndex = 0 Then
temperatura = TextBox1.Value * 1.8 + 32 MsgBox temperatura & " Fahrenheit" 'Si es Fahrenheit convertir a Celsius Else
temperatura = (TextBox1.Value - 32) * 5 / 9 MsgBox temperatura & " Celsius"
End If End Sub
La primera validación dentro del código verifica que el cuadro de texto no esté vacío en cuyo caso desplegará un mensaje solicitando ingresar una temperatura y posteriormente sale de la subrutina. La segunda validación es para saber si el cuadro de lista tiene seleccionada la opción Celsius y por lo tanto hacer la conversión a Fahrenheit para mostrar el resultado. Por el contrario, si el cuadro de lista tiene seleccionada la opción Fahrenheit se hará la conversión a Celsius. Para probar el funcionamiento del formulario es suficiente con pulsar el botón Ejecutar que se encuentra en la barra de herramientas Estándar:
Una vez terminada la construcción del formulario y el código necesario para su funcionamiento, se debe dar un paso adicional para llamar el formulario desde una hoja de Excel.
Cargar formulario desde una hoja de Excel
Para cargar un formulario desde una hoja de Excel será suficiente con agregar un botón de comando ActiveX desde la ficha Programador:
Al hacer doble clic sobre el botón se mostrará el código asociado al evento Click del botón y se debe colocar una sola instrucción:
Con esto será suficiente para que al momento de pulsar el botón de la hoja de Excel se muestre el formulario VBA recién creado y se pueda realizar la conversión de temperaturas.
LECCION 19 Novedades en Aplicaciones para Office en Excel 2013 : Nuevas funciones de hoja de cálculo, Análisis Rápido, Disponibilidad de nuevos orígenes de datos, Uso de los modelo OM, Creación de gráficos dinámicos y tablas dinámicas de forma independiente, Interfaz de un único documento, Las animaciones de encendido y apagado mediante programación, Escala de tiempo
Si se desarrolla soluciones de libro personalizadas, se pueden usar las siguientes mejoras de programabilidad y características en Microsoft Excel 2013
Aplicaciones para Office en Excel
Microsoft Excel 2013 aporta la web a Excel. Nuevas aplicaciones para Office ofrecen una gran cantidad de nuevos escenarios y soluciones en un entorno de fácil de usar para el desarrollo de aplicaciones web dinámicas. Esto significa que ahora se pueden usar populares lenguajes dinámicos (por ejemplo, Python, PHP, Perl y JavaScript) y las herramientas de desarrollo de web (por ejemplo, Microsoft Visual Studio 2012) para el desarrollo de
aplicaciones. El desarrollo de Web también permite usar las comunidades de Desarrollador global y la disponibilidad de varios ejemplos de código, frameworks y bibliotecas, que amplían la utilización de la aplicación para los
diferentes escenarios. Se pueden crear dos tipos de aplicaciones para Office en Excel, aplicaciones de contenido y aplicaciones del panel de tareas.
Aplicaciones de Contenido para Office integran las características basadas en web como contenido que se puede mostrar en conjunto con el documento. Aplicaciones de contenido para Office permiten integrar visualizaciones de datos basada en web; escenarios de multimedia incrustados, como la integración de un reproductor de vídeo de YouTube o una galería de imágenes así como otras funciones de contenido externos. La siguiente figura muestra una implementación de una aplicación de contenido en Excel 2013.
Applicaciones del panel de tareas para Office permite trabajar en-paralelo con un documento de Office y dejar que los profesionales desarrolladores de Office proporcionen información contextual y funcionalidad para mejorar la visualización y experiencia de creación de documentos. Por ejemplo, una aplicación de panel de tareas para Office puede buscar y recuperar información sobre el producto desde un servicio web basado en el nombre del producto o número de parte según una selección del usuario en el documento. En la siguiente figura, se muestra un ejemplo.
Nuevas funciones de hoja de cálculo
Microsoft Excel 2013 agrega cincuenta nuevas funciones de hoja de cálculo para la compatibilidad con el formato de documento abierto (ODF 1.2).
Y, ahora se pueden usar las funciones de servicio de web (en la categoría de las funciones de Web ) para acceder a los servicios de Web de REST de forma anónima. De especial atención es la función FILTERXML, que permite utilizar expresiones XPath para filtrar el XML devuelto por una llamada a función WEBSERVICE.
Análisis rápido
Análisis rápido, tal como se muestra en la siguiente figura, es una nueva herramienta de la interfaz de usuario contextual que permite el acceso con un solo clic a las características de análisis de datos, como las fórmulas, formato condicional, minigráficos, tablas, gráficos y tablas dinámicas. Se puede habilitar y deshabilitar la presentación de la nueva característica de análisis rápido en Excel 2013 mediante programación utilizando los métodos Show y Hide del objeto QuickAnalysis .
Disponibilidad de nuevos orígenes de datos
Se puede conectar a un conjunto de nuevos orígenes de datos compatibles con PowerPivot, incluyendo OData fuentes, Azure, fuentes de datos de SharePoint y los proveedores de OLE DB adicionales. Los nuevos
objetos DataFeedConnection, ModelConnection, TextConnection y WorksheetDataConnection contienen datos y la funcionalidad necesaria para conectarse a orígenes de datos nuevos.
Uso de los modelos OM
Un nuevo modelo de objetos de DataModel (una adición al existente de Visual Basic para el modelo de objetos de aplicaciones) permite cargar y actualizar los orígenes de datos mediante programación. Esta extensión para el modelo de PowerPivot introducida en Excel 2010 permite integrar orígenes de datos adicionales y la capacidad de combinar datos de varios orígenes de datos mediante programación. Los nuevos objetos en este modelo incluyen