Cary Walkin on the Internet

This post has been written by guest author Jordan Goldmeier of Advanced XL. 

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 that 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 method 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.

When I want to manipulate the variable as it exists now on a spreadsheet, I can use the bracket-based shorthand range syntax in my VBA code. For example:

   1: [Calc.Left] =  [Calc.Left] – 1
   2:
   3: 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] in the formula bar.

But this method goes beyond variables. You don’t have to use VBA to change the background color of a cell. Instead, you can color cells using Conditional Formatting (provided you are using Excel 2010 or greater) rather. You can retrieve 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.

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 two, 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 one 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.

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.

   1: Worksheets(“Sheet1”).Activate
   2: ActiveSheet.Range(“A1”).Value = 1

But why not do this instead?

   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.)

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:

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 in.

Jordan

NEXT: VBA4Play: Making a Maze (Part 1: Your First Maze)

Download files:

Form Control Example.xlsx

Maze Example.xlsm