Excel VBA Chart code for automation

I have been working on designing some code for excel VBA chart automation to generate an excel chart for every line in an excel spredsheet.  Every row in the excel spreadsheet has the information required to do the chart.  I am trying to generate a chart for every row in the spreadsheet to include in a mail merge between Word and Excel, where I want a different chart in every mail merged document.

So first things first, let me show you how to create a chart using Excel VBA.

There is a lot of code, so I will break down the Excel VBA chart creation code into smaller parts so that I can explain them, and then I will put it together in the end for you.

The first step is to see what my data looks like, here is a screenshot sample of the excel spreadsheet.

 

So using this example, I want to generate 5 different bar charts, with dollars on the y axis, the categories Employee Cost and Company Cost on the x axis, and the data will create the bars in the chart.  And I'm also picky about formatting, so I want to make things look nice too, so you will see how I automate the formatting of the chart also.

Function CreateBarCharts() As Boolean
    Dim myChtObj As ChartObject
    Dim rngChtData As Range
    Dim rngChtXVal As Range
    Dim iColumn As Long
    
    Dim sheetName As String
    sheetName = "DataSource"
    Dim WSD As Worksheet
    Set WSD = Worksheets(sheetName)
    
    Dim chartSheet As String
    chartSheet = "ChartOutput"
    Dim CSD As Worksheet
    Set CSD = Worksheets(chartSheet)

As you can see for an added degree of difficulty, I will be putting the charts that I am creating on a different sheet than my source data.

    ' get the current charts so proper overwriting can happen
    Dim chtObjs As ChartObjects
    Set chtObjs = CSD.ChartObjects

    ' Turn off autofilter mode
    WSD.AutoFilterMode = False
    
    ' Find the last row with data
    Dim finalRow As Long
    finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row

To determine how many values to loop over, I will find the last row in the data set. Here, that value would be 5.

    ' for each row in the sheet
    For i = 2 To finalRow
    
        Dim chartName As String
        chartName = WSD.Cells(i, 5).Value
    
        ' Delete chart if it already exists, we are making a new one
        Dim chtObj As ChartObject
        For Each chtObj In chtObjs
            If chtObj.Name = chartName Then
                chtObj.Delete
            End If
        Next
    
        ' define chart data range for the row (record)
        Dim dataString As String
        dataString = "C" & i & ":D" & i
        Set rngChtData = WSD.Range(dataString)
        
        ' define the x axis values
        Set rngChtXVal = WSD.Range("$C$1:$D$1")

        ' add the chart
        Charts.Add
        With ActiveChart
            
            ' make a bar chart
            .ChartType = xlColumnClustered

            ' remove extra series
            Do Until .SeriesCollection.Count = 0
                .SeriesCollection(1).Delete
            Loop
            

            ' add series from selected range, column by column

            With .SeriesCollection.NewSeries
                .Values = rngChtData
                .XValues = rngChtXVal
                .Name = "Cost"
            End With
            
            .Location Where:=xlLocationAsObject, Name:=chartSheet

        End With

Here I go through every row in the data, I use a cell value to determine the name of the chart, which is something very important for a mail merge. That is the purpose of the chartName variable. I use it to delete any existing charts, and to name the chart to make it callable from the mail merge, which I will discuss in another article.

So my data lies in columns C and D, so you see me define those data ranges, and also the x axis categories that are in C1 and D1. I then add the chart, set a couple options, and insert it into the spreadsheet. But I'm not done yet, due to some weirdness in the ordering of the commands, here is another With ActiveChart block to include inside the for loop to finish out the chart creation.

      With ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = "Benefits Cost"
            .Parent.Name = WSD.Cells(i, 5).Value
            .Legend.Delete
        
            .Axes(xlCategory).TickLabels.AutoScaleFont = False
            With .Axes(xlCategory).TickLabels.Font
                .Name = "Arial"
                .FontStyle = "Regular"
                .Size = 10
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
                .Background = xlAutomatic
            End With
            
            .Axes(xlValue).TickLabels.AutoScaleFont = False
            With .Axes(xlValue).TickLabels.Font
                .Name = "Arial"
                .FontStyle = "Regular"
                .Size = 8
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
                .Background = xlAutomatic
            End With
            
            .ChartTitle.AutoScaleFont = False
            With .ChartTitle.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 12
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
                .Background = xlAutomatic
            End With
     
            With .PlotArea.Interior
                .ColorIndex = 2
                .PatternColorIndex = 1
                .Pattern = xlSolid
            End With
        
        End With

All of this code will set the name of the chart, delete the legend, and do a bunch of formatting. But we're not done yet. I wanted to control the width and height of the chart, so I have another block to add in the For loop.

        ' Set the height and width
        With CSD.ChartObjects(chartName)
            .Width = 225
            .Height = 175
        End With

This was another case where the ordering of calling the different commands made a difference, so I separated it out at the end of the excel VBA chart creation.

So here is the finished entire block of code:


Function CreateBarCharts() As Boolean
    Dim myChtObj As ChartObject
    Dim rngChtData As Range
    Dim rngChtXVal As Range
    Dim iColumn As Long
    
    Dim sheetName As String
    sheetName = "DataSource"
    Dim WSD As Worksheet
    Set WSD = Worksheets(sheetName)
    
    Dim chartSheet As String
    chartSheet = "ChartOutput"
    Dim CSD As Worksheet
    Set CSD = Worksheets(chartSheet)
    
    ' get the current charts so proper overwriting can happen
    Dim chtObjs As ChartObjects
    Set chtObjs = CSD.ChartObjects

    ' Turn off autofilter mode
    WSD.AutoFilterMode = False
    
    ' Find the last row with data
    Dim finalRow As Long
    finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
    
    Dim i As Integer
    
    ' for each row in the sheet
    For i = 2 To finalRow
    
        Dim chartName As String
        chartName = WSD.Cells(i, 5).Value
    
        ' Delete chart if it already exists, we are making a new one
        Dim chtObj As ChartObject
        For Each chtObj In chtObjs
            If chtObj.Name = chartName Then
                chtObj.Delete
            End If
        Next
    
        ' define chart data range for the row (record)
        Dim dataString As String
        dataString = "C" & i & ":D" & i
        Set rngChtData = WSD.Range(dataString)
        
        ' define the x values
        Set rngChtXVal = WSD.Range("$C$1:$D$1")

        ' add the chart
        Charts.Add
        With ActiveChart
            
            ' make a bar chart
            .ChartType = xlColumnClustered

            ' remove extra series
            Do Until .SeriesCollection.Count = 0
                .SeriesCollection(1).Delete
            Loop
            

            ' add series from selected range, column by column

            With .SeriesCollection.NewSeries
                .Values = rngChtData
                .XValues = rngChtXVal
                .Name = "Cost"
            End With
            
            .Location Where:=xlLocationAsObject, Name:=chartSheet

        End With

        With ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = "Benefits Cost"
            .Parent.Name = WSD.Cells(i, 5).Value
            .Legend.Delete
        
            .Axes(xlCategory).TickLabels.AutoScaleFont = False
            With .Axes(xlCategory).TickLabels.Font
                .Name = "Arial"
                .FontStyle = "Regular"
                .Size = 10
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
                .Background = xlAutomatic
            End With
            
            .Axes(xlValue).TickLabels.AutoScaleFont = False
            With .Axes(xlValue).TickLabels.Font
                .Name = "Arial"
                .FontStyle = "Regular"
                .Size = 8
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
                .Background = xlAutomatic
            End With
            
            .ChartTitle.AutoScaleFont = False
            With .ChartTitle.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 12
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
                .Background = xlAutomatic
            End With
     
            With .PlotArea.Interior
                .ColorIndex = 2
                .PatternColorIndex = 1
                .Pattern = xlSolid
            End With
        
        End With
        
        ' Set the height and width
        With CSD.ChartObjects(chartName)
            .Width = 225
            .Height = 175
        End With
    Next i
End Function


Comments

Multiple rows per chart

Hi, I notice you make one chart for each row. How can I make one chart by a dynamic number of rows? I assume I change the following lines, however when I change just the range I don't get the result I need.           Dim dataString As String        dataString = "C" & i & ":D" & i        Set rngChtData = WSD.Range(dataString)                ' define the x values        Set rngChtXVal = WSD.Range("$C$1:$D$1")

You code is exactly what I need to do a similar task

Hello, Jeremy,I have been working on a very similar project as you have described for a week!  I am rusty on VBA and was never at a deep level.  Your code is really close except I have some questions before I can get it to work.  Would you be able to help me on this with these issues?1. I need a LineMarker Chart (Line with Markers) instead of a Bar Chart2. I don't know how to execute a function.  I only know how to execute a macro.  I assume I can turn this into a Sub and eliminate the Function command? Or, how would I execute a function?3. I am not clear on Line008 where sheetName = "DataSource"?  I have 8 columns of data in each row.  Column A contains the string that I want to have as the chart title as well as the name on the inserted worksheet that will hold each Chart. Column B-H are simply July thru January with numerical info in them.  I think I should be overwriting DataSource with the actual Cell A data with each record but not sure how to do that.4. The same with line 0012 thru 0015 where  chartsheet = "chartoutput".   Not sure what I should be putting between the quotes.