Flappy Bird in Excel VBA - Windows API Functions

Useful Links

Return to the Flappy Bird in Excel VBA Tutorial index.

Download Flappy Owl Pt3 - Windows API.

Links to all of our training in Excel and VBA training resources.

Introduction

This part of the tutorial begins the process of setting up a timing loop which controls how often the game will be updated. In order to do this we'll need to declare some Windows API functions. These functions will allow Excel to work with the small units of time that we need to make our game run at a sensible speed. If you'd prefer not to write the code yourself you can download the workbook for this part of the tutorial instead.

Making the Bird Fall 

One of the key features of our game is that the bird object continuously falls down the screen until it reaches the bottom. We could achieve this effect by adding a simple VBA Do Loop to our TestGameCode subroutine, something like this:

This would certainly achieve the end result; the problem is that it would happen so quickly that we'd barely be able to see it happen. Clearly, we need to control the speed at which the bird falls in order to have any hope of playing a game.

Making Excel Wait 

Excel actually has a built-in Wait method which is very simple to use. Here's a modified version of the loop we added above:

The additional line makes Excel wait for 1 second before processing the loop again. The Now function returns the date and time; the TimeValue function converts a string of text into an actual unit of time; the Wait method pauses execution of the code until the calculated time has been reached.

We've certainly managed to slow the procedure down, but now it's too slow! Unfortunately, the smallest unit of time that the Wait method can handle is a second. Clearly, this means that we need another way to control the time of the procedure.

Declaring Windows API Functions

VBA allows you to declare Windows API functions which you can then call into your VBA procedures. These functions give you far more control over the computer on which the code is running than the native Excel VBA methods - you can do everything from access the Windows clipboard to write values to the Windows registry. Powerful stuff!

For the rest of this tutorial you can copy your declarations from either the reference text file if you've downloaded it, or directly from this blog. The file that you'll need to use depends on which version of Microsoft Office you're using, as described in the table below:

You can read more about the differences in VBA between 32-bit and 64-bit editions of Microsoft Office on the Microsoft website.

The Sleep Function 

The first Windows API function that we're interested in is called Sleep (technically, it doesn't return a value but I'm still going to refer to it as a function for the purposes of this blog). It's a little like the Wait method except that you can use much smaller units of time, down to a millisecond.

As we're going to be declaring several Windows API functions (as well as lots of other things) we'll have a separate module for these declarations. Insert a new module as before and change its name to modPublicDeclarations.

Add the following code to the module, just below Option Explicit.

If you're using a 64-bit edition of Office 2010 or later you'll need a slightly different version which looks like this:

The word Public indicates that the function can be called in any module in the project and is optional - if you miss it out the function can still be called from any module. I prefer to leave it in as it makes the intent clearer.

The name of the function is Sleep and it belongs to a specific library called kernel32. 

The space and underscore character at the end of the first line is used to continue writing the same instruction on the next line. You can happily delete the space and underscore and put everything onto a single line. 

The parameter dwMilliseconds is declared in the same way you'd declare parameters for any procedure you might write in VBA.

Version-Independent Windows API Function Declarations

It's possible to write your Windows API function declarations so that they'll work in any version and any edition of Office. To do this you'll need to add a conditional directive to the declarations. Here's how to do this for the Sleep function.

This code makes sure that the correct function is declared for whichever version and edition of Office is being used. For the rest of the tutorial I'll make sure to provide this style of declaration. For convenience, if you're only going to be running this code on your own machine, you could just use the version of the function that relates to your own version of Office. You can read a little more about testing which version of Office is running at the Microsoft website.

Calling a Windows API Function

Now that we've declared the function we can call it in our procedures. Double-click on the modTestCode module to return to it. We can now replace the Wait method with a call to the Sleep function:

The line Sleep (50) tells the procedure to pause for 50 milliseconds (a 20th of a second) before processing the loop again. If you run the subroutine while you can see Excel in the background (just restore down the VB Editor window) you'll see the 'bird' fall at a steady rate. Feel free to adjust the number in parentheses to a different value to see the effect on the speed. For reference, there are 1000 milliseconds in a second.

What's Next

Now that we know about the Windows API we can declare a few more functions that will help us to create the game. Although we'll use the Sleep function in certain parts of our game this isn't actually the function that we'll use to control our main game loop.