In this article, I’ll show you how you can create a dynamic chart in Excel using VBA.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
5 Easy Steps to Create a Dynamic Chart Using Excel VBA
Here we’ve got a worksheet called Sheet1 that contains a table containing the 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: Opening the Visual Basic Window
Press ALT+F11 on your keyboard to open the Visual Basic window.
⧪ Step 2: Inserting a New Module
Go to the Insert > Module option in the toolbar. Click on Module. A new module called Module1 will be inserted.
⧪ Step 3: Putting the VBA Code
This is the most important step. Put the following VBA Code in the module.
⧭ VBA Code:
Sub Create_Dynamic_Chart() Application.ScreenUpdating = False With ActiveSheet.Shapes(Application.Caller).Fill.ForeColor If .Brightness = 0 Then .Brightness = -0.150000006 Else .Brightness = 0 End If End With Dim Sequence() As String Desired_Shapes = Array("Rounded Rectangle 1", "Rounded Rectangle 2", "Rounded Rectangle 3") For i = LBound(Desired_Shapes) To UBound(Desired_Shapes) With ActiveSheet.Shapes(Desired_Shapes(i)) If .Fill.ForeColor.Brightness = -0.150000006 Then Sequence(UBound(Sequence)) = .TextFrame2.TextRange.Characters.Text ReDim Preserve Sequence(UBound(Series) + 1) End If End With Next i If UBound(Sequence) > 0 Then ReDim Preserve Sequence(UBound(Sequence) - 1) Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter Field:=1 Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _ Field:=1, Criteria1:=Sequence, Operator:=xlFilterValues Application.ScreenUpdating = True End Sub
⧪ 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
Run the code from the Run Sub / UserForm option in the toolbar.
You’ll find a dynamic chart created based on the table in Sheet2 of the worksheet.
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.
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.