Cary Walkin on the Internet

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

Disclaimer: The following tutorial requires Conditional Formatting which is a feature of Excel 2010 and greater. If you are using an older version of Excel, please note that formatting will not work correctly for you.

This article 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.

This post is a continuation of my first post for Cary Walkin’s VBA4Play series, Development Principles for Excel Games and Applications, and Cary’s first post, VBA4Play Part 1: Movement and Collision Detection. If you have not read either of these posts, I strongly suggest you read them before moving forward.

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.

Tile sets, tiles, and squares

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.

Displaying your tiles

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.

Mapping your 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.

Getting Fancy #1: Creating a Player and Dealing with Collisions

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.

[=-1]":)";""

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.

Getting Fancy #2: Clipping and Viewports

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.

Getting Fancy #3: Illuminated Pathways

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.

=IF(SQRT(($E14-Calculations.Top)^2+(F$13-Calculations.Left)^2)<1,SQRT(($E14-Calculations.Top)^2+(F$13-Calculations.Left)^2)*(F14+1),SQRT(($E14-Calculations.Top)^2+(F$13-Calculations.Left)^2)*(F14+1)*4)

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:

[=0]":)";""

That’s it! If the map looks a little blocky, try zooming out, like this:

Download everything in Part 1: VBA4Play Map Part 1 Examples.xlsm

Download the complete 3D maze: Maze Example.xlsm

Homework

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!

Jordan