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.

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.

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: Opening the Visual Basic Window

Press ALT+F11 on your keyboard to open the Visual Basic window.

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

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

VBA Code to Create a Dynamic Chart 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

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.


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