Cat On A Spreadsheet

In the first part of this series, we established the foundation for our Excel RPG. We created a way to define terrain, store maps, load their contents into VBA arrays and render the resulting world on a worksheet. Our first location, Old Fort Approach, appeared exactly as intended, complete with walls, grass, trees, water and paths. It was a world that existed, but not yet one that could be explored.

For our second milestone, we are going to introduce the first inhabitant of that world: the player character. We will give that character a position, display it on the map, and allow it to move in eight directions using a set of clickable controls.

That may sound straightforward, but moving a character through a game world involves considerably more than changing the value of one cell and writing it somewhere else. We need to know whether the destination exists, whether its terrain permits movement, whether a diagonal move would carry the character through an impassable corner, and what should happen to the terrain underneath the character when they leave. We also want the world to react in a more interesting way than simply refusing an invalid move. Walking into a tree should produce a different message from walking into a wall, and walking across grass should feel different from stepping onto a wooden floor. Perhaps most importantly, we want to implement all this without compromising the separation between game data and presentation that we established in Part 1.

By the end of this article, we will have a working movement engine, a character that can explore our test location, and the beginnings of a contextual messaging system. We will not yet implement combat, monsters, inventory, fog of war or a full turn system. Each of those deserves its own development milestone.

The player is not a terrain type

In our existing map, every cell contains a numeric terrain ID. The ID identifies the terrain definition in GameData!tblTerrain, allowing the renderer to determine the character and formatting that should appear on the Screen worksheet. One tempting approach to adding a player would be to give the character another terrain ID, perhaps 99, and place that value into the map array. It would certainly make the character appear. Unfortunately, it would also destroy the distinction between the ground and whatever happens to be standing on it.

Imagine a character standing on grass. The grass is still there. The character hasn't transformed the grass into a person. When the character moves away, the grass should become visible again. The same reasoning will eventually apply to monsters, treasure and other objects. There may be several objects occupying a single position, and the displayed character must represent whichever is currently eligible to be seen.

For now, our solution is to maintain the player's coordinates separately from the terrain array. The map engine will continue to know what terrain exists at each coordinate. A new player module will know where the character is. The renderer will draw the terrain and then place the player's @ symbol above it. When the character moves, we will restore the underlying terrain at the previous position and display the character at the new one.

This is not yet a complete layered rendering engine, but it demonstrates the principle without introducing complexity we don't currently need.

Adding meaning to terrain

Our original tblTerrain already includes two important Boolean fields: BlocksMovement and BlocksVision. We deliberately separated these because something that prevents movement doesn't necessarily prevent sight. A window can be seen through but not walked through. A cloud of smoke may allow movement while obstructing visibility. In this milestone, we are only interested in BlocksMovement.

Before writing any movement code, however, we're going to extend our terrain definitions with two additional fields. The first, BlockedMessage, contains a message to display when the player attempts to enter an impassable tile. The second, EnterMessage, contains a message to display when the player successfully enters a tile. These fields belong in our existing terrain table because they describe the general interaction between the player and that terrain type. They are not properties of a particular map location.

For example, a wall might have the blocking message: "Mastercraft brickwork. Also, completely solid." A tree might respond with: "You hit your head on a tree growing there. Better try to go around it instead." Grass could provide an entry message such as: "You walk through a patch of grass." Having these messages in a worksheet rather than directly inside our VBA movement procedure has several advantages. We can change the wording without modifying the code. Every location using a particular terrain type will automatically use the appropriate message. And when we eventually add more terrain, we won't need to extend our movement routine with an increasingly long collection of conditional statements.

It's not a bad decision to allow the impassable terrain types to have entry messages, as well. What if in the future we decide to implement, let's say, a potion which gives the player the ability to pass through walls? Then the engine would automatically treat the terrain as passable, and return the appropriate message without us having to add anything special.

This is an early example of data-driven game design: the rules and descriptions are defined in data, while the engine interprets them.

Extending the terrain-definition loader

Our existing LoadTerrainDefinitions procedure already reads the records from tblTerrain into a dictionary. We will extend that procedure to include the two new columns. The complete revised version is shown below.

The procedure starts by locating tblTerrain on the GameData worksheet. It then retrieves the column positions by their names. This is an important distinction from reading a fixed range such as A4:I10. If we rearrange the table columns, our code will still retrieve the correct information. We then read the complete table into the data array using DataBodyRange.Value2, avoiding repeated access to the worksheet inside the loop. Every terrain definition becomes an entry in the mTerrainDefs dictionary, keyed by its numeric ID.

The dictionary's values are arrays, and the order of their elements is important. Our existing fields occupy indices 0 through 5, and the new messages are added at indices 6 and 7. Index 3 therefore continues to represent movement blocking, while indices 6 and 7 represent blocking and entry messages respectively.

The CStr and CBool conversions ensure that we store the expected types, and ColorFromHex, introduced in the previous article, converts the colour strings into numeric values that Excel understands.

This is a compact approach suitable for our current engine. If terrain definitions eventually acquire dozens of attributes, we may decide to replace these positional arrays with a dedicated class or another named structure. For now, there is little reason to introduce that additional machinery.

Asking the map whether movement is possible

With the new data available, we need a way for the player module to ask the map engine whether a destination can be entered. This responsibility belongs to the map engine, not the player module. The player module should not know how terrain definitions are stored, which dictionary index contains a blocking flag, or how map coordinates correspond to array indices. It should be able to ask a simple question and receive a simple answer.

Before checking a terrain definition, we must establish whether the requested position exists at all. Our coordinate system uses one-based coordinates, with X increasing to the right and Y increasing downwards. The top-left tile is (1, 1), and the bottom-right tile is (mMapWidth, mMapHeight). A coordinate beyond those limits is outside the map.

We introduce IsWithinMap, IsTileWalkable and GetTerrainMessage as public functions in modMapEngine.

The first function is a straightforward boundary check. It returns True only when both coordinates fall within the map's dimensions. The next function, IsTileWalkable, returns False if the terrain definitions have not been loaded or if the requested position is outside the map. Otherwise, it retrieves the terrain ID from our existing mTerrain(Y, X) array and looks up its definition.

The expression Not CBool(properties(3)) is worth examining. properties(3) is our BlocksMovement flag. A value of True means the terrain prevents movement, so we reverse it to obtain the answer to our question: whether the tile is walkable. A wall therefore returns False, while grass returns True. It is important to understand what this function does not yet check. It considers terrain only. When we eventually add monsters and NPCs, a perfectly walkable floor tile may be occupied by something the player cannot pass through. That will require a broader movement validation system. For now, the simpler terrain check is exactly what we need.

The third function, GetTerrainMessage, retrieves the description appropriate to the attempted action. The "blocked" parameter tells it whether to retrieve the blocking message or the entry message. We could have written two separate functions, but since both perform almost identical lookups, a single procedure is sufficient.

There is one particularly useful defensive check at the end of the function:

Here, Trim$ removes spaces from the beginning and end of a string, while Len counts the characters remaining. If the resulting length is zero, our message was either empty or consisted entirely of spaces. In that situation, the function supplies a sensible default. This means we do not need to write a unique message for every terrain type immediately. Blank entries are acceptable, and the game can still respond appropriately. It is a small piece of defensive programming, but one that makes the data easier to maintain.

Finding and restoring an individual screen tile

Our original renderer draws the entire terrain map. That is appropriate when entering a location, but it would be wasteful to redraw the complete map every time the character takes a single step. For this milestone, only two display cells need attention: the player's previous position and the new one. We therefore need a way to translate logical map coordinates into worksheet cells, and a way to restore the original terrain appearance at any given coordinate.

Add the following procedures to modMapEngine.

In our test workbook, the top-left terrain tile is displayed at Screen!B4, corresponding to map coordinate (1, 1). The GetScreenCell function translates map coordinates into Excel addresses by adding the appropriate offsets. The subtraction of one is necessary because our logical coordinates begin at one, while the first tile requires an offset of zero from the screen origin. The returned Range object represents the visible cell corresponding to that map position. RedrawTerrainTile then uses the terrain array and its definition to restore the original glyph, background and foreground colour. It also removes the bold font formatting used for the player.

Notice that it does not need to remember what was previously displayed in that worksheet cell. It asks the actual terrain data what belongs there. That distinction matters. Later, when items and monsters can occupy the same coordinates, we will replace this simple terrain restoration with a more capable tile renderer that determines the highest-priority visible object. For our single-character prototype, however, this procedure is sufficient and efficient.

Introducing the player module

With the map engine able to answer movement queries and restore terrain, we can create a separate standard VBA module called modPlayer. This module is responsible for the player's position, movement commands and temporary visual representation.

We will begin with three module-level variables: mPlayerX, mPlayerY and mPlayerReady. The first two are the character's map coordinates. The third indicates whether the player has been initialised and can accept movement commands. These are declared Private, so other modules cannot arbitrarily change the player's position.

This is deliberate. If we want to move the player, we should use the movement procedures, allowing them to enforce the necessary rules.

Here is the complete modPlayer code used for this milestone.

Let's examine how these procedures work together.

Initialising the character

The InitialisePlayer procedure accepts a starting X and Y coordinate. Before setting those coordinates, it marks the player as not ready and asks the map engine whether the proposed position is walkable. If the starting point is inside a wall, outside the map or on another impassable terrain type, the procedure raises an error rather than placing the character somewhere invalid. Once the position is accepted, it stores the coordinates, draws the player, marks the player as ready and displays the initial message.

The mPlayerReady flag also prevents movement commands from running against an uninitialised map. This is especially important because VBA module-level variables do not retain their values after the workbook is closed or the VBA project is reset. In our current prototype, the player must therefore be initialised whenever a new session begins. Eventually, a proper game-startup procedure will take responsibility for that. For now, we will use our existing ShowMap macro.

Drawing the player

The DrawPlayer procedure calls GetScreenCell to locate the worksheet cell corresponding to the player's logical coordinates. It then writes the @ symbol, changes the font colour and applies bold formatting. There is no terrain lookup here because the player's location has already been validated. Also notice that we don't change the cell's background colour. The underlying terrain formatting remains intact. This allows the character to remain visually associated with the terrain beneath it, whether that happens to be grass, a stone floor or a path. Later, a more advanced renderer may apply additional visual effects, but the current approach is adequate for the first playable version.

Separating movement commands from movement rules

The central movement routine is MovePlayer. It is declared Private, which means it can only be called from within modPlayer. The public movement procedures, such as MoveNorth, MoveEast and MoveSW, are small wrappers that pass the appropriate directional values to it. For instance, moving north decreases Y by one while leaving X unchanged. Moving south-east increases both X and Y by one. All eight directions therefore share the same movement algorithm.

This is preferable to writing eight different procedures containing their own collision checks, terrain updates and status messages. Not only would that duplicate code, but it would also make future changes much more difficult. Fixing a movement bug in one direction could leave the same bug in the other seven. Our approach puts the rules in one place and treats the directional procedures simply as different ways of requesting movement. It also means that when keyboard input is introduced later, those keyboard commands will be able to use the same movement routines. We will not need a separate keyboard movement engine.

Checking the destination before moving

The first significant operation in MovePlayer calculates the requested destination. We add the directional changes, deltaX and deltaY, to the current coordinates. The resulting position is then passed to IsTileWalkable. If the destination is impassable, the move is rejected immediately and GetTerrainMessage retrieves the appropriate blocking message.

This order is important. Suppose the character tries to move diagonally into a wall. We want the game to recognise that the destination is a wall and display its specific message. We do not want it to produce a generic diagonal movement error merely because adjacent terrain also happens to be obstructed. Therefore, the destination's own passability is always checked first. Only after the destination has been confirmed as passable do we consider the additional rules governing diagonal movement.

Preventing movement through impossible corners

Eight-directional movement introduces a problem that doesn't exist when characters can only move north, south, east and west. Imagine the character standing next to two walls, one immediately north and another immediately east. The north-east tile might be open floor, but allowing the character to move diagonally onto it would effectively permit movement through the solid corner between the two walls. For our game, we don't want that.

We also don't want to prohibit every diagonal move that passes near an obstacle. A character should be able to move diagonally past a single tree or wall where there is sufficient space. Our rule is therefore simple: a diagonal move into a passable destination is rejected if both of the adjacent orthogonal tiles are impassable.

The relevant condition is:

The expression deltaX <> 0 And deltaY <> 0 identifies diagonal movement because both coordinates are changing. For an ordinary northward move, X remains unchanged, so the additional check is skipped. For a north-east move, the procedure examines the eastern neighbour and the northern neighbour. If both are blocked, the move is refused.

A diagonal destination that is itself a wall will already have been rejected by the earlier destination check. It will therefore produce the wall's message instead of the corner-blocking message.

The same principle could eventually influence monster movement and pathfinding. We will want enemies to obey terrain restrictions consistently rather than finding shortcuts through supposedly solid corners.

Moving without damaging the map

Once the move has passed the validation checks, the procedure restores the terrain at the player's current position. It then updates mPlayerX and mPlayerY, draws the player at the destination and displays the appropriate entry message.

The sequence matters. If we updated the coordinates first, we would lose the old position unless we had stored it separately. By restoring the old tile before changing the coordinates, we can use the current player position directly. More importantly, notice what does not happen: we never change the original map array to represent the player's position. The terrain array remains unchanged throughout the entire movement process. If the player walks across a path, our source map still contains the path's terrain IDs. If the player moves onto grass, the grass remains grass. Only the screen representation is modified. This is the practical benefit of the architecture established in Part 1. We can introduce a moving object without changing how the map itself is defined.

Creating the mouse-controlled navigation pad

We have deliberately chosen to use mouse controls during this stage of development. Traditional roguelikes commonly use the numeric keypad for eight-directional movement, and we may introduce that interface later. For now, however, clickable Excel shapes are sufficient and avoid the additional complications of intercepting normal keyboard behaviour.

Excel's Application.OnKey method can associate keys with macros, but those assignments operate at the Excel application level rather than being inherently confined to a particular game worksheet. That would require careful management when switching workbooks, leaving the game screen or closing the workbook. It is a worthwhile feature, but one that deserves a separate implementation and article.

Our initial navigation interface uses nine shapes arranged in a three-by-three grid beneath the world view. The outer eight shapes represent movement directions, while the centre shape represents Wait. Each shape has a macro manually assigned through Excel's Assign Macro command. The macros are the public directional procedures defined in modPlayer. This works because the shapes don't need to pass arguments directly to MovePlayer. They simply invoke the appropriate public wrapper. The buttons are independent of worksheet selection changes and don't interfere with the user's usual keyboard behaviour.

The buttons are shapes built and configured directly in Excel, which is entirely reasonable for a small, fixed interface. For now, the user still needs to assign the macros to the shapes when rebuilding the .xlsm from the downloadable files. As this is not very friendly to all users, the next milestone will include automating their setup.

What does Wait actually do?

The centre button is slightly different from the movement buttons. Its purpose is to let the player spend time without changing position. In the completed game, that might allow a monster to approach, a status effect to progress or an environmental event to occur. At present, however, we haven't implemented a turn system. Our WaitTurn procedure therefore displays a placeholder message and does nothing else.

Connecting player initialisation to the game

There is one final change to our existing modMapEngine code. The ShowMap procedure already loads terrain definitions, retrieves the selected map, draws the terrain and activates the game screen. We insert a call to InitialisePlayer immediately after DrawMap. The relevant section becomes:

This means the map is completely loaded before we attempt to place the character on it. We start at coordinate (10, 8), which is a walkable floor tile in our demonstration location.

Running ShowMap again reloads the original terrain and places the character back at the starting position. This is useful for testing because we can reliably return the prototype to its initial state. It also means that after closing and reopening the workbook, we must run ShowMap again before the movement buttons will work.

That requirement is not a movement-engine defect. It simply reflects the fact that our module-level variables are initialised during the current VBA session and are not yet backed by a saved game state. A future startup system will make this process automatic.

Testing our first playable game feature

With the code and controls in place, we can finally explore Old Fort Approach. The first test is simply to run ShowMap and confirm that the @ character appears at the designated starting position. Clicking one of the directional controls should move the character by exactly one tile. Moving across grass, floors and paths should display the corresponding entry messages, while the original terrain characters and colours should reappear behind the player. Attempting to enter a wall or tree should leave the character in place and display that terrain type's blocking message. Water is also impassable in our initial terrain definitions, so trying to enter it should be refused. At the edges of the map, movement should fail safely rather than attempting to access an array element beyond its dimensions.

We should also test diagonal movement from several different positions. A diagonal destination that is itself impassable must be rejected with its own terrain message. A passable diagonal destination must also be refused when both adjacent orthogonal positions are blocked, but it should remain accessible when that particular obstruction does not exist.

Finally, the Wait button should leave the character in place and display its placeholder message. All of these tests were carried out successfully in our desktop Excel prototype.

What we've achieved

Our game is still extremely small. It has one handcrafted test location, one character and no opponents. Nevertheless, we have introduced several important capabilities.

The player is now a separate part of the game state rather than an entry in the terrain map. Movement is expressed through changes in logical coordinates rather than through arbitrary manipulation of worksheet addresses. The map engine is responsible for terrain queries, while the player module is responsible for interpreting movement requests. The visible worksheet remains a presentation layer.

We've also introduced contextual terrain messages without hard-coding them throughout the movement engine, giving our world the beginnings of personality. And by separating the movement rules from the mouse-control procedures, we've established a structure that can later support keyboard controls, alternative interfaces and perhaps other kinds of movement commands without duplicating the underlying logic.

There is still a limitation to our current presentation: the entire map is visible at all times. A character standing behind a wall can effectively see everything on the other side of it, and unexplored areas are revealed before the player ever visits them. That is not the behaviour we want in the finished game. Eventually, our world view will distinguish between terrain the player has never observed, terrain that is currently visible, and terrain that was observed previously but is now outside the character's sight.

The more interesting challenge is that the actual world must continue existing independently of what the player knows about it. A door might open outside the player's sight while its last observed appearance remains closed. A monster might die on a trap and leave treasure behind, even though the player cannot see the event. Those mechanics will require us to distinguish the true world state from the character's remembered version of it. The architecture we've created so far gives us a suitable foundation for that work. For now, however, we have achieved our second development milestone: our Excel RPG has a character that can move, encounter obstacles and interact with the terrain around it. The world is no longer merely a picture. It has become a place we can begin to explore.

The .xlsx file can be downloaded here, and the .bas modules are here and here.

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet