SpatialTables
Creates the spatial analysis ListObjects on the "__spatial_tables" worksheet at build time. Add takes one cross-table and builds, for each of its row variables, either four administrative tables (adm1 to adm4) or one health facility table. Entries filed along the way leave through HasCheckings and CheckingValues.
THE CONTRACT WITH LLSPATIAL
This class writes what LLSpatial reads in the generated linelist, and the two never reference each other: the contract is the set of names below, agreed by string convention across a workbook boundary. Change a name here and change it in LLSpatial in the same commit.
listofgeovars one row per spatial variable
spatial_
Every row of listofgeovars resolves to a real ListObject, because LLSpatial walks that list and asks for the table by name. So a variable is registered after its tables are built.
WHAT A FACILITY TABLE HOLDS
A facility table is two columns: the lookup key and the value. The population and the attack rate belong to an administrative table alone. The geobase gives a population per administrative level, adm1_pop to adm4_pop, and gives none for a facility.
LLSpatial.Update resizes a spatial table as it refreshes it, and reads the width off the table itself.
Depends on: CrossTable, TableSpecs, Formulas, FormulaBuilder, Checking
Version: 2026-08-12
Instantiation
Create #
create
Create a SpatialTables instance from a cross-table
Signature:
Public Function Create(ByVal tabl As CrossTable) As SpatialTables
Factory method on the predeclared instance. Validates that the cross-table is given and that the "__spatial_tables" worksheet exists in the same workbook, then hands the resolved worksheet to the instance so it is looked up once.
Parameters:
tabl: CrossTable. The cross-table whose row variable defines the spatial tables to create.
Returns: SpatialTables. A fully initialised spatial tables builder.
Throws:
- ProjectError.ObjectNotInitialized When tabl is Nothing.
- ProjectError.ElementNotFound When the spatial worksheet is missing.
Depends on:
- CrossTable
- TableSpecs
Seal #
seal
Seal the instance against further setup writes.
Signature:
Public Sub Seal()
Table #
table
Cross-table bound to this builder
Signature:
Public Property Get Table() As CrossTable
Returns the CrossTable instance whose row variable and specifications drive the spatial table creation in Add.
Returns: CrossTable. The bound cross-table.
Table #
table-set
Assign the cross-table reference
Signature:
Public Property Set Table(ByVal tabl As CrossTable)
Parameters:
tabl: CrossTable. The cross-table to bind.
SpatialSheet #
spatial-sheet-set
Assign the spatial worksheet the factory resolved
Signature:
Public Property Set SpatialSheet(ByVal sh As Worksheet)
Parameters:
sh: Worksheet. The "__spatial_tables" worksheet.
Add
Add #
add
Build the spatial tables of the cross-table row variable
Signature:
Public Sub Add(ByVal formObject As Formulas)
A geographic variable gets four tables, one per administrative level, each with the value formula, the population lookup and the attack rate. A facility variable gets one table with the value formula. Every table is two rows and four columns, and they are laid out left to right with a gap between them.
The variable is registered in listofgeovars after its tables are built. It used to be registered first, so a failure inside the loop left a name in the registry with no table behind it and LLSpatial walked to a table that was not there.
Parameters:
formObject: Formulas. The summary function the value column is built from.
Checkings
HasCheckings #
has-checkings
Whether this instance has anything to report
Signature:
Public Property Get HasCheckings() As Boolean
Returns: Boolean. True once one entry has been filed.
CheckingValues #
checking-values
The entries filed while the spatial tables were built
Signature:
Public Property Get CheckingValues() As Checking
Returns: Checking. The entries, or Nothing when none were filed.
Internal members (not exported)
Instantiation
GuardNotSealed #
guard-not-sealed
Guard a setup setter against post-creation writes.
Signature:
Private Sub GuardNotSealed(ByVal propName As String)
Parameters:
propName: String. The property name for the error message.
Worksheet
Wksh #
wksh
The spatial worksheet
Signature:
Private Property Get Wksh() As Worksheet
The factory resolves the worksheet and hands it over, so this is a read of what the instance already holds.
Returns: Worksheet. The spatial analysis worksheet.
ID #
id
The identifier of the table these spatial tables belong to
Signature:
Private Property Get ID() As String
Returns: String. The table identifier.
Naming Conventions
CurrentHeaderName #
current-header-name
Build the header name of a spatial table
Signature:
Private Property Get CurrentHeaderName(ByVal varName As String, _
ByVal adminName As String) As String
Helpers for building the spatial table and header names. LLSpatial builds the same two strings from the same pieces.
The shape is "tabl_
Parameters:
varName: String. The spatial variable name.adminName: String. The level identifier, such as "adm1" or "hf".
Returns: String. The header name.
CurrentLoName #
current-lo-name
Build the ListObject name of a spatial table
Signature:
Private Property Get CurrentLoName(ByVal varName As String, _
ByVal adminName As String) As String
The shape is "spatial_
Parameters:
varName: String. The spatial variable name.adminName: String. The level identifier.
Returns: String. The ListObject name.
LevelCount #
level-count
How many tables one spatial variable takes
Signature:
Private Property Get LevelCount(ByVal spatType As String) As Long
A facility variable takes one table and a geographic one takes four. An empty spatial type answers four, which is what a table whose spatial type cell was never filled produces.
Parameters:
spatType: String. The spatial type, "geo" or "hf".
Returns: Long. The number of tables to build.
TableWidth #
table-width
How wide one table of a spatial variable is
Signature:
Private Property Get TableWidth(ByVal spatType As String) As Long
An administrative table carries the value, the population and the attack rate. A facility table carries the value alone, because a health facility has no population to divide by.
Parameters:
spatType: String. The spatial type, "geo" or "hf".
Returns: Long. The number of columns.
LevelName #
level-name
The level identifier of one table of a spatial variable
Signature:
Private Property Get LevelName(ByVal spatType As String, ByVal counter As Long) As String
Parameters:
spatType: String. The spatial type, "geo" or "hf".counter: Long. The 1-based table number.
Returns: String. "hf", or "adm" followed by the level number.
Existence Checks
Exists #
exists
Whether the spatial tables of a variable are already built
Signature:
Private Function Exists(ByVal varName As String, ByVal spatType As String) As Boolean
Asks for the tables the spatial type actually produces. It used to probe the four administrative levels whatever the type, so a facility variable answered False every time: the guard never fired, the variable was registered twice and ListObjects.Add raised 1004 on the second pass over the same workbook.
Parameters:
varName: String. The spatial variable name.spatType: String. The spatial type, "geo" or "hf".
Returns: Boolean. True when every table the type needs is present.
LoExists #
lo-exists
Whether a ListObject is on the spatial sheet
Signature:
Private Function LoExists(ByVal loName As String) As Boolean
Parameters:
loName: String. The ListObject name to look for.
Returns: Boolean. True when the ListObject exists.
Column Helpers
LastColumn #
last-column
The column the next spatial table starts in
Signature:
Private Property Get LastColumn() As Long
Reads the last used column of row 1 and leaves a gap after it. A sheet carrying only the registry answers with the first table column, so the columns between the registry and the first table stay empty.
Returns: Long. The 1-based column index.
Formula Helpers
GeoPopulationFunction #
geo-population-function
Build the population lookup of one administrative level
Signature:
Private Function GeoPopulationFunction(ByVal adminName As String, _
ByVal rangeAddress As String) As String
An INDEX and MATCH over the geobase table of the level, wrapped in ISERROR so a unit the geobase does not carry reads 0. The population column index comes from the level number: T_ADM1 holds it in column 2, T_ADM2 in column 3, and so on.
Parameters:
adminName: String. The level identifier, such as "adm1".rangeAddress: String. The cell address MATCH looks the unit up by.
Returns: String. The formula text with no leading "=", or an empty string when the name carries no level number.
Remarks:
- A NAME THAT IS NOT adm
USED TO GIVE COLUMN ZERO. The level number was read with CLng under On Error Resume Next, so anything else left the index at 0 -- and INDEX(table, row, 0) is a whole-row reference rather than an error, so ISERROR answered False and the cell took the first column of the row. The level number is checked before it is used now, and a name without one files a warning and leaves the population cell empty.
Builder #
builder
The formula builder this class writes through
Signature:
Private Property Get Builder() As FormulaBuilder
Holds the criteria of the formula being built, asks the parser for the text and writes it into the cell. CrossTableFormula writes through one of these too, so both agree on how a formula is made and on what a refusal looks like.
Returns: FormulaBuilder. The builder, created on first use.
Preparation
Prepare #
prepare
Put the registry on the spatial sheet
Signature:
Private Sub Prepare()
Creates the listofgeovars ListObject LLSpatial walks. Add calls this the first time it finds no registry.
AddVarNameToList #
add-var-name-to-list
Register a variable in the registry
Signature:
Private Sub AddVarNameToList(ByVal varName As String)
Appends one row through the ListObject itself, which puts the value in the row the table ends at and resizes the table with it. The row used to be computed from CountA over the whole table range. CountA answers how many cells hold something, and the write needed a row number, so one blank row anywhere in the registry made the next variable overwrite an existing entry. LLSpatial walks that list to decide which variables get refreshed, so a lost entry is a spatial table that never updates in the delivered linelist.
Parameters:
varName: String. The spatial variable name to register.
Checkings
Checks #
checks
The report entries of this instance
Signature:
Private Property Get Checks() As Checking
What the class reports back to the generation report. CrossTableFormula harvests these after Add, and AnalysisOutput harvests them from there. A blank formula column used to reach the delivered table with nothing said about it, and the attack rate two cells over divided by it.
Returns: Checking. The entries, created on first use.
LogInfo #
log-info
File one entry
Signature:
Private Sub LogInfo(ByVal message As String, _
Optional ByVal scope As Byte = checkingSuccess)
Checking.Add raises on a duplicate key, and one report carries the entries of several classes over several tables, so the key names the class, the table and a counter.
Parameters:
message: String. The message text.scope: Optional Byte. The checking scope. Defaults to checkingSuccess.
Validation
CheckRequirements #
check-requirements
Validate the cross-table and find the spatial worksheet
Signature:
Private Function CheckRequirements(ByVal tabl As CrossTable) As Worksheet
The lookup used to overwrite the variable already holding the analysis worksheet, and a failed Set leaves the target at what it held, so the guard below could never fire and a missing worksheet surfaced later as a bare subscript error with nothing pointing at the sheet.
Parameters:
tabl: CrossTable. The cross-table to validate.
Returns: Worksheet. The spatial worksheet of the same workbook.
Throws:
- ProjectError.ObjectNotInitialized When tabl is Nothing.
- ProjectError.ElementNotFound When the spatial worksheet is missing.
Error Handling
ThrowError #
throw-error
Raise a ProjectError-based exception
Signature:
Private Sub ThrowError(ByVal errNumber As Long, ByVal message As String)
Wrapper around Err.Raise that standardises the source to CLASS_NAME.
Parameters:
errNumber: Long. The ProjectError code to raise.message: String. Human-readable description of the failure.
Throws:
- ProjectError. Always raises the specified error.
Used in (7 file(s))
- CrossTableFormula.cls
- FormulaBuilder.cls
- LLSpatial.cls
- TestCrossTableFormula.bas
- TestFormulaBuilder.bas
- TestSpatialTables.bas
- TestLLSpatial.bas