Cat On A Spreadsheet

Building an RPG in Excel, Part 4: Characters, Monsters and a Living Game World

In the previous instalment of our Excel RPG series, we made our game considerably easier to start. We introduced an automated main menu, created navigation controls directly through VBA and allowed the player to begin an adventure without manually running macros or assigning buttons. Our adventurer could move around Old Fort Approach, encounter impassable terrain and receive appropriate messages when attempting to walk through walls or squeeze between obstacles.

 

But there was something rather conspicuous missing from our world: everyone else. An abandoned fort can certainly be atmospheric, but even the most devoted explorer will eventually notice that there are no creatures to encounter, no enemies to avoid and nobody particularly interested in preventing them from wandering wherever they please.

 

Today, we're going to change that. We'll introduce a proper character system, give our adventurer health and damage statistics, and populate the starting location with a few monsters. More importantly, we'll establish the distinction between a character type and an individual character, allowing multiple creatures of the same type to exist independently.

 

We'll also improve our underlying VBA architecture. Some of our earlier modules still contain repeated worksheet names, display ranges and screen coordinates. As our RPG becomes more sophisticated, maintaining those references in several different places would quickly become inconvenient. We'll consolidate them into a shared module and adapt our map renderer accordingly.

 

By the end of this instalment, Old Fort Approach will contain two goblins and a wolf, each with an independent identity, position and statistics. Our adventurer will be able to walk around them but will no longer be able to occupy the same tile as a monster. The creatures won't move or attack yet. Those mechanics require a turn scheduler and a combat system, which deserve their own development stages. For now, we're going to establish the foundation on which those systems will eventually operate.

 

What exactly is a character?

 

Until now, our player character has been rather simple. The modPlayer module stores the character's X and Y coordinates, along with a Boolean variable indicating whether the player has been initialised. When the character moves, the module updates those coordinates, restores the terrain beneath the old position and draws the @ symbol in the new cell.

 

That's a perfectly reasonable approach when there is only one character in the entire game. It becomes less suitable when we introduce monsters.

 

Suppose we want two goblins to occupy different positions on the map. Both should have the same basic characteristics: a name, a symbol, a maximum amount of health and an attack damage value. However, they must remain separate individuals. If the first goblin loses three HP, the second goblin shouldn't suffer the same damage simply because both belong to the same monster type. If one goblin moves, the other should remain where it is.

 

This brings us to an important distinction in game development: definitions versus instances. A definition describes what a particular type of creature is. A goblin definition might specify a maximum of eight HP, two points of damage and the letter g as its map symbol. An instance represents one particular goblin existing in the game world. It has its own identifier, current HP and location, while drawing its initial properties from the shared definition. We can think of the definition as a template and an instance as an individual object created from that template.

 

We're going to store our definitions in Excel Tables, as we've already done with terrain. Our individual creatures will exist as VBA objects created when a game session begins. This will also apply to the player. Rather than maintaining entirely separate sets of variables for adventurers and monsters, we'll represent both using the same basic character structure.

 

Centralising our shared game configuration

 

Before introducing creatures, we need to address a growing maintenance issue. Our existing code refers to several worksheet names and screen locations directly. The map begins at B4, the visible map area occupies B4:AG19, the navigation controls occupy Y22:AG26, and various modules refer independently to the Screen, Maps and GameData worksheets. There is nothing inherently wrong with using fixed ranges. In fact, for our current design, where an entire location must fit on the screen without scrolling, having a defined display area makes good sense. The problem is repeating those definitions throughout the application. If we eventually decide to make the map wider, move the status panel or reorganise the game interface, we shouldn't have to search through every module for hard-coded worksheet addresses.

 

We'll therefore introduce a standard module named modGlobals. Despite its name, we won't use it to make every variable in the application global.  Temporary variables such as loop counters, worksheet references and the currently processed shape should generally remain local to the procedures using them. Instead, modGlobals will contain information that genuinely needs to be shared: worksheet identifiers, interface locations, starting coordinates and reusable functions that retrieve the relevant worksheet or range.

 

Create a new standard module, name it modGlobals, and enter the following code.

Option Explicit

' Central worksheet identifiers.
Public Const SHEET_SCREEN As String = "Screen"
Public Const SHEET_DATA As String = "GameData"
Public Const SHEET_MAPS As String = "Maps"

' Screen regions used by the game interface.
Public Const MAP_AREA_ADDRESS As String = "B4:AG19"
Public Const NAV_AREA_ADDRESS As String = "Y22:AG26"
Public Const STATUS_CELL_ADDRESS As String = "B23"
Public Const MESSAGE_CELL_ADDRESS As String = "K23"

' Starting location and initial player coordinates.
Public Const START_MAP_ID As String = "FORT01"
Public Const START_PLAYER_X As Long = 10
Public Const START_PLAYER_Y As Long = 8

Public Function GameScreen() As Worksheet

     ' Return the worksheet used to display the game.
     Set GameScreen = ThisWorkbook.Worksheets(SHEET_SCREEN)

End Function

Public Function GameDataSheet() As Worksheet

     ' Return the worksheet containing the game's definition tables.
     Set GameDataSheet = ThisWorkbook.Worksheets(SHEET_DATA)

End Function

Public Function MapsSheet() As Worksheet

     ' Return the worksheet containing our fixed map definitions.
     Set MapsSheet = ThisWorkbook.Worksheets(SHEET_MAPS)

End Function

Public Function MapArea() As Range

     ' Retrieve the complete rectangle allocated to map rendering.
     Set MapArea = GameScreen().Range(MAP_AREA_ADDRESS)

End Function

Public Function NavigationArea() As Range

     ' Retrieve the rectangle allocated to the movement controls.
     Set NavigationArea = GameScreen().Range(NAV_AREA_ADDRESS)

End Function

 

Our worksheet names are now defined in one place. The same applies to the map area, navigation area, status location and message location. The functions provide a small but useful convenience. Instead of repeatedly writing ThisWorkbook.Worksheets("Screen"), another module can simply call GameScreen().

 

We could store a worksheet object in a global variable and initialise it once, but that would create an additional dependency on the variable having been initialised correctly. VBA object references can also be reset when the project state is reset. An accessor function avoids that problem. It retrieves the appropriate worksheet whenever it is needed, while ensuring that the worksheet's actual name is defined centrally.

 

We now need to use these functions in our existing modules. In modInterface, the BuildGameInterface procedure should obtain its worksheet and layout areas using GameScreen(), NavigationArea() and MapArea().

 

One naming detail is important here. If a procedure already declares a local variable named mapArea, rename that variable to mapDisplayArea before assigning the result of MapArea(). VBA does not distinguish uppercase and lowercase letters in identifiers, so a local variable named mapArea would conflict with the function named MapArea.

 

The relevant assignments in BuildGameInterface should therefore be:

 

' Retrieve the worksheet and interface regions from modGlobals.
Set ws = GameScreen()
Set pad = NavigationArea()
Set mapDisplayArea = MapArea()

 

The New Game button's positioning calculations should then use mapDisplayArea.Left, mapDisplayArea.Top, mapDisplayArea.Width and mapDisplayArea.Height. The SetGameplayControlsVisible procedure can retrieve the screen using GameScreen(), while ShowMainMenu in modGame can use the same function and clear the map through With MapArea(). We can make equivalent changes in modMapEngine, using GameDataSheet() and MapsSheet() for table references.

 

This is a relatively small refactoring, but it establishes an important principle: modules should work with shared configuration through clear interfaces, rather than each maintaining their own independent copies of that configuration.

 

Making the map renderer independent of worksheet coordinates

 

The original map renderer understood both the logical map and the worksheet location where that map was displayed. It needed to know where the first map cell appeared on the Screen worksheet, how many columns were available and how many rows could be displayed. Now that we've centralised the map area, we can simplify this relationship.

 

A logical map coordinate of (1, 1) should correspond to the first cell of MapArea(), regardless of whether the visible map starts at B4, D6 or somewhere else. This lets us replace calculations involving absolute worksheet rows and columns with calculations relative to the map display range.

 

In modMapEngine, replace the existing GetScreenCell function with:

Public Function GetScreenCell( _
     ByVal x As Long, _
     ByVal y As Long) As Range

     ' Translate logical map coordinates into a worksheet cell.
     Set GetScreenCell = MapArea().Cells(y, x)

End Function

 

Remember that Excel's Cells collection uses row-first indexing, whereas our logical game coordinates are expressed as X and Y. That is why the arguments appear as (y, x).

 

The map-size validation inside LoadMap should likewise compare the loaded dimensions against MapArea().Columns.Count and MapArea().Rows.Count. We no longer need separate constants for the first display row, first display column or maximum dimensions, because the range itself supplies that information. Our rendering algorithm, however, remains largely unchanged. We still validate terrain IDs, prepare an array of display characters, write those characters to Excel in one operation and apply terrain formatting in horizontal runs.

 

Here is the complete updated DrawMap procedure.

Private Sub DrawMap()

     Dim ws As Worksheet
     Dim displayArea As Range
     Dim symbols() As Variant
     Dim ids() As Long

     Dim x As Long
     Dim y As Long
     Dim firstX As Long
     Dim terrainID As Long

     Dim raw As Variant
     Dim props As Variant
     Dim run As Range

     ' Retrieve the game screen and its allocated map area.
     Set ws = GameScreen()
     Set displayArea = MapArea()

     ' Prepare arrays for terrain characters and IDs.
     ReDim symbols(1 To mMapHeight, 1 To mMapWidth)
     ReDim ids(1 To mMapHeight, 1 To mMapWidth)

     ' Validate terrain data before changing the worksheet.
     For y = 1 To mMapHeight
          For x = 1 To mMapWidth

               raw = mTerrain(y, x)

               ' Reject Excel errors and non-numeric terrain IDs.
               If IsError(raw) Then
                    Err.Raise vbObjectError + 120, , _
                           "Invalid tile at X=" & x & ", Y=" & y
               End If

               If Not IsNumeric(raw) Then
                    Err.Raise vbObjectError + 121, , _
                             "Non-numeric tile at X=" & x & ", Y=" & y
               End If

               ' Terrain IDs must be whole numbers.
               If CDbl(raw) <> Fix(CDbl(raw)) Then
                    Err.Raise vbObjectError + 122, , _
                             "Non-integer tile at X=" & x & ", Y=" & y
               End If

               terrainID = CLng(raw)

               ' Confirm the terrain type exists in our definitions.
               If Not mTerrainDefs.Exists(terrainID) Then
                  Err.Raise vbObjectError + 123, , _
                         "Unknown terrain " & terrainID & _
                           " at X=" & x & ", Y=" & y
               End If

               ' Remember the terrain and its display character.
               ids(y, x) = terrainID
               props = mTerrainDefs(terrainID)
               symbols(y, x) = props(0)

          Next x
     Next y

     ' Clear the full display area, including any unused cells.
     With displayArea
          .ClearContents
          .Interior.Color = RGB(23, 35, 43)
          .Font.Color = RGB(221, 230, 232)
          .Font.Bold = False
     End With

     ' Write all terrain glyphs in a single worksheet operation.
     displayArea.Cells(1, 1).Resize( _
          mMapHeight, mMapWidth).Value2 = symbols

     ' Apply formatting in consecutive horizontal terrain runs.
     For y = 1 To mMapHeight

          x = 1

          Do While x <= mMapWidth

               terrainID = ids(y, x)
               firstX = x

               ' Find the end of this run of identical terrain.
               Do While x < mMapWidth
                    If ids(y, x + 1) <> terrainID Then Exit Do
                    x = x + 1
               Loop

               props = mTerrainDefs(terrainID)

               ' Identify the matching segment of worksheet cells.
               Set run = displayArea.Cells(y, firstX).Resize( _
                    1, x - firstX + 1)

               ' Apply the terrain's background and foreground colours.
               With run
                    .Interior.Color = props(1)
                    .Font.Color = props(2)
               End With

               ' Advance to the next unprocessed tile.
               x = x + 1

          Loop

     Next y

     ' Display the current location's name and size.
     ws.Range("B2").Value2 = UCase$(mMapTitle)
     ws.Range(STATUS_CELL_ADDRESS).Value2 = _
          mMapWidth & " x " & mMapHeight & " tiles"

     ws.Range(MESSAGE_CELL_ADDRESS).Value2 = _
          "Map loaded from tblMaps."

End Sub

 

Although the procedure is relatively long, the changes made during this milestone are limited. The most important one is that all worksheet positioning now starts with displayArea, obtained from MapArea(). We retain our original validation and horizontal-run formatting, both of which were introduced to make the map renderer more reliable and efficient.

 

The size validation in LoadMap should also refer to the same shared range:

' Ensure the requested map fits inside the visible display area.
If mMapWidth < 1 Or mMapHeight < 1 Or _
     mMapWidth > MapArea().Columns.Count Or _
     mMapHeight > MapArea().Rows.Count Then

     Err.Raise vbObjectError + 111, , _
          "Map dimensions exceed the available display area."

End If

 

Once these modifications are in place, the old constants describing the first screen row, first screen column and maximum display dimensions can be removed from modMapEngine, provided no remaining procedures refer to them.

 

At this point, we should compile the VBA project and briefly test our existing map and movement controls. They should behave exactly as before. We've changed how the renderer determines the worksheet location, not how it interprets terrain or moves the player.

 

Defining our first character types

 

Now we can turn our attention to the creatures themselves. We'll continue using the GameData worksheet as the central repository of game definitions. Rather than defining every goblin's characteristics directly in VBA, we'll create an Excel Table containing reusable character types.

 

Create a structured table named tblActorTypes with the columns TypeID, Name, Glyph, MaxHP, Damage and Foreground. For the initial test, enter three character definitions. The first has TypeID PLAYER, the name Adventurer, the symbol @, maximum HP of 24, damage of 4 and foreground colour #72D8F4. The second has TypeID GOBLIN, the name Goblin, the symbol g, maximum HP of 8, damage of 2 and foreground colour #E58B72. The third has TypeID WOLF, the name Wolf, the symbol w, maximum HP of 11, damage of 3 and foreground colour #C3B6A0.

 

These statistics aren't intended to represent our final game balance. They're simply different enough to make the initial character system easy to test. The TypeID field provides a stable reference to each definition. We can change a creature's display name or characteristics later without having to change the identifiers used by the rest of the game. The Foreground field uses the same hexadecimal colour format we've already adopted for terrain. We'll reuse our existing ColorFromHex function to convert these values into colours Excel can display. To allow the actor module to call that function, change its declaration in modMapEngine from Private Function ColorFromHex to Public Function ColorFromHex. The implementation itself does not need to change.

 

Next, create another table on GameData named tblMonsterSpawns, containing MapID, InstanceID, TypeID, X and Y. We'll use three records for our starting location. The first is a goblin with instance ID GOB01, type GOBLIN, on map FORT01 at (17, 8). The second is another goblin, GOB02, also of type GOBLIN, at (20, 8). The third is a wolf, WOLF01, of type WOLF, at (18, 5). These positions place our test monsters on walkable terrain in Old Fort Approach.

 

It's important that tblMonsterSpawns does not contain the monsters' current HP. The table only defines where those creatures initially appear when a new game is created. Their actual current HP and position will belong to the individual character instances created by VBA. This distinction will become essential when monsters begin moving, taking damage or dying. We don't want a creature's changing gameplay state to overwrite the original definition of where it should spawn in a new game.

 

Our first VBA class

 

Until this point, we've primarily used standard VBA modules. They're excellent for organising procedures, but they're not ideal for representing several independent objects that all share the same structure. For that, we'll introduce our first class module.

 

In the VBA editor, select Insert → Class Module and change its name in the Properties window to clsActor. Leave the class's Instancing property at its default value of 1 - Private. Our characters only need to be accessed within this workbook's VBA project, so there's no reason to expose the class to other projects.

 

Enter the following code:

Option Explicit

' Represents one individual character in the game world.
' Every instance stores its own identity, position and statistics.

' Identity and character type.
Public InstanceID As String
Public TypeID As String
Public ActorName As String

' Appearance on the map.
Public Glyph As String
Public GlyphColor As Long

' Coordinates within the active location.
Public X As Long
Public Y As Long

' Basic statistics for later combat mechanics.
Public HP As Long
Public MaxHP As Long
Public Damage As Long

 

A class defines the structure of an object. When we create a new instance of clsActor, VBA allocates a separate set of these fields to that particular character. This means a goblin at (17, 8) and another at (20, 8) can both begin with eight HP while remaining independent objects. The TypeID connects a creature to its reusable definition, while InstanceID distinguishes one individual from another.

 

We're using public fields at this stage to keep the class straightforward. Later, we might introduce private fields and property procedures to validate changes to HP, positions and other statistics. For now, we primarily need a reliable way to store character data.

 

An important design choice is that our class does not attempt to perform every action a character might eventually take. We're not giving it combat resolution, AI, inventory management or rendering responsibilities yet. Those systems have not been designed, and introducing them now would make this milestone unnecessarily complicated. Instead, clsActor is a deliberately simple representation of an individual character. The module we're about to create will manage the active collection of those objects.

 

Managing active characters with modActors

 

Our new class defines what an individual actor looks like, but we still need something responsible for creating and managing those actors. We'll introduce a standard module named modActors. This module will maintain three important pieces of information in memory: the first is a dictionary of shared actor definitions, loaded from tblActorTypes, the second is a reference to the active player character, and the third is a collection containing the individual monsters currently present in the active location.

 

We'll keep these variables private to the module. Other parts of the game will access actors through procedures and functions provided by modActors, rather than directly modifying the collection.

 

Loading definitions and constructing individual actors

 

Create modActors and start with the following code:

Option Explicit

' Shared character templates, indexed by TypeID.
Private mActorDefinitions As Object

' Active character instances.
Private mPlayer As clsActor
Private mMonsters As Collection

Private Sub LoadActorDefinitions()

     Dim tbl As ListObject
     Dim data As Variant
     Dim r As Long
     Dim typeID As String

     Dim idCol As Long
     Dim nameCol As Long
     Dim glyphCol As Long
     Dim hpCol As Long
     Dim damageCol As Long
     Dim colorCol As Long

     ' Retrieve the actor definitions from GameData.
     Set tbl = GameDataSheet().ListObjects("tblActorTypes")

     ' Locate fields by their names rather than fixed positions.
     idCol = tbl.ListColumns("TypeID").Index
     nameCol = tbl.ListColumns("Name").Index
     glyphCol = tbl.ListColumns("Glyph").Index
     hpCol = tbl.ListColumns("MaxHP").Index
     damageCol = tbl.ListColumns("Damage").Index
     colorCol = tbl.ListColumns("Foreground").Index

     ' Read the definition table into memory.
     data = tbl.DataBodyRange.Value2
     Set mActorDefinitions = CreateObject("Scripting.Dictionary")

     For r = 1 To UBound(data, 1)

          typeID = CStr(data(r, idCol))

          ' Store the common properties of each actor type.
          mActorDefinitions.Add typeID, Array( _
               CStr(data(r, nameCol)), _
               CStr(data(r, glyphCol)), _
               CLng(data(r, hpCol)), _
               CLng(data(r, damageCol)), _
               ColorFromHex(CStr(data(r, colorCol))))

     Next r

End Sub

Private Function CreateActor( _
     ByVal instanceID As String, _
     ByVal typeID As String, _
     ByVal x As Long, _
     ByVal y As Long) As clsActor

     Dim definition As Variant
     Dim actor As clsActor

     ' Look up the template belonging to this actor type.
     definition = mActorDefinitions(typeID)

     ' Create an independent character object.
     Set actor = New clsActor

     With actor

          ' Assign the character's identity and appearance.
          .InstanceID = instanceID
          .TypeID = typeID
          .ActorName = CStr(definition(0))
          .Glyph = CStr(definition(1))

          ' Initialise health and damage from the shared template.
          .MaxHP = CLng(definition(2))
          .HP = .MaxHP
          .Damage = CLng(definition(3))
          .GlyphColor = CLng(definition(4))

          ' Give this individual its starting coordinates.
          .X = x
          .Y = y

     End With

     Set CreateActor = actor

End Function

 

The first procedure should look familiar - it's similar to our earlier terrain-definition loader. We locate the required Excel Table by name, identify its columns by heading and load the contents into a VBA array. We then populate a Scripting.Dictionary, using each actor type's ID as its key. Using the column names rather than fixed numerical positions makes the code less sensitive to someone rearranging the columns of tblActorTypes. The dictionary stores the base characteristics of each type in an array.

 

For example, the definition belonging to GOBLIN contains the name, glyph, maximum HP, damage and foreground colour of a goblin. The CreateActor function then uses that definition to construct an individual object.

 

The crucial statement is Set actor = New clsActor. This creates a fresh instance of our class. We then populate its fields using the values from the shared definition and the identity and coordinates supplied by the caller.

 

Notice that the character's current HP is initially set equal to its maximum HP. Those two properties are then independent. Once combat is implemented, an individual character can lose health without changing the maximum health defined for its type.

 

This also demonstrates another useful form of encapsulation. The rest of the game won't need to know the order in which values are stored inside our definition arrays. It will ask modActors to create or retrieve characters.

 

Initialising the player and loading monster spawns

 

Next, we'll add the procedures that create an active game session. The player is constructed using the same CreateActor function as the monsters. The only difference is that the player's starting position is supplied directly by the game startup sequence, while monsters are loaded from tblMonsterSpawns.

 

Add the following code beneath the existing procedures in modActors:

Public Sub ResetActorSession()

     ' Discard all character state belonging to the previous session.
     Set mPlayer = Nothing
     Set mMonsters = Nothing
     Set mActorDefinitions = Nothing

End Sub

Public Sub InitialiseActorSession( _
     ByVal mapID As String, _
     ByVal startX As Long, _
     ByVal startY As Long)

     ' Begin with a fresh collection of character objects.
     ResetActorSession
     LoadActorDefinitions

     ' Make sure the player starts on passable terrain.
     If Not IsTileWalkable(startX, startY) Then
          Err.Raise vbObjectError + 201, , _
               "The player's starting tile is impassable."
     End If

     ' Create the player from the PLAYER definition.
     Set mPlayer = CreateActor( _
          "PLAYER", "PLAYER", startX, startY)

     ' Prepare the collection of active monsters.
     Set mMonsters = New Collection

     ' Create the monsters assigned to this starting map.
     LoadMonsterSpawns mapID

     ' Draw the creatures over the underlying terrain.
     DrawAllActors

End Sub

Private Sub LoadMonsterSpawns(ByVal mapID As String)

     Dim tbl As ListObject
     Dim data As Variant
     Dim r As Long
     Dim x As Long
     Dim y As Long

     Dim mapCol As Long
     Dim instanceCol As Long
     Dim typeCol As Long
     Dim xCol As Long
     Dim yCol As Long

     Dim existingMonster As clsActor

     ' Retrieve the spawn definitions from GameData.
     Set tbl = GameDataSheet().ListObjects("tblMonsterSpawns")

     ' Identify the required columns by heading.
     mapCol = tbl.ListColumns("MapID").Index
     instanceCol = tbl.ListColumns("InstanceID").Index
     typeCol = tbl.ListColumns("TypeID").Index
     xCol = tbl.ListColumns("X").Index
     yCol = tbl.ListColumns("Y").Index

     data = tbl.DataBodyRange.Value2

     For r = 1 To UBound(data, 1)

          ' Ignore spawn entries belonging to other maps.
          If StrComp(CStr(data(r, mapCol)), mapID, _
               vbTextCompare) = 0 Then

               x = CLng(data(r, xCol))
               y = CLng(data(r, yCol))

               ' Reject impassable spawn positions.
               If Not IsTileWalkable(x, y) Then
                    Err.Raise vbObjectError + 202, , _
                             "A monster has an impassable spawn position."
               End If

               ' Prevent a monster spawning on the player.
               If x = mPlayer.X And y = mPlayer.Y Then
                    Err.Raise vbObjectError + 203, , _
                         "A monster cannot spawn on the player."
               End If

               ' Prevent two monsters occupying the same tile.
               Set existingMonster = MonsterAt(x, y)

               If Not existingMonster Is Nothing Then
                    Err.Raise vbObjectError + 204, , _
                         "Two monsters have the same spawn position."
               End If

               ' Create a new monster from its reusable definition.
               mMonsters.Add CreateActor( _
                   CStr(data(r, instanceCol)), _
                   CStr(data(r, typeCol)), x, y)

          End If

     Next r

End Sub

 

The InitialiseActorSession procedure coordinates the creation of our active characters. It starts by clearing any previous actor state and loading the current definitions. It then checks that the player's requested starting position is walkable before creating the player object.

 

We next initialise a Collection for monsters and call LoadMonsterSpawns. This procedure reads our spawn table, filters its records by MapID and creates an actor for each matching entry. It also checks for invalid placements. We don't want to spawn a goblin inside a wall, place a monster on top of the player or assign two monsters to the same tile.

 

These checks are particularly useful in a data-driven game because an incorrect value in an Excel Table should not silently produce an inconsistent world. The monsters are stored in a collection, rather than in individual variables such as goblin1, goblin2 and wolf1. That means our game can eventually support an arbitrary number of creatures without requiring new VBA variables for each one. The collection contains references to individual clsActor objects, so every monster retains its own position and current statistics.

 

For the moment, this is only the active location's monster collection. When we implement multiple active and inactive map states, we'll expand the way character instances are preserved across locations.

 

Finding monsters and drawing characters

 

The last part of modActors deals with locating creatures and displaying them on the map. In the first two instalments, our drawing model was very simple: the map engine rendered terrain, then the player module placed @ over the appropriate cell. When the player moved away, RedrawTerrainTile restored the underlying terrain. Now we have two kinds of character occupying map tiles, and eventually there will be items, traps and other objects too. We therefore need to distinguish the actual world state from the visual representation displayed in a worksheet cell.

 

The terrain remains stored in our map array. Monsters exist as separate actor objects. The visible cell simply displays whichever object has the highest rendering priority. For this milestone, monsters are drawn over terrain and the player is drawn last.

 

Add the remaining code to modActors:

Public Function PlayerActor() As clsActor

     ' Return the current player object, if one exists.
     Set PlayerActor = mPlayer

End Function

Public Function MonsterAt( _
     ByVal x As Long, _
     ByVal y As Long) As clsActor

     Dim actor As clsActor

     ' An uninitialised collection contains no monsters.
     If mMonsters Is Nothing Then Exit Function

     ' Find a living monster at the requested coordinates.
     For Each actor In mMonsters

          If actor.X = x And actor.Y = y And actor.HP > 0 Then

               Set MonsterAt = actor
               Exit Function

          End If

     Next actor

End Function

Private Sub DrawActor(ByVal actor As clsActor)

     Dim target As Range

     ' Translate the actor's position into a display cell.
     Set target = GetScreenCell(actor.X, actor.Y)

     ' Place the actor's glyph over the terrain.
     With target
          .Value2 = actor.Glyph
          .Font.Color = actor.GlyphColor
          .Font.Bold = True
     End With

End Sub

Public Sub DrawAllActors()

     Dim actor As clsActor

     ' Display all living monsters first.
     If Not mMonsters Is Nothing Then

          For Each actor In mMonsters
               If actor.HP > 0 Then DrawActor actor
          Next actor

     End If

     ' Draw the player last, above any other actors.
     If Not mPlayer Is Nothing Then
          If mPlayer.HP > 0 Then DrawActor mPlayer
     End If

End Sub

Public Sub RedrawWorldTile( _
     ByVal x As Long, _
     ByVal y As Long)

     Dim monster As clsActor

     ' Start by restoring the underlying terrain.
     RedrawTerrainTile x, y

     ' Overlay a living monster if one occupies this tile.
     Set monster = MonsterAt(x, y)

     If Not monster Is Nothing Then
          DrawActor monster
     End If

     ' The player takes the highest rendering priority.
     If Not mPlayer Is Nothing Then

          If mPlayer.X = x And mPlayer.Y = y Then
               If mPlayer.HP > 0 Then DrawActor mPlayer
          End If

     End If

End Sub

Public Sub InspectActors()

     Dim actor As clsActor

     ' Print character information to the Immediate Window.
     If mPlayer Is Nothing Then
          Debug.Print "No active character session."
          Exit Sub
     End If

     Debug.Print "PLAYER", mPlayer.ActorName, _
          mPlayer.X, mPlayer.Y, _
          mPlayer.HP, mPlayer.MaxHP, mPlayer.Damage

     For Each actor In mMonsters

          Debug.Print actor.InstanceID, actor.ActorName, _
               actor.X, actor.Y, _
               actor.HP, actor.MaxHP, actor.Damage

     Next actor

End Sub

 

The PlayerActor and MonsterAt functions give the rest of the game controlled ways to retrieve characters. MonsterAt searches the active monster collection for a living creature at a particular coordinate. It returns that actor if one exists; otherwise, it returns Nothing. This will be useful for several systems. Player movement can check whether a destination is occupied; monster AI will eventually need to make similar decisions. Combat will need to identify the target of an attack. For now, the search is a simple loop through the collection. That is more than adequate for our small test map. If we later introduce hundreds of creatures on a much larger map, we can consider more efficient indexing without changing the general idea.

 

The rendering procedures are equally important. DrawActor is responsible for placing a single character's symbol and foreground colour into its corresponding worksheet cell. DrawAllActors iterates through the active monster collection and draws each living creature, then draws the player. RedrawWorldTile provides a more general replacement for the terrain-only restoration technique we used previously. It first restores the terrain, then overlays a living monster if one occupies the tile, and finally overlays the player if appropriate.

 

This is the beginning of our layered world renderer. Later, when we introduce items and traps, we'll extend the drawing order without needing to make terrain itself responsible for storing those objects.

 

The InspectActors procedure is included to help us verify the implementation. It prints each active character's identity, coordinates, HP, maximum HP and damage in the VBA Immediate Window. This gives us a way to inspect the actual character objects independently of their appearance on the worksheet.

 

Refactoring player movement to use the new actor system

 

We now have two different systems that could potentially store the player's position. Our existing modPlayer maintains private variables for the player's coordinates and readiness state, while our new clsActor object also contains coordinates and health information - but we shouldn't retain both. If two separate systems maintain the same information, sooner or later they'll disagree. One module might believe the player is at (10, 8) while another thinks the player has moved to (11, 8). We therefore need to establish a single source of truth.

 

For now, the active player object managed by modActors will own the player's identity, coordinates and statistics. modPlayer will remain responsible for processing movement commands, validating requested moves and generating messages, but it will retrieve the character object through PlayerActor(). Remove the original private player-coordinate and readiness declarations from modPlayer. We can also remove the old DrawPlayer procedure, because character drawing is now handled by modActors.

 

Replace the existing InitialisePlayer, ResetPlayer, UpdatePlayerStatus and MovePlayer procedures with the following code. The movement-direction wrappers introduced in Part 2 can remain unchanged.

Public Sub InitialisePlayer( _
     ByVal startX As Long, _
     ByVal startY As Long)

     ' Create the player and monsters for the starting location.
     InitialiseActorSession START_MAP_ID, startX, startY

     UpdatePlayerStatus "The adventure begins."

End Sub

Public Sub ResetPlayer()

     ' Clear the active character session.
     ResetActorSession

End Sub

Private Sub UpdatePlayerStatus(ByVal message As String)

     Dim player As clsActor
     Dim ws As Worksheet

     ' Retrieve the current player object.
     Set player = PlayerActor()

     ' Do not update gameplay information when no player exists.
     If player Is Nothing Then Exit Sub

     Set ws = GameScreen()

     ' Show health, damage and the current position.
     ws.Range(STATUS_CELL_ADDRESS).Value2 = _
          "HP " & player.HP & "/" & player.MaxHP & _
          " D" & player.Damage & _
          " (" & player.X & "," & player.Y & ")"

     ' Show the latest movement or interaction message.
     ws.Range(MESSAGE_CELL_ADDRESS).Value2 = message

End Sub

Private Sub MovePlayer( _
     ByVal deltaX As Long, _
     ByVal deltaY As Long)

     Dim player As clsActor
     Dim monster As clsActor

     Dim oldX As Long
     Dim oldY As Long
     Dim newX As Long
     Dim newY As Long

     ' Retrieve the player before attempting movement.
     Set player = PlayerActor()

     If player Is Nothing Then Exit Sub

     ' Calculate the requested destination.
     newX = player.X + deltaX
     newY = player.Y + deltaY

     ' Check destination terrain before other movement rules.
     If Not IsTileWalkable(newX, newY) Then

          UpdatePlayerStatus GetTerrainMessage(newX, newY, True)
          Exit Sub

     End If

     ' Prevent diagonal movement between two blocked neighbours.
     If deltaX <> 0 And deltaY <> 0 Then

          If Not IsTileWalkable(player.X + deltaX, player.Y) _
               And Not IsTileWalkable(player.X, player.Y + deltaY) Then

               UpdatePlayerStatus "You cannot squeeze through that gap."
               Exit Sub

          End If

     End If

     ' Prevent the player moving onto an occupied monster tile.
     Set monster = MonsterAt(newX, newY)

     If Not monster Is Nothing Then

          ' Combat will eventually replace this response.
          UpdatePlayerStatus "A " & LCase$(monster.ActorName) & _
               " blocks your way."

          Exit Sub

     End If

     ' Remember the previous position before moving the player.
     oldX = player.X
     oldY = player.Y

     ' Update the actual coordinates stored in the actor object.
     player.X = newX
     player.Y = newY

     ' Rebuild both affected tiles from terrain and actor state.
     RedrawWorldTile oldX, oldY
     RedrawWorldTile newX, newY

     ' Display any message associated with entering the new tile.
     UpdatePlayerStatus GetTerrainMessage(newX, newY, False)

End Sub

Public Sub WaitTurn()

     Dim player As clsActor

     Set player = PlayerActor()

     If player Is Nothing Then Exit Sub

     ' Actual turn scheduling will be introduced later.
     UpdatePlayerStatus "You wait."

End Sub

 

The basic movement algorithm should be familiar from Part 2. We still calculate a destination using the requested X and Y offsets and we still check the destination terrain and prevent diagonal squeezing between two blocked neighbouring cells. The major difference is that the current coordinates now come from the player object rather than private variables in modPlayer.

 

We've also introduced a new occupancy check. After confirming that the destination terrain is passable, we call MonsterAt(newX, newY) to determine whether a living monster occupies that tile. If one does, movement is cancelled and the player receives a message such as "A goblin blocks your way."

 

This is deliberately temporary behaviour. In many traditional roguelikes, attempting to move into an enemy's occupied tile initiates a melee attack. We'll eventually implement that, but we don't yet have a combat system capable of resolving the action. For now, preventing the player from overlapping a monster allows us to test the occupancy rules without introducing unfinished combat mechanics.

 

When movement succeeds, we update the player's actual coordinates and call RedrawWorldTile twice: once for the old position and once for the new one. This ensures that both cells are reconstructed from the underlying terrain and actor state.

 

The status display has also changed: instead of showing only coordinates, it now includes the player's current HP, maximum HP and damage. For our initial adventurer, it should display values equivalent to HP 24/24 D4 (10,8) at the starting position.

 

The eight public directional procedures that connect to our navigation buttons don't need to change. They already call MovePlayer with the appropriate offsets. The Wait procedure remains a placeholder. We're not going to introduce artificial time advancement here, because the next milestone will establish a proper turn scheduler that can accommodate multiple actors and different action speeds.

 

Connecting character creation to the game startup sequence

 

We now need to make sure the actor session is initialised when a new game begins. Fortunately, the work we did in Part 3 means that very little needs to change. The StartNewGame procedure in modGame already calls ShowMap, which handles the starting location. Previously, ShowMap loaded the terrain, rendered the map and called InitialisePlayer to place the @ symbol. Our new InitialisePlayer does something more substantial. It calls InitialiseActorSession, which creates both the player and all monsters belonging to the starting map.

 

The relevant part of ShowMap should now follow this sequence:

' Load the definitions and initial terrain.
LoadTerrainDefinitions
LoadMap START_MAP_ID

' Render the terrain before placing creatures.
DrawMap

' Initialise the player and the monsters in the starting location.
InitialisePlayer START_PLAYER_X, START_PLAYER_Y

' Bring the game screen into view.
GameScreen().Activate

 

Keep any existing procedure structure surrounding these instructions; we're only changing the references and initialisation sequence.

 

The order is important. First, we load and draw the terrain. Only after the underlying map is available do we create the character instances and draw them above it. We don't need to change the automated navigation controls or the main menu behaviour established in Part 3. When the workbook opens, Auto_Open still displays the main menu. When New Game is clicked, ShowMap prepares the location and the actor session, and the navigation controls become visible. Returning to the main menu calls ResetPlayer, which now clears the active actor session through ResetActorSession.

 

This also demonstrates why we separated application startup from gameplay in the previous milestone. We can substantially expand what happens during New Game without redesigning the interface responsible for initiating it.

 

Testing our first populated location

 

With the new tables, class and modules in place, compile the project using Debug → Compile VBAProject, then save the workbook, close Excel and reopen it with macros enabled. The main menu should appear as before, with the New Game button centered in the map display area and the navigation controls hidden.

 

Click New Game. Old Fort Approach should appear, and our adventurer should be located at (10, 8). The player will now have health and damage statistics displayed in the status area. You should also see two goblins represented by g symbols and one wolf represented by w. Their positions should correspond to the coordinates entered in tblMonsterSpawns. The monsters won't move or attack, regardless of how many times the player moves or waits. That's expected at this stage.

 

Try moving the player towards one of the goblins. If you attempt to enter its tile, the move should be rejected with the message "A goblin blocks your way."  The player should remain in the previous cell, and the goblin should remain visible.

 

Movement into ordinary walkable terrain should still succeed. Walls, trees and other obstacles should retain the contextual messages introduced earlier, and blocked diagonal movement should continue to work correctly. It's particularly important that the existing terrain doesn't become corrupted when the character moves away from a cell. The revised rendering logic should restore the underlying terrain correctly and keep stationary monsters visible.

 

To inspect the actual objects rather than just their on-screen symbols, open the VBA Immediate Window using Ctrl+G and run InspectActors. The output should contain one adventurer, two goblins and one wolf, each with its own identifier, coordinates and statistics. For our initial data, the player should begin with 24 HP and four damage, both goblins with eight HP and two damage, and the wolf with eleven HP and three damage.

 

Although the two goblins share the same definition, they're stored as separate clsActor instances. This will matter once we begin changing individual HP values during combat.

 

Next, return to the main menu and start another game. The same starting creatures should appear, without additional duplicate monsters from the previous session.

 

Finally, close and reopen the workbook to confirm that the automatic startup system still functions correctly after our refactoring.

 

These tests exercise several different parts of the application at once: our data tables, class construction, actor collections, map rendering, occupancy checks, player movement and startup sequence. If all of them work, we have a reliable foundation for introducing independent monster actions.

 

Why we're not introducing combat yet

 

At this point, we already have many of the values needed for basic combat. The adventurer has HP and damage, and so do the monsters. We can locate a creature on the map and distinguish its instance from other creatures of the same type. It might therefore be tempting to implement attacking immediately.

 

However, a roguelike's combat system is closely connected to its turn system. When the player attacks, how much time should that action consume? Does the monster get an opportunity to retaliate? Can a quick monster move or attack more frequently than a slow one? What happens when the player is affected by haste or slowing? These are not questions we want to answer through unrelated counters placed in different movement and attack procedures. Instead, we'll introduce a central turn scheduler in the next milestone. That scheduler will determine which actor can perform an action, how much game time the action consumes and when another actor becomes eligible to act.

 

Our current actor structure already gives us a useful starting point: every participant in the game has its own instance and state. We haven't yet introduced a speed property because we want to establish exactly how speed will interact with our scheduling model rather than adding a statistic whose meaning hasn't been defined. Once time and actions work, monsters can begin moving independently of the player. We can then introduce attack resolution and damage with a consistent understanding of how gameplay advances.

 

This development order also prepares us for eventual field-of-vision and fog-of-war mechanics. Testing visibility will be much more meaningful when the world contains independent creatures that can move, items that can be collected or dropped, and objects whose state can change outside the player's sight.

 

Looking ahead: from stationary monsters to an active world

 

Our RPG now contains something it didn't have before: independent entities. A goblin is no longer merely a letter that we might draw on the worksheet. It is an object with its own identity, location and statistics, created from a reusable definition stored in Excel. The same underlying structure represents our player character.

 

We've also established a clearer division of responsibilities. modGlobals provides shared configuration and worksheet access. modMapEngine remains responsible for terrain. modActors manages character definitions, instances and rendering. modPlayer handles the player's movement commands.  modInterface and modGame continue managing the user interface and application startup. Our map is still fully visible, and the monsters are still stationary, but the engine is becoming considerably more capable.

 

We've also laid the groundwork for future inventory and equipment systems. When we introduce items, they'll follow a similar principle: shared item definitions will describe the type of object, while individual item instances will represent actual swords, potions and other possessions existing in the world. Those objects can then be placed on terrain, carried by characters or assigned to equipment slots without requiring the terrain itself to store their properties. For now, however, our little band of monsters will remain patiently in place.

 

In the next instalment, we'll begin changing that by introducing a proper turn system. Our objective will be to give the game its own concept of time, allowing the player and monsters to perform actions according to a central schedule. We'll also lay the foundation for different actor speeds, haste and slowing, rather than assuming every creature must always perform exactly one action whenever the player moves.

 

Once that system is established, Old Fort Approach will no longer be populated by creatures that merely stand around waiting to be noticed. They'll finally have something to do. And perhaps our adventurer will begin to appreciate just how peaceful the fort was before we started adding goblins.

 

This article is Part 4 of the Building an RPG in Excel series. The accompanying VBA modules and development workbook allow readers to follow the implementation themselves, and they can be downloaded here.

20 November 2026

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet

Cat On A Spreadsheet