VBA Charts

Charts in Excel VBA

In data analysis, visual effects are the key performance indicators of the person who has done the analysis. Visuals are the best possible manner an analyst can convey his or her message. Since we are all excel users we usually spend a considerable amount of time analyzing the data and draw conclusions with numbers and charts. Creating a chart is the art to master and I hope you have good knowledge of creating charts with excel. In this article, we will show you how to create excel charts using VBA coding.

#1 – Create Chart Using VBA Coding in Excel

To create any chart we should have some sort of numerical data. For this example, I am going to use below simple data.

Ok, let’s jump to VBA editor.

Step 1: Start Sub Procedure.

Code:

Sub Charts_Example1()

End Sub

Step 2: Define the variable as Chart.

Code:

Sub Charts_Example1()

  Dim MyChart As Chart

End Sub

Step 3: Since the chart is an object variable we need to Set it.

Code:

Sub Charts_Example1()

  Dim MyChart As Chart
  Set MyChart = Charts.Add

End Sub

The above code will add a new sheet but not a worksheet but a chart sheet.

Step 4: Now we need to design the chart. Open With Statement.

Code:

Sub Charts_Example1()

  Dim MyChart As Chart
  Set MyChart = Charts.Add

  With MyChart

  End With

End Sub

Step 5: First thing with the chart we need to do is to Set the source range by selecting the “Set Source Data” method.

Code:

Sub Charts_Example1()

  Dim MyChart As Chart
  Set MyChart = Charts.Add

  With MyChart
  .SetSourceData

  End With

End Sub

Step 6: Here we need to mention the source range. In this case, my source range is in the sheet named as “Sheet1” and the range is “A1 to B7”.

Code:

Sub Charts_Example1()

  Dim MyChart As Chart
  Set MyChart = Charts.Add

  With MyChart
  .SetSourceData Sheets("Sheet1").Range("A1:B7")
  End With

End Sub

Step 7: Next up we need to select the kind of chart we are going to create in VBA. For this, we need to select Chart Type property.

Code:

Sub Charts_Example1()

  Dim MyChart As Chart
  Set MyChart = Charts.Add

  With MyChart
  .SetSourceData Sheets("Sheet1").Range("A1:B7")
  .ChartType =
  End With

End Sub

Step 8: Here we have a variety of charts. I am going to select “xlColumnClustered” chart.

Code:

Sub Charts_Example1()

  Dim MyChart As Chart
  Set MyChart = Charts.Add

  With MyChart
  .SetSourceData Sheets("Sheet1").Range("A1:B7")
  .ChartType = xlColumnClustered
  End With

End Sub

Ok, at this moment let’s run the code using F5 key or manually and see how the chart looks.

Step 9: Now change other properties of the chart. To change the chart title below is the code.

Like this, we have many properties and methods with Excel VBA charts. Use each one of them to see the impact and learn.

Sub Charts_Example1()

  Dim MyChart As Chart
  Set MyChart = Charts.Add

  With MyChart
  .SetSourceData Sheets("Sheet1").Range("A1:B7")
  .ChartType = xlColumnClustered
  .ChartTitle.Text = "Sales Performance"
  End With

End Sub

#2 – Create a Chart with the Same Excel Sheet as Shape

In the above example, we have created the VBA chart in the separate excel sheet only. But to create the chart with the same VBA worksheet (datasheet) as shape we need to use a different technique.

Step 1: First Declare threes Object Variables.

Code:

Sub Charts_Example2()

  Dim Ws As Worksheet
  Dim Rng As Range
  Dim MyChart As Object

End Sub

Step 2: Then Set the Worksheet reference.

Code:

Sub Charts_Example2()

  Dim Ws As Worksheet
  Dim Rng As Range
  Dim MyChart As Object

  Set Ws = Worksheets("Sheet1")

End Sub

Step 3: Now set the range object in VBA

Code:

Sub Charts_Example2()

  Dim Ws As Worksheet
  Dim Rng As Range
  Dim MyChart As Object

  Set Ws = Worksheets("Sheet1")
  Set Rng = Ws.Range("A1:B7")

End Sub

Step 4: Now set the chart object.

Code:

Sub Charts_Example2()

  Dim Ws As Worksheet
  Dim Rng As Range
  Dim MyChart As Object

  Set Ws = Worksheets("Sheet1")
  Set Rng = Ws.Range("A1:B7")
  Set MyChart = Ws.Shapes.AddChart2

End Sub

Step 5: Now, as usual, we can design the chart by using the “With” statement.

Code:

Sub Charts_Example2()

Dim Ws As Worksheet 'To Hold Worksheet Reference
Dim Rng As Range 'To Hold Range Reference in the Worksheet
Dim MyChart As Object

Set Ws = Worksheets("Sheet1") 'Now variable "Ws" is equal to the sheet "Sheet1"
Set Rng = Ws.Range("A1:B7") 'Now variable "Rng" holds the range A1 to B7 in the sheet "Sheet1"
Set MyChart = Ws.Shapes.AddChart2 'Chart will be added as Shape in the same worksheet

With MyChart.Chart
.SetSourceData Rng 'Since we already set the range of cells to be used for chart we have use RNG object here
.ChartType = xlColumnClustered
.ChartTitle.Text = "Sales Performance"
End With

End Sub

This will add the chart like the below.

#3 – VBA Code to Loop through the Charts

Like how we look through sheets to change the name or insert values, hide & unhide them. Similarly to loop through the charts we need to use chart object property in excel VBA.

Below VBA code will loop through all the charts in the worksheet.

Code:

Sub Chart_Loop()

  Dim MyChart As ChartObject
 
  For Each MyChart In ActiveSheet.ChartObjects
  'Enter the code here
  Next MyChart

End Sub

#4 – Alternative Method to Create VBA Chart in Excel

We can use below alternative method to create VBA charts. We can use the Chart Object.Add method to create the chart below is the example code.

This will also create a chart like the previous method.

Code:

Sub Charts_Example3()

  Dim Ws As Worksheet
  Dim Rng As Range
  Dim MyChart As ChartObject

  Set Ws = Worksheets("Sheet1")
  Set Rng = Ws.Range("A1:B7")
  Set MyChart = Ws.ChartObjects.Add(Left:=ActiveCell.Left, Width:=400, Top:=ActiveCell.Top, Height:=200)
  MyChart.Chart.SetSourceData Source:=Rng
  MyChart.Chart.ChartType = xlColumnStacked
  MyChart.Chart.ChartTitle.Text = "Sales Performance"

End Sub

Recommended Articles

This has been a guide to VBA Charts. Here we learn how to create a chart using VBA Code in excel along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

• How to use VBA InStr?
• Excel VBA Join Function
• ByRef Function in VBA
• Delete Files using VBA Code
• VBA OverFlow Error
• VBA ISNULL
• VBA Pivot Table
• VBA Remove Duplicates