How to Create a Dynamic Chart in Excel Using VBA (with Easy Steps)

In this article, I’ll show you how you can create a dynamic chart in Excel using VBA.

How to Create a Dynamic Chart in Excel Using VBA: 5 Easy Steps

Here we’ve got a worksheet called Dataset that contains a table containing the Country, Revenue, and Earnings of a company for some years.

Our objective today is to generate a dynamic chart from this table using Excel VBA.

⧪ Step 1: Creating Excel Table 

Select any cell in the dataset >> Go to the Insert tab >> Click on Tables >> Hit OK.

In the Table Design contextual tab, rename the table to “Financial_Data”.

⧪ Step 2: Opening the Visual Basic Window and Inserting a New Module

Press ALT+F11 on your keyboard to open the Visual Basic window >> Go to  Insert >> Click on Module option to insert a new module called Module1.

⧪ Step 3: Putting the VBA Code

This is the most important step. Paste the following VBA code inside the Module window.

⧭ VBA Code:

Sub Create_Dynamic_Chart()

Dim wsht As Worksheet
Dim chrt As Chart
Dim data_rng As Range

Set wsht = ActiveSheet
Set data_rng = Range("Financial_Data")
Set chrt = wsht.Shapes.AddChart2(Style:=-1, Width:=600, Height:=400, _
Left:=Range("G1").Left, Top:=Range("G1").Top).Chart

With chrt

    .SetSourceData Source:=data_rng
    .ChartType = xlColumnClustered
    .chartTitle.Text = "Revenue and Earnings by Country"
    .SetElement msoElementDataLabelOutSideEnd
    .SetElement msoElementPrimaryValueGridLinesMajor
    .SetElement msoElementPrimaryValueAxisShow
    .SetElement msoElementLegendBottom
    .SetElement msoElementPrimaryCategoryAxisTitleBelowAxis
    .Axes(xlCategory).AxisTitle.Text = "Country"
    
    
End With

End Sub

Read More: How to Create Chart with Dynamic Date Range in Excel

⧪ Step 4: Saving the Workbook in XLSM Format

Next, return to the workbook and save it as an Excel Macro-Enabled Workbook.

⧪ Step 5: Final Output

Click the Run button or press the F5 key to run the Macro.

Read More: How to Create Dynamic Chart with Multiple Series in Excel

Things to Remember

A table is the best way to create a dynamic chart. Because if you add or remove an element from the table, the table will automatically adjust, and so for the chart. But there are also other ways to accomplish this, like using a Named Range.

Download Practice Workbook

Conclusion

Therefore, this is the process to create a dynamic chart using VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.

Related Articles

• How to Make Dynamic Charts in Excel
• Create a Dynamic Chart Range in Excel
• How to Create Min Max and Average Chart in Excel
• How to Dynamically Change Excel Chart Data
• How to Create Dynamic Excel Charts with Drop-Down List
• How to Create Dynamic Charts in Excel Using Data Filters

<< Go Back to Dynamic Excel Charts | Excel Charts | Learn Excel