Building an RPG in Excel, Part 1: Maps, Terrain and the World Engine

For a long time now, I have been thinking about developing a game of my own. An Excel worksheet is not the first place most people would look for an adventure game. It is nevertheless a surprisingly natural place to build a traditional tile-based RPG. A worksheet is already a grid, every cell already has a position, and VBA gives us enough control over values, formatting and interaction to turn that grid into something other than a spreadsheet.

I am taking this opportunity to pay tribute to one of my most loved games of all time: Ancient Domains of Mystery (ADOM) and, with it, an entire genre of games that never relied on fancy graphics to engage the player, but used the power of our own imagination to build fantastic adventures that, in quite some cases, surpass even big budget AAA titles published in more recent times.

The real challenge is not drawing a wall or a stretch of grass. It is deciding how to represent a world that can eventually contain monsters, traps, doors, items, quests and randomly generated dungeons without turning the workbook into an unmaintainable collection of special cases.

This is the first article in a series documenting the development of an original roguelike game in Excel. The goal is a single-character, turn-based adventure with a handcrafted overworld, connected story locations, procedural dungeons and permadeath.

We are not going to build all of that today.

We are going to establish something more fundamental: a reliable way to describe a map, find it, load it into VBA and draw it on a dedicated game screen. When the last line of code runs, our first location will be visible. Nothing will move yet, and that is intentional.

First, the world is not the screen

It would be easy to colour a range of cells, put an @ in one cell and call the worksheet our game world. That approach is attractive for a short demonstration, but it creates serious problems as soon as more than one thing occupies a location. Is a goblin a replacement for the floor beneath its feet? If a trap is covered by gold, which value do we keep in the cell? When the character walks away, how do we recover the original terrain?

Instead, we will treat the game screen as a view of world data, not as the world itself.

Our source maps contain numeric terrain identifiers. The game engine loads those identifiers into a two-dimensional array and resolves them against separate terrain definitions. The renderer then translates the result into characters and colours on the visible worksheet. Eventually, the same system will decide whether the character can see the tile at all, whether it is showing live information or remembered information, and which member of a stack of objects should appear. None of those future systems requires us to make the displayed cell the authoritative record of the world.

A deliberately small workbook

Our development workbook currently has four worksheets. Screen is the player-facing interface. GameData stores static definitions in named Excel Tables. Maps stores a directory of handcrafted locations and the map blocks themselves. Start_Here explains how to install and test the demonstration; it is development documentation rather than a planned game screen.

This arrangement is a conscious alternative to making a worksheet for every monster type, weapon or map. Excel Tables allow us to keep several related definition tables on one sheet, while the map directory gives every location a permanent identity and a reliable way of finding its tiles. Later we may add more kinds of definitions and generated locations, but we do not need additional worksheets merely because we have added another piece of terrain or another castle courtyard.

We will stay with the macro-enabled .xlsm format for development. The starter workbook is provided as .xlsx with the VBA module separately available for inspection and import. Save it as .xlsm before bringing in the code.

The initial map is 32 columns by 16 rows: a convenient test size, not a final restriction on the overworld.

Give every terrain type an identity

On GameData, our first structured Table is named tblTerrain. Each record describes a terrain type, not a particular place in the world. The headers are TileID, Name, Glyph, Background, Foreground, BlocksMovement and BlocksVision. We start with six records: floor, wall, grass, water, tree and path. A wall is represented by the numeric ID 2; the glyph and colours are stored in its definition, rather than repeated in every map cell.

The distinction between BlocksMovement and BlocksVision is deliberate. They may often have the same value, but they answer different questions. A glass barrier could obstruct movement without stopping sight. A dense cloud might allow movement but prevent a character from seeing through it. We are not implementing either collision or line of sight yet; we are simply giving our terrain data enough meaning to support those features later.

We also resist the temptation to put monsters, coins, traps or doors into this terrain table just because they will eventually have visible characters. Underlying terrain is one layer of a location. Active creatures, dropped objects and environmental features will be separate layers. In particular, several objects may later coexist on a tile, even though only one eligible object will appear at a time. Keeping terrain independent now avoids an awkward redesign later.

One worksheet, many maps

The Maps worksheet begins with an ordinary Excel Table called tblMaps. For now it contains five columns: MapID, DisplayName, Width, Height and AnchorName. Our first row describes FORT01, displayed as Old Fort Approach, with dimensions 32 × 16 and the anchor name map_Fort01.

There is no requirement that the human-readable name remain unchanged. Game references will use the stable map ID.

Below the directory, each handcrafted map is a rectangular block of integers, separated from the next block by a little whitespace and a heading. We use numbers on the map rather than painted tiles because these numbers are unambiguous data. A change of visual theme can alter the look of every wall without changing a single map definition.

We also avoid turning the map itself into an Excel Table: a terrain grid is naturally two-dimensional, whereas tblMaps is a row-oriented catalogue of maps. The anchor is a workbook-scoped Excel defined name referring to the top-left terrain cell, not the title above it and not the coordinate heading. In our demonstration, map_Fort01 points to Maps!$G$13. The width and height in tblMaps tell VBA how much of the rectangle to read.

If the map moves down when new rows are inserted above it, Excel normally updates the named reference. We can therefore move map blocks around without changing coordinates written inside VBA, although we must still keep the registered dimensions correct.

The directory does not have to contain only one map. Later, Castle Doom might have separate entries for the front yard, barns and interior, all on the same Maps worksheet.

The design rule for the game is that every active location fits entirely on the physical game display; anything too large becomes several connected locations. The largest location will be the overworld, and its dimensions will define the supported maximum after we test readability and the space needed for the interface beneath the map.

Coordinates that do not depend on Excel addresses

For game logic, a map position has an X coordinate and a Y coordinate. X increases to the right, Y increases downwards, and the top-left terrain tile is (1, 1). The underlying VBA array, however, follows Excel's row-then-column convention, so we read a tile as mTerrain(Y, X). This is a small naming decision that prevents a great many transposition errors.

The game screen happens to begin drawing terrain at cell B4. That does not mean the first map coordinate is B4. Map coordinate (1, 1) is a logical position; screen cell B4 is its current presentation address. The two will be connected through a simple offset in the renderer.

Keeping these concepts separate will later allow a map to be loaded from a named anchor, generated procedurally, or reconstructed from a save snapshot without changing the rendering logic.

Loading our first map

We begin with a public macro named ShowMap. This is deliberately the only procedure a reader needs to run in the Macro dialog. It loads the definitions, loads the selected map, draws the terrain and activates the game screen. It also preserves the previous ScreenUpdating value and restores it if something fails.

Turning screen updates off temporarily helps prevent Excel from visibly repainting every intermediate formatting operation; restoration is important because this is an application setting.

Public Sub ShowMap()

     Dim previousScreenUpdating As Boolean

     previousScreenUpdating = Application.ScreenUpdating



     On Error GoTo Failed

     Application.ScreenUpdating = False



     LoadTerrainDefinitions

     LoadMap "FORT01"

     DrawMap

     ThisWorkbook.Worksheets("Screen").Activate



CleanExit:

     Application.ScreenUpdating = previousScreenUpdating

     Exit Sub



Failed:

     MsgBox "Map display failed:" & vbCrLf & Err.Description, _

          vbExclamation, "Excel RPG"

     Resume CleanExit

End Sub

Notice the line LoadMap "FORT01". The map is selected by ID, not by worksheet cell coordinates. We could later pass a different ID from a game controller without rewriting the loading procedure.

The selected map is the only one we draw; other handcrafted maps remain stored in the Maps worksheet.

Loading terrain definitions from an Excel Table

The next procedure retrieves the actual ListObject by name. Excel's DataBodyRange gives us the rows of data without the column headings. The ListColumns collection then lets us locate each field by its header. That means we can move Background to another position in the table without silently turning colours into glyphs. We do not rely on A, B or C being a particular field.

The entire table body is read into a VBA array using .Value2. From that point, the loop works with an in-memory array rather than repeatedly asking Excel for individual cell values.

Each definition is placed into a late-bound Scripting.Dictionary keyed by its numeric terrain ID. This provides fast lookup and means a tile with ID 2 can retrieve the wall's glyph, colours and two blocking flags without searching the worksheet every time it is drawn.

The complete procedure is included in the accompanying VBA module. Its central operations are:

Set tbl = ThisWorkbook.Worksheets("GameData").ListObjects("tblTerrain")



idCol = tbl.ListColumns("TileID").Index

glyphCol = tbl.ListColumns("Glyph").Index

bgCol = tbl.ListColumns("Background").Index

fgCol = tbl.ListColumns("Foreground").Index



data = tbl.DataBodyRange.Value2

Set mTerrainDefs = CreateObject("Scripting.Dictionary")

The dictionary values are small arrays. In this first version, element zero is the glyph, one and two are the background and foreground colours, three and four are the movement and vision flags, and five is the descriptive name. This keeps our first implementation compact. If the number of terrain properties grows considerably, a dedicated VBA class or user-defined type may become a better representation. It is not necessary yet.

Colours in tblTerrain are stored in the familiar #RRGGBB format. VBA expects the numeric colour returned by RGB, so a helper translates between them. That helper also makes the source worksheet easier to read, maintain and edit.

Finding a map without knowing where its cells are

Now we can load the chosen map. The loader reads tblMaps, finds the record matching the requested MapID, retrieves its title and dimensions and resolves its named anchor. The central operation is:

Set anchor = ThisWorkbook.Names(anchorName).RefersToRange

mTerrain = anchor.Resize(mMapHeight, mMapWidth).Value2

The first line retrieves the cell referred to by our named anchor. Excel exposes that functionality through the Name.RefersToRange property. The second expands the upper-left cell to the exact height and width registered in the directory, then transfers the entire terrain rectangle into a two-dimensional VBA array in one operation.

For the prototype, the screen has room for 32 × 16 terrain cells, so the loader rejects maps that exceed that test capacity. This is not intended to force all future maps to be 32 × 16. Once we settle on our minimum supported screen resolution, the overworld will establish the actual maximum capacity, and individual maps may be smaller.

The loader also checks that the anchor belongs to the Maps worksheet and refers to exactly one cell.

There is a small but useful VBA edge case here. Reading a rectangular multi-cell range with .Value2 returns a two-dimensional array, but reading a single cell returns a scalar. We normalise that special case into a one-element two-dimensional array so that the renderer can use the same (Y, X) indexing for every supported map size. A 1 × 1 map is not a particularly exciting dungeon, but the engine should behave consistently rather than fail for an avoidable technical reason.

Turning terrain IDs into the visible world

We now have a real map in memory, but the screen is still empty. The renderer's job is to translate every terrain ID into a character and a pair of colours. Crucially, it does not need to know where that map came from. The value 2 means a wall whether the source was a handcrafted worksheet, a dungeon generator or a previously saved map.

The rendering procedure first validates each terrain value. It rejects non-numeric values, non-integer IDs and IDs that have no definition in tblTerrain, and reports the offending logical map coordinate. This is useful during map authoring: an accidental 99 should produce a meaningful error rather than a mysteriously blank or incorrectly coloured wall.

The renderer builds an array of display glyphs in memory, then writes that entire rectangle onto Screen in a single assignment.

ws.Cells(SCREEN_FIRST_ROW, SCREEN_FIRST_COL). _

     Resize(mMapHeight, mMapWidth).Value2 = symbols

Reading and writing rectangular blocks of cells in single operations is a recognised Excel/VBA performance optimisation. It reduces the number of exchanges between VBA and the worksheet object model.

Because a future location might be smaller than the previous one, the entire allocated terrain area is cleared before drawing. Otherwise, stray characters from a larger previous map would remain around the edges.

The map values are transferred in one operation, but Excel formatting still requires separate work. To reduce the number of formatting calls, we collect horizontally adjacent tiles of the same terrain ID into runs and colour each run as a single range.

This approach is not the only possible renderer. We could use conditional formatting to handle some of the colour decisions, and we may benchmark other methods when movement and visibility require frequent redraws.

The important design is independent of the optimisation: map data and visual presentation remain separate. In the current version, the terrain definitions also carry movement and sight-blocking flags, but the renderer does not use them because those features have not been implemented.

Testing the map foundation

To test the demonstration, open the starter workbook in Windows desktop Excel and save a macro-enabled copy. Open the VBA editor with Alt+F11, import modMapEngine_v02.bas, and run ShowMap from the Macro dialog. The screen should show the coloured Old Fort Approach terrain, and the status region should report its dimensions.

Next, return to Maps, edit one terrain value inside the 32 × 16 block and run the macro again. A valid replacement ID should change the tile's appearance according to tblTerrain. Try changing a terrain definition's glyph or colour to verify that the same map data can produce a different appearance.

Finally, deliberately enter an undefined ID to see whether validation correctly identifies its X and Y coordinates.

These tests are more valuable than a screenshot alone because they verify that we have constructed a data-driven renderer, rather than merely painted a static picture.

The source and workbook are supplied separately so that readers can inspect the macro code before enabling it.

What the map system is preparing us for

One day the player will travel through this world rather than merely look at it. When movement is added, the engine will need to check whether a destination is passable. Later, vision will become another calculation: a tile can be within the location's overall screen area but still be unexplored or hidden behind an obstacle. The last observed appearance of a door, a pile of coins or another environmental feature will remain in fog of war until the character sees it again. Meanwhile, monsters on the active map can continue moving outside sight. None of that changes the numeric terrain stored in the source map.

Our world will eventually contain generated dungeons as well. A procedural floor will be generated once per run and will retain a complete map state for return visits and, once saving is implemented, for later play sessions. Locations that do not currently contain the player will be frozen: no game time passes there. This is a future game-state requirement, not something we need to implement to draw the first test map.

The common in-memory map representation is what will allow handcrafted, generated and restored maps to share the same renderer. The first milestone has therefore produced something modest but structurally important. We can describe terrain independently of its appearance, register maps independently of their worksheet addresses, load a chosen map into a real array, validate it and render it. More importantly, we have done so without tying future game rules to the contents of the visible Excel cells. That is the foundation on which the more interesting systems can stand.

In the next article we will give the world its first inhabitant and make eight-directional movement possible through clickable controls. Keyboard handling can have its own article later; there is no reason to complicate the map foundation with application-wide key interception before the basic engine is ready.