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.

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


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

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

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF