Wishing everyone a happy holiday and a terrific new year!
Wishing everyone a happy holiday and a terrific new year!
Today, we’ll be focusing on how you can turn regions of your spreadsheet into a map for a game. This will be similar to the illuminated pathway featured in the bottom-right of the spreadsheet maze shown below.
Let’s get started. The first thing you’ll need to do is define a tile set. The tile set is the exhaustive set of all possible tiles to be displayed on your map. A tile is a region, usually larger than a square, that contains specific information about the the terrain encoded as a number. This may sound complicated but our example really only includes two key pieces of information: if a square contains a 1, then the square represents a wall; if it contains a 0, it represents an open pathway.
Well, that sounds pretty easy, right? If each cell is a square, we can combine several squares to make a tile. Below, I’ve made a tile that defines a pathway that leads east from the center using. I’ve used each cell as the square; a three-by-three region of cells then becomes a tile.
Rather than have Excel look for ones and zeros and used VBA to color cell-by-cell, we’ll skip a step and just use Conditional Formatting to define a cell’s background. In the Conditional Format rules, I’ve set the cell background to be black when its value is equal to 1. I then delete the zeros because Excel treats an empty cell as a zero. This let’s us avoid the needless step of applying additional conditional formatting. If you’d like to follow along, open a new spreadsheet and try to recreate the tile below.
You’ll now have to define all the tiles in your tile set. I say “now,” but in my original formulation, I only started with eight. As I began developing the map, I realized I needed more. So try your best to define all the necessary tiles before moving forward but always remember you can add more later if need be. In all, I’ve defined 15 tiles (see left, sidebar below).
The numbers to the left of each tile are really important. You can’t really see them in the pic to the left because I’ve zoomed out to capture the tile set, but they start from 1 and count down to 15. They act as a key, or a unique identifier, for each tile. If you’ve done database work before, the concept should be pretty familiar to you. If you’re on the accounting side of experience, think of each number as an identifier to a particular account.
So now comes the incredibly monotonous task of naming each of these tiles. Start with the first tile, select the tile region and give it a name like “Tile1”.
Now keep doing this until you have no more tiles left to name. If you misspell a name or give it the wrong key, you can fix or delete the name by using the Name Manager on the Format tab.
Giving each tile a number works to our advantage because now we need only to use the CHOOSE function to return the tile we’re interested in (see image below). If you’re new to the CHOOSE function, just remember to count by the number in the first parameter. Below, CHOOSE will return the named range, Tile1. If I want it to return Tile5, I’ll put a 5 in the first parameter. Make sense? Just imagine how many nested IFs this would have required! CHOOSE is so great.
Ok, so we’ve typed up the formula above into a cell and it’s returning a 1. Remember that each tile is comprised of a range of cells. So drag the formula to the right and down (Copy + Paste) until you have a 3-by-3 grid. Now click into the formula bar and then Ctrl+Shit+End to let Excel know you want to return a range and not a single cell.
Ok, that’s just one tile, what about the rest of them? Good question! Let’s move away from the array formula for a moment. If we wanted to return only one cell at a time, we’d have to pull them out of the tile one square at a time. So make a table that looks like this:
Inside the table, we’ll have to use a formula that can pull each individual cell from a chosen tile. For that, we’ll need to use the INDEX formula. The INDEX formula takes a range or an array in its first parameter, and a row and column indices in its second and third. Use the INDEX formula as I have below.
Your addresses may be different from my above (W40,X39), so take care to ensure your formula works. Note, I’ve done absolute addressing on the row and column arguments so that I can drag across and down without problem.
Now that you’ve dragged the region across and down, select column headers (including 1,2,3) and the entire tile as I have below. Copy it, and then paste another to the right of it.
Keep pasting until you have 5 sets of tiles. Now let’s select the entire row. This time, select the row headers but not the column headers. Copy that and paste another below, as I’ve done in this graphic:
Keep doing that until you have five rows.
OK, so now you should have a 5 by 5 tile grid all with the same tiles.
What we need now is a way to easily map a specific tile to a specific area on the grid. So, in a space next to your tile grid, create a new table that looks like this:
Note the five-by-five grid above is similar to the five-by-five set of tiles in the larger grid. For now, just put a series of 1’s into the grid as I’ve done below. Then select the data region and give it a name like “TileMap”.
So here’s how this is going to work. The key you enter into the table above defines what tile will appear in the larger map. The problem is that we must keep track of two indices. We need to keep track of the the rows and columns inside of each tile and we must also keep track of the overall location of the tile. What we can do is a provide another series of indices around the larger grid as I’ve done below.
For example, the region that intersects with both series of 1s in the rows and columns of this square grid corresponds to the first row and first column of the smaller tile map we created a moment earlier.
We’ll use INDEX one more time. We’ll use the new row and column to help us map the current tile location back to its definition on the smaller grid. The number that’s returned will inform our CHOOSE formula.
Try your hand at the formula above. Now drag down and across to copy and paste the formula to the rest of the large grid. Afterward, pick a few cells on your larger map at random and press F2. Make sure the correct references are being pulled. If you’re satisfied, go to your tile map and start changing numbers. Notice how they are update immediately on the larger map. Remember you can consult your tile set as legend to find the right number for the desired tile.
When you’re done with your map, select the entire data region of your square map and give it a name like “SquareMap.”
Time to get fancy.
If you want to work with the same layout I’m using, use this sequence in your tile map.
Now go start a new tab and title it something like “Calculations”. Near the top of the new workbook, create placeholders for the variables Top and Left as I’ve done below. Then name the corresponding cells Calculations.Top and Calculations.Left.
Create a larger table a few cells away from the placeholder. First, create the column header by initiating a consecutive series from 1 to 15. Then do the same for row headers. (See the table below as a guide.) Now use INDEX like we did before to pull out the corresponding cells from the larger map. Pay attention to setting the correct row and column references.
Use conditional formatting like we did earlier in the tutorial to turn the 1s black. For now, leave the zeros as they are. Note that an entry point to our map appears at the intersection of the second row and the first column. So enter the number 2 in the space next to the Top label. Enter a 1 next to the Left label.
We want our player to appear at the intersection of the coordinates given by Top and Left. There are a couple of ways we can do that. The easiest way is to employ an IF formula. But for a much larger map, the IF formula is going to slow us down. So we’re going to prefer a more complicated method even if the performance improvement in this example is trivial. In the upper left of your new table, retype the formula as I’ve done below:
Remember to be mindful of the references if your spreadsheet is laid our differently. Now drag right and down ensuring the correct references are being used.
Let’s take a moment and think about what’s going on. The AND() function will return a TRUE when all conditions inside its parenthesis evaluate to TRUE. It will return a FALSE in all other cases. In Excel, mathematical operations on Boolean conditions treat TRUE as a 1 and FALSE as a zero. So when the AND evaluates to true, it will be subtracted from either one or zero. If our player is correctly on a path in our map, it will show up as a –1.
Now select the entire data region. Click on the small arrow in the Numbers group from on the Format tab.
Select Custom for the category, then paste in the following code and press OK.
Custom Formats are beyond the scope of this article, but you can read more about them here. What this will do is if the value is -1 then the cell will have a smiley face visible without changing the data or formula.
Now we need to code some movement and controls. Open the Visual Basic Editor and double-click the sheet object (in the Project Explorer in the upper left) corresponding to the sheet were were just looking at. When that opens, we’ll insert our first sub:
This code will simply restart the player to the beginning of the map. In an ideal world, you would not use explicit numbers in your code. Instead, you’d have constants either residing in your code or on the spreadsheet.
Next, we’ll add the following. The code is relatively similar to that of Cary’s in his Movement and Collision Detection tutorial.
Going back to the spreadsheet, draw five shapes as I’ve done below. You can find these shapes on the Insert tab.
Now right-click each shape and assign it to its corresponding macro. Check each button to make sure the macro works. If everything is working, it’s now time to do some collision detection. What we need to do is test if the intended direction will make us hit a wall. To do this, we need to simply modify the code:
Notice how the If command always evaluates the intended direction. One extra benefit to this method is that it also handles the boundaries of the map. Try to move the player off the map.
A viewport is a view that only shows a subset of a larger image. Clipping describes how you trim the view for your viewport. Showing the entire map was a good start, but we don’t need to show the user everything. That’s too easy for them. Instead, we’ll create a viewport to show the user only a small amount of the map at a time. Below, I’ve trimmed the entire map to be only 7-by-7 tiles:
Starting with the 2 in the column header, I’m going to select it and change its formula to look like this:
Now I’m going to drag right until the rest of the cells after the second have the same formula. I’ll do the same thing for the 2 in the row header and then drag down.
Finally, I’ll click the 1 in the in the column header row and set it equal to Calculations.Left (see below). Likewise, I’ll click on the 1 in the header row and set it equal to Calculations.Top.
Now if we move the player around, we see that the viewport moves along with him. He’s always in the upper-left, minding his own business. Looks good, but what if we want him in the middle instead. Well, if we look at the at our row and column headers, there are seven numbers for each. The middle then is three numbers in. So if we want to push the player three spaces in, we’ll subtract the first cell of our row and column headers by three. Below, I’ve captured this for the column header.
Make sure to do the same for the row header. There’s no need to drag down or across after performing these calculations.
Those hashmarks you see are actually errors. Having too many unhandled spreadsheet errors can be a cause for some major slowdown. If you like how they look, you can use IFERROR to place them there without error as I’ve done below.
I generally don’t like those hashmarks so I’ve placed a 1 in that final parameter instead. That makes them black due to the conditional formatting we set up earlier.
To begin, copy the viewport and paste it a few rows down, as I’ve done below. Once pasted, click on a few cells in the bottom viewport to ensure the formulas are pulling from the correct references. For the bottom viewport, the formulas should pull from the bottom header row and not from the top header row.
In the top viewport, delete everything in the content area and then recreate the static row and column headers (1 to 7) like we had before as shown below. You’ll need to select the content area and change it from the Custom type we set in Getting Fancy #1 back to General. There might also still be conditional formatting in the table so make sure to clear that out as well. In addition, I’ve enclosed a border around the map area to make it a little easier to read. We will not use the numbers in the header and column rows; they’re just there to guide us.
Now to create the illuminated pathway, we need to something that can measure the distance from the player to everything else. For that, we can use the distance formula. Now this next part is more art than science. I’ve found that the illumination works after you play around; that is, I haven’t found a fool proof method to always make it look how I want. You just gotta try a few things out.
So the goal here is to essentially make two different stratifications of numbers. The walls and objects farther away should be darker, so we want them to be a higher number. Closer objects and even paths that are farther away we want to be lighter, so we need to them a comparatively smaller than the former. So, in the first cell of the top viewport…
…I used this formula.
Here’s how it works. The distance formula is everything inside the SQRT function. The IF function tests if the distance is really close, for instance, if the distance that results is less than one away. The larger your viewport, the greater this number can be. For instance, the illuminated pathway in the example file tests for values 10 away or less. Like I said, this is more art than science.
(I know I’m using an IF. You could do this without an IF, but that formula is beyond the scope of this article.)
If it’s less than 1 away, then we’ll multiply it by 1 plus the value in the same spot on the normal tile viewport. Since the walls are actually a 1 or hallways a 0 in the bottom viewport, multiplying the distance formula by the corresponding value plus 1 will ensure we’ll get a number back (so we’re not multiplying by zero) and that the walls will always produce greater numbers than hallways. Finally, if the distance is more than one away, we want to make the value sufficiently greater. Notice the last part of the IF statement is pretty similar to the previous argument except that we multiply everything by 4. Drag the formula down and across the entire viewport.
Because we used cells from the bottom viewport, the cells in the top viewport might borrow the bottom’s custom number format a conditional format.
For now, select the content area of the top viewport range and go into the Conditional Formats dialog box. Delete any rules that might exist. Once complete, click New Rule…
Now, change your settings to match mine below.
Make sure the tones are white and black. Don’t worry about that weird fade to red in the middle. So why the 65th percentile? No reason in particular, I just think it looked the best. In the example maze I think I used 90th percentile. Alternatively, you can make this a 3-Color Scale and try to get better control over the middle tones. I’ll leave that to you. Press OK when complete. You should now see faded colors blooming from the center of your map.
Finally, because our custom type was set to show the smiley player when it found a –1, we’ll need to change to show when it finds a 0, the center point of the map. (The distance formula always returns a 0 in the center.) So select the content area of the viewport and assign this custom format:
That’s it! If the map looks a little blocky, try zooming out, like this:
Interested in pursuing these topics further? Here’s your chance.
1. If we turned the map above into a maze, create a mechanism to let the user know he or she has reached the end of the maze.
2. The tiles in this map always have the middle portion open to represent a hallway. Create a few more tiles that allow for a more “open concept” map. Share it in the comments, if you think it’s super awesome. Try making an even larger map.
3. If you have several pre-made maps already created (think levels), how can you replace the current map in view quickly?
4. Come up with a better way to fade the colors in the illuminated pathway.
5. Make a new subroutine that will randomize the tiles so that every time the subroutine is run it creates a new map. Note that you will want a restriction so that all tiles can be accessed by the player.
I hope you’ve found this tutorial useful. The next tutorial will make this maze three dimensional! Depending on how much time I have, I may also do an answer session for the homework. I’ll leave you with one thought: look how much you accomplished without tons of code!
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.)
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.
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
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.
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.
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.
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.
ActiveSheet.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…
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!
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.
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.
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.