Generating Dynamic Charts With VBA in Excel

A powerful graph range is an information range that refreshes naturally when you change the information source. This unique reach is then utilized as the source information in an outline. As the information changes, the powerful reach refreshes right away which prompts an update in the outline. 

The following is an illustration of a graph that utilizes a unique outline range.

Sample Data 

 

Follow the further steps in Generating Dynamic Charts with VBA in Excel

Step 1: Opening the Visual Essential Window. Press ALT+F11 on your console to open the Visual Essential Window.

 

Step 2: Embedding Another Module. Go to the Addition > Module choice in the toolbar. Click on Module. Another module called Module1 will be embedded.

 

Step 3: Putting the VBA Code

sub create _dynamic_ chart ()

Application . ScreenUpadting  = False

with Activesheet. shapes ( Application .caller ) .Fill.Forecolor

      If  . Brightnesss = 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 

  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 ) ) = . Text Frame2.TextRange . Characters . Text

                 ReDim Preseve Sequence ( UBound ( Series ) + 1 )

         End If

  End With

Next i 

If UBound ( Sequence ) >0 Then ReDim Preserve Sequence ( UBound ( Sequence ) – 1 )

Worksheets (“sheet 1″ ) . ListObjects ( ” Table 1 ” ). Range . AutoFilter Field  :=1

Worksheets ( ” sheet 1″). ListObjects ( “Table 1 ” ) . Range .AutoFilter _

      Field : =1 , Criterial : = Sequence  , Operator : =xl FilterValues

Application.ScreenUpadting = True

End Sub

Step 4: Saving the Workbook in XLSM Format, then return to the workbook and save it as an Excel Macro-Enabled Workbook.

 

Step 5: Run the code from the Run Sub/UserForm choice in the toolbar.

 

You’ll find a powerful graph made in view of the table in Sheet2 of the worksheet.