Creación de gráficos en VBA/Excel a partir de la versión 2007

Estándar

Hola a todos, las estadísticas de mi blog me reportan que este es el post más visitado: “Creación de gráficos con VBA/Excel”. El código de ese post va dirigido a Excel 2003 y actualmente ya estamos en la versión 2013. Actualmente yo utilizo la versión 2010, que es la que utilizan en la empresa donde laboro, espero el próximo año migren a la nueva versión la cual trae nuevas funcionalidades. Pero bien para no salirnos del tema, ahora les voy a mostrar cómo crear y manipular gráficos para versiones superiores a la 2003.

Si retrocedemos en el tiempo hasta la creación de Excel, sabremos que inicialmente los gráficos se creaban en su propia hoja de gráfico y que no fue sino hasta mediados de los 90, que Excel brindo la oportunidad de agregar un gráfico directamente en una hoja de cálculo.

Estas dos formas de trabajar los gráficos hicieron indispensable trabajar con dos modelos de objetos diferentes, el objeto Chart y ChartObject. En Excel 2007 se introdujo una tercera y novedosa forma que explica que los objetos de una hoja de cálculo son miembros de la colección Shapes (si la de las autoformas y objetos incrustados).

En Excel 2003, si el grafico pertenecía a una hoja y queríamos referenciar el color del área del mismo, teníamos que proceder de la siguiente manera:


Whorksheets(“Enero”).ChartObjects(“Chart 1”).Chart.ChartArea.Interior.ColorIndex = 4

A partir de Excel 2007 podemos utilizar la colección Shapes:


Whorksheets(“Enero”).Shapes(“Chart 1”).Chart.ChartArea.Interior.ColorIndex = 4

En cualquier versión de Excel, si un gráfico está en su propia hoja de gráfico, no es necesario especificar el contenedor, solo hay que referirse al objeto Chart:


Sheets(“Chart 1”).ChartArea.Interior.ColorIndex = 4

Más adelante estaré publicando ejemplos de código al respecto.

Anuncios

Creación de gráficos con VBA/Excel

Estándar

Crear gráficos personalizados puede ser una tarea práctica y sencilla si sabemos utilizar el objeto Chart y ChartObjetc, al igual que sus métodos y propiedades.

Inicialmente en Excel solo se podían crear hojas de gráficos (Chart), es decir un grafico 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 este tutorial se muestra como crear un grafico, manipular el área de grafico, 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 grafico circular con un subgrafico de barras que agrupe porcentajes menores a un valor establecido y por último como exportar un grafico como una imagen.

La ventaja de crear gráficos y exportarlos como imagen es que podemos utilizar dichas imágenes exportadas para poderlas cargas en un formulario, poderlas visualizar en páginas web y otras posibilidades mas…


Option Explicit

‘Creacion de un grafico desde VBA
Sub Crear_Grafico()
Dim ws As Worksheet
Dim migrafico As ChartObject

Set ws = ActiveSheet
Charts.Add ‘Se crea una hoja de grafico

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

Aqui se puede ver la aplicacion del codigo anterior: Graficos

Aqui anexo un video: Video Explicativo