Flappy Bird in Excel VBA Part 3 - Windows API Functions
This part of the tutorial describes how to make use of Windows API functions to extend the power of Excel VBA.

Posted by Andrew Gould on 11 April 2014

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# Flappy Bird in Excel VBA - Windows API Functions

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

Do Until BirdCell.Offset(1, 0).Row >= FloorRange.Row

BirdCell.Clear

Set BirdCell = BirdCell.Offset(1, 0)

BirdCell.Interior.Color = rgbCornflowerBlue

Loop

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.

If you want to run the procedure as a test it's probably worth restoring the VB Editor window so that you can see Excel in the background. There are several other ways to run Excel VBA macros.

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

Do Until BirdCell.Offset(1, 0).Row >= FloorRange.Row

BirdCell.Clear

Set BirdCell = BirdCell.Offset(1, 0)

BirdCell.Interior.Color = rgbCornflowerBlue

Application.Wait Now + TimeValue("00:00:01")

Loop

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!

Unlike many other tasks in VBA, writing declarations for Windows API functions isn't the sort of thing you can learn to do intuitively. Instead you tend simply to copy and paste the declarations from a reference list whenever you need them. You can download this reference, the win32api.txt file, from the Microsoft website. There is also a version for 64-bit editions of Microsoft Office 2010 or later called Win32API-PtrSafe.txt.

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:

Use this version of the reference file... ...for these versions of Microsoft Office
win32api.txt Office 2007 or earlier, and 32-bit editions of Office 2010 or later.
Win32API-PtrSafe.txt 64-bit editions of Office 2010 or later.

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.

Public Declare Sub Sleep Lib "kernel32" ( _

ByVal dwMilliseconds As Long)

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

Public Declare PtrSafe Sub Sleep Lib "kernel32" ( _

ByVal dwMilliseconds As Long)

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.

#If Win64 Then

'Code is running in 64-bit Office

Public Declare PtrSafe Sub Sleep Lib "kernel32" ( _

ByVal dwMilliseconds As Long)

#Else

'Code is running in 32-bit Office

Public Declare Sub Sleep Lib "kernel32" ( _

ByVal dwMilliseconds As Long)

#End If

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:

Sub TestGameCode()

Dim BirdCell As Range

Dim FloorRange As Range

shTest.Select

Range("A1").Select

Cells.Clear

Set BirdCell = Range("R5")

Set FloorRange = Range("A40:Z40")

BirdCell.Interior.Color = rgbCornflowerBlue

FloorRange.Interior.Color = rgbBlack

Do Until BirdCell.Offset(1, 0).Row >= FloorRange.Row

BirdCell.Clear

Set BirdCell = BirdCell.Offset(1, 0)

BirdCell.Interior.Color = rgbCornflowerBlue

Sleep (50)

Loop

End Sub

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.