Rifat Hassan
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
Read More: Excel VBA: Get Source Data Range from a Chart (with Quick Steps)
⧪ 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.
Read More: How to Create a Combo Chart in Excel (2 Easy Ways)
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.
