Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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. |
In this blog
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.
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.
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.
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.
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 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.
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.
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.
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.
Some other pages relevant to the above blog include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.