VBA Charts

Updated on January 1, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA Charts

We can term charts as objects in VBA. Similar to the worksheet, we can also insert charts in VBA. First, we select the data and chart type we want for our data. Now, there are two different types of charts we provide. One is the embed chart, where the chart is in the same sheet of data. Another is known as the chart sheet, where the chart is in a separate data sheet.

In data analysis, visual effects are the key performance indicators of the person who has done the analysis. Visuals are the best way an analyst can convey their message. Since we are all Excel users, we usually spend considerable time analyzing the data and drawing conclusions with numbers and charts. Creating a chart is an art to master. We hope you have good knowledge of creating charts with excelCreating Charts With ExcelIn Excel, a graph or chart lets us visualize information we've gathered from our data. It allows us to visualize data in easy-to-understand pictorial ways. The following components are required to create charts or graphs in Excel: 1 - Numerical Data, 2 - Data Headings, and 3 - Data in Proper Order.read more. This article will show you how to create charts using VBA coding.

VBA Charts

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Charts (wallstreetmojo.com)

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

How to Add Charts using VBA Code in Excel?

You can download this VBA Charts Excel Template here – VBA Charts Excel Template

#1 – Create Chart using VBA Coding

To create any chart, we should have some numerical data. For this example, we are going to use the below sample data.

VBA Chart Example 1

First, let us jump to the VBA editorVBA EditorThe Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft software.read more.

Step 1: Start Sub Procedure.

Code:

Sub Charts_Example1()

End Sub
VBA Chart Example 1-1

Step 2: Define the variable as Chart.

Code:

Sub Charts_Example1()

  Dim MyChart As Chart

End Sub
VBA Chart Example 1-2

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
VBA Chart Example 1-3

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

VBA Chart Example 1-4

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
VBA Chart Example 1-5

Step 5: The first thing we need to do with the chart 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
VBA Chart Example 1-6

Step 6: We need to mention the source range. In this case, my source range is in the sheet named “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
Example 1-7

Step 7: Next up, we need to select the kind of chart we are going to create. For this, we need to select the 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
VBA Chart Example 1-8

Step 8: Here, we have a variety of charts. I am going to select the “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
Example 1-9

Now let’s run the code using the F5 key or manually and see how the chart looks.

VBA Chart Example 1-10

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

Example 1-11

Like this, we have many properties and methods with 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

We need to use a different technique to create the chart with the same worksheet (datasheet) as the shape.

Step 1: First, declare three object variables.

Code:

Sub Charts_Example2()

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

End Sub
VBA Chart Example 2

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
Example 2-1

Step 3: Now, set the range object in VBASet The Range Object In VBARange is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.read more

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
Example 2-2

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
Example 2-3

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

VBA Chart Example 2-4

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

It will add the chart below.

VBA Chart Example 2-5

#3 – Code to Loop through the Charts

Like how we look through sheets to change the name, insert values, and hide and unhide them. Similarly, we need to use the ChartObject property to loop through the charts.

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

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

It 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 article has been a guide to VBA Charts. Here, we learn how to create a chart using VBA code, practical examples, and a downloadable template. Below you can find some useful Excel VBA articles: –