Microsoft Excel

Using Excel and Visual Basic for Applications to Create a Game

Greg Stemp and Jean Ross

At a Glance:

• Using Visual Basic for Applications
• Intercepting Events
• Launching Subroutines from Custom Buttons
• Cheats for Solitaire and FreeCell

Code download available at: StempRossExcel2009_05.exe(817 KB)

Contents

Gaming in Excel
How Word Maker Works
Protected and Unprotected
Within Range
Making a Change
Submitting a Word
Getting Hands-On

For better or worse, people often do things that they'd rather no one else know about. What sorts of things? To tell you the truth, those are things that we'd rather no one else know about.

Wait, here's an example: playing computer games at work. We've all played computer games at work one time or another.

There's a reason why Solitaire has been dubbed the "most-used program in the Windows universe."

Note: You may be wondering if there is any way to cheat in Windows Solitaire. As a matter a fact, there are several ways. For example, if you're playing the "draw three" version, you can hold down Ctrl+Shift+Alt and then click on the deck. Doing that allows you to draw just one card at a time. Or, you can press Shift+Alt+2. This will cause the cards to begin cascading as if you'd actually won the game. (Unless you're running Windows Vista—it looks like the available cheats went away in Windows Vista.) Anyone walking by will think you're a Solitaire master. Assuming, of course, they don't fire you for playing computer games at work.

Speaking of being fired (something that crosses our minds when we're supposed to be writing an article for TechNet Magazine and end up telling people how to cheat at Solitaire), being caught—and then fired—is a common concern for people who play computer games at work. As you might expect, a lot of companies frown on employees playing games during office hours. (Interestingly enough, this doesn't apply to either Jean or Greg. Microsoft believes they do less damage to the company by playing games than they would do if they actually did some work.)

Because of the inherent danger of being caught (and then fired), computer games often feature a "boss button" or "boss key." The idea here is sheer genius—when you hear the boss coming, you press the boss key and the game immediately disappears, being replaced by a bogus ( but very real-looking) Excel spreadsheet. As soon as the boss is gone, you press the key a second time and the game is restored. ( Yes, it would be better if you could click press a key and your boss would disappear. Someday…)

Note: To be fair, we should acknowledge that not everyone plays Solitaire at work. Some people play FreeCell. If you want to cheat in FreeCell, try this. At any point in the game, press Ctrl+Shift+F10. In the dialog box, click Abort. Move any card anywhere (even if it's not a valid move) and you'll automatically win. Or if you're running Windows Vista, try this instead: Press F3, enter -3 in the dialog box, and click OK. Then double-click on any card to win the game.

Gaming in Excel

The boss button is certainly one of the great innovations in the history of computing. But that doesn't mean there isn't room for improvement. We think it's great that a computer game can make a fake copy of Excel pop up on screen. However, we can do better than that—we can write our computer games in Excel. Do that and you won't have to figure out a way to pop up a fake copy of Excel. Instead, a real-live version of Microsoft Excel will already be up and running. And playing your game to boot!

Why would you want to write computer games using Microsoft Excel? Good question. It's possible you might need to create a game to be used for employee training. To be perfectly honest, though, the odds are pretty good that you'll never have a valid business reason for creating a computer game in Microsoft Excel.

On the other hand, computer games do provide a fun way to learn techniques that might be useful to someone who needs to develop a business application of some kind. For example, computer games often have to do things like the following:

• Respond appropriately when the user clicks on a button or (in the case of a spreadsheet) a particular cell.
• Prevent a user from typing in data, except when the user is given the OK to do so and that data is typed into a specified location.
• Keep track of information, both during a session and between sessions.
• Look up information stored elsewhere, such as in a spreadsheet, a database, or a text file.

That's standard gaming stuff; it's also pretty standard for a business application.

We see that some of you are skeptical. Couldn't you learn how to do these things while writing an Excel application that does a cost/benefit analysis of international hedge fund investment possibilities? Probably, but it wouldn't be anywhere near as much fun. And we don't think it would be as easy, either; after all, you'd likely spend so much time worrying about the hedge fund investment algorithm that you would never get around to figuring out how to carry out some of these other activities.

When you first learn how to play basketball, you don't spend all your time working on double-teaming the dribbler out of a 1-3-1 zone. Instead, you start out by just throwing the ball at the basket. The best way to learn something is to start out by doing simple and fun things.

OK, that makes sense. But why Excel? Wouldn't it be better to learn a true programming language, like C# or C++?

Well, maybe. But to learn a language like C++ you pretty much have to be a full-time developer. If you just want to write simple little applications Visual Basic for Applications ( VBA), the development language included with most Microsoft Office products, is often good enough. On top of that, if you write an application from scratch using C++ or C#, you'll have to figure out how to display items in a table, apply formulas and calculate values, change font sizes and colors, and graph selected data. One cool thing about Excel is that it already knows how to do these things. You don't have to figure out how to draw a graph in Excel. All you really have to do is figure out how to tell Excel to draw a graph then tell it which type of graph you'd like it to draw.

In other words, Microsoft Excel, along with VBA, makes for an excellent environment for simple development projects. And writing a simple computer game makes for an excellent introduction to that environment.

To show you what we mean, we've created a simple word game, which we've dubbed Word Maker 2009. When you start a new game of Word Maker 2009, a random set of 64 letters are displayed onscreen. At that point, your task is to click on some of those letters and create a word. As soon as you've done that, you click the Check Word button and the program checks to see if you've created a valid word. If you have, you're awarded a specified number of points.

Note: In the game, different point totals are assigned to each letter in the word. The points for a given letter are determined by the value of the letter's row times the letter's column. For example, a letter in row 3 column 4 is worth 3 x 4, or 12 points.

If you don't have a valid word, those points are subtracted from your score. You keep making words until you can't make any more. At that point, you click the Done! button and you're, well, done.

Note: Yes, this is way more fun than Halo 3. But let's keep that between ourselves; we don't want the Xbox people to feel bad.

The game, which is designed to run under Excel 2007 (we can't promise it will run under any other version of Excel), looks an awful lot like the screen shown in Figure 1. This doesn't look anything at all like Excel? Well, that was the whole idea.

Figure 1 Word Maker 2009 runs under Excel 2007

How Word Maker Works

That's Word Maker 2009 in a nutshell. We don't have space to walk you through each line of code in the game (even though there aren't all that many lines of code in the thing). However, we've made the game available as a download. You can play the game and, even better, view the fully-commented code.

Still, we can walk you through a few of the more interesting parts of the code, beginning with the subroutine shown in Figure 2. Before we go much further, though, we should note this isn't the complete subroutine. If you download and look at the code, you'll see that we've edited out a few lines. We did that simply so we could focus on one particular task: when you click on one of the letter squares (and only when you click on one of the letter squares) the clicked-on square becomes green. (We told you this was way better than Halo 3!)

Figure 2 Subroutine for selecting a cell

Public Sub Worksheet_SelectionChange(ByVal ObjTarget As Range)  
    Sheet1.Unprotect
    Set objRange = Range(ObjTarget.Address)
    Set objRange2 = Range("C5:J12")

    Set objIntersection = Application.Intersect(objRange2, objRange)

    If Not objIntersection Is Nothing Then
If objTarget.Cells.Value = "" Or objTarget.Cells.Interior.ColorIndex = 4 Then
            Sheet1.Protect
            Exit Sub
        End If
ObjTarget.Cells.Interior.ColorIndex = 4
        strCurrentWord = strCurrentWord &ObjTarget.Cells.Value
    Else
        Exit Sub
    End If

    Sheet1.Protect
End Sub

What happens if you click on a cell that isn't a letter square? Nothing. We'll explain how we manage to do that in just a moment.

What if you click on more than one cell at a time? For example, what happens if you drag your mouse across a whole range of cells? Well, that could create problems, especially if some of those cells are letter squares and some aren't. We probably could have written code to separate the valid cells (that is, the letter squares) from the invalid cells. However, that sounded like way too much work. Therefore, we decided to make it so you can't click on more than one cell at a time. If you drag the pointer across a range of cells, nothing will happen. The full subroutine includes these three lines of code:

If Selection.Cells.Count > 1 Then
    Exit Sub
End If

Without going into too much detail, this code simply uses the Count property to see how many cells have been selected. If the Count is greater than 1 (meaning we've selected/clicked on more than one cell), the Exit Sub command exits the subroutine. The net result is like a typical day at Greg's desk: nothing happens.

See? We haven't even started and you've already learned a useful Excel technique you might not have known about.

As for the subroutine itself, you might notice that we named our chunk of code Worksheet_SelectionChange. This just happens to be the same name Greg gave his son. However, we didn't choose this name solely for sentimental reasons; we chose it because Worksheet_SelectionChange is also the name of an event that gets fired each time you click on a new cell in an Excel worksheet. ( In other words, each time the selection changes.) By giving our subroutine the same name as the event, we can effectively "intercept" that event: each time the selection changes, our subroutine runs.

Note: You might be wondering what other events you can intercept in Excel? To tell you the truth, there are too many for us to list in this article. However, you can find information on all the Excel events—as well as all of the Excel objects, properties, and methods—by taking a peek at the Excel 2007 Developer Reference.

Each time the selection changes on the worksheet, our subroutine is called. But we don't just get notified that the selection has change; we also get some detailed information about what has been selected—that's what the parameter (ByVal ObjTarget As Range) is all about. When the Worksheet_SelectionChange event fires, the selected cells are passed to the subroutine as a Range object. What we're doing here is grabbing that Range object and assigning it to a variable named objTarget. Why do we do that? Because that's how we can determine which cell has been clicked on. That, in turn, enables us to tell whether the user clicked on one of the letter squares or not.

Protected and Unprotected

If that's not entirely clear, it will be in a minute. In the meantime, let's take a momentary side trip and talk about this line of code:

Sheet1.Unprotect

Our game has a number of subroutines, including one named Workbook_Open. (As the name implies, this subroutine automatically runs each time the workbook is opened.) We do several things in this subroutine, including randomly assigning letters to the letter squares and hiding UI elements, such as the ribbon, scroll bars, and the status bar. ( Hiding UI elements is another technique you might be interested in, but have had no idea how to do.)

In addition, we also run this line of code:

Sheet1.Protect

What does the Protect method do? Pretty much what you'd expect from something called the Protect method—it protects the worksheet by locking all the cells. This prevents anyone from typing values into the cells, changing the formatting of the cells, or doing much beyond simply clicking on a cell. If you try to type something in a cell, you get a message box telling you the cell "is protected and therefore read-only." (see Figure 3).

Figure 3 Message telling you the cell is read-only

That is how we can prevent people from messing around with our game interface. They can't type data into our game, they can't delete items from our game, they can't do anything that we don't want them to do.

Of course, while we don't want the user doing things like changing the background color of a cell, we do want the game itself to be able to make changes like that. If nothing ever happened, it wouldn't be much of a game. Although, it would be a lot like soccer, wouldn't it?

Note: Jean would like to point out that all you angry soccer fans should send your comments to Greg and not to her. She happens to like soccer. She also says she'd be perfectly happy to have a computer game that didn't do anything.

When a worksheet is protected, even our subroutine can't make any changes to that worksheet. That's why we kick things off by calling the Unprotect method: that method enables us to temporarily unlock the worksheet and make some changes.

Within Range

What kind of changes are we going to make? We'll get to that in just a minute. First, however, we need to talk about these two lines of code:

Set objRange = Range(ObjTarget.Address)
Set objRange2 = Range("C5:J12")

What we're doing here is creating a pair of Range objects. Our first object (objRange) represents the cell that was clicked on; we retrieve that by retrieving the value of the Address property of our object reference ObjTarget. The second object (objRange2) encompasses cells C5 through J12, a set of cells that represents our 64 letter squares.

That brings us to this line of code:

Set objIntersection = Application.Intersect(objRange2, objRange)

Here we're using the Intersect method to determine if our two ranges intersect. ( In other words, is there at least one cell in objRange that is also in objRange2?) If the ranges intersect, a new Range object (representing that intersection) is created. Otherwise, a new Range object is not created.

If you're still confused by the Intersect method, suppose you have a range that contains cells A1, B1, and C1 and another range that contains cells C1, D2, and E1. The intersection is cell C1 because it appears in both ranges.

This line of code (with its admittedly clumsy grammar) checks to see if a new Range object has been created:

If Not objIntersection Is Nothing Then

Suppose a new Range object has been created. That means our two ranges intersect at some point. If the two ranges intersect, that means the user clicked on one of the letter squares—that's the only way the two ranges could intersect.

Making a Change

If we discover that a new Range object has been created, meaning a cell was selected, we run this block of code:

If objTarget.Cells.Value = "" Or objTarget.Cells.Interior.ColorIndex = 4 Then
    Sheet1.Protect
    Exit Sub
End If
objTarget.Cells.Interior.ColorIndex = 4
strCurrentWord = strCurrentWord & objTarget.Cells.  Value

Our first task here is to check to see if the cell has a Value (does it have a letter typed into it?) or if the background color of the cell is green (ColorIndex = 4):

If objTarget.Cells.Value = "" Or objTarget.Cells.Interior.ColorIndex = 4 Then

Why do we make this check? Well, if the cell has no value that means the letter that used to be in that letter square has already been used. It makes no sense to add a blank letter to a word so we skip cells that have no value. Likewise, if a cell has a green background, that means it is already selected. We don't want to allow a player to keep clicking on a cell a million times to add a million copies of that letter to the current word. Therefore, we ignore any cells that have a green background (which we almost always do, game or no game).

If the cell is a valid letter square, we then do two things: we change the color of the cell to green and we add the letter to the end of the current word. After making the changes, we do a little bookkeeping (for example, updating the value of the current word, a task we didn't include in the edited version of our edited subroutine), re-lock the worksheet, and then exit the subroutine.

What if the two ranges don't intersect? Well, that means the user didn't click on one of the letter squares. In that case, we call the Protect method to re-lock the worksheet and then exit our subroutine, all without changing a single cell. The moral of the story: Unless the user clicks on a valid letter square, you shouldn't expect much to happen.

Submitting a Word

After the user has a word spelled out, he should click the Check Word button to see if it spelled a real word.

Note: How did we manage to get a subroutine to run when we clicked a button? In Office 2007, that's a piece of cake. From the Insert tab, we simply clicked Shapes, selected the desired shape, and then drew a button onscreen. After that, we right-clicked the button, clicked Assign Macro, and then selected the desired subroutine.

When the Check Word button is clicked, we run a subroutine named, well, CheckWord. The primary task of that subroutine is to run these two lines of code:

Set objRange = Sheet2.Range("A1").EntireColumn
Set strChecker = objRange.Find(strCurrentWord, , ,  xlWhole)

In the first line, we create a Range object that represents all the cells in column A on Sheet2. In the second line, we use the Find method to search that column for the word. (The word is stored in a global variable named strCurrentWord.) Why would we search column A on Sheet 2? You got it: because column A on Sheet 2 just happens to contain our list of valid words.

You could easily customize this game so that users had to spell Spanish words or Portuguese words, or maybe words associated with an academic subject like biology or chemistry. All you'd have to do is replace the words in column A on Sheet 2 with your own set of words. You could even put different lists in different columns. Column A could hold Spanish words, column B could hold Portuguese words, and so on. A player could then choose which word list they want to use and—well, we're getting a little ahead of ourselves here.

Incidentally, we also make use of Sheet3 in the workbook. That's where we keep track of the 10 highest scores. Each time a game ends we execute this block of code:

Set objRange = Worksheets("Sheet3").Range("B1:B10")
intAnswer = Application.WorksheetFunction. Min(objRange)

What we're doing here is creating a Range encompassing cells B1 through B10 on Sheet3. We then use the WorksheetFunction.Min method to retrieve the minimum value from that set of cells; that's going to be the lowest of our ten highest scores. After that we check to see if the new score is higher than the minimum score. If it is, we have a new entry for the top 10 and we ask the user to enter her name. Then we replace the lowest score in the top 10 with the new score, re-sort the list of top 10 scores, and update the top 10 scores listed on the game page.

That's right: just like a real computer game. And if you're a little more ambitious than we are, you could get really fancy here. For example, you could keep the 10 highest scores per person as well as the ten highest scores overall. You could track the ten lowest scores. You could track the average score. You get the idea.

Getting Hands-On

It looks like we're about out of time for today, even though we've barely covered half of the techniques used in our little word game. But don't despair. Download the fully-commented code and take a look at what we've done—or just play a little. Keep in mind, though, that the game is designed to be played as a game: that means that, when you start it up, it will open in full game-play mode. You won't see anything but Sheet1 and you won't have access to the code. But that's OK.

Here's another useful little tip: If you hold down the Shift key when you open the game, the Workbook_Open subroutine won't be called. Then you'll be able to edit the game to your heart's content. (This also prevents the worksheet from being locked.)

Alternatively, you can start the game in the usual fashion, and then press Alt+F11. That will open up the Visual Basic Editor, and give you a chance to look at—or even modify—the code. Even if that means adding a way to cheat.

Before you ask, here's one way to cheat. Add this code to the beginning of the CheckWord subroutine:

If strCurrentWord = "u" Then
        intWordScore = 1000
End If

Now, click just the letter U and then click Check Word. Just like that, 1,000 points have been added to your score!

Still skeptical? Well, here's one final reason you might want to write games using Microsoft Excel: learning some of these techniques will help you master the gauntlet of challenges you'll face as part of OfficePalooza, the online VBA extravaganza scheduled for April 20th through May 1st, 2009. See the OfficePalooza homepage for details.

Against all odds, Greg Stemp and Jean Ross work for Microsoft.