VBA Create Chart in Excel

Create Chart in Excel VBA

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.

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.

  1. Embedded Charts
  2. 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.

Chart Sheet using Excel VBA
Chart Sheet using Excel VBA

Instructions to run macro or procedure:

Here are the step by step instructions to use above macro codes in Visual basic editor.

  • Open Visual Basic Editor(VBE) or you can click Alt +F11
  • Go to code window or click F7
  • Copy above specified macro code or procedure
  • Paste above copied code in code window
  • Run macro by clicking F5 or Run command
  • See output screenshots for output of the above macro procedures.

Other Useful Resources

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

Charts Tutorial VBA Tutorial VBA Functions List Blog

Other related References

Excel Chart VBA Examples and Tutorials

Leave a Comment