Animate a Chart in Excel

Animate a chart in Excel to create a cool effect for your charts. You will see the chart build itself in front of your very eyes.

The chart can be created in the usual way and then VBA is used to create the animation effect.

In this example a combo box is used to provide a way for the user to select the chart they want to see. The chart then gradually appears one data point at a time.

The VBA used to Animate the Chart

The code below is used to animate the chart from the change event of the combo box.

Private Sub cboQtr_Change()
Dim i As Single
Dim QtrNo As Integer
Dim SalesPerson As Integer
Dim SalesValue As Single
Sheets("Calculations").Range("B2:B8").ClearContents
QtrNo = Right(Range("I2").Value, 1)
For SalesPerson = 1 To 7
  SalesValue = Range("C3:F9").Cells(SalesPerson, QtrNo).Value
  For i = 1 To SalesValue Step 3
    Sheets("Calculations").Range("B2:B8").Cells(SalesPerson) = i
    DoEvents
  Next i
Next SalesPerson
End Sub

The Explanation

Four variables are used in the code.

• One for looping through each salesperson in the table
• One to store the Qtr selected by the used from the combo box
• One for storing the salespersons sales total currently being animated
• And one for incrementing from 1 to the salesperson sales total creating the animated effect

The Calculations sheet contains the table that the chart is created from. This data is cleared first and then re-created.

The Right function is used to extract the number from the Qtr selected by the user.

There are two loops. One for moving through each salesperson in turn. Then an interior loop to increment a value up to the salesperson total.

This code steps 3 at a time. This value can be increased to speed the animation up, or decreased to slow it down.

The DoEvents function is used to create a mini pause in the loop each time it iterates. This function is essential to the working of the VBA procedure.

To learn these VBA techniques and a whole lot more, make sure you sign up for our Excel VBA course.

Watch the Video