Animations from VBA

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
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)
            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:

Zoom in x2 24 times

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:

Zoom in x2 48 times
This entry was posted in Animation, Excel, Link to Python, Maths, Newton, PyXLL, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

1 Response to Animations from VBA

  1. Pingback: Display Matplotlib animations in Excel | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.