LLSpatial

Manages the spatial analysis tables that aggregate filtered linelist data by geographic admin level and health facility. The spatial sheet contains a "listofgeovars" ListObject that lists which variables have spatial tables, and one ListObject per admin level per variable (named "spatial_adm1_varname" etc.) holding aggregated values, formula columns, and attack rate columns. This class populates those tables from filtered companion sheets, sorts them by formula or attack rate, and retrieves ranked values for use in analyses and graphs.

Depends on: BetterArray

Factory

create #

Create a new LLSpatial instance from the spatial tables worksheet

Signature:

Public Function Create(ByVal sh As Worksheet) As ILLSpatial

Factory method that validates the supplied worksheet meets requirements (must be named "spatial_tables__" and contain the "listofgeovars" ListObject), then returns a new instance through the ILLSpatial interface. Called on the predeclared instance.

Parameters:

  • sh: Worksheet. The spatial tables worksheet.

Returns: ILLSpatial. 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 name exists in the spatial variables list

Signature:

Private Function Exists(ByVal varName As String) As Boolean

Searches the "listofgeovars" ListObject range for a partial match of the supplied variable name using xlPart matching. Returns True when at least one cell contains the variable name as a substring, indicating that spatial analysis tables exist for this variable.

Parameters:

  • varName: String. Variable name or partial name to look up.

Returns: Boolean. True when the variable is found in the list.

Update

update #

Update all spatial tables from HList filtered data

Signature:

Private Sub Update()

Iterates all worksheets in the workbook looking for HList sheets. For each HList sheet, locates its filtered companion sheet (from cell(1,5)), then walks the variable columns looking for geo1 and hf control values. When a geo1 column is found, extracts the concat column data from the filtered companion and calls UpdateSpatialValue to populate the spatial tables for all four admin levels. When an hf column is found, does the same for the HF spatial table. After processing all sheets, recalculates the spatial sheet so formula columns update.

Sort

sort #

Sort spatial tables by formula value or attack rate for a given table id

Signature:

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

Locates the spatial variable whose name partially matches tabId in the "listofgeovars" list, then iterates admin1 through admin4 and sorts each corresponding ListObject by either the formula column or the attack_rate column in descending order. Silently skips tables that do not exist, have no data body, or whose first column is empty.

Parameters:

  • tabId: String. Table identifier used to locate the variable name via partial match.
  • onAR: Optional Boolean. True to sort by attack_rate column, False for formula column. Defaults to False.
TopGeoValue / TopHFValue

top-geo-value #

Value at a given rank from a sorted spatial geo table

Signature:

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

Constructs the ListObject name from the admin name and variable name (with tabId suffix), then retrieves the value at the requested rank position. The concatValue parameter selects the column: 1 returns the name column (first column), any other value returns the concat column (third column). Returns an empty string when the table does not exist, has no data, or the rank exceeds available rows.

Parameters:

  • adminName: String. Admin level name ("adm1", "adm2", "adm3", or "adm4").
  • orderValue: Long. Rank position in the sorted table (1-based).
  • inputVarName: String. Base variable name.
  • tabId: Optional String. Table identifier suffix appended to the variable name. Defaults to vbNullString.
  • concatValue: Optional Byte. 1 for the name column, other values for the concat column. Defaults to 1.

Returns: String. The value at the requested rank, or empty string if not found.

top-hf-value #

Value at a given rank from a sorted spatial HF table

Signature:

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

Constructs the HF ListObject name from the variable name (with tabId suffix), then retrieves the value at the requested rank position from the first column. Returns an empty string when the table does not exist, has no data, or the rank exceeds available rows.

Parameters:

  • orderValue: Long. Rank position in the sorted table (1-based).
  • inputVarName: String. Base variable name.
  • tabId: Optional String. Table identifier suffix appended to the variable name. Defaults to vbNullString.

Returns: String. The value at the requested rank, or empty string if not found.

Internal members (not exported)

Factory

self #

Current object instance

Signature:

Public Property Get Self() As ILLSpatial

Convenience accessor so consuming code can fluently retrieve the interface reference from the predeclared Create method.

Returns: ILLSpatial. The current instance cast to the interface.

wksh #

Spatial tables worksheet

Signature:

Public Property Get Wksh() As Worksheet

Returns: Worksheet. The backing spatial tables worksheet.

wksh-set #

Assign the spatial tables worksheet

Signature:

Public Property Set Wksh(ByVal sh As Worksheet)

Parameters:

Naming Conventions

current-header-name #

Named range name for a spatial table header

Signature:

Private Property Get CurrentHeaderName(ByVal varName As String, _
                                       ByVal adminName As String) As String

Builds the conventional named range name used to locate the header row of a spatial table. The pattern is "tabl_{adminName}_{varName}".

Parameters:

Returns: String. The constructed named range name.

current-lo-name #

ListObject name for a spatial table

Signature:

Private Property Get CurrentLoName(ByVal varName As String, _
                                   ByVal adminName As String) As String

Builds the conventional ListObject name for a spatial table. The pattern is "spatial_{adminName}_{varName}".

Parameters:

Returns: String. The constructed ListObject name.

Exists

lo-exists #

Check if a ListObject exists on the spatial sheet

Signature:

Private Function LoExists(ByVal loName As String) As Boolean

Attempts to retrieve the named ListObject from the spatial worksheet. Returns True when the object is found, False otherwise. Uses error trapping to handle the case where the name does not exist.

Parameters:

Returns: Boolean. True when the ListObject exists.

List of Variable Names

list-of-existing-var-names #

Build a list of all spatial variable names matching a base name

Signature:

Private Function ListOfExistingVarNames(ByVal varName As String) As BetterArray

Searches the "listofgeovars" ListObject for all cells that partially match the supplied variable name. Returns a BetterArray of the full variable names found. The search progresses through the range by narrowing it after each match to avoid finding the same cell again.

Parameters:

Returns: BetterArray. All matching spatial variable names.

Update Spatial Values

update-spatial-value #

Populate spatial ListObjects from a filtered input range

Signature:

Private Sub UpdateSpatialValue(ByVal inputRng As Range, _
                               ByVal inputVarName As String, _
                               Optional ByVal isHF As Boolean = False)

For each matching variable name returned by ListOfExistingVarNames, iterates admin levels 1 through 4 (or just HF when isHF is True). For each admin level, pastes the input range values to a scratch column (RNG_PastingCol), removes duplicates, clears the existing spatial table data, pastes the deduplicated values into the ListObject first column, resizes the table to fit, and sorts by the formula column in descending order. The input range is shifted one column right for each admin level to cascade through the concat columns.

Parameters:

Validation

check-requirements #

Validate that the worksheet is the spatial tables sheet

Signature:

Private Sub CheckRequirements(ByVal sh As Worksheet)

Guards the factory Create method by ensuring the worksheet is not Nothing and its name matches the expected "spatial_tables__" constant. Raises an error when either condition fails.

Parameters:

Throws:

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, providing a consistent stack trace across all methods in this class.

Parameters:

Throws:

Interface Implementation

ILLSpatial_Exists #

Signature:

Private Property Get ILLSpatial_Exists(ByVal varName As String) As Boolean

Delegated members satisfying the ILLSpatial contract. See the corresponding members above for full documentation.

Used in (7 file(s))