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: