Animated charts in Excel using VBA and the Sleep function

How to make animated charts in Excel using Windows' Sleep API function to pause code execution. Spreadsheet with examples available for download.

This is fairly easy to do with a VBA macro. For an animated bar chart you just make a loop that updates a cell value, however on most computers the chart will update much too fast.

So the trick is to pause code execution sufficiently for the human eye to follow the animation. Watch the video below to see examples of animated charts. You can download the spreadsheet used for the video here.

Pausing VBA code execution

There are different ways to pause VBA code execution. Excel has a built-in Wait function:

Application.Wait Now + TimeSerial(0, 0, SecondsToWait)

Where code execution will pause for SecondsToWait. However waiting even 1 second between each chart update is too long - the animation will not be "fluid".

Excel also has an OnTime function that can "plan" execution of a procedure at a given time. On the page Blinking cells are examples with the OnTime function.

There are other ways to pause code (see Chip Pearson's page Pausing Code Execution), but the best for animated charts is the Windows API function "Sleep" in combination with VBA's DoEvents function, that allows things to update before proceeding.

Sleep and DoEvents

The Sleep API function suspends code execution for X number of milliseconds. To use the Sleep function you need to insert the following at module level, that is at the top where you can also declare variables for the module or the project as a whole:

#If VBA7 And Win64 Then
    '64 bit Excel
    Public Declare PtrSafe Sub Sleep Lib "kernel32" ( _
        ByVal dwMilliseconds As Long)
#Else
    '32 bit Excel
    Public Declare Sub Sleep Lib "kernel32" ( _
        ByVal dwMilliseconds As Long)
#End If

Sub Snooze()
   Sleep 100
   'Action code goes here
   DoEvents 'Wait for things to update
End Sub

Below you can see sample code for animated charts. It is the same code as in the sample spreadsheet, and you can see the animation in the video above.

There are many ways to supply the charts with source values. You can either read existing values, or the VBA macro can generate the values. That is not so important.

One important thing however is to set the Y-axis to a fixed scale. If the Y-axis changes automatically, the chart will flicker and look sick. If you don't know the max value, you can calculate it and scale the Y-axis runtime before animating the chart.

Sub Chart1()
'Animates a chart by adding
'source values cell by cell
'and pausing code execution.
'The charts' source values are in
'the range B4:B28 on the sheet
'"Data".
'The macro copies the values one by
'one from the range C4:C28, and for
'each copied value the chart is updated.
Dim rValues As Range 'Input range for the chart
Dim rCell As Range   'Range variable

On Error GoTo ErrorHandle

Application.ScreenUpdating = False

Worksheets("Data").Activate

Set rValues = Range("B4:B28")    'Input values for the chart
rValues.ClearContents            'Clear old input values
Worksheets("Chart1").Activate    'Activate sheet with chart

Application.ScreenUpdating = True

'Loop through the range and copy the input
'values cell by cell. By pausing code
'execution, we get the animation effect.
'If we didn't pause code execution, it
'would all happen in a flash.
For Each rCell In rValues
   'Pause code execution for 50 milliseconds
   Sleep 50
   'Copy value
   rCell.Value = rCell.Offset(0, 1).Value
   'Allow chart updating
   DoEvents
Next

BeforeExit:
Set rValues = Nothing
Set rCell = Nothing

Exit Sub
ErrorHandle:
MsgBox Err.Description & " Procedure Chart1"
Resume BeforeExit
End Sub

The next macro animates a bar chart and a pie chart on the sheet "Chart2". The 3 bars will show values and the pie chart will show the values in percent.

The bars' source values are in the cells A4:C4 on the sheet "Data," and the pie chart's source values are in the cells N4:P4.

A loop copies the original source values from a table to the charts' source cells, and by pausing code execution we get the desired animation effect.

The Sleep delay in milliseconds is read from cell C25 on the sheet "Chart2". The video above shows how the animation looks. Here we go:

Sub Chart2()
'Makes an animated bar and pie chart.
Dim rInput As Range  'First column with input values
Dim rCell As Range   'Range variable
Dim rBarA As Range   'The cell for bar A
Dim rBarB As Range
Dim rBarC As Range
Dim rPieA As Range   'The cell for pie A
Dim rPieB As Range
Dim rPieC As Range
Dim lSleep As Long

On Error GoTo ErrorHandle

Application.ScreenUpdating = False

Worksheets("Data").Activate

'Set the ranges
Set rInput = Range("E4:E28")
Set rBarA = Range("K4")
Set rBarB = Range("L4")
Set rBarC = Range("M4")
Set rPieA = Range("N4")
Set rPieB = Range("O4")
Set rPieC = Range("P4")


Worksheets("Chart2").Activate

'Read the sleep value (delay) in milliseconds
lSleep = Range("C25").Value

Application.ScreenUpdating = True

'Loop
For Each rCell In rInput
   'Pause the code execution lSleep milliseconds
   Sleep lSleep
   With rCell
      rBarA.Value = .Value
      rBarB.Value = .Offset(0, 1).Value
      rBarC.Value = .Offset(0, 2).Value
      rPieA.Value = .Offset(0, 3).Value
      rPieB.Value = .Offset(0, 4).Value
      rPieC.Value = .Offset(0, 5).Value
   End With
   DoEvents 'Necessary for updating the charts
Next

BeforeExit:
Set rBarA = Nothing
Set rBarB = Nothing
Set rBarC = Nothing
Set rPieA = Nothing
Set rPieB = Nothing
Set rPieC = Nothing
Set rInput = Nothing
Set rCell = Nothing

Exit Sub
ErrorHandle:
MsgBox Err.Description & " Procedure Chart2"
Resume BeforeExit
End Sub

Animated charts in Excel using VBA macros

Pausing VBA code execution

Sleep and DoEvents