The VBA Coding Guide For Excel Charts & Graphs

The VBA Coding Guide To Charts & Graphs For Microsoft Excel

Charts, Charts, & More Charts!

Graphical visualizations are arguably the pinnacle of how an analyst shares his/her results and possessing the ability to manipulate them is key to the field.  Since we as data analysts spend some much time creating graphs, it is more valuable than ever to understand how to automate them.

What if you have 20 graphs on a spreadsheet and they all need to have their legends in the exact same spot?  What if you create a bunch of charts and your manager needs the series colors changed at the last minute? Do you want to do this all manually?

Below will be your cheat sheet for manipulating Excel charts & graphs with VBA code.  Please let me know via the comments section if there are areas missing from this guide so I can expand on them.  Enjoy!

Inserting A Chart

Method 1:

Sub CreateChart()
'PURPOSE: Create a chart (chart dimensions are not required)

Dim rng As Range
Dim cht As Object

'Your data range for the chart
  Set rng = ActiveSheet.Range("A24:M27")

'Create a chart
  Set cht = ActiveSheet.Shapes.AddChart2

'Give chart some data
  cht.Chart.SetSourceData Source:=rng

'Determine the chart type
  cht.Chart.ChartType = xlXYScatterLines

End Sub

Method 2:

Sub CreateChart()
'PURPOSE: Create a chart (chart dimensions are required)

Dim rng As Range
Dim cht As ChartObject

'Your data range for the chart
  Set rng = ActiveSheet.Range("A24:M27")

'Create a chart
  Set cht = ActiveSheet.ChartObjects.Add( _
    Left:=ActiveCell.Left, _
    Width:=450, _
    Top:=ActiveCell.Top, _
    Height:=250)

'Give chart some data
  cht.Chart.SetSourceData Source:=rng

'Determine the chart type
  cht.Chart.ChartType = xlXYScatterLines
      
End Sub

Looping Through Charts & Series

Sub LoopThroughCharts()
'PURPOSE: How to cycle through charts and chart series

Dim cht As ChartObject
Dim ser As Series

'Loop Through all charts on ActiveSheet
  For Each cht In ActiveSheet.ChartObjects

  Next cht

'Loop through all series in a chart
  For Each ser In grph.Chart.SeriesCollection

  Next ser
  
'Loop Through all series on Activesheet
  For Each cht In ActiveSheet.ChartObjects
    For Each ser In grph.Chart.SeriesCollection

    Next ser
  Next cht
  
End Sub

Adding & Modifying A Chart Title

Sub AddChartTitle()
'PURPOSE: Add a title to a specific chart

Dim cht As ChartObject

Set cht = ActiveSheet.ChartObjects("Chart 1")

'Ensure chart has a title
  cht.Chart.HasTitle = True

'Change chart's title
  cht.Chart.ChartTitle.Text = "My Graph"

End Sub

Sub RepositionChartTitle()
'PURPOSE: Reposition a chart's title

Dim cht As ChartObject

Set cht = ActiveSheet.ChartObjects("Chart 1")

'Reposition title
  With cht.Chart.ChartTitle
    .Left = 100
    .Top = 50
  End With

End Sub

Adding & Modifying A Graph Legend

Sub InsertChartLegend()

Dim cht As Chart

Set cht = ActiveSheet.ChartObjects("Chart 1").Chart

'Add Legend to the Right
  cht.SetElement (msoElementLegendRight)
  
'Add Legend to the Left
  cht.SetElement (msoElementLegendLeft)
  
'Add Legend to the Bottom
  cht.SetElement (msoElementLegendBottom)
  
'Add Legend to the Top
  cht.SetElement (msoElementLegendTop)
  
'Add Overlaying Legend to the Left
  cht.SetElement (msoElementLegendLeftOverlay)
  
'Add Overlaying Legend to the Right
  cht.SetElement (msoElementLegendRightOverlay)

End Sub

Sub DimensionChartLegend()

Dim lgd As Legend

Set lgd = ActiveSheet.ChartObjects("Chart 1").Chart.Legend

lgd.Left = 240.23
lgd.Top = 6.962
lgd.Width = 103.769
lgd.Height = 25.165

End Sub

Adding Various Chart Attributes

Sub AddStuffToChart()

Dim cht As Chart

Set cht = ActiveSheet.ChartObjects("Chart 1").Chart

'Add X-axis
  cht.HasAxis(xlCategory, xlPrimary) = True '[Method #1]
  cht.SetElement (msoElementPrimaryCategoryAxisShow) '[Method #2]
  
'Add X-axis title
  cht.Axes(xlCategory, xlPrimary).HasTitle = True '[Method #1]
  cht.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis) '[Method #2]
  
'Add y-axis
  cht.HasAxis(xlValue, xlPrimary) = True '[Method #1]
  cht.SetElement (msoElementPrimaryValueAxisShow) '[Method #2]
  
'Add y-axis title
  cht.Axes(xlValue, xlPrimary).HasTitle = True '[Method #1]
  cht.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis) '[Method #2]
  
'Add Data Labels (Centered)
  cht.SetElement (msoElementDataLabelCenter)

'Add Major Gridlines
  cht.SetElement (msoElementPrimaryValueGridLinesMajor)
    
'Add Linear Trend Line
  cht.SeriesCollection(1).Trendlines.Add Type:=xlLinear
  
End Sub

Modifying Various Chart Attributes

Sub ChangeChartFormatting()

Dim cht As Chart

Set cht = ActiveSheet.ChartObjects("Chart 1").Chart

'Adjust y-axis Scale
  cht.Axes(xlValue).MinimumScale = 40
  cht.Axes(xlValue).MaximumScale = 100

'Adjust x-axis Scale
  cht.Axes(xlCategory).MinimumScale = 1
  cht.Axes(xlCategory).MaximumScale = 10
  
'Adjust Bar Gap
  cht.ChartGroups(1).GapWidth = 60

'Format Font Size
  cht.ChartArea.Format.TextFrame2.TextRange.Font.Size = 12
  
'Format Font Type
  cht.ChartArea.Format.TextFrame2.TextRange.Font.Name = "Arial"
  
'Make Font Bold
  cht.ChartArea.Format.TextFrame2.TextRange.Font.Bold = msoTrue
  
'Make Font Italicized
  cht.ChartArea.Format.TextFrame2.TextRange.Font.Italic = msoTrue

End Sub

Removing Various Chart Attributes

Sub RemoveChartFormatting()

Dim cht As Chart

Set cht = ActiveSheet.ChartObjects("Chart 1").Chart

'Remove Chart Series
  cht.SeriesCollection(2).Delete

'Remove Gridlines
  cht.Axes(xlValue).MajorGridlines.Delete
  cht.Axes(xlValue).MinorGridlines.Delete
  
'Remove X-axis
  cht.Axes(xlCategory).Delete

'Remove Y-axis
  cht.Axes(xlValue).Delete

'Remove Legend
  cht.Legend.Delete

'Remove Title
  cht.ChartTitle.Delete
    
'Remove ChartArea border
  cht.ChartArea.Border.LineStyle = xlNone

'No background color fill
  cht.ChartArea.Format.Fill.Visible = msoFalse
  cht.PlotArea.Format.Fill.Visible = msoFalse

End Sub

Change Your Colors

Sub ChangeChartColors()

Dim cht As Chart

Set cht = ActiveSheet.ChartObjects("Chart 1").Chart

'Change first bar chart series fill color
  cht.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(91, 155, 213)

'Change X-axis label color
  cht.Axes(xlCategory).TickLabels.Font.Color = RGB(91, 155, 213)

'Change Y-axis label color
  cht.Axes(xlValue).TickLabels.Font.Color = RGB(91, 155, 213)
  
'Change Plot Area border color
  cht.PlotArea.Format.Line.ForeColor.RGB = RGB(91, 155, 213)
  
'Change Major gridline color
  cht.Axes(xlValue).MajorGridlines.Format.Line.ForeColor.RGB = RGB(91, 155, 213)

'Change Chart Title font color
  cht.ChartTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(91, 155, 213)

'No background color fill
  cht.ChartArea.Format.Fill.Visible = msoFalse
  cht.PlotArea.Format.Fill.Visible = msoFalse

End Sub

More Of My Charting Articles

Anything You Would Like To See?

There are a ton of things you can do with VBA and Excel charts.  I attempted through this guide to tackle the most general ones, but please don't hesitate to leave a comment if there is something that you would like to see added to the code in this VBA guide.  Hopefully, you were able to find what you were looking for!


Share This Post!

Did you find this post helpful? Do you want to support this blog because you're just that awesome?!  By sharing this post on FacebookTwitter, or Google+ you are not only providing others with great information, you are creating exposure for The Spreadsheet Guru website.  Growing this community is my number one goal as I have found learning to increase exponentially when lots of people are contributing to the conversation.  Learning is the whole reason why this blog exists!  If you want to spread the word just click on the Share button right below this paragraph (next to the Like button -- I enjoy "likes" too!) or on the Floating Share Bar to the left and select your preferred social medium.  Thank you so much for reading and I hope I can continue to provide you with great content in the future!  Cheers!