LLGeo

Manages the geobase worksheet that stores the four-level administrative hierarchy (admin1 through admin4), health facility data, translation names, and historic entries used across the linelist. Provides factory creation with worksheet validation, lazy-loaded translation caching via parallel BetterArray keys/values, hierarchical geo-level lookups using AutoFilter cascades, import/export in both flat-data and full-structure modes, and synchronisation of translated labels across linelist sheets, dictionary, and show/hide tables.

Depends on: BetterArray, IPasswords, ILLdictionary, ICustomTable, ILLVariables, IDropdownLists, DropdownLists, LLVariables, CustomTable, HiddenNames, IHiddenNames

Factory

create #

Create a new LLGeo instance from a geobase worksheet.

Signature:

Public Function Create(ByVal sh As Worksheet) As ILLGeo

Factory method that validates all required tables, named ranges, and hidden names on the supplied worksheet, then constructs and returns a new LLGeo instance bound to that worksheet. This is the only supported way to create a usable LLGeo object because the class relies on specific ListObjects, named ranges, and HiddenNames being present.

Parameters:

sh: Worksheet. The geobase worksheet containing T_ADM1 through T_ADM4, T_HF, T_NAMES, T_HISTOGEO, T_HISTOHF, T_METADATA, and required named ranges/hidden names.

Returns: ILLGeo. A fully initialised LLGeo instance exposed through the ILLGeo interface.

Translation Cache

geo-names #

Translated display name for a raw admin column name.

Signature:

Private Property Get GeoNames(ByVal admname As String) As String

Converts a raw admin column name (such as "adm1_name" or "hf_name") to its translated display name by searching the lazily loaded translation cache. If no mapping is found the raw name is returned unchanged. The cache is built on first access from cell-based named ranges on the geo worksheet.

Parameters:

admname: String. Raw column name to translate.

Returns: String. Translated display name, or the raw name if no translation exists.

GeoLevel

geo-level #

List of values at a given admin level.

Signature:

Private Property Get GeoLevel(ByVal adminLevel As Byte, _
                              Optional ByVal geoType As Byte = GeoScopeAdmin, _
                              Optional adminNames As Variant = vbNullString) As BetterArray

Returns a BetterArray of unique geographic names at the requested admin level, optionally filtered by parent admin selections. For admin1 the full unfiltered list is returned. For admin2 through admin4 the caller supplies parent selections so the result cascades hierarchically via AutoFilter. The geoType parameter switches between the admin hierarchy and the health facility table. Calculation is temporarily disabled for performance during filtering.

Parameters:

adminLevel: Byte. Admin level index (0-3 for admin1-admin4).
geoType: Optional Byte. GeoScope value: GeoScopeAdmin (0) or GeoScopeHF (1). Defaults to GeoScopeAdmin.
adminNames: Optional Variant. Parent admin selections for filtering: String for level 2, BetterArray for levels 3-4. Defaults to vbNullString.

Returns: BetterArray. Unique values at the requested level, or an empty array when the geobase is empty.

Throws:

ProjectError.InvalidArgument When adminNames has wrong type or length.
ProjectError.ErrorUnexpectedState When the ListObject has no data.

Translate

translate #

Translate or revert geobase table headers.

Signature:

Private Sub Translate(Optional ByVal rawNames As Boolean = False)

Applies or reverts translated display names on all geobase table headers (admin1 through admin4 and health facility). When rawNames is False, headers are translated to display names read from the geobase named ranges. When True, headers are reverted to raw column names. The method skips the operation when the geobase is already in the requested translation state and resets the translation cache both before and after the change.

Parameters:

rawNames: Optional Boolean. True to revert to raw names, False to apply translated names. Defaults to False.

Clear

clear #

Clear all geobase data.

Signature:

Private Sub Clear()

Removes all data from every ListObject on the geobase worksheet except T_LevelNames and T_CONCAT, and sets the update flag to "empty". Resets the translation name cache so subsequent GeoNames lookups will rebuild from fresh data.

clear-historic #

Clear historic entries.

Signature:

Private Sub ClearHistoric(Optional ByVal scope As Byte = 3)

Clears the data body of historic ListObjects. The scope parameter controls which tables are cleared: 0 for geo only (T_HISTOGEO), 1 for HF only (T_HISTOHF), and 3 for both. Errors are silently suppressed so the method succeeds even when a table has no data body.

Parameters:

scope: Optional Byte. 0 = geo only, 1 = HF only, 3 = both. Defaults to 3.

Import

import #

Import geobase data from an external workbook.

Signature:

Private Sub Import(ByVal fromWkb As Workbook, _
                   Optional ByVal histoOnly As Boolean = False)

Imports geographic data from the supplied workbook into the geobase worksheet. Each worksheet whose name matches a known table name (ADM1 through ADM4, HF, NAMES, HISTOGEO, HISTOHF, METADATA) is imported column by column into the corresponding ListObject. Concat formulas are regenerated for filtered lookup support. When histoOnly is True only historic tables are refreshed and the main admin/HF data is left intact. After import, the geobase is marked as updated and headers are translated.

Parameters:

fromWkb: Workbook. Source workbook containing geo data sheets.
histoOnly: Optional Boolean. True to import only historic data. Defaults to False.

Export

export-to-file #

Export geobase tables to a workbook as flat data.

Signature:

Private Sub ExportToFile(ByVal outputWkb As Workbook, _
                         Optional ByVal onlyHistory As Boolean = False)

Exports geobase tables to the target workbook with one sheet per table and a Metadata sheet. Headers are reverted to raw names before export and restored to translated form afterwards. HiddenNames entries (RNG_GeoUpdated, RNG_GeoName, etc.) are exported to the Metadata sheet so they survive round-trip import. When onlyHistory is True only the HISTOGEO and HISTOHF tables are exported.

Parameters:

outputWkb: Workbook. Target workbook to receive exported sheets.
onlyHistory: Optional Boolean. True to export only historic tables. Defaults to False.

export-to-wkb #

Export the geobase worksheet structure to a workbook.

Signature:

Private Sub ExportToWkb(ByVal outputWkb As Workbook, _
                        Optional ByVal llName As String = vbNullString, _
                        Optional ByVal setupPath As String = vbNullString)

Copies the entire geobase worksheet to the target workbook, preserving ListObjects, named ranges, formulas, pcode references, concat references, and metadata. Creates a hidden Metadata sheet with creation metadata including linelist name, setup name, language, and timestamp. Cell-based named ranges (RNG_PastingGeoCol, RNG_HFNAME, RNG_ADM1NAME-4) are exported as regular named ranges. HiddenNames entries (RNG_GeoUpdated, RNG_GeoName, etc.) are exported via the HiddenNames manager. Raises an error when sheets with conflicting names already exist in the target workbook.

Parameters:

outputWkb: Workbook. Target workbook.
llName: Optional String. Linelist name for metadata. Defaults to vbNullString.
setupPath: Optional String. Setup file path for metadata. Defaults to vbNullString.

Throws:

ProjectError.ElementShouldNotExists When target sheets already exist.

Update

update #

Update linelist geo column headers and dropdowns.

Signature:

Private Sub Update(ByVal pass As IPasswords)

Iterates all HList worksheets in the parent workbook, finds columns controlled by geo1 through geo4 and hf, and updates their header labels with translated geo display names. Also updates corresponding labels on print sheets. Finally, refreshes the admin1 dropdown list on the dropdown_lists__ sheet so that it reflects the current geobase content.

Parameters:

pass: IPasswords. Password manager for sheet protection and unprotection.

update-dict #

Update dictionary labels for geo-related variables.

Signature:

Private Sub UpdateDict(ByVal dict As ILLdictionary)

Scans the dictionary control column for geo1 through geo4 and hf entries and overwrites the corresponding main label cells with translated geo display names from the geobase. This keeps the dictionary in sync after a geobase import or translation change.

Parameters:

dict: ILLdictionary. The linelist dictionary whose labels should be updated.

update-show-hide #

Update show/hide tables with translated geo labels.

Signature:

Private Sub UpdateShowHide(ByVal sh As Worksheet, ByVal dict As ILLdictionary)

Iterates all ListObjects on the show/hide worksheet, finds variables controlled by geo1 through geo4 and hf via the dictionary, and updates their main label cells to include the subsection prefix followed by the translated geo display name. This keeps the show/hide interface labels in sync with the current geobase translation.

Parameters:

sh: Worksheet. The show/hide worksheet containing ListObjects to update.
dict: ILLdictionary. The linelist dictionary for resolving control and subsection values.

Historic

update-historic #

Add a new entry to the historic data.

Signature:

Private Sub UpdateHistoric(ByVal newValue As String, _
                           Optional ByVal scope As Byte = 0)

Appends a value to the appropriate historic ListObject (T_HISTOGEO or T_HISTOHF), resizes the table to include the new row, removes duplicates, and sorts alphabetically. Handles the edge case where the ListObject has no data body or is empty by inserting at the first data row.

Parameters:

newValue: String. The value to add to the historic list.
scope: Optional Byte. 0 = geo historic (T_HISTOGEO), 1 = HF historic (T_HISTOHF). Defaults to 0.

AdminCode

admin-code #

Resolve the admin code from a translated display name.

Signature:

Private Function AdminCode(ByVal selectedAdmin As String) As String

Compares the supplied translated admin name against GeoNames for each admin level and returns the matching raw code ("adm1" through "adm4"). Falls back to "adm1" when no match is found, which serves as a safe default for downstream callers.

Parameters:

selectedAdmin: String. The translated admin display name to resolve.

Returns: String. Admin code ("adm1", "adm2", "adm3", or "adm4").

has-no-data #

Whether the geobase has no imported data.

Signature:

Private Property Get HasNoData() As Boolean

Returns True when the geobase update flag (RNG_GeoUpdated) reads "empty", indicating that no geo data has been imported yet or that all data has been cleared. Used by consumers to skip geo-dependent operations when no data is available.

Returns: Boolean. True when the geobase is empty.

Internal members (not exported)

Factory

wksh #

Get the geobase worksheet.

Signature:

Public Property Get Wksh() As Worksheet

Returns the worksheet that holds all geobase ListObjects and named ranges. This worksheet is set during factory creation and should not change during the lifetime of the object.

Returns: Worksheet. The geobase worksheet.

wksh-set #

Set the geobase worksheet.

Signature:

Public Property Set Wksh(ByVal sh As Worksheet)

Assigns the internal worksheet reference. Called only from the factory Create method after validation. Not intended for external use.

Parameters:

sh: Worksheet. The geobase worksheet to bind to this instance.

Hidden Names Store

geo-hidden-store #

Lazy-loaded HiddenNames manager for the geo worksheet.

Signature:

Private Function GeoHiddenStore() As IHiddenNames

Creates and caches an IHiddenNames instance bound to the geobase worksheet on first access. Used by all methods that read or write the 5 hidden name values (RNG_GeoUpdated, RNG_GeoName, RNG_MetaLang, RNG_GeoLangCode, RNG_FormLoaded).

Returns: IHiddenNames. The worksheet-level hidden names manager.

Table Names

table-names #

Ordered list of all geobase table names.

Signature:

Public Property Get TableNames() As BetterArray

Builds and returns a BetterArray containing the eight canonical ListObject names used on the geobase worksheet: T_ADM1, T_ADM2, T_ADM3, T_ADM4, T_HF, T_NAMES, T_HISTOGEO, and T_HISTOHF. The order matches the admin hierarchy followed by support tables.

Returns: BetterArray. Ordered list of table name strings.

Translation Cache

ensure-names-cache #

Ensure the translation name cache is loaded.

Signature:

Private Sub EnsureNamesCache()

Lazily initialises two parallel BetterArray instances (keys and values) that map raw admin column names (adm1_name, adm2_name, etc.) to their translated display names resolved from cell-based named ranges on the geo worksheet. If the cache is already loaded this method returns immediately. The named range values are populated by UpdateLevelNames (called from Import and Translate) so no worksheet recalculation is needed here.

cache-one-name #

Cache one translation mapping from a cell-based named range.

Signature:

Private Sub CacheOneName(ByVal sh As Worksheet, ByVal rawName As String, _
                         ByVal rngName As String)

Reads a single value from a cell-based named range on the worksheet and stores the raw-name to resolved-value pair in the parallel cache arrays. If the range does not exist or the value is not a non-empty string, the raw name is stored as both key and value so lookups fall back gracefully.

Parameters:

sh: Worksheet. The geobase worksheet to read from.
rawName: String. The raw column name used as the cache key.
rngName: String. The cell-based named range whose value provides the translation.

reset-names-cache #

Reset the translation cache.

Signature:

Private Sub ResetNamesCache()

Marks the cache as unloaded and releases the key/value BetterArray references. Must be called after any operation that changes translation state (Import or Translate) so the next GeoNames access rebuilds the cache from fresh data.

update-level-names #

Programmatically update RNG_ADM*NAME and RNG_HFNAME from T_NAMES.

Signature:

Private Sub UpdateLevelNames()

Reads the current language code from the RNG_GeoLangCode hidden name, looks up the matching column in T_NAMES, and writes the translated level display name into each cell-based named range (RNG_ADM1NAME through RNG_ADM4NAME and RNG_HFNAME). This replaces the previous INDEX/MATCH formula approach that required sh.Calculate and could fail when RNG_GeoLangCode was stored as a hidden name rather than a cell-based range. Silently exits when no language code is set or when T_NAMES does not have the expected columns.

lookup-level-name #

Find the translated name for a level in T_NAMES.

Signature:

Private Function LookupLevelName(ByVal loNames As ListObject, ByVal levelCol As Long, _
                                  ByVal langCol As Long, ByVal levelName As String) As String

Searches the data body of T_NAMES for a row where the level column matches the requested level identifier, then returns the value from the language column in that row. Returns vbNullString when no match is found.

Parameters:

loNames: ListObject. The T_NAMES ListObject containing level translations.
levelCol: Long. 1-based column index for the level identifiers in T_NAMES.
langCol: Long. 1-based column index for the target language in T_NAMES.
levelName: String. The level identifier to search for (e.g. "adm1_name").

Returns: String. The translated display name, or vbNullString if not found.

Column Helpers

column-index #

Find the 1-based column index of a name within a header range.

Signature:

Private Function ColumnIndex(ByVal colName As String, ByVal headerRng As Range, _
                             Optional ByVal inRange As Boolean = True) As Long

Searches a header row range for an exact case-sensitive match of the given column name. When inRange is True the returned index is relative to the range start (1-based); when False the absolute worksheet column number is returned. Returns -1 when no match is found.

Parameters:

colName: String. The column name to search for.
headerRng: Range. The header row range to search in.
inRange: Optional Boolean. True to return relative position, False for absolute column number. Defaults to True.

Returns: Long. Column index (1-based), or -1 if not found.

geo-column-index #

Resolve the column index for a geo admin table.

Signature:

Private Function GeoColumnIndex(ByVal colName As String, ByVal adminLevel As Byte) As Long

Looks up a column name in the header of the specified admin-level ListObject. If the geobase is in translated state the column name is first converted to its translated display name via GeoNames. Raises an error when the column cannot be found in the table header.

Parameters:

colName: String. The raw column name to look up.
adminLevel: Byte. Admin level index selecting which table to search (0-3 for T_ADM1 through T_ADM4).

Returns: Long. 1-based column index within the table.

Throws:

ProjectError.ElementNotFound When the column is not found in the admin table.

hf-column-index #

Resolve the column index for the HF table.

Signature:

Private Function HFColumnIndex(ByVal colName As String) As Long

Looks up a column name in the header of the T_HF ListObject. If the geobase is in translated state the column name is first converted to its translated display name via GeoNames. Raises an error when the column cannot be found.

Parameters:

colName: String. The raw column name to look up.

Returns: Long. 1-based column index within the T_HF table.

Throws:

ProjectError.ElementNotFound When the column is not found in the HF table.

unique-range-value #

Extract unique values from a range into a BetterArray.

Signature:

Private Function UniqueRangeValue(ByVal rng As Range) As BetterArray

Iterates every cell in the supplied range and collects values that have not already been added, producing a deduplicated list in encounter order. Used internally to build dropdown-ready lists from filtered or unfiltered columns.

Parameters:

rng: Range. The range of cells to extract unique values from.

Returns: BetterArray. Deduplicated values in encounter order.

hf-range #

Get the data body range for a named column in the HF table.

Signature:

Private Function HFRange(ByVal colName As String) As Range

Resolves the column index for the given column name in T_HF and returns the DataBodyRange of that ListColumn. Used by GeoLevel when retrieving health facility data at admin level 1.

Parameters:

colName: String. The raw column name whose data body range is needed.

Returns: Range. The DataBodyRange of the matching column in T_HF.

Internal Helpers

update-metadata #

Update the metadata sheet from the geo metadata table.

Signature:

Private Sub UpdateMetadata()

Copies the current content of T_METADATA into the hidden Metadata worksheet at the RNG_Meta_Anchor position. Clears any previously written metadata rows before writing the new data. Silently exits if the Metadata sheet or anchor range does not exist.

Validation

check-requirements #

Validate that the worksheet has all required tables, named ranges, and hidden names.

Signature:

Private Sub CheckRequirements(ByVal sh As Worksheet)

Checks that the supplied worksheet is not Nothing, then verifies the presence of all nine required ListObjects (T_ADM1 through T_ADM4, T_HF, T_NAMES, T_HISTOGEO, T_HISTOHF, T_METADATA), five required HiddenNames entries (RNG_GeoUpdated, RNG_GeoName, RNG_GeoLangCode, RNG_FormLoaded, RNG_MetaLang), and six required cell-based named ranges (RNG_PastingGeoCol, RNG_HFNAME, RNG_ADM1NAME through RNG_ADM4NAME). Raises an appropriate error when any requirement is missing.

Parameters:

sh: Worksheet. The worksheet to validate.

Throws:

ProjectError.ObjectNotInitialized When the worksheet is Nothing.
ProjectError.ElementNotFound When required tables, named ranges, or hidden names are missing.

hidden-name-required #

Verify that a hidden name exists in the store.

Signature:

Private Sub HiddenNameRequired(ByVal store As IHiddenNames, ByVal nameId As String)

Checks whether the specified name exists in the given HiddenNames store. If not found, raises an ElementNotFound error with a descriptive message.

Parameters:

store: IHiddenNames. The hidden names manager to check.
nameId: String. The hidden name identifier to look for.

Throws:

ProjectError.ElementNotFound When the hidden name does not exist.

range-exists #

Verify that a named range exists on a worksheet.

Signature:

Private Sub RangeExists(ByVal sh As Worksheet, ByVal rngName As String)

Attempts to resolve a named range on the given worksheet. If the range cannot be found, raises an ElementNotFound error with a descriptive message.

Parameters:

sh: Worksheet. The worksheet to check.
rngName: String. The named range to look for.

Throws:

ProjectError.ElementNotFound When the named range does not exist on the worksheet.

lo-exists #

Verify that all listed ListObjects exist on a worksheet.

Signature:

Private Sub LoExists(ByVal loNames As BetterArray, ByVal sh As Worksheet)

Iterates over the supplied BetterArray of table names and checks that each ListObject can be found on the given worksheet. Raises an ElementNotFound error for the first missing table encountered.

Parameters:

loNames: BetterArray. List of ListObject names to verify.
sh: Worksheet. The worksheet to check.

Throws:

ProjectError.ElementNotFound When a required ListObject does not exist on the worksheet.

Error Handling

throw-error #

Raise a ProjectError-based exception.

Signature:

Private Sub ThrowError(ByVal errNumber As Long, ByVal message As String)

Wraps Err.Raise to throw a structured error using a ProjectError error code and a descriptive message. The error number is passed as Long because ProjectError values exceed the Byte range (they start at 1001+).

Parameters:

errNumber: Long. The ProjectError error code to raise.
message: String. Descriptive error message.

Used in (31 file(s))