LLGeo

Manages the geobase worksheet: the four-level administrative hierarchy, admin1 through admin4, the health facility table, the level-name translations and the historic entries. GeoNames and GeoBaseName answer the names, GeoLevel walks the hierarchy through AutoFilter cascades, AdminCode answers the code of one entry, and HasNoData says whether the geobase carries anything. Translate rewrites the level labels, Clear and ClearHistoric empty the tables, Import reads a geobase in, and ExportToFile and ExportToWkb write it out as flat data or as the full structure. Update, UpdateDict and UpdateHistoric carry the translated labels across the linelist sheets and the dictionary, and SkippedSheets names the sheets an update left alone.

The show/hide form takes its geo labels from the dictionary UpdateDict rewrites: ShowHide composes ": " itself, every time it is built. A third routine here used to write that same string into per-sheet tables on the show/hide worksheet, which is how the FIRST show/hide design carried it. ShowHideStore deletes those tables on sight now, so the routine walked a sheet holding nothing it could read and raised 91 on every geobase import. It went on 2026-08-14 with its one caller.

WHERE THE STATE OF THE GEOBASE LIVES

Two hidden names on the geo worksheet hold two separate facts. RNG_GeoUpdated answers whether the geobase carries data and reads "empty" or "updated". RNG_GeoTranslated is a Boolean and answers whether the table headers carry translated labels. A workbook built before this split holds one combined string; the first read moves it to the two names and writes them back.

WHERE THE FIVE LEVEL NAMES LIVE

RNG_ADM1NAME through RNG_ADM4NAME and RNG_HFNAME are workbook-scoped hidden names. CrossTable writes "= RNG_ADM1NAME" as a worksheet formula on the spatial analysis sheet, and a workbook-scoped name resolves there. A geo sheet built before this change still carries them as cells; the factory reads each cell once, writes the hidden name and moves on. RNG_PastingGeoCol stays a real cell, because DesignerPreparation uses it as a paste anchor.

WHO OWNS RNG_MetaLang

This class reads RNG_MetaLang in ExportToWkb and writes it nowhere. The writers are InitTransfer, which stamps the meta language picked on the designer's Main sheet onto the designer geo sheet just before the export, and the geobase import round trip.

Depends on: BetterArray, DropdownLists, HiddenNames, LLdictionary, Passwords

Factory

Create #

create

Create a new LLGeo instance from a geobase worksheet.

Signature:

Public Function Create(ByVal sh As Worksheet, _
                       Optional ByVal runChecks As Boolean = True) As LLGeo

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.

The two hidden name stores are built here, once each, and handed to the instance. Building one walks every Name of its container, and the class reads both stores on nearly every call.

A LINELIST OLDER THAN THE HIDDEN NAME STORE

A linelist generated before this version kept its geo metadata in plain named ranges, and the hidden name store is what replaced them. HiddenNames tracks a name only when its Visible property is False, so on such a file every one of the four names the checks demand reads as absent and the factory refuses a geobase that is entirely there. That is what the other-linelist export meets when a user points it at an old file.

So the checks are optional. They stay on for everything that builds or edits a linelist, where a missing name is a real fault worth stopping on, and the export walks turn them off: an export reads the geobase tables and writes them out, and the tables are what it needs. Whatever the file cannot answer fails at the read that wants it, which names the missing piece instead of one error at the door.

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.
  • runChecks: Optional Boolean. When False the tables, named ranges and hidden names are not checked before the instance is built. Defaults to True. It is NOT named checkRequirements: CheckRequirements is a Sub of this class, and a parameter of that name turns the call below into an index on a Boolean.

Returns: LLGeo. A fully initialised LLGeo instance.

Throws:

  • ProjectError.ObjectNotInitialized When the worksheet is Nothing.
  • ProjectError.ElementNotFound When a required table, hidden name or named range is missing and runChecks is True.
Level Names

GeoNames #

geo-names

Translated display name for a raw admin column name.

Signature:

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

Converts a raw admin column name (such as "adm1_name" or "hf_name") to its stored display label. An unknown key comes back unchanged, and so does a level whose label has never been written.

Parameters:

  • admname: String. Raw column name to translate.

Returns: String. Display label, or the raw name when no label is stored.

GeoBaseName #

geo-base-name

The name of the geobase the worksheet was filled from.

Signature:

Public Property Get GeoBaseName() As String

Import stores the source file name in RNG_GeoName. LLExporter reads this when it builds the file name of an exported geobase.

Returns: String. The stored geobase name, or an empty string.

GeoLevel

GeoLevel #

geo-level

List of values at a given admin level.

Signature:

Public 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 turned off while the filtering runs.

An empty geobase answers an empty list, and it answers before any handler is armed. Every other failure is raised to the caller, and the AutoFilter of the table is dropped on the way out whichever way the call ends.

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: a single value for level 2, a 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 the wrong shape or length.
  • ProjectError.ErrorUnexpectedState When the ListObject has no data.
  • ProjectError.ElementNotFound When a geo column is missing from the table.
Translate

TryTranslate #

try-translate

Translate the headers, and carry on when the sheet will not take it

Signature:

Private Function TryTranslate(ByVal rawNames As Boolean, _
                              ByRef wasTranslated As Boolean) As Boolean

Applies or reverts translated display labels on all geobase table headers (admin1 through admin4 and health facility). When rawNames is False, headers take the labels stored for the five levels. When True, headers go back to the raw column names. The method exits when the headers already carry what is asked for, and it refreshes the level labels from T_NAMES only once it knows it has work to do.

The admin table headers are written one array per table. The health facility headers are resolved by name, because that table carries its levels in an order the class does not fix.

Translate writes through HiddenNames and into the header rows of the admin tables. Two things stop it on a real file: a linelist older than the hidden name store, whose geo names are visible and therefore invisible to the store, and a source workbook opened read-only, which an export walk always does.

Neither is a reason to lose a geobase. The refusal is recorded in words and written to the linelist''s own log, and the caller carries on. THE READ OF THE FLAG IS INSIDE THE GUARD, AND HAS TO BE

IsTranslated looks like a read and is not. EnsureFlagsCache, underneath it, writes the flag back when the geo sheet does not already carry it -- EnsureName then two SetValue calls, one of them onto RNG_GeoUpdated. On a linelist older than the hidden name store that name is present and VISIBLE, the store refuses to see it, and the write raises. So the refusal used to land one line above the guard and escape it. Both the read and the translate sit inside now.

Parameters:

  • rawNames: Optional Boolean. True to revert to raw names, False to apply the stored labels. Defaults to False.
  • rawNames: Boolean. True to put the raw names back, False for the labels.
  • wasTranslated: Boolean. Receives the state the headers were in, and False whenever it could not be established -- which is what the restore paths want, since nothing was changed and nothing needs putting back.

Returns: Boolean. True when the headers were changed, False when they were left.

Clear

Clear #

clear

Clear all geobase data.

Signature:

Public Sub Clear()

Puts the raw column names back on the table headers, empties the data body of every ListObject on the geobase worksheet, and sets the data flag to "empty".

The header revert is what lets a caller clear and stop. EventsDesignerAdvanced calls this on its own, and a header left reading "Province" makes every column lookup in the class raise afterwards.

Values go and formats stay: the population columns keep their number format and the validation of every column survives an import.

ClearHistoric #

clear-historic

Clear historic entries.

Signature:

Public Sub ClearHistoric(Optional ByVal scope As Byte = GeoScopeBoth)

Empties the data body of the historic ListObjects named by the scope.

Parameters:

  • scope: Optional Byte. GeoScope value: GeoScopeAdmin (0) for T_HISTOGEO, GeoScopeHF (1) for T_HISTOHF, GeoScopeBoth (3) for both. Defaults to GeoScopeBoth.
Import

Import #

import

Import geobase data from an external workbook.

Signature:

Public 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 rewritten over the whole data column. When histoOnly is True only historic tables are refreshed and the main admin/HF data is left intact.

A source workbook carrying a Metadata sheet also carries the hidden names ExportToFile wrote there, including the language code, and those are read back before the flags are set. The geobase is marked as carrying data only when the ADM1 table came across, so a workbook holding none of the expected sheets leaves the flag alone.

Parameters:

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

ExportToFile #

export-to-file

Export geobase tables to a workbook as flat data.

Signature:

Public 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 go back to raw names before the export and take their labels again afterwards, and they do so whichever way the export ends. HiddenNames entries of the geo worksheet are written onto the Metadata sheet, and Import reads them back from there. 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.

LastFailure #

last-failure

What the last export of this geobase failed on

Signature:

Public Property Get LastFailure() As String

ExportToFile raises, and the caller reads a description that names the method and nothing else, because the error crosses out of this class. The step, the number, the source and the text are kept here before the raise, so the caller can log what actually refused. ExportToFile empties it on entry.

Returns: String. The step, error number, source and text, or vbNullString.

ExportToWkb #

export-to-wkb

Export the geobase worksheet structure to a workbook.

Signature:

Public 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: the linelist name, the setup name, the language, the timestamp, the platform the build ran on and the version of the designer that ran it. RNG_PastingGeoCol is exported as a cell-based named range. The hidden names of the geo worksheet go with the sheet, and the five workbook-scoped level labels are written into the target workbook so CrossTable's spatial dropdown resolves them there. Raises 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

Update linelist geo column headers and dropdowns.

Signature:

Public Sub Update(ByVal pass As Passwords)

Walks the HList worksheets of the parent workbook, finds the columns controlled by geo1 through geo4 and hf, and writes the level labels into their header cells and into the matching cells of the print sheet. Then refreshes the admin1 dropdown list on the __dropdown_lists sheet.

A sheet the walk cannot finish leaves its name in SkippedSheets and the walk carries on with the next one. Every sheet that is unprotected is protected again, whichever way its own pass ends.

Parameters:

  • pass: Passwords. Password manager for sheet protection and unprotection.

SkippedSheets #

skipped-sheets

The sheets the last Update could not finish.

Signature:

Public Property Get SkippedSheets() As BetterArray

Holds the name of every HList worksheet Update left part way through, which today means a sheet with no table_name hidden name, a sheet whose START cell cannot be resolved, and a sheet with no print partner. An empty list means every HList sheet was updated in full.

Returns: BetterArray. Worksheet names, empty when Update has not run.

UpdateDict #

update-dict

Update dictionary labels for geo-related variables.

Signature:

Public Sub UpdateDict(ByVal dict As LLdictionary)

Scans the dictionary control column for geo1 through geo4 and hf entries and overwrites the corresponding main label cells with the level labels of the geobase. This keeps the dictionary in step after a geobase import or a translation change.

Parameters:

  • dict: LLdictionary. The linelist dictionary whose labels should be updated.
Historic

UpdateHistoric #

update-historic

Add a new entry to the historic data.

Signature:

Public Sub UpdateHistoric(ByVal newValue As String, _
                           Optional ByVal scope As Byte = GeoScopeAdmin)

Appends a value to the appropriate historic ListObject (T_HISTOGEO or T_HISTOHF), grows the table to include the new row, then removes duplicates and sorts the table as it now stands. The range is re-read after the resize, so the row just written is de-duplicated and placed with the rest.

Parameters:

  • newValue: String. The value to add to the historic list.
  • scope: Optional Byte. GeoScope value: GeoScopeAdmin (0) for T_HISTOGEO, GeoScopeHF (1) for T_HISTOHF. Defaults to GeoScopeAdmin.
AdminCode

AdminCode #

admin-code

Resolve the admin code from a level label.

Signature:

Public Function AdminCode(ByVal selectedAdmin As String) As String

Compares the supplied label against the label of each admin level and returns the matching raw code ("adm1" through "adm4").

A label that matches no level answers an empty string. It used to answer "adm1", which reads on the sheet as a deliberate choice of the first level. Three states reach this arm and none of them is that choice: a freshly generated table still carrying its "select a level" prompt in the dropdown cell, a dropdown a user cleared with Delete, and a geobase re-imported under labels the cell never saw. The two call sites in EventLinelist that rewrite formulas refuse an empty answer outright, and the two that read fall back to PREVIOUS_ADM_, the cell recording the level those formulas stand on.

Parameters:

  • selectedAdmin: String. The level label to resolve.

Returns: String. Admin code ("adm1", "adm2", "adm3", or "adm4"), or an empty string when the label names no level.

HasNoData #

has-no-data

Whether the geobase has no imported data.

Signature:

Public Property Get HasNoData() As Boolean

Answers True when the data flag reads "empty", which is the state a geobase is in before its first import and after Clear. The flag says nothing about the headers, so an export that reverts and re-applies the headers leaves this answer alone.

Returns: Boolean. True when the geobase is empty.

Internal members (not exported)

Factory

Seal #

seal

Seal the instance against further setup writes.

Signature:

Public Sub Seal()

Marks the instance as sealed so setup-only setters raise if called again. Called by the factory after the last setup write.

GuardNotSealed #

Guard a setup-only setter against post-seal writes.

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

Parameters:

Wksh #

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 holds for the lifetime of the object.

Returns: Worksheet. The geobase worksheet.

Wksh #

wksh-set

Set the geobase worksheet.

Signature:

Public Property Set Wksh(ByVal sh As Worksheet)

Assigns the internal worksheet reference. Called from the factory Create method after validation, and only from there.

Parameters:

GeoStore #

geo-store-set

Set the worksheet hidden name store.

Signature:

Public Property Set GeoStore(ByVal store As HiddenNames)

Setup-only setter. The factory builds the store while it validates the worksheet and hands the same instance over, so the Names collection is walked once per Create.

Parameters:

LevelStore #

level-store-set

Set the workbook hidden name store.

Signature:

Public Property Set LevelStore(ByVal store As HiddenNames)

Setup-only setter. Holds the store over the workbook that owns the geobase worksheet, which is where the five translated level labels live.

Parameters:

Hidden Names Stores

GeoHiddenStore #

geo-hidden-store

HiddenNames manager for the geo worksheet.

Signature:

Private Function GeoHiddenStore() As HiddenNames

Returns the store the factory built. Builds one on demand when the instance was made another way. It holds RNG_GeoUpdated, RNG_GeoTranslated, RNG_GeoName, RNG_MetaLang and RNG_GeoLangCode.

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

LevelNameStore #

level-name-store

HiddenNames manager for the workbook holding the geo worksheet.

Signature:

Private Function LevelNameStore() As HiddenNames

Returns the store the factory built. The five level labels are workbook-scoped because CrossTable resolves them from a worksheet formula on the spatial analysis sheet.

Returns: HiddenNames. The workbook-level hidden names manager.

Geobase Flags

EnsureFlagsCache #

ensure-flags-cache

Read the two geobase flags once per instance.

Signature:

Private Sub EnsureFlagsCache()

Reads RNG_GeoUpdated and RNG_GeoTranslated into the instance. A geo sheet written before the two facts were split carries one combined string in RNG_GeoUpdated, such as "updated, translated". That string is parsed once here, the two clean values are written back, and no reader parses a string again.

ResetFlagsCache #

reset-flags-cache

Drop the cached flags so the next read goes to the store.

Signature:

Private Sub ResetFlagsCache()

Called after anything that writes the hidden names behind the class's back, which today is the name import Import runs off a geobase Metadata sheet.

GeoUpdatedFlag #

geo-updated-flag

The data flag of the geobase.

Signature:

Private Function GeoUpdatedFlag() As String

Returns: String. "empty" or "updated".

SetGeoUpdatedFlag #

set-geo-updated-flag

Write the data flag and keep the cache in step.

Signature:

Private Sub SetGeoUpdatedFlag(ByVal flagValue As String)

Parameters:

IsTranslated #

is-translated

Whether the table headers carry translated labels.

Signature:

Private Function IsTranslated() As Boolean

Returns: Boolean. True when the headers are translated.

SetTranslatedFlag #

set-translated-flag

Write the translation flag and keep the cache in step.

Signature:

Private Sub SetTranslatedFlag(ByVal flagValue As Boolean)

Parameters:

Table Names

TableNames #

table-names

Ordered list of all geobase table names.

Signature:

Private Property Get TableNames() As BetterArray

Returns the eight canonical ListObject names of 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, and the lower bound is 0, which is what GeoColumnIndex and GeoLevel index against. The array is built once per instance and handed back as it stands, because GeoColumnIndex asks for it on every column resolution.

Returns: BetterArray. Ordered list of table name strings.

TableNameAt #

table-name-at

One geobase table name by position.

Signature:

Private Function TableNameAt(ByVal position As Long) As String

Parameters:

Returns: String. The ListObject name at that position.

Level Names

LevelRawName #

level-raw-name

The raw column name of one level.

Signature:

Private Function LevelRawName(ByVal position As Long) As String

Parameters:

Returns: String. "adm1_name" to "adm4_name", or "hf_name".

LevelNameId #

level-name-id

The hidden name holding the label of one level.

Signature:

Private Function LevelNameId(ByVal rawName As String) As String

Parameters:

Returns: String. The hidden name identifier, or an empty string for an unknown level.

EnsureNamesCache #

ensure-names-cache

Ensure the five level labels are held on the instance.

Signature:

Private Sub EnsureNamesCache()

Reads the five workbook-scoped hidden names into five String fields. A level with no stored label answers its own raw column name, which is what every reader falls back to.

ReadLevelName #

read-level-name

Read one level label from the workbook store.

Signature:

Private Function ReadLevelName(ByVal rawName As String) As String

Parameters:

Returns: String. The stored label, or the raw name when nothing is stored.

WriteLevelName #

write-level-name

Write one level label into the workbook store.

Signature:

Private Sub WriteLevelName(ByVal rawName As String, ByVal label As String)

Parameters:

ResetNamesCache #

reset-names-cache

Reset the level label cache.

Signature:

Private Sub ResetNamesCache()

Marks the cache as unloaded so the next GeoNames access rebuilds it from the store. Called after anything that changes translation state.

UpdateLevelNames #

update-level-names

Refresh the five level labels from T_NAMES.

Signature:

Private Sub UpdateLevelNames()

Reads the current language code from RNG_GeoLangCode, finds the matching column in T_NAMES, and writes each level label into its workbook-scoped hidden name. The whole T_NAMES body is read in one crossing and all five levels are resolved in one pass over it. Exits when no language code is set or when T_NAMES lacks the level column or the language column.

Column Helpers

ColumnIndex #

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.

Every option of the search is pinned. Range.Find inherits LookIn, SearchOrder and SearchDirection from the last search of the Excel session, including one the user ran from the Find dialog, and a prior LookIn:=xlComments makes every column lookup in this class miss.

Parameters:

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

GeoColumnIndex #

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. When the headers carry translated labels the column name is converted first. Raises when the column cannot be found in the table header.

Parameters:

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

Throws:

HFColumnIndex #

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. When the headers carry translated labels the column name is converted first. Raises when the column cannot be found.

Parameters:

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

Throws:

HFColumnIndexOrMissing #

hf-column-index-or-missing

Resolve a column of T_HF, answering -1 when it has none.

Signature:

Private Function HFColumnIndexOrMissing(ByVal colName As String) As Long

The health facility table of a production geobase carries four levels and the one of a test fixture can carry fewer, so the header translation walks the levels and skips the ones the table has no column for.

Parameters:

Returns: Long. 1-based column index within T_HF, or -1 when absent.

CellText #

cell-text

Read one variant as text, answering an empty string for an error value.

Signature:

Private Function CellText(ByVal cellValue As Variant) As String

CStr on a cell holding #N/A raises 13. Every read of a cell value in this class goes through here.

Parameters:

Returns: String. The value as text, or an empty string when it is an error.

RangeValues #

range-values

Read a range into a two-dimensional Variant array in one crossing.

Signature:

Private Function RangeValues(ByVal rng As Range) As Variant

Range.Value answers a scalar for a single cell and a 2-D array for anything larger, so a single cell is wrapped into a 1x1 array here and every caller reads one shape.

Parameters:

Returns: Variant. A two-dimensional array of the values.

UniqueRangeValue #

unique-range-value

Extract unique values from a range into a BetterArray.

Signature:

Private Function UniqueRangeValue(ByVal rng As Range) As BetterArray

Reads one Variant array per area of the range and dedupes in memory against a keyed Collection, then pushes the values in encounter order. The range handed in is the visible part of a filtered column, which on a large geobase is tens of thousands of cells.

WHY A COLLECTION AND NOT A STRING INDEX

The index used to be one long string with every value already seen in it, and every cell was an InStr over the whole of it. That is quadratic in the number of distinct values, and the string was rebuilt on every hit, so a level with a few thousand children cost far more than the filter that produced it. A Collection key is hashed, so the test is flat however many values arrive. Dictionary is missing on Mac Excel; a keyed Collection is the replacement this project uses.

The dedupe is now CASE INSENSITIVE, because Collection keys are. That follows the AutoFilter above it, which matched these rows case insensitively in the first place, so two spellings of one place name were already being treated as one by the time they got here.

Parameters:

Returns: BetterArray. Deduplicated values in encounter order.

Level memo

LevelMemoKey #

level-memo-key

The identity of one GeoLevel answer.

Signature:

Private Function LevelMemoKey(ByVal adminLevel As Byte, ByVal geoType As Byte, _
                              ByRef adminNames As Variant) As String

Scope and level, then the parents that narrow it. The parents arrive as three shapes -- nothing for admin 1, a scalar for admin 2, a BetterArray below that -- and each becomes text here. A parent carrying the separator itself would read as two parents, so the separator is a character no place name holds.

Parameters:

Returns: String. The key of that answer.

LevelMemoRead #

level-memo-read

The held answer for one key, as a list of its own.

Signature:

Private Function LevelMemoRead(ByVal key As String) As BetterArray

A fresh BetterArray every time, filled from the items held under the key. Two reasons it is not the held object itself: VarWriter clears and refills what GeoLevel hands it while it writes the geo1 column, so one caller would empty the memo for every other; and a caller must not be able to tell a hit from a miss. Setting Items on a new BetterArray adopts the bounds of what is stored, so a hit answers the same shape the filter would have built.

Parameters:

Returns: BetterArray. The held answer, or Nothing when the key is not held.

LevelMemoWrite #

level-memo-write

Hold one answer under its key.

Signature:

Private Sub LevelMemoWrite(ByVal key As String, ByVal values As BetterArray)

The items are held rather than the BetterArray, for the reason LevelMemoRead gives. An empty answer is not held: an empty list has no one array shape to store, and a level with no children at all is a geobase fault rather than something a user meets on the way down a column.

A full memo is emptied whole. Dropping the oldest entry would need a second structure to carry the order, and the cap is a guard against a session that never ends rather than a working limit.

Parameters:

ResetLevelMemo #

reset-level-memo

Drop every held level answer.

Signature:

Private Sub ResetLevelMemo()

HFRange #

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:

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

ClearListFilter #

clear-list-filter

Drop the AutoFilter of a table when it carries one.

Signature:

Private Sub ClearListFilter(ByVal Lo As ListObject)

Parameters:

GeoLevel

GuardLevelNames #

guard-level-names

Validate the parent selections handed to GeoLevel.

Signature:

Private Sub GuardLevelNames(ByVal adminLevel As Byte, ByRef adminNames As Variant)

Admin 1 needs no parent. Admin 2 needs the name of one admin 1 and takes any scalar, because two call sites in EventLinelist hand over a raw cell value: an empty cell reads as Empty and a geobase whose admin 1 labels are numeric codes reads as Double. Admin 3 and admin 4 need a BetterArray of the parent names, one entry per level above them, with 1 as its lower bound.

Parameters:

Throws:

Translate

ReportTranslateRefusal #

report-translate-refusal

Put in words, in lastFailure, that the headers were left as they were

Signature:

Private Sub ReportTranslateRefusal(ByVal rawNames As Boolean, _
                                   ByVal errNumber As Long, _
                                   ByVal errSource As String, _
                                   ByVal errMessage As String)

This class does not reach a log, and it must not start: it is hand-exported into the designer, so a dependency added here has to be carried by hand into another workbook. lastFailure is how it already talks to whoever called it, and the caller is the one holding a log.

LLExporter.ExportGeo reads this after a geobase export that WORKED, and writes the warning to the linelist''s own log from there.

Parameters:

TranslateHFHeaders #

translate-hf-headers

Write the level headers of T_HF, each one resolved by name.

Signature:

Private Sub TranslateHFHeaders(ByVal rawNames As Boolean)

The health facility table holds the level columns in an order that is not fixed and can carry a column this loop has nothing for. Each header is found by the label it holds now, so the two directions are symmetric and an extra column in the table shifts nothing.

Parameters:

Clear

ClearHistoricTable #

clear-historic-table

Empty the data body of one historic table.

Signature:

Private Sub ClearHistoricTable(ByVal loName As String)

Parameters:

Import

ImportOneTable #

import-one-table

Copy one source worksheet into its geobase table.

Signature:

Private Sub ImportOneTable(ByVal impsh As Worksheet)

The first row of the used range of the source is its header row, and the data runs from the row under it to the last row of that used range. A source sheet whose first row is blank therefore imports every record it holds.

The destination row numbers come from the header row of the ListObject, so a table that does not start on row 1 lands in the right place.

The concat column is written as a formula, calculated, and then replaced by the values it answered. The geo worksheet therefore carries no live formula once an import has run. A table filled by hand afterwards gets no concat for the rows added, because a column of constants is no longer a calculated column.

Parameters:

ConcatFormulaFor #

concat-formula-for

Build the concat formula of one geobase table.

Signature:

Private Function ConcatFormulaFor(ByVal Lo As ListObject) As String

The concat column of a table joins the level columns above it with a pipe, so a filtered lookup can match one full path. A table with no concat column answers an empty string.

Parameters:

Returns: String. The formula for the first data row, or an empty string.

Export

ExportOneTable #

export-one-table

Write one geobase table onto a sheet, a column at a time.

Signature:

Private Sub ExportOneTable(ByVal Lo As ListObject, _
                            ByVal outsh As Worksheet, _
                            ByRef atStep As String)

The header row goes out with the data, because a ListColumn range carries its header, and Import reads the first used row of the sheet as the header row. The destination starts at A1 and each column lands under its own position in the table, so the sheet comes out in the order of the table. BetterArray sizes the destination from what it read, so no row count is worked out here, and an empty table writes its header row and nothing else. The step is written back to the caller on every column, so a failure names the column it stopped on rather than the table.

Parameters:

SheetNamesOf #

sheet-names-of

The worksheet names of a workbook with their used rows, on one line.

Signature:

Private Function SheetNamesOf(ByVal wkb As Workbook) As String

Written for the failure account of ExportToFile. Two questions a reader has when a sheet will not be added: whether the name was already taken, and how much had already been written into the workbook. Each sheet is reported as name(used rows), so both are answered by one line.

Parameters:

Returns: String. The sheets separated by commas, or a note when they cannot be read.

PlatformTag #

platform-tag

The platform the linelist was built on, on one tag

Signature:

Private Function PlatformTag() As String

A linelist is opened on machines that are not the one that built it, and the two platforms do not behave the same: a geobase export Windows accepts has refused on a Mac. The metadata sheet therefore names the platform of the build. The name and the bitness come from the compile constants, so they are the build that is running and cannot be misread. The Excel version comes from the application.

Returns: String. Something of the shape "mac-64 excel-16.90".

DesignerVersion #

designer-version

The version of the designer that built the linelist

Signature:

Private Function DesignerVersion() As String

The version is a workbook-scoped hidden name on the designer. DesignerPreparation.Prepare writes it from the RNG_Version value name the owner keeps on the Dev worksheet, so a designer that was never initialised with a version there does not carry it. That is the ordinary case rather than a fault, and it answers "(not found)". A name that is present but empty answers the same, because an empty cell on the metadata sheet says nothing to the person reading it.

Returns: String. The version, or "(not found)".

ExportLevelNames #

export-level-names

Write the five level labels into another workbook.

Signature:

Private Sub ExportLevelNames(ByVal outputWkb As Workbook)

The five labels are workbook-scoped hidden names, so a sheet copy leaves them behind. Each one is written by name into the target workbook.

Parameters:

Update

UpdateOneHList #

update-one-hlist

Write the level labels into the headers of one HList sheet.

Signature:

Private Sub UpdateOneHList(ByVal sh As Worksheet, ByVal wb As Workbook, _
                            ByVal shHn As HiddenNames, ByVal pass As Passwords)

The variable a column belongs to is the value of the _START cell of its column, and its control value is a hidden name built from that variable name. VarWriter writes that name as " -- control".

The label goes into the row above the data, on the sheet and on its print partner. A sheet with no print partner keeps its own headers updated and is reported through SkippedSheets.

Parameters:

LevelLabelForControl #

level-label-for-control

The level label a control value asks for.

Signature:

Private Function LevelLabelForControl(ByVal controlValue As String) As String

Parameters:

Returns: String. The label of the level, or an empty string for a control that is not geographic.

Internal Helpers

UpdateMetadata #

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, after clearing whatever was written there before. A workbook with no Metadata sheet and a sheet with no anchor are both ordinary states and answer with no work. A failure of the write itself is raised.

Validation

CheckRequirements #

check-requirements

Validate that the worksheet carries what the class reads.

Signature:

Private Sub CheckRequirements(ByVal sh As Worksheet, ByVal geoStore As HiddenNames, _
                               ByVal levelStore As HiddenNames)

Verifies the presence of all nine required ListObjects (T_ADM1 through T_ADM4, T_HF, T_NAMES, T_HISTOGEO, T_HISTOHF, T_METADATA), the four hidden names of the geo worksheet (RNG_GeoUpdated, RNG_GeoName, RNG_GeoLangCode, RNG_MetaLang), and the RNG_PastingGeoCol cell.

The five level labels are ensured. A Geo worksheet built before they moved into HiddenNames carries them as cells: the cell is read once, the hidden name is written with that value, and no reader touches the cell again. A worksheet with no such cell gets five empty names, which UpdateLevelNames fills on the next Translate.

Parameters:

Throws:

EnsureLevelNames #

ensure-level-names

Ensure the five level labels exist as workbook hidden names.

Signature:

Private Sub EnsureLevelNames(ByVal sh As Worksheet, ByVal levelStore As HiddenNames)

A level whose hidden name is missing takes the value of the cell-based named range of the same identifier when the worksheet still carries one, and an empty value otherwise. That read is the whole migration off the cells.

Parameters:

CellSeedValue #

cell-seed-value

Read the value of a cell-based named range, when there is one.

Signature:

Private Function CellSeedValue(ByVal sh As Worksheet, ByVal rngName As String) As String

Parameters:

Returns: String. The cell value, or an empty string when the range is absent or holds an error.

HiddenNameRequired #

hidden-name-required

Verify that a hidden name exists in the store.

Signature:

Private Sub HiddenNameRequired(ByVal store As HiddenNames, 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:

Throws:

RangeExists #

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:

Throws:

LoExists #

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:

Throws:

Error Handling

ThrowError #

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:

Used in (23 file(s))