VBA Chart




Making a chart using VBA can be easy. VBA provides a handle to a chart object using the “ChartObject” class and to the chart data series trough the “Series” class. Let’s look at a basic example where we use VBA to create an empty chart to our spreadsheet and then add data to it.

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub CreateChart()
Dim ChartObj As ChartObject
Dim ChartSeries as Series
 
Set ChartObj = ActiveSheet.ChartObjects.Add ( Left: = 100, Width: = 550, Top: = 75, Height: = 325)
Set ChartSeries = ChartObj.Chart.SeriesCollection.NewSeries

With ChartSeries
            .Name = "Chart Series 1"
            .Values = Array(1, 2, 3, 4, 5)
            .XValues = Array("alpha", "beta", "gamma", "delta", "epsilon")
End With
End Sub

In the example above, we’ve instantiated a chart using the ChartObject class. As you would have noticed, at the time of creation of the chart, we also specified the X-Y coordinates plus the height and width parameters. If you were to stop the code at this point and go back and look at the spreadsheet, you will see a blank chart – one that has all the features but no data. In the next step, we use the “Series” object to add data to the chart. In crude terms, a data series is collection of data points and appears in the chart’s source data under a single name. Let’s take a closer look at each of these.

The Chart Object

A simple chart can be made using just a single line of code:

1
Set ChartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=550, Top:=75, Height:=325)

What we’ve asked the application to do is to add another chart to the spreadsheet with given XY-coordinated and size. The add function returns the reference to the newly created chart. We can now use functions such as .activate, .delete, .copy etc to work with this chart object. We can also specify the chart type at this stage using the ActiveChart.ChartType = xlLineMarkers (or any other chart type that you wish to use).

In all the succeeding steps, whenever you need to refer to the chart, you can use ChartObj.Chart (the chart embedded in the Chart Object. Here’s a sample:

1
2
Set ChartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=550, Top:=75, Height:=325)
MsgBox ChartObj.Chart.Name

(Data) Series

A single data series consists of multiple data points. For example, a data series in a line chart typically contains data for more than one data point. You can add a series to the chart using the following VBA code

1
2
3
4
5
6
Set ChartSeries = ChartObj.Chart.SeriesCollection.NewSeries
With ChartSeries
            .Name = "Chart Series 1"
            .Values = Array(1, 2, 3, 4, 5)
            .XValues = Array("alpha", "beta", "gamma", "delta", "epsilon")
End With

where:

.Name = The named that you want to give to this data series. This is the name as the you would like to appear in the legend.

.Values = These are the values that you would like to plot

.XValues = These are the category x lables

Important to note is that the .XValues parameter can be specified in two ways:

Specifying .XValues as array (or string)

One can specify the .XValues as an array using by creating an array as given in the example above or by providing an array of strings using .XValues = label_values (where lable_values = the array of strings with each individual array in the string holding one data label)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Sub try()
Dim label_values() As String
Dim individual_label_value As Variant
Dim counter As Long
Dim ChartObj As ChartObject
Dim ChartSeries As Series
 

'Get the number of lables that you will need to have for the chart Ex. say 7
ReDim label_values(4) As String
counter = 1

For counter = 0 To 4
    label_values(counter) = "MySeries " & counter + 1
Next counter


Set ChartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=550, Top:=75, Height:=325)
Set ChartSeries = ChartObj.Chart.SeriesCollection.NewSeries

With ChartSeries
    .Name = "Chart Series 1"
    .Values = Array(1, 2, 3, 4, 5)
    .XValues = label_values
End With
End Sub

Specifying .XValues as a range

The .XY values can also be specified as a range of the worksheet. The code would read something like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub CreateChart()
Dim ChartObj As ChartObject
Dim ChartSeries as Series

Set ChartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=550, Top:=75, Height:=325)

Set ChartSeries = ChartObj.Chart.SeriesCollection.NewSeries
With ChartSeries
            .Name = "Chart Series 1"
            .Values = Sheets("Sheet1").Range("A1:A10")
            .XValues = Sheets("Sheet1").Range("B1:B10")
End With
End Sub

where the range A1:A10 and B1:B10 contain the data and the X-category labels. Now if you wanted to have more than one data series, you could have added them by using this code in a VBA loop.

The intersting thing to note is that there is a restriction on the maximum number of characters that can be added as .XValues in above code. If you look at the second example, this limit will not be breached under normal circumstances since only the address of the range is being provided. That can ensure that you can add a large number of labels without having to worry about this limitation. However, if you wanted to specify those values as static values using an Array, you’d end up having to restrict the number of lables you can accomodate in the chart. If you exceed this value, the application will throw the following errror.

vba-chart-runtime-error-1004

So if you are going to write code that may breach this limit, ensure that you catch the runtime error using the On Error statement.

Chart Type

You can specify the chart type parameter for the entire chart using the ActiveChart.ChartType = [chart_type] instruction or for each individual data series using the .ChartType = [chart_type] instruction (where chart_type is an application defined constant indicating the type of chart to use). Oftentimes when you record a macro, the resulting VBA code will have these constants mentioned as xlLine or xlColumnClustered etc. However it is entirely possible to use numbers insted of constants as the .ChartType parameter as well. So for the chart that you’ve just created, you can simply write ChartObj.Chart.ChartType = xlRadarMarkers (or ChartObj.Chart.ChartType = 81) and that would change the chart type to radar chart.

List of all Chart Types (.ChartTypes) in VBA

As I said before, one can use numerical values instead of constants for creating charts in VBA. By using numerical values instead of constants, you can parameterize your VBA code and have better interactivity between the excel spreadsheet and VBA code (say a named list and chart type in VBA)

vba-chart-type-list

You can download this example showing how to create a chart using VBA here or click on the button below:

create chart using vba


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Jon Peltier wrote:

    You’ve declared and populated a chartobject variable, then not made good use of it. Your code will be more effective is you don’t spend time activating the chart.

    Do something like this instead.

    Set ChartObj = ActiveSheet.ChartObjects.Add (Left:=100, Width:=550, Top:=75, Height:=325)
    Set ChartSeries = ChartObj.Chart.SeriesCollection.NewSeries

  2. Ajay wrote:

    In coding terms, that was ….well….a blunder. All I can say in my defense was that try as I might, I could not get the ChartObj.SeriesCollection.NewSeries to work. I guess I was missing the Chart object in the ChartObject :-)

    Repaired.

    Thanks,

  3. Spencer wrote:

    Very useful since I have no help files or manuals available to me here.

  4. Ajay wrote:

    Hi Spencer,

    Welcome to da TaB ! Glad this was of help.

    Regards,

  5. Aaron Wolfe wrote:

    I was just Debugging my code that uses an IF based on “ActiveChart.ChartType”. “ActiveChart.ChartType” is returning a constant of -4111. I expected it to return one of the XYScatter choices. It doesn’t show up on your list of chart types above either. Any idea why it would be returning this constant, or what it represents?

  6. Faisal wrote:

    Can multiple charts be inserted in one chart object?

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel