Category Archives: Standards

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

Spreadsheet Guidelines and Best Practices

Today, I found this journal article, The role of OR [Operations Research] specialists in ‘do it yourself’ spreadsheet development, from the European Journal of Operational Research, by John S Edwards, Paul N Finlay, John M Wilson. The article outlines 20 Guidelines for Spreadsheet Development and 21 Best Practices Points, which I excerpt below.

Guidelines for Spreadsheet Development

Fig. 2. Guidelines for spreadsheet development.

Best Practices for Spreadsheet Development

Fig. 3. Best Practices Points. 
My favorite point is #14 from the list of Best Practices: 

Never input the same piece of data more than once.
This rule reminds me of the 17th rule of composition from the Elements of Style: “Omit needless words.” Perhaps Best Practice-point #14 can be rewritten as “Omit needless data.” 
_____
Figure 2 and Figure 3, from John S Edwards, Paul N Finlay, John M Wilson, The role of OR specialists in ‘do it yourself’ spreadsheet development, European Journal of Operational Research, Volume 127, Issue 1, 16 November 2000, Pages 14-27, ISSN 0377-2217, 10.1016/S0377-2217(99)00331-8.

Toward a Proper Excel Filename Style

Most importantly, use your words
An ideal Excel filename should be two or three succinct words and contain few numbers. Current operating systems no longer constrain filename character length, so there is neither excuse for nor cleverness in using shorthand. Capitalize each word as you would a document title.    

Only abbreviate proper nouns
If your file is an example to someone, it should have the full word “Example” not “ex” in its title. If your Excel dashboard is the second version of the “Cost Analysis and Reporting System” you may abbreviate your filename to “CARS v2.xlsx,” but a VBA Chart Tutorial should never be named “VB ChrtTut.xlsm.”

Always connect words with a space and nothing else
The name of your file is not a programming variable or engineering quantity. The words in your filename should not be connected with underscores (“_”) nor dashes (“-“).

Use clear dates, but don’t include dates in every filename
Unless your file is a report that comes out on a specific, periodic schedule, there’s likely not a good reason to put today’s date in your filename. If you must put a date in your file, place the date at the beginning, left-hand side of the filename so it appears first. This ensures the date is not cut off when viewed in a file explorer. Dated files are likely to be stored with similar files in the same folder, so cutting off the last bit of each filename on the right is less harmful than cutting off the date.

If the workbook is a monthly report, the name should by the three character month followed by the year, then another space for the filename (e.g. “May 2012 Cost Report”). For reports that occur more frequently, a MM-DD-YYYY is preferable for sorting within the file directory.  \However, dates must always be clear; do not write “03-12” as this could mean March 12 or March 2012. Finally, full dates should not be clumped together without a dash (bad: “03122012”).

Numbers are preferable to dates
If you have several iterations of a file, use a numbering system instead of dates. Using dates leads to the horrible practice of adding extra numbers at the end of the filename. For example: “InventoryList 22 Feb 2001_1.xlsx”, “InventoryList 22 Feb 2001_2.xlsx” … etc.  Moreover, using dates and the former practice will not instantly make clear the latest version of your file when viewed in a file directory. However, placing a number at the end of your filename (“Inventory List 1.xlsx”, “Inventory List 2.xlsx”) always will make clear the latest (and first) iteration of the file whether sorted by filename, file type, or date modified when viewed in a directory (these files will always be either first or last). Numbers always should appear as the last character on the right.