The VBA Coding Guide For Excel Charts & Graphs

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!

VBA Chart Guide Contents

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

Add 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

Move/Reposition Chart Title

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

Insert/Create Chart 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

Resize/Move Chart Legend

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 Chart 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

Chart Excel Add-ins

• Waterfall Chart Excel Add-in – Automatically create editable Waterfall Charts directly in your spreadsheet.
• AutoChart Excel Add-in – This add-in will allow you to create, manipulate series ranges, and format all your charts at once. Making adjustments has never been easier!
• myBrand Excel Add-in – Stores your favorite colors to the Excel Ribbon and allows you to color cells, shapes, and charts.
• Peltier Tech Charts for Excel – A chart-building toolkit with the automated creation of difficult chart builds such as Histograms, Pareto, Marimekko, and many more.

More Of My Charting Articles

• Automatically Extending Excel Chart Series’ Ranges For Next Year
• Make All Charts Plot Non-Visible Cells in Your Excel Workbook
• Scroll To Chart Based on Form Control Combo Box Selection

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!

Hidden Hacks For VBA Macro Coding

After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!

Learn These Hacks Now For Free!

Keep Learning

Explore more →

Hiding A To-Date Column When Reporting Month or Quarter End Results

The "To-Date" Column I was first introduced to this concept a few years ago when I first started working in...

Remove Chart Data Labels With Specific Value

There may be times when you would like to de-clutter your data labels so that smaller or zero values are...

Loop Through All Chart Sheets In An Excel Workbook

What This VBA Code Does There are two types of Sheets within an Excel Workbook. Worksheets Chart Sheets The below...

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.