Development Principles for Excel Games and Applications

This is part of a series called “VBA4Play,” written in conjunction with Excel game developer, Cary Walkin, for his blog. Many thanks to Cary for inviting me to write a few tutorials for his series.

My development approach is different from Cary’s. To be sure, there are many different development styles when in comes to Excel and, for that matter, in the software development world. However, my principles are ones I hold fast to. You may disagree with them, and I welcome you to challenge me in the comments should you feel inclined. But it’s far more important you understand these principles and why I believe in them. Think of it as an Elements of Style for Excel development. (For the unfamiliar, The Elements of Style is English style guide with a list of rules and principles to English writing style.)

Principle 1: Render unto Excel the things that are Excel’s, and unto VBA only the things that require VBA

You can do most everything with VBA, true; but you shouldn’t use VBA if you don’t have to. For example, it’s tempting to store your all your program’s global variables in a module. This methods brings the advantage of accessibility: the variables can be accessed by any procedure. However, these variables are also “freed” from memory whenever your code errors out or whenever you tell Excel to “Restart” your code. When this memory is dumped, you must start over: usually, you must create a “new” or “restore” method that reinitiates these variables. I don’t prefer this method.

Spreadsheets are inherent storage spaces. What I like to do is create a new spreadsheet tab that stores all my variables. In the figure below, I’ve created a sheet called “Calc” to store my variables and calculations. I’ve given named the cell C3, “Calc.Left.” Note that periods are allowed in named ranges. It gives named ranges an object-oriented feel.

image

When I want to manipulate the variable as it exists now on a spreadsheet, I can use the bracket-based shorthand range syntax, for example:

[Calc.Left] =  [Calc.Left] – 1

Msgbox [Calc.Left]

In addition, I can access this variable anywhere in my code as I’ve done above and anywhere in my workbook using =[Calc.Left].

But it goes beyond variables. You can color cells using Conditional Formatting (provided you are using Excel 2010 or greater) rather than addressing their location through VBA and changing their backgrounds colors. You can address a cell’s value using something like =INDEX([GameMap], [Character.Row], [Character.Column]) rather than using Sheet1.Cells([Character.Row], [Character.Column]).Value.

You can even set the location of a character on a game map by quickly manipulating the [Character.Row] variable. In fact, you can even assign a scroll bar form control to the [Character.Row] cell and change a character’s location without ever writing a single bit of code. I’ve done this in the example below.

Form Control Example

As you can see, it’s pretty slick. Like I said, no VBA code is required do this.

I know what you’re thinking: ok, that’s just another way to do the same thing. Well, yes, that’s true. But it’s also a much faster and more organized way of doing the same thing. In the finest book ever published on VBA and Excel application development, Professional Excel Development, Bulley et al show reducing the amount of VBA instructions Excel must execute can deliver performance enhancements. Consider for a moment how many instructions are required to move a character on a game board. You have to store the character’s location in a global variable. You have to select the cell in which he (or she!) will go. You have to write the smiley to the new location. And you have to clear out the old location. A lot of this can be handled on your behalf by Excel.

This principle might not be immediately or intuitively clear right way. But as we wade through more examples in the coming tutorials, you’ll see it in action.

Note from Cary: It is important to note that contrary to the above, my style is to do as much as possible in VBA rather than in a worksheet. The main benefit being a simpler debugging process which is critical when you have more complex systems interacting with each-other. This tutorial is an opportunity for you to see a different style when it comes to Excel programming and you can make your own conclusions as to what feels right for you.

Principle 2: Think Encapsulation!

Encapsulation is a tenant of object-oriented programming that argues (1) associated data and procedures should be organized together; and (2) access to and manipulation of the former items should be restricted or granted in only certain circumstances. For you accountants out there who shudder at the thought of software development, think of encapsulation as the segregation of duties and restricted access internal controls…for code.

Let’s deal with item 2, first. Think back to how we would use the variable, Calc.Left, in a formula. For instance, to find out the location to the right of a character, we’d have to do something like =Calc.Left + 1. Notice that this formula can only access Calc.Left but it can’t change it. This is an example of enforcing encapsulation on the spreadsheet. If there are no form controls attached to cell corresponding to Calc.Left, then the only way to change this variable is through code (or you could just type in a new value yourself—but let’s pretend for now we’re talking about a production level item and your variables tab is hidden). And for the Model-View-Controller wonks out there, consider how this dynamic mimics MVC.

Now let’s focus on the first item of encapsulation, item 1 from above. If you’ve been coding for a while, the advice about to give might feel pretty counter-intuitive. Here it is: don’t place your sub procedures in a module. Instead, place related variables and procedures an their associated Sheet objects as I’ve done below.

image

Modules feel like the natural place for subs because that’s what Microsoft’s macro recorder uses. But there’s no reason for it.

[Cue the organ]

How many times have you accidentally performed work on the wrong sheet because you used the ActiveSheet object but were looking at the wrong sheet when you ran your macro? (Raise your hand!) How many times on the wrong cell because ActiveCell wasn’t where it was supposed to be? (You know I’m talking to you!) The Selection object referred to wrong object? (Can I get an amen?)

When you place pertinent code in its associated Sheet object, you no longer need to Activate the desired sheet or reference the sheet the long way.

Worksheets(“Sheet1”).Activate

ActiveSheet.Range(“A1”).Value = 1

Me.Range(“A1”) .Value=1

Here’s why this is great. When you run the code in Sheet1, you never have to worry about it accidentally modifying the wrong sheet. And when you use “Me,” instead of ActiveSheet, the intellisense dropdown appears! Hallelujah!

Oh, and while we’re on the subject…

Principle 3: Stop using ActiveSheet, ActiveCell, ActiveWorkbook, and the Selection object

You really don’t need these objects. Use ThisWorkbook instead of ActiveWorkbook to ensure you are always modifying workbook in which your code resides. If you want to modify a cell, address it directly. Give it a named range like I’ve done above to make it easier. For worksheets, modify their names in the VBA project explorer. (You’ll also need to change the  tab caption as it appears in your tab list to make them match.)

image

Forget Worksheet(1).Range(“A1”) or Worksheets(“Game”).Range(“A1”) or Activesheet.Range(“A1”). Each of these methods relies on something: the desired worksheet being in the first position; the name of the worksheet still being named “Game,”; the sheet being active. Set it and forget it: Just type Game.Range(“A1”), instead, to reference A1 on the worksheet called “Game”. Look at the figure above: isn’t it annoying that Sheet1 is the caption for an object named Sheet2? Change the names!

Principle 4: Break every rule.

Look, I appreciate that you’ve listened to me so far, but astute observers will point out the shorthand range syntax (remember the “[Calc.Left]” from above?) is one of the slowest ways to read and write to a worksheet. And there are some instances where selecting an object appears unavoidable to achieve a desired effect. Also, sometimes hard and fast rules can be broken; like the idea that creating a mouseover effect with over an Excel cell is impossible, turns out, that isn’t always true. Finally, some things, like variable constants, probably DO belong in modules.

The point is, few have thought about using the Excel platform for a game, but that’s exactly what Cary did. He thought outside the cell, so to speak. (I use that concept, trite as it seems, throughout my upcoming book.) All Excel applications require creativity. If you internalize anything from this list of principles it should be that. Rote memorization of rules and formulas will only get you so far, but sometimes you need to try things differently.

Here’s what I’m talking about. One morning I woke up and thought, “wouldn’t it be cool to create a maze in Excel.” I tried a few things but they didn’t really work. And then a few days later, I woke up and it came to me. See:

Maze

Like it? The illuminated pathway on the right is just formulas and conditional formatting. In fact, the pseudo-3D maze in the middle is nothing more than a stacked area chart. See, you don’t need mad GUI skills to create something like this, you just have to think about how to use your current tools differently.

Principle 5: Always use Option Explicit

Actually, this is a rule you should never break. In fact, if you break it, I will judge you. Look, there’s no reason not to use Option Explicit. I believe so strongly in it, I named my blog after it.

What’s next?

If you plan on sticking around, the maze presented above will become the main focus of the following tutorials. We’ll talk about how you can create something like it for yourself. In addition, I’ll provide a little more depth to the different pieces above. For example, the mechanism that creates the illuminated pathway can be expanded to create scrollable arenas—and you can easily create a map editor, too.

Like I said, my approach is different from Cary’s. Different enough, that it was worth pointing out before going forward. But whether you follow or ignore my approach, it’s important to have an approach before diving right in.

Jordan

Grab the download file…

Maze Example

 

Advertisements

4 thoughts on “Development Principles for Excel Games and Applications

  1. pedrowave

    Hi Jordan,

    It is very interesting this article with development principles subject. All 5 principles are strong to make an excellent Excel development.

    If you allow me, I would add two additional principles, the first game’s worksheet protectio and the second using With…End With statement to executes a series of statements that repeatedly refers to a single object or structure.

    I have ventured to modify your code including the two previous principles and changing a bit the layout of the game. I want to make note the use of the name “Figurine” for better orientation on the map.

    I share with you the following link from my SkyDrive to know your opinion.
    https://skydrive.live.com/redir?resid=6B219F16DA7128E3!455&authkey=!AE8lqg0rwWorAeU

    Reply
    1. Jordan G Post author

      Pedro! I wish you could have seen my face when I opened your file. IT LOOKS AWESOME! I love the new layout – way better than what I started out with – and the figurine is great! I hope you don’t mind if I use only your updated version from now on.

      And yes I agree with you both about the With statement and using sheet protection.

      Reply
  2. pedrowave

    Sure Jordan, you can use my updated version from now on, at your convenience. I appreciate that you like the new layout and the new figurine.

    I have to do some remarks about my upgrade. The Game sheet is protected without password. At first, the other sheets are hidden. On the Game sheet I added two new buttons, MAP and MAZE EDIT to show these sheets, and on these ones, the GAME button to go back to it. As you may have noticed, pressing this new GAME button, it fire the new “ShowSheet” private macro to put the other sheets very hidden with the xlSheetVeryHidden property. Also I have grouped and renamed some shapes in the Game sheet.

    You must observe that in my file I had duplicate some shape’s names that I’ve fixed and uploaded it to my SkyDrive link again, so I suggest you to download it again and to rename it to erase PW1. I would appreciate if you keep my link on the Game sheet when you publish it.

    One last favor, will you allow me to use this game to write an article on my blog?
    Of course always referring and linking to yours.

    Sincerely yours,
    Pedro.

    Reply
  3. Jan Karel Pieterse

    A bit late to the discussion I admit. I like your principles, I adhere to most of them. But I must warn against #2, writing code in sheet modules. The VBA editor does a poor job regarding getting rid of editing garbage and sometimes your code gets corrupted and starts exhibiting odd behaviour. The way out of that is to “clean your code” using handy tools like Rob Bovey’s Code Cleaner.
    Now here is the snag: The cleaning method involves exporting and removing the VBObject in question. Unfortunately, Sheet object modules CANNOT be removed. You risk having to redo the worksheet in question if its code module gets corrupt.

    Reply

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