Creating Animated Charts

I created the following charts a long time ago for my girlfriend (now my wife) who wanted some animated charts for an Excel presentation at work.  Not long after we had a request to display data in a chart which moves on a periodic basis.  This can be very useful especially if you want to show data aligned with rolling forecasts.  Displaying the last 12 months in a given period need not be as manual as changing all of the charts every month or quarter to update the chart for the last n periods.  

The way it is done is with dynamic ranges and a small amount of VBA. I am sure this could be done without VBA and I may get round to replicating this without code one day. As you move the Spinner Button (the object with the arrows at the bottom of the screen) to the right , the charts will increment in steps of one month. The chart can also decrement ( go back in time ) by clicking on the spinner button control on the left. I only have 17 months data in the data table above but you could include as many months as you wish.

The Static Ranges which are controlled by VBA are originally titled.  

Control

Header

xData

xFigs

xLabel

I have used the x to prefix the named ranges to show that these are the labels which are referred to in the Chart.

VBA to Control the Spinner Button

The following will control the whole process calling the Increase or Decrease routine each time the spinner Button is clicked.

Option Explicit
Private Sub
ScrollBar1_Change() 'Excel VBA macro to animate chart
If Range("Control") >=0 Then
Call Increase
Else
Call Decrease
End If
End Sub

VBA to Increase the Chart

The following procedure is the Excel VBA for increase the amount of series shown in a chart. This is for the manual animation where a button is pressed to trigger chart movement.

Sub Increase() 'Excel VBA for the increase part
Dim i As Integer
Dim j As Integer

i=Range("Control").Value
j=Range("Control").Offset(, 1).Value

If Range("Control").Value=17 Then
Exit Sub
Else:
Range(Cells(Range("xLabel").Row, i), Cells(Range("xLabel").Row, j)).Name="Header"
Range(Cells(Range("xfigs").Row, i), Cells(Range("xfigs").Row, j)).Name="xData"
End If

End Sub

VBA to Decrease the Chart

The following procedure is the Excel VBA for decreasing the amount of series shown in a chart. This is for the manual animation where a button is pressed to trigger chart movement.

Sub Decrease() 'Excel VBA for the decrease part
Dim i As Integer
Dim j As Integer

i=Range("Control").Offset(, 2).Value
j=Range("Control").Offset(, 3).Value

If Range("Control").Value=1 Then
Exit Sub
Else:
Range(Cells(Range("xLabel").Row, i), Cells(Range("xLabel").Row, j)).Name="Header"
Range(Cells(Range("xfigs").Row, i), Cells(Range("xfigs").Row, j)).Name="xData"
End If

End Sub

The Animation Process on Opening the File

The animation process itself on entry can not be achieved without the Assistance of VBA. There needs to be a trigger to change the cell value so the chart moves. The following Excel VBA procedure will run every one second.

Option Explicit

Sub Animate() 'Excel VBA to annimate, process runs 1 second apart for 12 seconds.
Dim i As Long
Call Reset
For i=1 To 12 'Loop for the 12 months in 12 seconds.
Range("Control").Offset(i Mod 1, 0)=Range("Control").Offset(i Mod 1, 0) + 2
DoEvents
Application.Wait Now() + TimeSerial(0, 0, 1)
Next i
Call Reset
End Sub


The following will reset the page to its original position.

Sub Reset() 'Excel VBA for a reset of charts back to original position
Range(Cells(Range("xLabel").Row, 1), Cells(Range("xLabel").Row, 12)).Name="Header"
Range(Cells(Range("xfigs").Row, 1), Cells(Range("xfigs").Row, 12)).Name="xData"
Range("Control").Value=1
End Sub


The following sets the chart off when the workbook opens.

Private Sub Workbook_Open() 'Excel VBA on open event for animation.
Dim i As Long
ActiveSheet.Shapes("ScrollBar1").Visible=False
Call Reset
For i=1 To 12
Range("Control").Offset(i Mod 1, 0)=Range("Control").Offset(i Mod 1, 0) + 2
DoEvents
Application.Wait Now() + TimeSerial(0, 0, 1)
Next i
Call Reset
ActiveSheet.Shapes("ScrollBar1").Visible=True

End Sub


The following file shows a practical example of the animated charts moving which contains all of the above Excel VBA procedures. The macros should be adaptable to just about any Excel file type.