by Allen Wyatt
(last updated March 28, 2020)
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 Excel in Office 365.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Want to print a document by using a macro? One way is to display the Print dialog box and allow the user to interact with ...Discover More
A common part of working with text strings in a worksheet is normalizing those strings so that they follow whatever rules ...Discover More
If you need to limit the cells that are accessible by the user of a worksheet, VBA can come to the rescue. This doesn't ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.