EventSetup

Encapsulates workbook-level setup events and lazily-cached domain managers. This class is the single entry point for all workbook event handling in the setup workbook. It reacts to worksheet activation, change, and open events, manages setup rows (add, insert, delete, sort) through cached helpers (LLdictionary, LLChoices, LLExport, Analysis), and provides analysis table value lookups. All domain managers are created lazily on first use and cached for the lifetime of the instance.

Depends on: BetterArray, HiddenNames, Passwords, UpdatedValues, DropdownLists, LLdictionary, LLVariables, LLChoices, LLExport, Analysis, SetupTranslationsTable, CustomTable, ChoiceFormula, RibbonDev, ProjectError

Factory helpers

create #

Build a configured EventSetup instance bound to a host workbook.

Signature:

Public Function Create(ByVal hostBook As Workbook) As IEventSetup

Factory method using the predeclared-instance pattern. Creates a new EventSetup, calls Configure to bind it to the host workbook and reset all internal caches, then returns the initialised object through the IEventSetup interface. Raises an error when hostBook is Nothing because Configure performs the validation.

Parameters:

hostBook: Workbook. The workbook owning the setup sheets.

Returns: IEventSetup. Configured service instance.

Throws:

ProjectError.ObjectNotInitialized When hostBook is Nothing (raised by Configure).

IEventSetup implementation

configure #

Configure the service with its host workbook context.

Signature:

Public Sub Configure(ByVal hostBook As Workbook)

Direct implementations and thin delegation stubs for each IEventSetup interface member. Stores the host workbook reference and resets every lazily-cached domain manager so subsequent calls rebuild them from the new workbook. Must be called before any event handler or row operation. Raises an error when the supplied workbook is Nothing.

Parameters:

hostBook: Workbook. The workbook providing setup worksheets and infrastructure.

Throws:

ProjectError.ObjectNotInitialized When hostBook is Nothing.

on-workbook-open #

React to workbook open notifications.

Signature:

Private Sub IEventSetup_OnWorkbookOpen()

Switches update watcher tags to "yes" so tracked columns are marked as needing recalculation, resets the translation counter hidden name to zero, disables Application.FormatStaleValues to suppress recalculation prompts, and applies the full password protection matrix across all setup sheets. Silently skips each step when the required helper cannot be created.

on-sheet-activate #

Handle worksheet activation events.

Signature:

Private Sub IEventSetup_OnSheetActivate(ByVal sh As Worksheet)

Invalidates relevant ribbon controls (delete row, delete column, sort) so their enabled state reflects the newly active sheet. When the Analysis sheet becomes active, refreshes all analysis-related dropdown lists to ensure they are current.

Parameters:

sh: Worksheet. The worksheet that became active.

on-sheet-change #

Handle worksheet change events fired by Excel.

Signature:

Private Sub IEventSetup_OnSheetChange(ByVal sh As Worksheet, ByVal target As Range)

Forwards every change to the update watcher so tracked columns are flagged. When the Choices sheet changes, recalculates label formulas via HandleChoicesChange and exits. When the Analysis sheet changes, ensures the analysis cache tables are initialised, recalculates them, and applies choices and geo dropdown validation to the edited cell when it falls inside a relevant ListObject.

Parameters:

sh: Worksheet. The worksheet raising the change event.
target: Range. The edited cells.

update-analysis-dropdowns #

Refresh cached dropdowns and related analysis helpers.

Signature:

Private Sub UpdateAnalysisDropdowns(Optional ByVal forceUpdate As Boolean = False)

Conditionally rebuilds dropdown lists used by the Analysis sheet. When forceUpdate is True or when the update watcher reports that "control_details" or "variable_name" columns changed, refreshes geo, choices, and special- variable dropdowns. When "variable_type" or "variable_name" changed, refreshes the time-variable dropdown. Finally invalidates the LLVariables cache so subsequent lookups reflect the latest dictionary state.

Parameters:

forceUpdate: Optional Boolean. When True, forces a full refresh regardless of update tags. Defaults to False.

reset-caches #

Clear cached resources so they are lazily rebuilt on demand.

Signature:

Private Sub IEventSetup_ResetCaches()

recalculate-analysis #

Recalculate analysis tables in response to structural edits.

Signature:

Private Sub IEventSetup_RecalculateAnalysis()

reset-translation-counter #

Reset the translation update counter stored in hidden names.

Signature:

Private Sub IEventSetup_ResetTranslationCounter()

Row management and sorting

manage-rows #

Manage setup worksheet rows using cached helpers.

Signature:

Public Sub ManageRows(ByVal sheetName As String, Optional ByVal del As Boolean = False)

Delegates to the appropriate domain manager (dictionary, choices, analysis, exports, or translations) to add or remove rows. Each branch lazily creates its helper, unlocks the target sheet, invokes the manager, then re-locks the sheet in a cleanup block. For exports, the dictionary sheet is also unlocked because export row changes can propagate to dictionary columns. Silently exits when the sheet name does not match a known key.

Parameters:

sheetName: String. Worksheet name to process.
del: Optional Boolean. When True, removes rows instead of adding them. Defaults to False.

Throws:

ProjectError.SomethingWentWrong When the underlying domain manager operation fails.

insert-rows #

Insert rows relative to a selection for supported setup sheets.

Signature:

Public Sub InsertRows(ByVal sheetName As String, _
                      ByVal targetCell As Range, _
                      Optional ByVal insertShift As Boolean = False)

Delegates to the appropriate domain manager to insert rows at the position indicated by targetCell. Handles sheet protection around the operation. For the Analysis sheet, insertShift is always forced to True to preserve stacked tables. For exports, also unlocks the dictionary sheet because insert results may propagate to dictionary columns. Silently exits for unrecognised sheets.

Parameters:

sheetName: String. Worksheet name identifying which helper should process the insertion.
targetCell: Range. The worksheet selection whose height dictates the number of rows to insert.
insertShift: Optional Boolean. When True, worksheet rows are inserted to protect stacked tables. Defaults to False.

Throws:

ProjectError.SomethingWentWrong When the underlying insert operation fails.

delete-rows #

Delete rows relative to a selection for supported setup sheets.

Signature:

Public Sub DeleteRows(ByVal sheetName As String, _
                      ByVal targetCell As Range, _
                      Optional ByVal forceShift As Boolean = False)

Delegates to the appropriate domain manager to delete rows at the position indicated by targetCell. Handles sheet protection around the operation. For the Analysis sheet, forceShift is always forced to True. For exports, also unlocks the dictionary sheet because deletion results may propagate to dictionary columns. Silently exits for unrecognised sheets.

Parameters:

sheetName: String. Worksheet name identifying which helper should process the deletion.
targetCell: Range. The worksheet selection to remove.
forceShift: Optional Boolean. When True, worksheet rows are deleted to preserve stacked tables. Defaults to False.

Throws:

ProjectError.SomethingWentWrong When the underlying delete operation fails.

sort-tables #

Sort setup worksheets leveraging cached managers.

Signature:

Public Sub SortTables(ByVal sheetName As String)

Delegates to the appropriate domain manager to sort the worksheet tables. Handles sheet protection around the operation. For exports, also unlocks the dictionary sheet because sort results may propagate to dictionary columns. Silently exits for unrecognised sheets.

Parameters:

sheetName: String. Worksheet name to sort.

Throws:

ProjectError.SomethingWentWrong When the sort operation fails.

Internal members (not exported)

Factory helpers

self #

Current object instance cast to the interface.

Signature:

Public Property Get Self() As IEventSetup

Returns: IEventSetup. The current instance.

IEventSetup implementation

build-time-series-header #

Signature:

Private Function IEventSetup_BuildTimeSeriesHeader(ByVal timeVar As String, _
                                                   ByVal groupVar As String, _
                                                   ByVal sumLabel As String) As String

analysis-graph-value #

Signature:

Private Function IEventSetup_AnalysisGraphValue(ByVal graphTitle As String, _
                                                Optional ByVal graphCol As String = "Graph ID") As String

analysis-time-series-value #

Signature:

Private Function IEventSetup_AnalysisTimeSeriesValue(ByVal seriesTitle As String, _
                                                     Optional ByVal tsColumn As String = "Series ID") As String

spatio-temporal-spec-value #

Signature:

Private Function IEventSetup_SpatioTemporalSpecValue(ByVal sectionName As String, _
                                                     Optional ByVal specColumn As String = "N geo max") As String

Internal helpers

reset-internal-caches #

Clear all lazily-cached domain managers and table helpers.

Signature:

Private Sub ResetInternalCaches()

Sets every cached reference to Nothing so the next access triggers lazy creation. Called by Configure and by the ResetCaches interface method.

add-or-update-drop #

Add or update a dropdown list entry through the cached manager.

Signature:

Private Sub AddOrUpdateDrop(ByVal lst As BetterArray, ByVal dropName As String)

Adds a new dropdown list entry when the name does not already exist, or updates the existing entry. Ensures the dropdown manager is initialised before attempting the operation.

Parameters:

lst: BetterArray. The dropdown values to store.
dropName: String. Name of the dropdown list entry.

ensure-updated-values #

Lazily initialise the update watcher from the registry worksheet.

Signature:

Private Sub EnsureUpdatedValues()

Creates an IUpdatedValues instance from the "__updated" registry worksheet. Exits silently when the worksheet does not exist or creation fails, so callers must always check this.updater before use.

Lazily initialise the dropdown manager from the variables worksheet.

Signature:

Private Sub EnsureDropdownValues()

Creates an IDropdownLists instance from the "__variables" worksheet using the standard dropdown prefix. Exits silently when the worksheet does not exist or creation fails.

ensure-ribbon #

Lazily cache the ribbon instance.

Signature:

Private Sub EnsureRibbon()

ensure-dictionary #

Lazily create the dictionary manager.

Signature:

Private Function EnsureDictionary() As Boolean

Creates an LLdictionary instance from the "Dictionary" worksheet when not already cached. Returns True when the dictionary is available so callers can guard subsequent operations.

Returns: Boolean. True when the dictionary is available.

ensure-vars #

Lazily initialise the variables helper from the cached dictionary.

Signature:

Private Sub EnsureVars()

Creates an LLVariables instance from the cached dictionary reference. Requires EnsureDictionary to succeed first; exits silently when the dictionary is unavailable.

ensure-choices #

Lazily create the choices manager.

Signature:

Private Function EnsureChoices() As Boolean

Creates an LLChoices instance from the "Choices" worksheet when not already cached. Returns True when the choices helper is available.

Returns: Boolean. True when the choices helper is available.

ensure-analysis-manager #

Lazily create the analysis manager.

Signature:

Private Function EnsureAnalysisManager() As Boolean

Creates an Analysis instance from the "Analysis" worksheet when not already cached. Returns True when the analysis helper is available.

Returns: Boolean. True when the analysis helper is available.

ensure-exports #

Lazily create the exports manager.

Signature:

Private Function EnsureExports() As Boolean

Creates an LLExport instance from the "Exports" worksheet when not already cached. Returns True when the exports helper is available.

Returns: Boolean. True when the exports helper is available.

ensure-translations-manager #

Lazily create the translations table manager.

Signature:

Private Function EnsureTranslationsManager() As Boolean

Creates a SetupTranslationsTable instance from the translations ListObject. Disables display prompts on the newly created manager so operations proceed silently. Returns True when available.

Returns: Boolean. True when the translations manager is available.

ensure-analysis-table #

Ensure the cached analysis tables are created lazily and reused.

Signature:

Private Function EnsureAnalysisTable(ByRef tableRef As ICustomTable, _
                                     ByVal listName As String, _
                                     ByVal keyColumn As String) As Boolean

Creates a CustomTable wrapper around the named ListObject on the Analysis sheet. The ByRef tableRef parameter is populated on success and reused on subsequent calls. Returns True when the table is available.

Parameters:

tableRef: ByRef ICustomTable. Cached table reference, populated on first call and reused.
listName: String. Name of the ListObject on the Analysis worksheet.
keyColumn: String. Column to use as the table key.

Returns: Boolean. True when the table is available.

ensure-table-calculated #

Calculate the associated ListObject once the cache is primed.

Signature:

Private Sub EnsureTableCalculated(ByRef tableRef As ICustomTable, _
                                  ByVal listName As String, _
                                  ByVal keyColumn As String)

Ensures the analysis table is initialised via EnsureAnalysisTable, then recalculates its data range. Silently exits when the table cannot be created.

Parameters:

tableRef: ByRef ICustomTable. Cached table reference.
listName: String. Name of the ListObject on the Analysis worksheet.
keyColumn: String. Column to use as the table key.

analysis-list-object #

Retrieve a ListObject from the Analysis worksheet by name.

Signature:

Private Function AnalysisListObject(ByVal loName As String) As ListObject

Parameters:

loName: String. Name of the ListObject to retrieve.

Returns: ListObject. The ListObject, or Nothing when not found.

translations-list-object #

Retrieve the translations ListObject from the Translations worksheet.

Signature:

Private Function TranslationsListObject() As ListObject

Returns: ListObject. The translations ListObject, or Nothing when not found.

sheet-or-nothing #

Retrieve a worksheet from the host workbook or Nothing.

Signature:

Private Function SheetOrNothing(ByVal sheetName As String) As Worksheet

Parameters:

sheetName: String. Name of the worksheet to retrieve.

Returns: Worksheet. The worksheet, or Nothing when the workbook is unset or the sheet does not exist.

normalised-sheet-name #

Normalise a sheet name to lowercase for comparison.

Signature:

Private Function NormalisedSheetName(ByVal sheetName As String) As String

Parameters:

sheetName: String. Raw sheet name to normalise.

Returns: String. Lowercase trimmed sheet name.

password-manager #

Build a password helper from the passwords worksheet.

Signature:

Private Function PasswordManager() As IPasswords

Creates an IPasswords helper from the "__pass" worksheet. Returns Nothing when the sheet does not exist or creation fails.

Returns: IPasswords. The password helper, or Nothing when unavailable.

cached-password-manager #

Return the cached password helper, creating it on first call.

Signature:

Private Function CachedPasswordManager() As IPasswords

Lazily creates the IPasswords instance once and caches it for the lifetime of this EventSetup instance. Eliminates repeated Passwords.Create calls during BeginSheetUpdate / EndSheetUpdate cycles.

Returns: IPasswords. The cached password helper, or Nothing when unavailable.

begin-sheet-update #

Temporarily unlock the provided sheet and return the password helper.

Signature:

Private Function BeginSheetUpdate(ByVal sheetName As String) As IPasswords

Removes sheet protection using the cached password helper so callers can modify protected cells. Returns the IPasswords instance for later re-locking via EndSheetUpdate.

Parameters:

sheetName: String. Name of the sheet to unprotect.

Returns: IPasswords. The password helper for later re-locking.

end-sheet-update #

Restore sheet protection previously removed via BeginSheetUpdate.

Signature:

Private Sub EndSheetUpdate(ByVal pass As IPasswords, ByVal sheetName As String)

Parameters:

pass: IPasswords. Password helper returned by BeginSheetUpdate.
sheetName: String. Name of the sheet to re-protect.

calculate-analysis #

Recalculate analysis tables, optionally targeting a single table.

Signature:

Private Sub CalculateAnalysis(Optional ByVal target As Range = Nothing)

When a target range is provided, identifies which analysis table it belongs to via target.ListObject.Name and recalculates only that table (75% savings on per-cell edits). When no target is given (structural edits, post-import), recalculates all four tables.

Parameters:

target: Optional Range. The edited cell(s); Nothing means recalculate all.

handle-choices-change #

Recalculate label formulas when the Choices sheet changes.

Signature:

Private Sub HandleChoicesChange(ByVal target As Range)

Detects when the edited cell falls within the "translated label" column of the Tab_Choices table. When it does, recalculates the "label" column so dependent formulas stay in sync. Exits silently when the target is outside the relevant range.

Parameters:

target: Range. The edited cells on the Choices sheet.

reset-translation-counter-impl #

Reset the translation update counter stored in hidden names.

Signature:

Private Sub ResetTranslationCounter()

Resets the "_SetupTranslationsCounter" hidden name to zero on the registry worksheet. Uses HiddenNames to ensure the name exists before setting its value, so the operation is safe on fresh workbooks.

build-time-series-header-impl #

Compose the time-series header from variable labels and a separator.

Signature:

Private Function BuildTimeSeriesHeaderInternal(ByVal timeVar As String, _
                                               ByVal groupVar As String, _
                                               ByVal sumLabel As String) As String

Concatenates the summary label, time variable label, and group variable label using a horizontal-rule character as separator. Resolves variable names to their main labels via VariableLabel. Omits segments when the resolved label is empty.

Parameters:

timeVar: String. Time variable name.
groupVar: String. Group variable name.
sumLabel: String. Summary label prefix.

Returns: String. The composed header text.

analysis-graph-value-impl #

Retrieve a value from the graph time-series table.

Signature:

Private Function AnalysisGraphValueInternal(ByVal graphTitle As String, _
                                             ByVal graphCol As String) As String

Parameters:

graphTitle: String. Graph title used as the row key.
graphCol: String. Column to read.

Returns: String. The retrieved value, or empty.

analysis-time-series-value-impl #

Retrieve a value from the time-series data table.

Signature:

Private Function AnalysisTimeSeriesValueInternal(ByVal seriesTitle As String, _
                                                  ByVal tsColumn As String) As String

Parameters:

seriesTitle: String. Series title used as the row key.
tsColumn: String. Column to read.

Returns: String. The retrieved value, or empty.

spatio-temporal-spec-value-impl #

Retrieve a value from the spatio-temporal specifications table.

Signature:

Private Function SpatioTemporalSpecValueInternal(ByVal sectionName As String, _
                                                 ByVal specColumn As String) As String

Parameters:

sectionName: String. Section name used as the row key.
specColumn: String. Column to read.

Returns: String. The retrieved value, or empty.

variable-label #

Resolve the main label for a variable name via the cached dictionary.

Signature:

Private Function VariableLabel(ByVal varName As String) As String

Looks up "Main Label" in the cached LLVariables helper. Falls back to the raw variable name when the helper is unavailable or the lookup returns empty.

Parameters:

varName: String. Variable name to look up.

Returns: String. The main label, or the raw variable name as fallback.

safe-table-value #

Safely retrieve a value from a CustomTable, returning empty on error.

Signature:

Private Function SafeTableValue(ByVal tableRef As ICustomTable, _
                                ByVal columnName As String, _
                                ByVal keyName As String) As String

Parameters:

tableRef: ICustomTable. The table to query.
columnName: String. Column header to read.
keyName: String. Key value to look up.

Returns: String. The retrieved value, or empty on error.

analysis-table-value #

Shared lookup helper for analysis ListObjects.

Signature:

Private Function AnalysisTableValue(ByRef tableRef As ICustomTable, _
                                    ByVal listName As String, _
                                    ByVal keyColumn As String, _
                                    ByVal valueColumn As String, _
                                    ByVal keyValue As String) As String

Ensures the requested analysis table is initialised via EnsureAnalysisTable, then delegates to SafeTableValue for the actual retrieval.

Parameters:

tableRef: ByRef ICustomTable. Cached table reference.
listName: String. Name of the ListObject.
keyColumn: String. Column used as the table key.
valueColumn: String. Column to read.
keyValue: String. Key value to look up.

Returns: String. The retrieved value, or empty.

Apply geo-type dropdown validation to spatio-temporal analysis rows.

Signature:

Private Sub AddGeoDropdown(ByVal target As Range)

When the edited cell falls within the spatio-temporal table, clears the "geo" column cell and sets its dropdown validation to either "__hfonly_vars" or "__geoonly_vars" depending on the spatial type of the row. Temporarily unlocks the Analysis sheet for the modification.

Parameters:

target: Range. The edited cell on the Analysis sheet.

sync-geo-from-specs #

Propagate spatial type changes from specs to analysis table.

Signature:

Private Sub SyncGeoFromSpecs(ByVal target As Range)

When the user changes the "spatial type" column in the spatio-temporal specifications table, locates the matching section in the spatio-temporal analysis table and clears + revalidates the "geo" column dropdown to reflect the new type ("hf" or "geo"). Temporarily unlocks the Analysis sheet for the modification.

Parameters:

target: Range. The edited cell on the Analysis sheet.

Apply choices dropdown validation to graph series rows.

Signature:

Private Sub AddChoicesDropdown(ByVal target As Range)

When the user edits a "series title" cell in the graph table, resolves the column variable's control details, fetches category lists via GetCategories, and sets dropdown validation on the "choice" and "values or percentages" cells. Temporarily unlocks the Analysis sheet for the modification.

Parameters:

target: Range. The edited cell on the Analysis sheet.

get-categories #

Resolve choice categories for a graph column variable.

Signature:

Private Function GetCategories(ByVal columnVariable As String, _
                               ByVal seriesValue As String, _
                               ByRef choiceName As String) As BetterArray

When the control details start with "CHOICE_FORMULA", delegates to ChoiceFormula to parse the formula and retrieve categories. Otherwise fetches categories from the choices helper by name. Appends "Total" when the time-series table indicates addTotal is "yes" for the series.

Parameters:

columnVariable: String. The variable whose control details determine the category list.
seriesValue: String. The series title for looking up the addTotal flag.
choiceName: ByRef String. Populated with the resolved choice list name.

Returns: BetterArray. The list of categories.

choice-categories #

Return the BetterArray of categories for a named choice list.

Signature:

Private Function ChoiceCategories(ByVal listName As String) As BetterArray

Fetches categories from the cached LLChoices helper. Returns an empty BetterArray when the helper is unavailable or the list does not exist.

Parameters:

listName: String. Name of the choice list to retrieve.

Returns: BetterArray. The categories, or an empty BetterArray.

format-lock-cell #

Toggle font styling and lock state on a single cell.

Signature:

Private Sub FormatLockCell(ByVal cellRng As Range, Optional ByVal locked As Boolean = True)

Sets the font colour to blue and italic when locked, or black and normal when unlocked. Used to visually indicate whether a cell is user-editable.

Parameters:

cellRng: Range. The cell to format.
locked: Optional Boolean. Lock state to apply. Defaults to True.

throw-error #

Raise a ProjectError-aligned error from the current class.

Signature:

Private Sub ThrowError(ByVal errNumber As ProjectError, ByVal errorMessage As String)

Wrapper around Err.Raise that standardises the source to CLASSNAME, providing a consistent stack trace across all methods in this class.

Parameters:

errNumber: ProjectError. The error code to raise.
errorMessage: String. Human-readable description of the failure.

Throws:

ProjectError. Always raises the specified error.

Used in (6 file(s))

EventSetup

create #

configure #

on-workbook-open #

on-sheet-activate #

on-sheet-change #

update-analysis-dropdowns #

reset-caches #

recalculate-analysis #

reset-translation-counter #

manage-rows #

insert-rows #

delete-rows #

sort-tables #

Factory helpers

self #

IEventSetup implementation

build-time-series-header #

analysis-graph-value #

analysis-time-series-value #

spatio-temporal-spec-value #

Internal helpers

reset-internal-caches #

add-or-update-drop #

ensure-updated-values #

ensure-dropdown-values #

ensure-ribbon #

ensure-dictionary #

ensure-vars #

ensure-choices #

ensure-analysis-manager #

ensure-exports #

ensure-translations-manager #

ensure-analysis-table #

ensure-table-calculated #

analysis-list-object #

translations-list-object #

sheet-or-nothing #

normalised-sheet-name #

password-manager #

cached-password-manager #

begin-sheet-update #

end-sheet-update #

calculate-analysis #

handle-choices-change #

reset-translation-counter-impl #

build-time-series-header-impl #

analysis-graph-value-impl #

analysis-time-series-value-impl #

spatio-temporal-spec-value-impl #

variable-label #

safe-table-value #

analysis-table-value #

add-geo-dropdown #

sync-geo-from-specs #

add-choices-dropdown #

get-categories #

choice-categories #

format-lock-cell #

throw-error #