Animation in Excel using VBA
Requirement: Visual Basic Knowledge
File to download: Excel Animation.xlsm
Related article: Different Looping Mechanisms in Excel VBA, Use graphical functions in Microsoft Office to create impressive graphics
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.
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 !
