This post shows how to make a dynamic chart using Excel VBA macro. This dynamic chart adjusts graphs automatically when a length of data is changed.

Dynamic Excel Chart

Expected Output

The following animation video shows how useful a dynamic chart is when data is added or a length of data is changed frequently or periodically or unexpectedly.

Now let's learn how to do this work.

Setting some Excel ranges

Given a data and a chart, we make two Excel ranges which are used in Excel VBA macro for a flexible cell shifts.

range	name
F2	template_chart_name
B5	origin

template_chart_name is a chart name and origin is used for the relative location. In particular the name of a chart can be found by clicking that chart and see the box above A column.

Worksheet_Change macro function

When data is changed, our chart needs to reflect this change. For this purpose Worksheet_Change Excel macro function is used. This is called when some change is detected in the sheet.

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

It is to note that Worksheet_Change Excel macro function is defined not in a module but in its own sheet script (sheet2 in our case, of course, it depends on your Excel environment) as follows.

Excel VBA macro code

Now that we add Worksheet_Change Excel macro function, we need to add VBA code into it as follows.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
    
  Dim ws As Worksheet
  Dim RngX As Range, RngY As Range
  Dim Origin As Range, ser As Series
  Dim ChtObj As ChartObject, tcn As String
  Dim i As Long, ncnt As Long
    
  Set ws = Application.ActiveWorkbook.ActiveSheet
  tcn = Range("template_chart_name")
  Set Origin = ws.Range("origin")
  
  With ws.ChartObjects(tcn)
    For i = 1 To .Chart.SeriesCollection.Count
        
      Set ser = .Chart.SeriesCollection(i)
            
        ' get a new number of data
        ncnt = Origin.Offset(1, 0).End(xlDown).Row - Origin.Row
 
        ' set new X and Y data
        Set RngX = ws.Range(Origin.Offset(1, 0), Origin.Offset(ncnt, 0))
        ser.XValues = "=" & RngX.Address(False, False, xlA1, xlExternal)
        Set RngY = ws.Range(Origin.Offset(1, i), Origin.Offset(ncnt, i))
        ser.Values = "=" & RngY.Address(False, False, xlA1, xlExternal)
        
        Set RngX = Nothing
        Set RngY = Nothing
        Set ser = Nothing
      Next i
    End With
    
  Set ws = Nothing
  Set Origin = Nothing
 
End Sub
 
Colored by Color Scripter
cs

As soon as this VBA macro code is added into the Worksheet_Change function, we can find that a dynamic chart is working on the fly. Output is the same as the animation video file which is shown at first.

Concluding Remarks

In this post we made a dynamic chart using Excel VBA macro function. Now every time data is augmented or deleted, you can see the chart which you want to see without any modification or effort. \(\blacksquare\)

SHLee AI Financial Model

Pages