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

Get FREE Advanced Excel Exercises with Solutions!

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


Watch Video – 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.

Data Table to Create a Dynamic Chart Using Excel VBA

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.

Inserting Excel Table from Insert tab

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

Renaming Excel Table


⧪ 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.

Opening the VBA Window to Create a Dynamic Chart Using Excel VBA


⧪ 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

VBA Code to Create a Dynamic Chart in Excel

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.

Saving the File to Create a Dynamic Chart Using Excel VBA


⧪ Step 5: Final Output

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

Dynamic chart using Excel VBA

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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo