LLSpatial

SpatialTables builds these tables at build time and this class fills them at run time. The two never reference each other: the contract is a set of names agreed by string convention across a workbook boundary, and both files hold their own copy of the four constants and the two name builders. That is the house rule, a class keeps every constant it needs inside itself. Change a name here and change it in SpatialTables in the same commit.

listofgeovars one row per spatial variable, "" spatial one ListObject per level column 1 tabl the lookup key this class writes column 2 formula the value this class sorts on column 3 population_ administrative levels only column 4 attack_rate_ administrative levels only

The width of a table and the meaning of each column are SpatialTables to decide. Every column is resolved by name here, so a table that grows a column keeps working.

Update walks the HList sheets of the workbook. A column controlled by geo1 or by hf names a variable, and the filtered companion sheet of that HList carries the concatenated geo values of that variable, one column per administrative level. The distinct values of that column are the rows of the spatial table. The reading and the de-duplication happen in memory: one read of the column and a delimited index of the values already kept.

Linelist creates the spatial worksheet on every linelist, and SpatialTables creates the listofgeovars table when a spatial cross-table is defined. So a linelist with geo variables and no spatial analysis has the sheet with no table in it, and Update on such a sheet does nothing: Exists, the variable lookup and Sort all answer empty when the table is absent.

SpatialTables.Prepare creates it and SpatialTables.FailedFormula reads it. This class leaves it alone.

Depends on: BetterArray, HiddenNames, Checking

Version: 2026-08-12

Create #

create

Create a new LLSpatial instance from the spatial tables worksheet

Signature:

Public Function Create(ByVal sh As Worksheet) As LLSpatial

Factory method that checks the supplied worksheet is given and is named "__spatial_tables", then returns a new instance. Called on the predeclared instance.

The listofgeovars table is left out of the check on purpose: the worksheet and the table have different lifecycles, and a linelist that carries the sheet with no table still needs an instance for the two callers that build one on every run. See the class description.

Parameters:

• sh: Worksheet. The spatial tables worksheet.

Returns: LLSpatial. A fully initialised instance ready for use.

Throws:

• ProjectError.ObjectNotInitialized When the worksheet is Nothing.
• ProjectError.InvalidArgument When the worksheet name is not "__spatial_tables".

---

Exists #

exists

Check if a variable has spatial tables

Signature:

Public Function Exists(ByVal varName As String) As Boolean

Answers True when the listofgeovars table carries at least one name built on this variable. A spatial sheet with no listofgeovars table answers False.

Parameters:

• varName: String. The bare variable name to look up.

Returns: Boolean. True when the variable is registered.

---

Update #

update

Update all spatial tables from HList filtered data

Signature:

Public Sub Update()

Walks the worksheets of the workbook and works on the HList ones. For each of them it resolves the filtered companion sheet, then walks the variable columns of the table looking for a control value of geo1 or of hf. A geo1 column takes the concat column of the companion and fills the four administrative tables of the variable; an hf column fills the single facility table. The spatial sheet is recalculated at the end so the formula columns answer for the rows just written.

The control value of a variable is a hidden name of the sheet, written by VarWriter as " -- control". This routine used to read it from a hidden worksheet row that stopped being written on 2026-02-06, so it matched nothing and every spatial table stayed at the one blank row the build gave it.

The tag test runs through HiddenNames.QuickValue, which reads the one stored name off the worksheet. The full store is built for the HList sheets alone, because those are the sheets whose variable columns are read below. The walk used to build a store per worksheet of the workbook, and every name of an analysis sheet was walked to answer a tag that named it as no HList.

Throws:

• Any error raised while walking a sheet, after the spatial sheet has been recalculated. The caller is what puts Excel back the way it found it.

---

Sort #

sort

Order the spatial tables of one analysis table

Signature:

Public Sub Sort(ByVal tabId As String, Optional ByVal onAR As Boolean = False)

Finds the variable the table identifier belongs to, then orders its tables on the value column or on the attack rate column, highest first. A facility variable carries one table and a geographic one carries four.

Parameters:

• tabId: String. The identifier of the analysis table.
• onAR: Optional Boolean. True to order on the attack rate. Defaults to False.

---

RewriteFormulas #

rewrite-formulas

Replace a token in every formula of a range

Signature:

Public Sub RewriteFormulas(ByVal valuesRng As Range, ByVal oldText As String, _
                           ByVal newText As String)

The formula rewriting of the spatial analysis events. The caller reads what the user changed and hands the sheet, the table identifier and the admin code over; the loops that touch the formulas live here, so the array-formula handling is written once for every caller.

Walks the cells of the range and rewrites each formula that holds the old text, with the new text in its place. A plain formula is written back through Formula and an array formula through FormulaArray, decided by HasArray. Range.FormulaArray answers the ordinary formula string for a cell outside an array formula, so a read through it cannot tell the two apart: a reader built on it pushed every write through FormulaArray, which turns a plain formula into an array one and raises 1004 for any formula over 255 characters.

Parameters:

• valuesRng: Range. The block of cells to rewrite.
• oldText: String. The text to replace.
• newText: String. The replacement.

---

ChangeAdminLevel #

change-admin-level

Move the formulas of one spatial table to a new admin level

Signature:

Public Sub ChangeAdminLevel(ByVal sh As Worksheet, ByVal tabId As String, _
                            ByVal newAdmin As String)

Reads the level the table stands on from its PREVIOUS_ADM_ cell, rewrites every formula of the OUTER_VALUES_ block from the old concat column to the new one, writes the new level back, and recalculates the block. The caller reads the dropdown and turns its label into an admin code, because that answer needs the geo sheet and this class holds none.

The rewrite reaches the FindTopAdmin and FindTopPop formulas of the block too, and moves the level inside their quoted variable argument with the rest. That is why EventLinelist.BareSpatialName strips the concat tag at any level: it used to know the adm1 spelling alone, so the first level change broke every ranked lookup of the table.

Parameters:

• sh: Worksheet. The spatial analysis sheet.
• tabId: String. The identifier of the analysis table.
• newAdmin: String. The admin code to move to, "adm1" to "adm4" or "hf".

---

PreviousSectionLevel #

previous-section-level

Read the admin level a spatio-temporal section stands on

Signature:

Public Function PreviousSectionLevel(ByVal sh As Worksheet, _
                                     ByVal rngName As String, _
                                     ByVal tabId As String) As Long

The cell right of the level selector records the level the section's formulas read. A value outside 1 to 4 means the cell was cleared or overwritten: a rewrite on it would migrate nothing while still recording the new level, and the sheet would then be one level behind with no way back. GeoModule reads this before it unprotects the sheet, so a raise here leaves a deliberately open sheet open.

Parameters:

• sh: Worksheet. The spatio-temporal analysis sheet.
• rngName: String. The named range of the level selector.
• tabId: String. The identifier of the analysis table, for the message.

Returns: Long. The recorded level, 1 to 4.

Throws:

• ProjectError.InvalidArgument When the cell reads outside 1 to 4.

---

MigrateSection #

migrate-section

Move the formula columns of one spatio-temporal section to a new admin level

Signature:

Public Sub MigrateSection(ByVal sh As Worksheet, ByVal rngName As String, _
                          ByVal tabId As String, ByVal prevAdm As Long, _
                          ByVal actAdm As Long)

Walks the SPT_FORMULA_COLUMN_ header row of the section. A header formula naming the selector marks a formula column, the VALUES block of that column derives from the name of its header cell, and a header cell with no name leaves its column alone -- a cell has a name only when something named it. The block is grown by two rows to reach the Total and Missing rows of its own table, which RowsCategoriesRange trims by count at build time, and their formulas move levels with the block. Every formula of the grown block moves from the old concat column to the new one, and the new level is recorded beside the selector.

The caller owns one recalculation of the sheet per validated place. It fires on every validate and reaches here only when the level changed, so a recalculation at the foot of this sub would leave the other paths dry and would run the same used range twice on the path it does reach.

Parameters:

• sh: Worksheet. The spatio-temporal analysis sheet.
• rngName: String. The named range of the level selector.
• tabId: String. The identifier of the analysis table.
• prevAdm: Long. The level the section stands on.
• actAdm: Long. The level to move to.

---

ApplyPopulationFactor #

apply-population-factor

Divide the values of one spatial table by the population, or restore them

Signature:

Public Sub ApplyPopulationFactor(ByVal sh As Worksheet, ByVal tabId As String, _
                                 ByVal adminName As String, _
                                 Optional ByVal revertBack As Boolean = False)

Walks the OUTER_VALUES_ block and rewraps each formula that reads the concat column of the given admin code. The first application wraps the formula as "IFERROR(factor * formula / population cell, "")", so a row whose population cell holds 0 or nothing shows an empty cell. The division used to be written bare, and every such row showed #DIV/0!. A later application swaps the old factor for the new, and revertBack unwraps the whole thing, the bare shape an older linelist wrote included. The factor comes from the POPFACT_ cell and falls back to 100 when that cell does not read as a number. The tables are reordered on the attack rate while the division is on, and on the plain value once it is off. The factor applied is written into the POPPREVFACT_ cell, so the next call knows what to swap or unwrap.

Parameters:

• sh: Worksheet. The spatial analysis sheet.
• tabId: String. The identifier of the analysis table.
• adminName: String. The admin code the formulas read, "adm1" to "adm4" or "hf".
• revertBack: Optional Boolean. True restores the plain formulas. Defaults to False.

---

FormatPopulationFactor #

format-population-factor

Show or hide the population factor cells of one spatial table

Signature:

Public Sub FormatPopulationFactor(ByVal sh As Worksheet, ByVal tabId As String, _
                                  ByVal factorVisible As Boolean)

The factor cell and its label are white, locked and hidden while the division is off, and black, editable and readable while it is on. The caller decides which, from the dropdown the user changed.

Parameters:

• sh: Worksheet. The spatial analysis sheet.
• tabId: String. The identifier of the analysis table.
• factorVisible: Boolean. True shows the factor cells.

---

TopGeoValue #

top-geo-value

Value at a given rank from an ordered spatial table

Signature:

Public Property Get TopGeoValue(ByVal adminName As String, _
                                 ByVal orderValue As Long, _
                                 ByVal inputVarName As String, _
                                 Optional ByVal tabId As String = vbNullString, _
                                 Optional ByVal columnChoice As Byte = 1) As String

Reads the table of one variable at one administrative level and answers the value at the requested rank, either the admin unit itself or its population. Answers an empty string when the table is absent, holds no data, or is shorter than the rank asked for.

A rank below 1 used to read the header cell or the cells above the table, and the rank comes straight from the argument of a worksheet function, so =FindTopAdmin(ADM_DROPDOWN_sp1, 0, ...) in a cell answered with the internal table name. The level arrives here as the raw code; the worksheet function takes the label of the level and EventLinelist resolves the two.

Parameters:

• adminName: String. Level identifier ("adm1" to "adm4", or "hf").
• orderValue: Long. Rank position in the ordered table, 1 for the top one.
• inputVarName: String. The bare variable name.
• tabId: Optional String. The identifier of the analysis table. Defaults to vbNullString.
• columnChoice: Optional Byte. 1 for the admin unit, anything else for its population. Defaults to 1.

Returns: String. The value at that rank, or an empty string.

---

TopHFValue #

top-hf-value

Value at a given rank from an ordered facility table

Signature:

Public Property Get TopHFValue(ByVal orderValue As Long, _
                                ByVal inputVarName As String, _
                                Optional ByVal tabId As String = vbNullString) As String

The facility table is the same shape as an administrative one under another level name, so this is that read with the level fixed. The two used to be two copies of one function, and every fix to one of them had to be made twice.

Parameters:

• orderValue: Long. Rank position in the ordered table, 1 for the top one.
• inputVarName: String. The bare variable name.
• tabId: Optional String. The identifier of the analysis table. Defaults to vbNullString.

Returns: String. The facility at that rank, or an empty string.

---