A convenient way to create an animation in Excel is to create on-sheet formulas or user defined functions to generate the required data, then use VBA to iterate through the range of input values so that the chart or image will update for each iteration. A problem with this approach is that often the image will not redraw, while the code is running.

Searching for a solution to this problem, there were surprisingly few discussions on this problem, and most of those I did find I couldn’t get to work, but eventually I found a solution using ActiveWindow.SmallScroll, which has the twin benefits of being very simple, and obvious how it works. Example VBA code is shown below.

Edit 2 Dec. 2021: Modifications to the code for generating the images resulted in very short cycle times, so I added some code to “sleep” between cycles, so the cycle time was at least 1 second. Sleep is a Windows function, so has to be “declared” at the top of the code module. I have also added code to time each iteration, so the sleep period is reduced as the time for generating the image increases. An added benefit of this code is that it has removed the white screen flashing between each iteration, so the resulting video is much smoother.

#If VBA7 Then ' Excel 2010 or later
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
#Else ' Excel 2007 or earlier
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
#End If

Sub Zoomin()
Dim Startfact As Double, inc As Double, steps As Long, Fact As Double
Worksheets("Sheet4").Activate
With Application.ActiveSheet
    Startfact = Range("Start").Value
    inc = Range("Factor").Value
    steps = Range("steps").Value
   
    Fact = Startfact
    For i = 1 To steps
        STime = Timer()
        Range("Scale").Value = Fact
        Range("Iteration") = i
        ActiveWindow.SmallScroll down:=1
        ActiveWindow.SmallScroll up:=1
        Fact = Fact * inc
        TimeDiff = (Timer() - STime) * 1000
        If TimeDiff < 950 Then
            Sleep (1000 - TimeDiff)
        Else
            Sleep (50)
        End If
    Next i
End With
End Sub

An example animation generated with this routine (plus a Python user defined function to generate the images) is shown below. More details of the code for generating the images will be provided in a later post:

With the faster image generation I have added a second video zooming in 48 times, with a final magnification of 2.8E+14 times, which is about as far as you can get with the resolution allowed with 64 bit float values: