Animation in Excel using VBA

msofficeshare logo

Excel does not offer supports to animation as good as PowerPoint does, but it is still possible to do limited animation.  It is a lot of fun and a good way to impress your friends and co-workers.

The two key functions in VBA to allow the animations are the timing function and the visibility function.  The timing function controls the speed of the animations.  The Visibility function controls the appearing and disappearing of the picture frames.  

It is much like the making of cartoons before the availability of the computer graphic (CG).  Human eyes cannot detect the individual picture frame if the picture frames go faster than 24 frames in a second.  As a result, cartoonist drew 24 pictures (the minimum requirement) for each second of cartoon that you watch.  Then each picture frame is photographed one by one.  When the cartoon is played, the current picture frame (the “visible” one) will “overwrite” the previous one (the “hidden” one).

The example to be used is a running horse which contains only 12 frames.  The first step is to collect images with a consecutive movement sequence.  When I put them into a blank Excel worksheet, each image will be automatically named as “Picture 1”, “Picture 2”, … “Picture 12”.

If the image has a background, it will be better to remove it by using the “Remove Background” function of the “Picture Format Tools”.

Afterwards, I stacked up the images on a horizontal basis.  Each frame will move a bit right of the image with the preceding movement sequence.

pic1 excel animation
pic2 excel animation
pic3 excel animation

Each frame will be run 0.1 second.  Since 0.1 second is only 10 frames per second, the resulted animation will not be as smooth as what you can see in a cartoon on TV, but it is still look good enough.  The user actually has the choice of a faster frame turnover by changing the parameters in the VB codes.  The VB module that I use to control the frame timing is as below:

Dim StepTime As Single, StepFreq As Single, StepDuration As Single

   

    StepTime = 0

    StepFreq = 0.1

StepDuration = StepFreq * 11    '12 picture frames

 

Sub DelayTime(StepFreq)

    Dim PauseTime, Start, Finish, TotalTime

   

    PauseTime = StepFreq    ' Set duration.

    Start = Timer    ' Set start time.

    Do While Timer < Start + PauseTime

        DoEvents

    Loop

    Finish = Timer    ' Set end time.

    TotalTime = Finish - Start

End Sub

The variable “StepFreq” in the above VB codes is the timing control of the frame movement.  “0.1” stands for 0.1 second from switching one frame to another other.  User can manually modify it on the VB codes directly or create an interface with the Excel worksheet if desired.  The “StepFreq” will then multiply the number of frames (minus one) to create the “StepDuration” which is the entire amount of time that the whole process will run though.  The “StepFreq” will run through a subroutine called “DelayTime” which controls the timing of the operations.

The “DoEvents” statement can be confusing to user who never used it before.  What it does is that the statement releases the computer power back to the CPU instead of holding it up when running the VB module.  The CPU can use that split of a second of the pause time to do processing for other things that your computer needs to perform.

The next step is to make individual image appear and disappear.  There should always be only one image appear during the whole time when the animation is run.  I used the “visibility” function in Excel VBA to hide or display the selected image.  The codes are as follow:

ActiveSheet.Shapes.Range(Array(SelectPicture)).Visible = True

ActiveSheet.Shapes.Range(Array(SelectPicture)).Visible = False

When “Visible” is true, Excel will display the image.  If it is false, Excel will hide the image.

The final step is create looping to run the 12 picture frames to create the animation effect.  The entire VB codes (with 2 subroutines) is as follow:

Sub RunningHorse()

    Dim StepTime As Single, StepFreq As Single, StepDuration As Single

    Dim PictureFrame As Integer, SelectPicture As String

   

    StepTime = 0

    StepFreq = 0.1

    StepDuration = StepFreq * 11    '12 picture frames

   

    Call PictureDisappear

   

    PictureFrame = 1

    SelectPicture = "Picture " & PictureFrame

    Do Until StepTime > StepDuration

        ActiveSheet.Shapes.Range(Array(SelectPicture)).Visible = True

        Call DelayTime(StepFreq)

        ActiveSheet.Shapes.Range(Array(SelectPicture)).Visible = False

       

        StepTime = StepTime + StepFreq

        PictureFrame = PictureFrame + 1

        SelectPicture = "Picture " & PictureFrame

    Loop

    Range("A1").Activate

End Sub

 

Sub DelayTime(StepFreq)

    Dim PauseTime, Start, Finish, TotalTime

   

    PauseTime = StepFreq    ' Set duration.

    Start = Timer    ' Set start time.

    Do While Timer < Start + PauseTime

        DoEvents

    Loop

    Finish = Timer    ' Set end time.

    TotalTime = Finish - Start

End Sub

 

Sub PictureDisappear()

    PictureFrame = 1

 

    Do Until PictureFrame = 13

        ActiveSheet.Shapes.Range(Array("Picture " & PictureFrame)).Visible = False

        PictureFrame = PictureFrame + 1

    Loop

End Sub

Have fun and enjoy the show !

running horse
running horse