SpatialTables
Creates spatial analysis ListObjects on the "spatial_tables__" worksheet at linelist build time. For each cross-table row variable, builds either four admin-level tables (adm1-adm4 for geo type) or one health-facility table (for hf type), each with formula, population, and attack rate columns. Manages formula conditions and validates that the spatial sheet exists before writing.
Depends on: ICrossTable, ITableSpecs, IFormulas, IFormulaCondition, BetterArray
Version: 2025-01-15
Instantiation
create #
Create a SpatialTables instance from a cross-table
Signature:
Public Function Create(ByVal tabl As ICrossTable) As ISpatialTables
Factory method on the predeclared instance. Validates that the cross-table is not Nothing and that the "spatial_tables__" worksheet exists in the same workbook. Binds the cross-table to the new instance so subsequent Add calls can read the row variable and table specifications.
Parameters:
tabl: ICrossTable. The cross-table whose row variable defines the spatial tables to create.
Returns: ISpatialTables. A fully initialised spatial tables builder.
Throws:
- ProjectError.ObjectNotInitialized When tabl is Nothing.
- ProjectError.ElementNotFound When the spatial worksheet is missing.
Depends on:
- ICrossTable
- ITableSpecs
Add
add #
Create spatial ListObjects for the cross-table row variable
Signature:
Private Sub Add(ByVal formObject As IFormulas)
Builds the spatial table set for the current cross-table. For geo-type tables, creates four admin-level tables (adm1-adm4) each with formula, population, and attack rate columns. For hf-type tables, creates a single health-facility table with a formula column only. Each table is created as a 2-row ListObject (header + one data row) positioned at the next available column on the spatial sheet. The formula column is populated from the parsed analysis formula via CurrentExcelFormula. Skips creation when the tables already exist for this variable.
Parameters:
formObject: IFormulas. The formula object whose parsed expression is written into each spatial table formula column.
Internal members (not exported)
Instantiation
self #
Current object instance
Signature:
Public Property Get Self() As ISpatialTables
Convenience accessor for fluent retrieval of the interface reference from the predeclared Create method.
Returns: ISpatialTables. The current instance cast to the interface.
table #
Cross-table bound to this builder
Signature:
Public Property Get Table() As ICrossTable
Returns the ICrossTable instance whose row variable and specifications drive the spatial table creation in Add.
Returns: ICrossTable. The bound cross-table.
table-set #
Assign the cross-table reference
Signature:
Public Property Set Table(ByVal tabl As ICrossTable)
Parameters:
tabl: ICrossTable. The cross-table to bind.
Worksheet
wksh #
Lazy-loaded spatial worksheet reference
Signature:
Private Property Get Wksh() As Worksheet
Returns the "spatial_tables__" worksheet from the cross-table workbook. The reference is cached after the first access to avoid repeated lookups.
Returns: Worksheet. The spatial analysis worksheet.
Naming Conventions
current-header-name #
Build the header named range name for a spatial table
Signature:
Private Property Get CurrentHeaderName(ByVal varName As String, _
ByVal adminName As String) As String
Helpers for constructing spatial table and range names. Concatenates "tabl_", the admin name, and the variable name to form a unique header range name (e.g. "tabl_adm1_cases_sp1").
Parameters:
varName: String. The spatial variable name.adminName: String. The admin level identifier (e.g. "adm1", "hf").
Returns: String. The header named range name.
current-lo-name #
Build the ListObject name for a spatial table
Signature:
Private Property Get CurrentLoName(ByVal varName As String, _
ByVal adminName As String) As String
Concatenates "spatial_", the admin name, and the variable name to form a unique ListObject name (e.g. "spatial_adm1_cases_sp1").
Parameters:
varName: String. The spatial variable name.adminName: String. The admin level identifier.
Returns: String. The ListObject name.
Existence Checks
exists #
Check if spatial ListObjects already exist for a variable
Signature:
Private Function Exists(ByVal varName As String) As Boolean
Iterates through admin levels 1-4 and tests whether each corresponding ListObject exists on the spatial worksheet. Returns True only when all four exist. Used by Add to skip duplicate creation.
Parameters:
varName: String. The spatial variable name to check.
Returns: Boolean. True when all four admin-level ListObjects exist.
lo-exists #
Check if a ListObject exists on the spatial sheet
Signature:
Private Function LoExists(ByVal loName As String) As Boolean
Uses the standard VBA error-trapping pattern to test for ListObject existence on the spatial worksheet.
Parameters:
loName: String. The ListObject name to look for.
Returns: Boolean. True when the ListObject exists.
Column Helpers
last-column #
Next available column on the spatial sheet
Signature:
Private Property Get LastColumn() As Long
Finds the last used column on row 1 and returns the next available position with a 2-column gap. Returns column 7 when the sheet only has the initial 3-column setup (listofgeovars occupies column 3).
Returns: Long. The 1-based column index for the next spatial table.
Formula Helpers
geo-population-function #
Build a geo population INDEX/MATCH formula for an admin level
Signature:
Private Function GeoPopulationFunction(ByVal adminName As String, _
ByVal rangeAddress As String) As String
Helpers for building and testing Excel formula expressions. Constructs an INDEX/MATCH formula that looks up the population value for an admin level from the corresponding geo reference table (T_ADM1 through T_ADM4). The formula is wrapped in ISERROR to return 0 when the lookup fails. The population column index is derived from the admin level number.
Parameters:
adminName: String. The admin level identifier (e.g. "adm1").rangeAddress: String. The cell address used as the MATCH lookup value.
Returns: String. The complete INDEX/MATCH formula string.
failed-formula #
Test whether a formula expression is valid
Signature:
Private Function FailedFormula(ByVal frmName As String) As Boolean
Writes the formula to cell A1 of the spatial sheet inside an error handler. If the assignment succeeds, the formula is valid and the cell is cleared. If it fails, the method returns True. DisplayAlerts is temporarily disabled to suppress Excel prompts.
Parameters:
frmName: String. The formula expression to test (without leading "=").
Returns: Boolean. True when the formula is invalid.
emp-str #
Empty string constant for formula conditions
Signature:
Private Function EmpStr() As String
Returns a pair of double-quote characters for use in Excel formula strings.
Returns: String. Two double-quote characters (empty string literal in Excel).
Condition Management
add-table-conditions #
Add a variable/condition pair for formula generation
Signature:
Private Sub AddTableConditions(ByVal varName As String, ByVal conditionName As String)
Manages variable/condition pairs for formula generation. Pushes the variable name and condition test onto the parallel condVars and condTests BetterArrays. Lazily initialises both arrays with LowerBound 1 on first use. Ignores empty variable names.
Parameters:
varName: String. The variable name for the condition.conditionName: String. The condition expression.
clear-table-conditions #
Clear condition arrays
Signature:
Private Sub ClearTableConditions(Optional ByVal removeNumber As Long = 0)
When removeNumber is zero or negative, clears both arrays entirely. When positive, removes only the last N entries from both arrays via Pop. Lazily initialises the arrays if they are Nothing.
Parameters:
removeNumber: Optional Long. Number of trailing entries to remove. Defaults to 0 (clear all).
current-excel-formula #
Build an Excel formula string using the current conditions
Signature:
Private Property Get CurrentExcelFormula(ByVal tabFormObject As IFormulas, _
Optional ByVal scope As Byte = FormulaScopeAnalysis, _
Optional ByVal prefix As String = "f") As String
Constructs a parsed formula from the IFormulas object, applying either linelist scope (with table name prefix) or analysis scope (with condition pairs from condVars/condTests). Validates the result via FailedFormula and returns an empty string when the formula is invalid or the object is not valid.
Parameters:
tabFormObject: IFormulas. The formula definition to parse.scope: Optional Byte. FormulaScopeLinelist or FormulaScopeAnalysis. Defaults to FormulaScopeAnalysis.prefix: Optional String. Table name prefix for linelist scope. Defaults to "f".
Returns: String. The parsed formula string, or empty when invalid.
Preparation
prepare #
Initialise the spatial sheet with the listofgeovars ListObject
Signature:
Private Sub Prepare()
Creates the "listofgeovars" ListObject in column 3 and defines the RNG_PastingCol and RNG_TestingFormula named ranges. Called by Add when the listofgeovars table does not yet exist.
add-var-name-to-list #
Register a variable name in the listofgeovars ListObject
Signature:
Private Sub AddVarNameToList(ByVal varName As String)
Appends the variable name to the next empty row in the listofgeovars ListObject. Uses CountA to determine the current row count.
Parameters:
varName: String. The spatial variable name to register.
Validation
check-requirements #
Validate cross-table and spatial sheet existence
Signature:
Private Sub CheckRequirements(ByVal tabl As ICrossTable)
Guards the Create factory by ensuring the cross-table is not Nothing and that a worksheet named "spatial_tables__" exists in the same workbook.
Parameters:
tabl: ICrossTable. The cross-table to validate.
Throws:
- ProjectError.ObjectNotInitialized When tabl is Nothing.
- ProjectError.ElementNotFound When the spatial worksheet is missing.
Error Handling
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.
Interface Implementation
ISpatialTables_Exists #
Signature:
Private Property Get ISpatialTables_Exists(ByVal varName As String) As Boolean
Delegated members satisfying the ISpatialTables contract. See the corresponding Private members above for full documentation.