VBA4Play Part 2: Animation in Excel

This is the second part of the VBA4Play tutorial series. This tutorial is going to leverage off some of the code in VBA4Play Part 1: Movement and Collision Detection so be sure to view that prior to starting.

By the end of this tutorial, you’ll be able to make your own customized animations within Microsoft Excel. This tutorial is going to cover three different types of animation techniques: Uncontrolled, Wait, and Real-Time. Each type of animation has its own benefits and drawbacks so I’ll be going through the implementation of each of them step-by-step so that you will have the skills to use them in your own projects. Animation appears in every type of game so it’s important that we understand how to make effective animations so that we can make bigger and better games!

Okay, now let’s get started!

Part 2: Animation in Excel

Getting Started

To begin, open the workbook from VBA4Play Part 1: Movement and Collision Detection. You can download the finished workbook here: VBA4Play Part 1.

Uncontrolled Animation

Recall in VBA4Play Part 1 we were able to get the player to move left, right, up, and down when the player executes the MoveLeft(), MoveRight(), MoveUp(), and MoveDown() subroutines. This is an example of uncontrolled animation. The VBA code is executed immediately and the screen updates to show the player moving in a single frame. Uncontrolled animation is effective for this purpose because we only need 1 frame to be shown to the user. Lets create an animation that is a bit more complicated to illustrate the problems with uncontrolled animation.

Creating an Animated Spell Effect

I am going to be using the code from the Summon Angelic Horde spell in Arena.Xlsm but the same tools can be applied to any visual effect with as many frames that you want to make. The Summon Angelic Horde animation consists of 2 frames that repeat: the first frame consists of 4 angels on the diagonal sides of the target, the second frame consists of 4 angels on the vertical and horizontal sides of the target. When these 2 frames are repeated it creates the appearance that the angels are circling around the target.

Let’s start by creating a new module, the same way that we created the Move module in VBA4Play Part 1, by going to the menu bar and clicking Insert and then click Module. Rename this module to Spells which can be done in the properties window. Inside the Spells module, we will create a subroutine called Summon_Angelic_Horde() which will contain the code that executes when we want the animation to play. We will also create 2 functions: Make_Angel(Rownum As Integer, Colnum As Integer) and Remove_Angel(Rownum As Integer, Colnum As Integer) which will make the individual angels appear and disappear respectively. A function differs from a subroutine in that it can accept variables as parameters and return values. We need Make_Angel and Remove_Angel to be functions because we need to tell them where on the spreadsheet to make and remove the angels. At this point in time your Spells module should look like this:

Option Explicit
Option Base 1

Sub Summon_Angelic_Horde()

End Sub

Function Make_Angel(Rownum As Integer, Colnum As Integer)

End Function

Function Remove_Angel(Rownum As Integer, Colnum As Integer)

End Function

The Summon_Angelic_Horde() subroutine is going to require a new concept that we have not covered in VBA4Play yet. “For Loops” are chunks of code that will run a predefined number of times. We want the angels to circle the target 3 times so we will use a For Loop to repeat the 2 frames of our animation so that we do not have to copy and paste the 2 frames 3 times. As an example (don’t actually code this, this is just a sample to explain For loops):
For AngelLoopCounter = 1 To 5
Call Make_Angel(Target.Offset(-1, -1).Row, Target.Offset(-1, -1).Column)
Next AngelLoopCounter

Executes the exact same code as would:
Call Make_Angel(Target.Offset(-1, -1).Row, Target.Offset(-1, -1).Column)
Call Make_Angel(Target.Offset(-1, -1).Row, Target.Offset(-1, -1).Column)
Call Make_Angel(Target.Offset(-1, -1).Row, Target.Offset(-1, -1).Column)
Call Make_Angel(Target.Offset(-1, -1).Row, Target.Offset(-1, -1).Column)
Call Make_Angel(Target.Offset(-1, -1).Row, Target.Offset(-1, -1).Column)

Using For loops makes repetitive animations easier to code.

Okay, let’s code the Summon_Angelic_Horde() subroutine!
We’ll start by declaring our variables at the top of the subroutine. We need 2 variables: AngelLoopCounter as an Integer as it will tell us how many times the animation has looped inside the For loop, and Target as Range as it will store the cell that the player is trying to cast the spell on. The variable declarations should look like this:

Dim AngelLoopCounter As Integer
Dim Target As Range

Next, we need to create an If statement for error checking purposes because we only want to run the code if the player is casting a spell within the playable area. If the player has selected a valid location then we will set our Target variable to the ActiveCell and execute the animation. Notice the syntax for assigning a range variable, it requires the word “Set”.

If (ActiveCell.Row >= 5 And ActiveCell.Row = 5 And ActiveCell.Column <= 23) Then
Set Target = Cells(ActiveCell.Row, ActiveCell.Column)
End If

After setting the Target variable but still within the if statement is where we need to put our animation. In order to do this we need to call the functions Make_Angel and Remove_Angel and pass the locations of where we want to create angels. We use the Offset function to determine where the location of the angels should be, similar to how we used the Offset function in VBA4Play Part 1 to determine where the player should be moving.

For AngelLoopCounter = 1 To 3
‘Frame 1: Make Diagonal Angels and Remove Horizontal and Vertical Angels
Call Make_Angel(Target.Offset(-1, -1).Row, Target.Offset(-1, -1).Column)
Call Make_Angel(Target.Offset(1, 1).Row, Target.Offset(1, 1).Column)
Call Make_Angel(Target.Offset(1, -1).Row, Target.Offset(1, -1).Column)
Call Make_Angel(Target.Offset(-1, 1).Row, Target.Offset(-1, 1).Column)
Call Remove_Angel(Target.Offset(0, -1).Row, Target.Offset(0, -1).Column)
Call Remove_Angel(Target.Offset(1, 0).Row, Target.Offset(1, 0).Column)
Call Remove_Angel(Target.Offset(0, 1).Row, Target.Offset(0, 1).Column)
Call Remove_Angel(Target.Offset(-1, 0).Row, Target.Offset(-1, 0).Column)

‘Frame 2: Make Horizontal and Vertical Angels and Remove Diagonal Angels
Call Make_Angel(Target.Offset(0, -1).Row, Target.Offset(0, -1).Column)
Call Make_Angel(Target.Offset(1, 0).Row, Target.Offset(1, 0).Column)
Call Make_Angel(Target.Offset(0, 1).Row, Target.Offset(0, 1).Column)
Call Make_Angel(Target.Offset(-1, 0).Row, Target.Offset(-1, 0).Column)
Call Remove_Angel(Target.Offset(-1, -1).Row, Target.Offset(-1, -1).Column)
Call Remove_Angel(Target.Offset(1, 1).Row, Target.Offset(1, 1).Column)
Call Remove_Angel(Target.Offset(1, -1).Row, Target.Offset(1, -1).Column)
Call Remove_Angel(Target.Offset(-1, 1).Row, Target.Offset(-1, 1).Column)

Next AngelLoopCounter

‘Remove the remnants of Frame 2 to clear all angels
Call Remove_Angel(Target.Offset(0, -1).Row, Target.Offset(0, -1).Column)
Call Remove_Angel(Target.Offset(1, 0).Row, Target.Offset(1, 0).Column)
Call Remove_Angel(Target.Offset(0, 1).Row, Target.Offset(0, 1).Column)
Call Remove_Angel(Target.Offset(-1, 0).Row, Target.Offset(-1, 0).Column)

Now the Summon_Angelic_Horde subroutine is done and you have a subroutine that does absolutely nothing! Congratulations! :D

The reason nothing is happening is because the functions Make_Angel and Remove_Angel are still empty, so while the Summon_Angelic_Horde subroutine is calling them, there is nothing in them to execute. Let’s change that by coding the Make_Angel function!

This function checks to ensure the area is empty, and if so creates an angel by changing the text to “^i^” and changing the background colour to white. The entire Make_Angel function should look like the following:

Function Make_Angel(Rownum As Integer, Colnum As Integer)
Dim location As Range
Set location = Cells(Rownum, Colnum)

If (location.Value = “”) Then
location.Value = “^i^”
location.Interior.Color = VBA.RGB(255, 255, 255)
End If

End Function

Now let’s code the Remove_Angel function which checks to ensure the area is an angel and makes it empty by removing the text and changing the background colour to our ground colour. The entire Remove_Angel function should look like the following:

Function Remove_Angel(Rownum As Integer, Colnum As Integer)
Dim location As Range
Set location = Cells(Rownum, Colnum)

If (location.Value = “^i^”) Then
location.Value = “”
location.Interior.Color = VBA.RGB(250, 192, 144)
End If
End Function

All done! We now have a perfectly working uncontrolled animation! If you run the Summon_Angelic_Horde subroutine, (optional) you can create a button to call the subroutine like we did in VBA4Play1 with the Move subroutines if you wish, or just press F5 while in the subroutine, you can see the animation… very quickly… for a moment. I did this to illustrate the limitations of uncontrolled animations, you do not have the ability to control how quickly the frames of the animation animate. This means that the animation will run as quickly as the computer can process it. This also means that the animation will not run consistently across various computers. I made an animated gif so we can all enjoy this abomination of an animation that we have created together!

Kill... Me...

Uncontrolled animations are good for single frame animations such as when we coded the moving player in VBA4Play Part 1 but they become problematic when handling multiple frames. Not to worry though, we can fix this with the Wait animation type!

Wait Animation

A Wait animation uses the Application.Wait function to slow down an animation so that it is visible to the user and will be consistent every time the animation is run. This gives us control over the animation speed and can display multiple frames.

Go back to our For loop in the Summon_Angelic_Horde subroutine and add the following line of code after each frame inside the For loop:

Application.Wait (VBA.Now + TimeValue(“0:0:01″))

This will pause the entire application from updating for 1 second between each of the frames. If you run the Summon_Angelic_Horde subroutine you will now notice that the animation is exactly the same as the animation that appears in the game Arena.Xlsm!

VBA4Play Part 2 Wait Animation

The wait animation clearly shows what we were trying to display. However, it pauses the entire application to do so, this is problematic if we want to be able to receive user input while the animation is playing. This is also problematic because we cannot speed up the animation beyond 1 frame per second. In order to address these issues, we’ll examine the third animation method: real-time animations!

Real-time Animation

Real-time animations will allow us to speed up the animations to the millisecond as well as allow for seamless input from the player so that the player can move while the animation is still running. For the purposes of demonstration, I created a duplicate subroutine called Summon_Angelic_Horde_Realtime in the workbook file at the end of this tutorial so that you will have working copies of all 3 types of animation methods. You may choose whether you want to create a duplicate subroutine or just edit your earlier subroutine. Let’s get coding! :D

At the top of your Spells module after your Option statements but before your subroutines, insert the following line of code. This will import the “Sleep” function from the kernel32.dll file.

Declare Sub Sleep Lib “kernel32″ (ByVal dwMilliseconds As Long)

The Sleep function works like Application.Wait except it works with milliseconds rather than seconds which allows for a much faster animation. WARNING: This may cause compatibility problems with non-windows operating systems if they do not have a kernel32.dll file.

Now we’re going to need to declare some new variables. At the top of your subroutine, include the following lines of code:

Dim UpdateCounter As Double
Const RepeatNum = 10
Const FlipSpeed = 50

UpdateCounter is going to keep track of how much time has passed since the animation begins. We will use that variable to ascertain when the appropriate time is to change the animation frame. RepeatNum is the number of times that we want the animation to repeat. In the prior example we had hard coded this value to 3. FlipSpeed is how quickly we want the animation to run in 10s of milliseconds so a speed of 50 is 500 milliseconds or half of a second.

Inside of our If statement that verfies the target is in the active playing area, and above our for loop, we need to reset the new counter variables to 0. Add the following lines of code:

UpdateCounter = 0
AngelLoopCounter = 0

We now need to replace our For loop with a Do While loop. While a For loop runs of a predetermined number of iterations (3 in the previous example), a Do While loop will continue to iterate infinitely while a condition is true. WARNING: Do While loops have the potential to crash your spreadsheet if you do not properly ensure that the condition eventually evaluates to false so the loop can end. Replace your For Loop with the following code for the Do While loop: (Note that I’ve removed the code underneath the frame comments for spacing purposes, please ensure that you have the various Make_Angel and Remove_Angel function calls that are available in the “Creating an Animated Spell Effect” section.)

Do While AngelLoopCounter < RepeatNum
VBA.DoEvents
Sleep 10
If (UpdateCounter = FlipSpeed * AngelLoopCounter) Then
‘Frame 1: Make Diagonals and Remove Horizontal and Vertical
Call Make_Angel…
ElseIf (UpdateCounter = FlipSpeed * AngelLoopCounter + FlipSpeed * 0.5) Then
‘Frame 2: Horizontal and Vertical and Remove Diagonals
Call Make_Angel…

AngelLoopCounter = AngelLoopCounter + 1
End If
UpdateCounter = UpdateCounter + 1
Loop

This code will keep looping while the animation plays until it hits our RepeatNum. The VBA.DoEvents line is what tells the application to accept other events outside of that loop and this is what will allow the player to move around while the animation is still playing. The Sleep 10 line is doing the same thing that Application.Wait did except instead of waiting 1 second it’s now waiting 10 milliseconds. If you’re doing real-time animation, it’s important to limit your sleep time so it does not appear as if your game is unable to keep up with the player input. I’ve found that between 1 and 10 milliseconds of Sleep is ideal depending on what you are trying to accomplish. Lastly, the if statements tell us when we want to switch the frame based on the FlipSpeed that we defined earlier. Save your work and try it out, you should be able to see a faster animation as well as move around at the same time. Yay! :D

VBA4Play Part 2 Real-time Animation

Summary

VBA4Play-Part-2-Summary
I hope you’ve enjoyed this tutorial. While the animation covered in the tutorial was a simple 2 frame animation, the concepts can be used to create any animation that you want. Everything discussed in this article can be found in the excel workbook here: VBA4Play Part 2. Please let me know if you have any feedback, as well as suggestions for what you’d like me to do in the future. Feel free to ask me any questions you may have about the content covered and I will get back to you as soon as I can. As always, you can reach me in the comments, by e-mail, or on Twitter @CaryWalkin. Thanks! :)

About these ads
  1. Hello, I tried to use your tutorial to compile a simple VBA. As I have no experience in programming, I had difficulty. Since you asked for feedback, I decided to give it.

    You wrote the following:

    If you want to, you can verify that this is working by creating a Hello World! application by adding the following line of code into the NewGame subroutine.

    I didn’t understand where to put the code. I ended up with this:

    Sub NewGame()

    MsgBox(“Hello World!”)

    End Sub

    • Hi Russ,

      That is exactly correct. If you have followed along with the steps of VBA4Play Part 1 you will see the “Hello World!” message box appear when the NewGame subroutine is run. In the step prior to this we coded that the subroutine would run automatically whenever the workbook is opened. If you don’t feel like closing/reopening, you can run any macro by simply pressing F5 while you are editing the subroutine. Later on in VBA4Play Part 1 I show how to assign macros to game objects so you can run them by simply clicking an object in the spreadsheet.

      I hope this helps!

  1. June 28th, 2013

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 43 other followers

%d bloggers like this: