Cat On A Spreadsheet

In the previous installment of our Excel RPG series, we finally brought our game world to life. We introduced a player character, gave it a position independent of the underlying terrain, and implemented eight-directional movement with collision detection and contextual messages. Our little adventurer could now wander through Old Fort Approach, walk across grass, encounter solid walls and discover that trees are considerably less accommodating than they might first appear. There was, however, one rather unappealing aspect of the experience: starting the game.

To play our prototype, we had to open Excel and manually assign macros to nine navigation buttons, then run another macro to start. For someone familiar with Excel and VBA, this isn't terribly difficult. For someone following the series with little programming experience, it is an unnecessary obstacle. More importantly, it doesn't feel much like starting a game.

Most games don't expect players to open their development environment and execute an initialisation procedure. They present a main menu, offer a New Game option, and prepare the interface automatically. That's what we're going to implement today.

By the end of this article, opening our workbook will display a simple main menu. Clicking New Game will load the world, place the player at the starting location and reveal a fully functioning navigation pad. All the buttons will be created, formatted, positioned and connected to their macros by VBA. We will also organise the new functionality into separate modules, keeping the map engine, player logic, application startup and interface creation independent of one another.

As with our previous milestones, we're deliberately limiting the scope. We won't implement a save system, continue-game functionality, a complete application-state manager or elaborate error-handling routines. Those are subjects for another day. For now, our goal is to make the game easier to start and more welcoming to people who are learning how to build it.

From a collection of macros to a small application

Until now, our workbook has had two main VBA modules. The first, modMapEngine, handles terrain definitions, map loading and rendering. It knows how to interpret our map data and how to display the resulting world on the Screen worksheet. The second, modPlayer, maintains the character's position and processes movement requests. It knows how to validate a destination, move the character and update the displayed messages. This separation has worked well, and we have no reason to abandon it.

What we don't yet have is something responsible for coordinating the application as a whole. At present, that responsibility falls largely on our ShowMap macro. Running it loads the map, displays the terrain and initialises the player. That's adequate for development, but it isn't how we want the eventual game to operate. Opening the application should not necessarily mean starting a new adventure. Our finished RPG will eventually support persistent characters and saved games, so we need to distinguish between opening the workbook and beginning a new game.

We'll therefore introduce two more standard VBA modules: modGame will control the startup sequence, the main menu and the creation of a new game session, while modInterface will handle the visual interface, including the creation, formatting and macro assignments of our clickable buttons. Neither of these modules needs to understand the details of terrain IDs or collision detection. Conversely, our existing map and player modules shouldn't need to know how to construct an Excel shape.

This is the main architectural principle behind today's work: each component should have a clear responsibility, and the game should coordinate those components rather than mixing their implementations together.

How can Excel start a game automatically?

Excel provides more than one way of executing VBA when a workbook opens. The modern event-driven approach is the Workbook_Open event, placed in the ThisWorkbook object module. This event runs when the workbook opens, provided VBA is permitted to execute. There is also an older mechanism called Auto_Open.

When Excel opens a workbook normally, it can execute a procedure named Auto_Open stored in a standard VBA module. Microsoft retains this mechanism for compatibility, although workbook events are generally recommended for new applications. For our project, Auto_Open has one particularly useful advantage: it can be exported and imported as part of an ordinary .bas file.

Because we're documenting the development of this game as a programming series, we want readers to be able to download the VBA modules separately, inspect them and add them to their own workbooks. Using Auto_Open means we don't have to ask readers to paste a special event procedure into ThisWorkbook just to make the startup system work.

The implementation is remarkably simple:

That's all the automatic startup procedure needs to do. It doesn't load a dungeon, create a character or begin advancing game time. It simply asks the application to display its main menu. We'll define ShowMainMenu shortly.

One practical detail is worth mentioning: importing the module into an already open workbook doesn't itself trigger Auto_Open. The reader will run the startup procedure manually once after importing the code, or save and reopen the macro-enabled workbook. Thereafter, opening the workbook normally with macros enabled will launch the menu automatically.

This also gives us a useful future extension point. Once we implement saved games, the main menu will be able to offer New Game and Continue Game without changing the way the workbook starts.

Giving the player an inactive state

Before introducing our new modules, we need one small change to modPlayer. In Part 2, we created a private Boolean variable named mPlayerReady. Our movement procedures check this variable before allowing the character to move. It is set to True when the player has been successfully initialised. We now need a way to deactivate the player when returning to the main menu.

Since the variable is private to modPlayer, other modules cannot change it directly. This is desirable, because other components should interact with the player through clearly defined procedures rather than modifying internal variables without permission.

We'll add the following procedure to modPlayer:

Notice what this procedure does not do. It doesn't delete the player's coordinates, alter the map or attempt to clear the screen. It simply prevents the movement routines from processing commands until the player is initialised again. When a new game begins, our existing InitialisePlayer procedure will establish a valid starting position and restore the ready state. This is sufficient for the current prototype.

Creating the interface dynamically

We can now start constructing modInterface. In the VBA editor, select Insert, then Module, and name the new module modInterface.

One of our main objectives is to eliminate the need for users to create and configure nine navigation buttons manually. Excel exposes worksheet shapes through the Shapes collection, and VBA can create a shape using its AddShape method. The method accepts a shape type and four measurements: its left position, top position, width and height. These measurements are expressed in points. We can therefore create a rounded rectangle, place it at a calculated position on the worksheet, add a caption and assign a macro to execute when it is clicked.

A navigation pad consisting of nine buttons is an ideal example because all the controls share the same general appearance and layout. Rather than write nine separate shape-creation procedures, we'll write reusable routines that can generate and configure them from arrays of names, captions and macro assignments.

Giving each button a permanent identity

First, we define the names of our nine navigation controls. Add the following function to modInterface:

Every Excel shape has a name, and these names give us a reliable way to identify our interface elements independently of their positions. That is useful because the navigation controls may be recreated, reformatted or moved during development. The north button remains btnMoveNorth regardless of where we place it.

The function is declared Private because it is an internal detail of the interface module. Other game systems don't need to know the names of the individual Excel shapes. The return type is Variant because VBA's Array function returns a Variant containing an array.

Creating a button without creating duplicates

We could simply call Shapes.AddShape every time the game opens. Unfortunately, that would produce an increasingly cluttered worksheet. Every startup would create another nine buttons, potentially stacking new shapes over the old ones. Instead, we'll first check whether a shape with the required name already exists. If it does, we'll reuse it. If not, we'll create it.

Add this function:

The function accepts a worksheet and the required button name, then returns a Shape object. We use Set when returning the shape because it is an object reference rather than a simple number or string.

The newly created rectangle initially receives a default position and size. Those are merely temporary values; we'll calculate its actual position later.

This approach has an important property often described as idempotence: calling the interface-building procedure repeatedly will produce the same intended result rather than continually adding new controls. For our purposes, it simply means that opening the game ten times won't leave us with ninety navigation buttons.

Formatting buttons consistently

Creating a rounded rectangle is only the beginning. We also want the controls to look reasonably consistent with our dark game interface. Each button should have a background colour, border, centred caption and suitable font. Rather than applying these properties separately for every control, we'll create a formatting procedure.

This procedure demonstrates several useful aspects of working with objects in VBA. The With statement lets us apply multiple properties to the same object without repeatedly writing the object's full name. We also have nested With statements. The outer block refers to the button. The next block refers to its TextFrame, which contains the caption and its alignment properties. The innermost block refers to the caption's font. This is a convenient way of grouping related instructions, making the structure of the object model more apparent in the code.

For the directional controls, we're using the Segoe UI Symbol font. This is a Windows font with broad support for Unicode symbols, making it suitable for the arrows we'll be displaying. The .Placement = xlMoveAndSize setting tells Excel that the shape should move and resize with its underlying cells. Our current interface uses a fixed worksheet layout, and this behaviour is satisfactory for the prototype. Later, if we change screen dimensions or redesign the interface, we may want to reconsider how the shapes respond to worksheet resizing.

Notice also that the background colour is passed as a parameter rather than fixed inside the procedure. That will allow us to reuse the same formatting routine for both the navigation buttons and the New Game button, while giving them different colours.

Connecting shapes to VBA procedures

Once a button exists and has been formatted, we need to make it do something. Excel provides the OnAction property for this purpose. It specifies which macro Excel should execute when a shape is clicked. We could assign a macro by name, but that becomes less reliable if several open workbooks contain procedures with identical names. For example, a reader might have two versions of our RPG open simultaneously, each containing a procedure named MoveNorth. To identify the intended workbook explicitly, we'll generate a workbook-qualified macro reference.

Add this function to modInterface:

ThisWorkbook.Name identifies the workbook containing our running VBA code, rather than whichever workbook happens to be active in Excel. We surround the filename with apostrophes and append the macro name after an exclamation mark. For example, a movement button in a workbook named ExcelRPG.xlsm would receive a reference equivalent to 'ExcelRPG.xlsm'!MoveNorth.

The Replace call doubles apostrophes within the filename so that a filename containing an apostrophe doesn't interfere with the reference.

We won't have to enter any of these assignments manually. The interface-building procedure will do that for every button. This also gives readers the freedom to save their development workbook under a different name. The next interface initialisation will build references using the actual workbook name.

Building the complete navigation interface

Now we're ready for the main procedure in modInterface. Our navigation controls will occupy the region Y22:AG26 on the Screen worksheet, which we reserved when creating the game layout. We'll arrange them in a three-by-three grid.

Instead of hard-coding the position of each individual button, we'll calculate the dimensions of the available area and distribute the controls evenly within it. This also gives us a useful demonstration of how ordinary arithmetic can be used for automated interface layout.

Add the following code:

This is the longest procedure we've introduced in this milestone, but its responsibilities are straightforward. It first prepares three arrays: one containing the shape names, one containing the macros, and one containing the visible captions. The entries are deliberately kept in the same order. For example, the first entry in each array refers to north-west movement. The second refers to north, the third to north-east, and so on.

The ChrW function is used to generate Unicode arrows. The hexadecimal values identify the appropriate symbols without requiring us to paste the actual arrows directly into the VBA editor.

Next, we calculate the space available for the navigation pad. The pad.Width and pad.Height properties provide the dimensions of the worksheet range in points. We subtract the space reserved for gaps, then divide the remainder by three to obtain the button dimensions.

The loop then determines where each button belongs. The expression i Mod 3 gives us the column within the three-column grid. The integer division expression i \ 3 gives us the row. For indices zero through eight, these calculations produce every position in the three-by-three layout without requiring separate positioning instructions for each shape. The resulting coordinates are added to the navigation area's left and top positions.

This last detail is important: the shape positions are measured relative to the worksheet, and the pad.Left and pad.Top properties identify where our designated navigation area actually begins. The gap is added to those positions, while the calculated row and column offsets determine the location of each button within the pad.

Once the shape is positioned, we apply its formatting and assign its movement macro.

After creating the navigation controls, the same procedure prepares our New Game button. Instead of positioning it in the navigation area, we locate the map display region and calculate its centre. We then position a larger, green button in that space and assign it the StartNewGame macro.

We now have a single procedure capable of building our entire initial interface, including both the main-menu button and the navigation pad.

Switching between menu and gameplay controls

There's one more job for modInterface. Our game will initially have two interface states. In the main menu, we want to show New Game and hide the navigation controls; during gameplay, we want exactly the opposite. Luckily, we don't need to delete and recreate the buttons every time the interface changes. Excel shapes have a Visible property, allowing us to hide or reveal them without losing their formatting or macro assignments.

Add the following procedure to complete modInterface:

The playing parameter determines which controls should be displayed. When playing is True, the navigation pad is made visible and New Game is hidden. When it is False, New Game is displayed and the directional controls are hidden. This keeps the visual details within the interface module.

Our game controller won't need to know the names of nine individual movement shapes or manipulate their visibility one by one. It can simply request the appropriate interface state.

That completes modInterface.

Building the game controller

We can now create the second new module, modGame. This module is much shorter than modInterface because it is primarily responsible for coordinating the procedures we've already written. It will contain three public procedures: Auto_Open, ShowMainMenu and StartNewGame.

Insert another standard module, name it modGame, and enter the following complete code:

Notice how little code our game controller actually needs. Auto_Open delegates to ShowMainMenu, which first deactivates the player and asks modInterface to prepare the controls. It then clears the old map display, resets the visible menu text and asks the interface module to show the appropriate buttons. Finally, it activates the Screen worksheet. When New Game is clicked, the StartNewGame procedure deactivates any previous player state and calls ShowMap.

We developed ShowMap in Part 1 and extended it in Part 2. It already knows how to load our terrain definitions, locate the starting map, render it and initialise the character at (10, 8).

Once that procedure has completed, we reveal the navigation controls and hide New Game. We haven't duplicated the map-loading or player-initialisation logic inside modGame. The controller simply calls the relevant existing procedures in the correct order.

Putting the modules together

Our workbook now contains four standard VBA modules: modMapEngine manages terrain and map rendering, modPlayer manages the character and its movement, modInterface constructs and controls the visible interface and modGame coordinates startup and game initialisation.

Only one small addition was required in modPlayer: the ResetPlayer procedure. We didn't need to rewrite our movement rules, terrain definitions or map storage system. We also haven't changed the starting map. New Game still loads FORT01, our Old Fort Approach location, and places the player at (10, 8). The navigation buttons use the movement procedures established in Part 2, so the same rules governing walls, water, trees, diagonal movement and contextual messages continue to apply. The central Wait button also retains its placeholder behaviour. We haven't implemented the game's time system yet, and we will avoid introducing a makeshift turn counter into the player module.

Installing and testing the startup system

If you're following along, start with the working Milestone 2 workbook. Save a development copy as an Excel Macro-Enabled Workbook (.xlsm) if you haven't already done so. Add the new ResetPlayer procedure to modPlayer, then create the modInterface and modGame modules containing the code above.

The starter workbook and VBA source files accompanying this series are distributed separately. This allows readers to inspect the macros, learn how the procedures fit together and build their own workbook rather than simply opening a finished program.

If you're importing .bas files instead of typing the source yourself, use File → Import File in the VBA editor, and avoid importing duplicate modules with the same procedures. As always, only enable VBA macros in files whose source you have reviewed and trust. Excel may apply additional security restrictions to workbooks obtained from the internet.

Once the modules have been added, use Debug → Compile VBAProject in the VBA editor to check for syntax and declaration errors. Then run ShowMainMenu from Excel's Macro dialog. You should see the game screen cleared of terrain, with a New Game button displayed in the centre of the world-view area. The movement controls should be hidden.

Click New Game. The starting map should appear, with the @ character at the familiar (10, 8) position, and all nine navigation controls should become visible beneath the map. Test movement in each direction, including attempts to walk into walls and through blocked diagonal corners. The messages and terrain restoration should behave exactly as they did in Milestone 2.

Next, run ShowMainMenu a second time. The interface should return to the main menu, but without creating additional buttons. Running it repeatedly should still leave us with one New Game button and one set of navigation controls.

Finally, save the workbook, close Excel and reopen it. Provided macros are enabled and the workbook is opened normally, Auto_Open should display the main menu automatically.

Our current implementation doesn't attempt to preserve an active adventure between sessions. Each New Game operation starts the prototype from the original location and starting coordinates. That is intentional, because persistence belongs to a later milestone.

The importance of separating startup from New Game

This distinction is worth emphasising because it will become increasingly important as the RPG develops. Opening the workbook is an application event, while starting a new game is a gameplay action. Today, we only have one starting location and one temporary character state, so the difference may appear relatively minor, but imagine the same application after we've implemented a randomly generated dungeon, a persistent inventory, quests and permadeath. At that point, opening the workbook must not automatically discard the current run and generate a new world - instead, the startup system will need to prepare the interface and allow the player to choose what happens next.

The main menu might eventually contain Continue Game, New Game, Settings and other options. The important thing is that the architecture we've introduced can accommodate those additions without requiring us to abandon our existing map and player modules.

A few limitations we're deliberately accepting

Our interface currently uses fixed worksheet areas. The world view occupies B4:AG19, and the navigation controls occupy Y22:AG26. These addresses are referenced directly in the code, which is acceptable for a small prototype. Later, however, we will want to centralise our screen layout definitions so that changing the size or position of the game display doesn't require searching through multiple modules. We may introduce a dedicated configuration module containing shared worksheet names, layout constants and helper functions.

Similarly, we'll eventually want the entire game to manage its active state more explicitly. For now, a simple ready flag in modPlayer and a pair of main-menu/gameplay procedures are sufficient.

We also haven't introduced a comprehensive error-handling system. That will be worth considering when the game has more data, external save files and other operations where failures could cause meaningful data loss. At this point, however, there is little benefit in constructing an elaborate framework before the game itself has developed further. Our current objective is to establish a working foundation and continue adding playable features.

What we've achieved

This milestone hasn't introduced a new dungeon, monster or character ability. Nevertheless, it represents an important improvement in the overall development of the project. Our workbook now knows how to prepare itself for use.

The interface no longer depends on readers knowing how to create Excel shapes or manually associate them with macros. Opening the workbook presents a recognisable starting point, and clicking New Game performs the necessary initialisation.

We've also demonstrated several useful VBA techniques along the way: automatic workbook macros, reusable shape-creation functions, nested object formatting, Unicode captions, programmatic macro assignments, calculated interface layouts and the separation of application control from gameplay logic. Most importantly, we've accomplished this without substantially modifying the systems established in the first two articles. The map engine remains responsible for maps. The player module remains responsible for movement. The interface module handles buttons, and the game controller decides when the relevant components should be initialised.

Our Excel RPG is still an early prototype, but it is becoming more than a series of disconnected macros. It is beginning to behave like an application.

In the next development stage, we'll return to the game world itself. One of the most important missing features is visibility: our adventurer can currently see every tile on the map, even when walls, trees or other obstacles should block the view. We'll eventually need to distinguish between what the character can currently see, what they remember seeing and what remains completely unexplored.

Implementing field of vision, line of sight and fog of war will make our world considerably more interesting, while introducing another set of algorithms and design challenges. For now, though, we can open our workbook, click New Game and begin exploring—without having to configure a single navigation button manually. And that is a very satisfying improvement.

The current milestone code modules and a clean .xlsx file can be downloaded here. 

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet