VBA Create Chart in Excel I Tutorial

VBA Create Chart in Excel VBA. Chart is an object in a Workbook. Charts are also called as graphs. The charts collection has a chart object. Graphs or Charts are used to represents data visually. In this tutorial we learn how to create an embed chart and chart sheet in Excel VBA.

Table of Contents:

Overview
Create Chart in Excel Workbook using VBA
Embedded Charts using VBA
Chart Sheets using VBA in Excel
Instructions to Run VBA Macro Code
Other Useful Resources

Create Chart in Excel Workbook using VBA

We can create chart in two different ways. Let us learn about those two types of chart creation.

Embedded Charts
Chart Sheets

The first method we can embed chart in the existing or specified Worksheet in a Workbook. In the second method chart sheet is created separately in the Workbook.

Create Chart Input Data in Excel VBA — Chart Input Data

Embedded Charts using VBA in Excel

Let us learn how to create the embedded charts in Excel VBA. An embed chart created in the active Worksheet in a Workbook.

  
'Create an Embedded chart
Sub VBAF1_Create_Embedded_Chart_Simple()
    
    ActiveSheet.Shapes.AddChart.Chart.SetSourceData Sheets("Charts").Range("A1:B5")
    
End Sub

We have created quite simple one line statement to create a chart. The above code also written as in the following way.

‘An Embedded chart using VBA
Sub VBAF1_Create_Embedded_Chart()
    
    ‘Declare Variables
    Dim oWs As Worksheet
    Dim rRng As Range
    Dim oCht As Object
    
‘Define an object   
 Set oCht = ActiveSheet.Shapes.AddChart
    ‘Embedded Chart statement
    oCht.Chart.SetSourceData Sheets("Charts").Range("A1:B5")
    
End Sub

Output: You can see below output screenshot from the above procedures.

Chart using Excel VBA — An Embedded chart using Excel VBA

Chart Sheets using VBA in Excel

We can create Chart Sheet(s) on its own chart sheet in Excel workbook using VBA. Here is the VBA code to create simple chart sheet in Excel workbook.

'Chart Sheet creation using VBA
Sub VBAF1_Create_ChartSheet()

    'Define Variables
    Dim MyChart As Chart
    Set MyChart = Charts.Add
    
    With MyChart
        .SetSourceData Sheets("Charts").Range("A1:B5")
        .ChartType = xlColumnClustered
    End With
 
End Sub

Output: Here is the output screenshot from the above VBA code. In the below image we can notice that separate sheet created on its own chart sheet. Default name of the chart sheet is Chart1 , Chart2, Chart3,…etc.