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