Creating an Animated Count Up

Ray would like, if possible, a way to animate a "count up" for a value in a cell. For instance, in cell B7 he might have a value of 23. He would like to reference that value in cell E4 and have cell E4 count up from 0 to 23 (0, 1, 2, 3, etc.), showing each number in turn.

There is no built-in way to do this in Excel, but you can use a macro to do the animation. Basically, the macro would need to find out what is in cell B7, and then use a For...Next loop to step through the values between 0 and whatever is in B7. During each iteration of the loop, the value in E4 is changed and some sort of delay is introduced.

The delay portion of the macro is what actually provides the ability to vary how the macro does its work. The delay is necessary to make the animation seem to work; without it, the numbers in E4 would increment too quickly. Excel provides a couple of handy ways to implement the delay. For instance, this example of the macro relies on the Sleep function:

#If VBA7 Then
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim n As Integer
    Const cellWatch As String = "$B$7"
    Const cellCount As String = "$E$4"
    Const msec As Long = 200 ' milliseconds
    If Target.Address = cellWatch Then
        Application.EnableEvents = False
        Range(cellCount).Show
        If IsNumeric(Target) Then
            For n = 0 To Target ' skipped if Target < 0
                Range(cellCount) = n
                Sleep msec ' delay each increment
            Next n
        End If
        Range(cellCount) = Target
        Application.EnableEvents = True
    End If
End Sub

This code should be added to the ThisWorksheet module, as it is designed to run every time something changes in the worksheet. The code checks to see if the cell being changed is the target cell (B7). If it is, then it grabs the value there and jumps into a For...Next loop that updates whatever is in cell E4. The Sleep function is used to delay, in this instance, 200 milliseconds between each update of E4.

If you want a macro that is shorter and doesn't rely on the Worksheet_Change event handler, you might consider the following. It utilizes the Wait method to pause in the For...Next loop:

Sub CountUp()
    Dim J As Integer
    For J = 0 To Range("B7").Value
        Range("E4").Value = J
        Application.Wait (Now + TimeValue("0:00:01"))
    Next J
    Range("E4").Value = Range("B7").Value
End Sub

This version of the macro pauses a full second between each update to cell E4.

Whenever you use a macro like this that implements some sort of delay, remember that your worksheet may seem less responsive. This is because of that delay and however large the value is that is in cell E4. (The larger the value, the longer the aggregate delay.)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13753) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021.