Crear y modificar gráficos con VBA
De la página excelyvba.com les dejo este interesante y útil artículo:
En el ejemplo de hoy vamos a enseñaros como crear gráficos con VBA y modificarlos usando también VBA. Las herramientas que ofrece Excel en cuanto a manipulación de gráficos son bastante completas. Sin embargo, mediante VBA, podemos sacar mucho más provecho a estos gráficos especialmente en cuanto a modificación dinámica se refiere.
En este artículo vamos a explicar cómo crear un gráfico mediante VBA, seleccionar el tipo de gráfico, añadir datos y modificar el formato.
Para demostrar estas posibilidades que nos ofrece VBA hemos creado un ejemplo con varias series de datos.
Contenido [Ocultar]
- 1 Datos iniciales:
- 2 Crear gráficos con VBA
- 3 Seleccionar un gráfico existente
- 4 Añadir una serie de datos a un gráfico
- 5 Seleccionar una serie de datos de un gráfico
- 6 Borrar series de datos de un gráfico mediante un bucle
- 7 Editar el titulo del gráfico
- 8 Editar los ejes de un gráfico:
- 9 Editar el formato de una serie de datos
- 10 Editar la leyenda del gráfico
- 11 Ejemplo 1
Datos iniciales:
A lo largo de todo este artículo vamos a usar los siguientes datos para nuestro gráfico:
Año | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 |
Serie 1 | 265 | 105 | 25 | 45 | 155 | 133 | 196 | 10 | 128 | 249 | 289 |
Serie 2 | 90 | 249 | 186 | 98 | 211 | 121 | 231 | 170 | 220 | 64 | 2 |
Serie 3 | 12 | 117 | 222 | 71 | 262 | 224 | 250 | 188 | 1 | 28 | 84 |
Serie 4 | 193 | 136 | 262 | 88 | 168 | 132 | 5 | 228 | 14 | 68 | 57 |
Para entender mejor los ejemplos mostrados a lo largo del artículo destacar que la tabla se encuentra en el rango de “B7:M11”.
El link para descargar el ejemplo completo con todas las macros incluidas esta al final del articulo.
Crear gráficos con VBA
Como siempre lo primero abrimos el Editor de Visual Basic (VBE) e insertamos un módulo.
El código básico para crear un gráfico, elegir el tipo de gráfico y asignarle unos datos es el siguiente:
Sub crear_grafico()
Dim grafico As ChartObject
Dim wks As Worksheet
Set wks = ActiveWorkbook.Sheets(1)
Set grafico = wks.ChartObjects.Add(Left:=20, Width:=400, Top:=50, Height:=200)
grafico.Name = «Grafico_1»
grafico.Chart.ChartType = xlXYScatterLines
grafico.Chart.SetSourceData Source:=wks.Range(«C8:M11»)
End Sub
En la segunda línea de código hemos creado una colección de gráficos (ChartObject). Nuestro gráfico formará parte de esta colección de gráficos, que a su vez está englobado en la primera hoja del documento. Si en vez de eso hubiéramos escrito la siguiente línea de código “ActiveWorkbook.Charts.Add” Excel hubiera añadido una nueva pestaña a nuestro documento conteniendo únicamente un gráfico.
Las unidades usadas en la quinta linea del código (Left:=20, Width:=400, Top:=50, Height:=200) mostrado arriba corresponden a puntos. En una pulgada (25.4 mm) hay 72 puntos, es decir nuestro gráfico tendrá un tamaño de 7.05 cm de alto por 14.11 cm de ancho (para más información ver https://support.microsoft.com/en-us/kb/76388)
Left and Top indican el margen que dejamos en nuestro gráfico a la izquierda y arriba respectivamente.
A la hora de seleccionar el tipo de gráfico, una vez escrito el símbolo igual en esa línea de código, podemos pulsar “Ctrl + espacio” para que VBE nos muestre una lista con todas la opciones disponibles. Esta herramienta es muy útil y yo personalmente la uso de manera continua, eligiendo entre las opciones disponibles en vez de tratar de averiguar el código correcto.
Seleccionar un gráfico existente
Mediante la macro mostrada a continuación seleccionamos el gráfico “Grafico_1”. También podríamos seleccionar este gráfico haciendo referencia al número de gráfico (“ChartObjects(1)”)
Sub seleccionar_grafico()
Dim wks As Worksheet
Set wks = ActiveWorkbook.Sheets(1)
wks.ChartObjects(«Grafico_1»).Select
End Sub
Añadir una serie de datos a un gráfico
Mediante el siguiente código vamos a añadir únicamente una serie de datos a nuestro gráfico:
Sub añadir_datos()
Dim cht As Chart
Set cht = ActiveWorkbook.Sheets(1).ChartObjects(«Grafico_1»).Chart
cht.SeriesCollection.NewSeries.Select
With Selection
.Name = Range(«B8»).Value
.XValues = Range(«C7:M7»)
.Values = Range(«C8:M8»)
End With
End Sub
Podríamos añadir todas la series de datos mediante un bucle:
Sub añadir_datos_2()
Dim cht As Chart
Dim i As Integer
Set cht = ActiveWorkbook.Sheets(1).ChartObjects(«Grafico_1»).Chart
For i = 1 To 4
cht.SeriesCollection.NewSeries.Select
With Selection
.Name = Cells(i + 7, 2).Value
.XValues = Range(«C7:M7»)
.Values = Range(«C8:M8»).Offset(i – 1, 0)
End With
Next i
End Sub
Seleccionar una serie de datos de un gráfico
Para seleccionar una serie de datos del gráfico debemos usar el siguiente código:
Sub seleccionar_serie()
Dim wks As Worksheet
Set wks = ActiveWorkbook.Sheets(1)
wks.ChartObjects(«Grafico_1»).Chart.SeriesCollection(1).Select
End Sub
También podemos escribir el código de la siguiente manera:
Sub seleccionar_serie()
Dim cht As Chart
Dim wks As Worksheet
Set wks = ActiveWorkbook.Sheets(1)
Set cht = wks.ChartObjects(«Grafico_1»).Chart
cht.SeriesCollection(«Series1»).Select
End Sub
Borrar series de datos de un gráfico mediante un bucle
Podemos borrar todas las series de datos de un gráfico de varias maneras, entre las cuales están las siguientes dos:
Método 1 – Bucle For / Next (link):
For i = cht.SeriesCollection.Count To 1 Step -1
cht.SeriesCollection(i).Delete
Next i
Método 2 – Bucle For / Each
Dim series As series
For Each series In cht.SeriesCollection
series.Delete
Next series
Editar el título del gráfico
En este ejemplo hemos creado el título del gráfico, lo hemos llamado “Grafico 1” y posteriormente hemos modificado la letra, tamaño y color del texto.
Sub editar_titulo()
Dim cht As Chart
Set cht = ActiveWorkbook.Sheets(1).ChartObjects(«Grafico_1»).Chart
cht.HasTitle = True
cht.ChartTitle.Text = «Grafico 1»
With cht.ChartTitle.Font
.Size = 16
.Bold = True
.Color = RGB(255, 0, 0)
End With
End Sub
Editar los ejes de un gráfico:
Mostrar / Ocultar los ejes:
cht.Axes(xlCategory, xlPrimary).Delete
cht.Axes(xlValue, xlPrimary).Delete
cht.HasAxis(xlCategory, xlPrimary) = True
cht.HasAxis(xlValue, xlPrimary) = True
Mostrar gridlines:
cht.Axes(xlCategory).HasMajorGridlines = True
cht.Axes(xlCategory).HasMinorGridlines = True
cht.Axes(xlValue).HasMajorGridlines = True
cht.Axes(xlValue).HasMinorGridlines = True
Editar los ejes horizontal y vertical:
cht.Axes(xlCategory).MaximumScale = 2020
cht.Axes(xlCategory).MinimumScale = 2000
cht.Axes(xlCategory).MajorUnit = 2
cht.Axes(xlCategory).MinorUnit = 1
cht.Axes(xlCategory).MajorTickMark = xlTickMarkOutside
cht.Axes(xlCategory).MinorTickMark = xlTickMarkOutside
cht.Axes(xlCategory).ReversePlotOrder = True
cht.Axes(xlCategory).LogBase = 10
cht.Axes(xlCategory).DisplayUnit = xlHundreds
cht.Axes(xlCategory).TickLabelPosition = xlTickLabelPositionNextToAxis
cht.Axes(xlCategory).Crosses = xlMaximum
Editar el formato de una serie de datos
En este ejemplo vamos a modificar el formato de una serie de datos de nuestro gráfico:
Modificar la linea:
srs.Format.Line.ForeColor.RGB = RGB(255, 0, 0)
srs.Format.Line.Weight = xlThin
srs.Format.Line.Style = msoLineThinThick
srs.Format.Line.DashStyle = msoLineDashDot
srs.Format.Line.Transparency = 0.5
Modificar los puntos:
srs.MarkerSize = 10
srs.MarkerStyle = xlMarkerStyleDiamond
srs.MarkerBackgroundColor = RGB(0, 255, 0)
srs.MarkerForegroundColor = RGB(0, 0, 255)
Editar la leyenda del gráfico
Algunas de las opciones que tenemos para modificar la leyenda de un gráfico son las siguientes
cht.HasLegend = True
cht.Legend.Font.Color = RGB(150, 150, 0)
cht.Legend.Position = xlLegendPositionBottom
cht.Legend.Format.Fill.ForeColor.RGB = RGB(50, 50, 0)
Ejemplo 1
Usando muchos de los comandos mostrados en este artículo y lo explicado en el artículo “Diversas Maneras de ejecutar una macro”, hemos creado un ejemplo con un gráfico dinámico. En este ejemplo vamos a tener un gráfico con varias series de datos de manera que podamos seleccionar en cualquier momento que series de datos queremos mostrar y entre qué periodo de tiempo actualizándose automáticamente el gráfico.
Crear-y-Modificar-Graficos-con-VBA