SetupErrors
Checks the setup sheets and publishes a report of what it finds. Create binds the workbook, Initialise sets the run up, Run walks every check, and Checkings answers the entries. The dictionary is checked for duplicate variables, missing labels, unknown controls and formulas that fail to parse; the choices for unused entries and missing labels; the exports for missing metadata, for an "Admin levels" cell the export cannot read, and for variables the dictionary has none of; and the translations for missing and duplicate labels. Entries are collected as Checking objects and printed to the __checkRep worksheet through CheckingOutput.
THE CHECKS FOLLOW THE BUILDER
Every check here answers what the linelist build will do with the same setup sheet. Three rules are copied from the class that builds them: a file-name template is parsed the way LLExport.ResolveTemplateChunk parses it, a formula is explained in the context it was validated in, and a choice name is matched without regard to case, the way LLChoices.Categories matches it at build time.
THE SHEETS ARE TIDIED FIRST
Each checker sorts and resizes the sheet it is about to read, so every check below reads a sheet already in shape. The column arrays CheckDictionary works from are therefore read AFTER the sort and the resize: the tidy-up moves rows, and an array taken before it points at rows that have moved.
THE REPORT SHEET IS WRITABLE
__checkRep sits outside the hidden list and outside the protection list, and Passwords returns early for it by name. PrintReport clears it and writes to it directly, which is what the setup ribbon's check button reaches.
Factory
Create #
create
Create and initialise a setup checker instance
Signature:
Public Function Create(ByVal hostBook As Workbook, _
Optional ByVal passwordService As Passwords, _
Optional ByVal dictionary As LLdictionary, _
Optional ByVal choices As LLChoices, _
Optional ByVal formulaStore As FormulaData, _
Optional ByVal transManager As SetupTranslationsTable, _
Optional ByVal stateScope As ApplicationState) As SetupErrors
Factory method on the predeclared instance. Creates a new SetupErrors, calls Initialise with the supplied workbook and optional collaborators, seals it, then returns the newly built instance.
Parameters:
hostBook: Workbook. The workbook containing setup data.passwordService: Optional Passwords. Sheet protection handler.dictionary: Optional LLdictionary. Dictionary data accessor.choices: Optional LLChoices. Choices data accessor.formulaStore: Optional FormulaData. Formula validation handler.transManager: Optional SetupTranslationsTable. Translations table accessor.stateScope: Optional ApplicationState. Application state guard.
Returns: SetupErrors. A fully initialised instance ready to run checks.
Throws:
- ProjectError.ObjectNotInitialized When hostBook is Nothing.
Seal #
seal
Close the instance against a second Initialise
Signature:
Public Sub Seal()
Called by the factory. A holder that could call Initialise again could swap the workbook between Run and Checkings and read results that belong to another file. LLdictionary, LLChoices, Passwords, TableSpecs, Checking, CustomTable, ChoiceFormula and DropdownLists all carry the same guard.
Initialisation
Initialise #
initialise
Bind the checker to a workbook and optional collaborators
Signature:
Public Sub Initialise(ByVal hostBook As Workbook, _
Optional ByVal passwordService As Passwords, _
Optional ByVal dictionary As LLdictionary, _
Optional ByVal choices As LLChoices, _
Optional ByVal formulaStore As FormulaData, _
Optional ByVal transManager As SetupTranslationsTable, _
Optional ByVal stateScope As ApplicationState)
Validates the workbook reference, stores it along with optional collaborators, then calls ConfigureDependencies to create any missing default instances and ResetCheckContainer to prepare the results array.
Parameters:
hostBook: Workbook. The workbook containing setup tables.passwordService: Optional Passwords. Sheet protection handler.dictionary: Optional LLdictionary. Dictionary data accessor.choices: Optional LLChoices. Choices data accessor.formulaStore: Optional FormulaData. Formula validation handler.transManager: Optional SetupTranslationsTable. Translations table accessor.stateScope: Optional ApplicationState. Application state guard.
Throws:
- ProjectError.ObjectNotInitialized When hostBook is Nothing.
- ProjectError.SomethingWentWrong When the instance is sealed.
Execution
Run #
run
Execute all setup checks and publish results
Signature:
Public Sub Run()
Suspends Excel UI via EnterBusyState, resets the check container, then runs each checker in sequence: CheckDictionary, CheckChoice, CheckExports, CheckAnalysis, CheckTranslations. On completion, publishes all collected Checking objects to the reporting worksheet via PrintReport. Restores Excel state even when an error occurs during the checking run. The handler is armed above EnterBusyState. ApplicationState.ApplyBusyState switches ScreenUpdating and DisplayAlerts off as its first two statements and raises afterwards if a later assignment fails, so a raise in there has to reach LeaveBusyState. Otherwise the user is left in an Excel session that no longer repaints.
Results
Checkings #
checkings
Collection of generated checking objects
Signature:
Public Property Get Checkings() As BetterArray
Returns a clone of the BetterArray holding the Checking instances produced by the most recent Run call, so a caller that clears it leaves this object's own results alone. Formulas.Varlists and LLChoices.Categories answer the same way. Raises when the checker has not been initialised.
Returns: BetterArray. A copy of the generated Checking instances.
Internal members (not exported)
Helper workflow
EnsureInitialised #
ensure-initialised
Ensure the checker has been initialised
Signature:
Private Sub EnsureInitialised()
Throws:
- ProjectError.ObjectNotInitialized When the workbook reference is Nothing.
GuardNotSealed #
guard-not-sealed
Reject a second Initialise on a sealed instance
Signature:
Private Sub GuardNotSealed(ByVal propName As String)
Parameters:
propName: String. Name of the routine being guarded.
Throws:
- ProjectError.SomethingWentWrong When the instance is sealed.
ConfigureDependencies #
configure-dependencies
Prepare collaborators when not provided
Signature:
Private Sub ConfigureDependencies()
Creates default instances for any optional collaborator that was not supplied during Initialise: Passwords, LLdictionary, LLChoices, FormulaData and SetupTranslationsTable. The __formula worksheet is resolved once, and only when the formula store has to be built.
ResetCheckContainer #
reset-check-container
Ensure the container for checkings exists and is empty
Signature:
Private Sub ResetCheckContainer()
ApplicationScope #
application-scope
Retrieve or create the application state scope
Signature:
Private Function ApplicationScope() As ApplicationState
Returns: ApplicationState. The cached or newly created scope.
EnterBusyState #
enter-busy-state
Suspend Excel UI features while running checks
Signature:
Private Sub EnterBusyState()
A scope that is already busy belongs to the caller who made it busy, so this object leaves it alone and LeaveBusyState will not restore it. The ownership flag is written BEFORE ApplyBusyState, because a failure inside that call still leaves settings to put back.
LeaveBusyState #
leave-busy-state
Restore Excel UI configuration
Signature:
Private Sub LeaveBusyState(Optional ByVal silent As Boolean = False)
Restores only when this object was the one that entered the busy state. ApplicationState.Restore puts the whole snapshot back and clears IsBusy every time it runs, so restoring a scope handed in by a caller would drop that caller's own busy block in the middle of its work.
Parameters:
silent: Optional Boolean. When True, suppresses errors during restore. Defaults to False.
ResolveWorksheet #
resolve-worksheet
Resolve a workbook worksheet by name
Signature:
Private Function ResolveWorksheet(ByVal sheetName As String) As Worksheet
Parameters:
sheetName: String. The worksheet name to look up.
Returns: Worksheet. The resolved worksheet.
Throws:
- ProjectError.ElementNotFound When the worksheet does not exist.
ValidateWorkbook #
validate-workbook
Validate the incoming workbook reference
Signature:
Private Sub ValidateWorkbook(ByVal hostBook As Workbook)
Parameters:
hostBook: Workbook. The workbook to validate.
Throws:
- ProjectError.ObjectNotInitialized When hostBook is Nothing.
Column readers
CellText #
cell-text
Read one cell value as text
Signature:
Private Function CellText(ByVal cellValue As Variant) As String
A cell holding an error value raises on CStr, and so does a Null. Every read in this class goes through here.
Parameters:
cellValue: Variant. The raw cell value.
Returns: String. The text, or an empty string when there is none.
ColumnText #
column-text
Read a one-column Range into a 1-based array of text
Signature:
Private Function ColumnText(ByVal columnRng As Range, ByVal rowCount As Long) As Variant
A missing column answers an array of empty strings of the requested length, so the loop that reads it can index it freely. One worksheet read replaces one read per row.
Parameters:
columnRng: Range. The column to read, or Nothing.rowCount: Long. Number of rows the caller walks.
Returns: Variant. A String array indexed 1 to rowCount.
ReadTableColumn #
read-table-column
Read one named column of a table into an array
Signature:
Private Function ReadTableColumn(ByVal tabl As CustomTable, _
ByVal colName As String, _
ByVal rowCount As Long, _
ByRef found As Boolean) As Variant
The lookup is strict, so a request for "Control" can never answer with "Control Details" and a request for "Export 1" can never answer with "Export 10".
Parameters:
tabl: CustomTable. The table to read.colName: String. The exact column header.rowCount: Long. Number of rows the caller walks.found: Boolean. Set to True when the column exists.
Returns: Variant. A String array indexed 1 to rowCount.
BuildIndex #
build-index
Build a delimited lookup index from a list of names
Signature:
Private Function BuildIndex(ByVal values As BetterArray) As String
The separator sits on both sides of every entry, so InStr on separator & name & separator can never match part of a longer name. This replaces a linear scan that copied a whole BetterArray on each call.
Parameters:
values: BetterArray. The names to index.
Returns: String. The delimited index.
IndexHolds #
index-holds
Test whether a delimited index holds one name
Signature:
Private Function IndexHolds(ByVal indexValue As String, ByVal nameValue As String) As Boolean
The comparison ignores case, because LLChoices.Categories reads the Choices sheet with AutoFilter criteria and those ignore case. The build therefore finds a list whose name differs only in case, and the checker follows it.
Parameters:
indexValue: String. A delimited index built by BuildIndex.name: String. The name to look for.
Returns: Boolean. True when the name is in the index.
CountNames #
count-names
Count how many times each name appears
Signature:
Private Function CountNames(ByVal values As Variant, ByVal rowCount As Long) As Collection
One pass over the array replaces one WorksheetFunction.CountIf per row, each of which scanned the whole column inside Excel. A Collection matches its keys without regard to case, which is how CountIf compared too.
Parameters:
values: Variant. A 1-based array of names.rowCount: Long. Number of entries to read.
Returns: Collection. Counts keyed by name.
NameCount #
name-count
Read one count out of the counts built by CountNames
Signature:
Private Function NameCount(ByVal counts As Collection, ByVal nameValue As String) As Long
Parameters:
counts: Collection. Counts keyed by name.name: String. The name to read.
Returns: Long. The number of rows holding that name.
ExportIdentifierNumber #
export-identifier-number
Read the number out of one "Export Number" cell
Signature:
Private Function ExportIdentifierNumber(ByVal rawValue As Variant) As Long
LLExport writes that cell through ExportColumnName, so it holds "Export 1"
rather than "1", and LLExport.ParseExportIdentifier reads it back the same
way. A bare number is taken as well, because that is what a hand-typed row
holds.
Parameters:
rawValue: Variant. The value of the cell.
Returns: Long. The export number, or 0 when the text carries none.
ExportStatuses #
export-statuses
Status of every export row, keyed by the number the row carries
Signature:
Private Function ExportStatuses(ByVal expTab As CustomTable) As Collection
Built once so the dictionary loop can ask about an export number without searching the Exports table again for each one. A number appearing twice keeps the status of its first row.
Parameters:
expTab: CustomTable. The Exports table.
Returns: Collection. Statuses keyed by "k" & the export number.
ExportStatusOf #
export-status-of
Read the status of one export number
Signature:
Private Function ExportStatusOf(ByVal statuses As Collection, _
ByVal exportNumber As Long, _
ByRef statusValue As String) As Boolean
Parameters:
statuses: Collection. The statuses built by ExportStatuses.exportNumber: Long. The export number to read.statusValue: String. Receives the status, empty when there is no row.
Returns: Boolean. True when the Exports table holds a row for that number.
KnownControl #
known-control
Test whether a control name is one this build understands
Signature:
Private Function KnownControl(ByVal controlValue As String) As Boolean
Parameters:
controlValue: String. The control read from the dictionary.
Returns: Boolean. True for one of the ten known controls.
ContainsIgnoringCase #
contains-ignoring-case
Test membership of a BetterArray without regard to case
Signature:
Private Function ContainsIgnoringCase(ByVal values As BetterArray, ByVal target As String) As Boolean
BetterArray.Includes ends in a plain equality under binary compare, so a category written Yes in a CHOICE_FORMULA against yes on the Choices sheet reads as absent. The build matches the two, so the checker does too.
Parameters:
values: BetterArray. The values to search.target: String. The value to look for.
Returns: Boolean. True when the value is present in any case.
IsAdminLevelsCell #
is-admin-levels-cell
Test whether an "Admin levels" cell is written the way an export reads it
Signature:
Private Function IsAdminLevelsCell(ByVal levelsText As String) As Boolean
Mirrors LLExporter.AdminLevelOfChunk: the cell holds the admin levels the
export writes, joined with a plus and written admin1+admin2. Each chunk is
trimmed and read without regard to case, so Admin2 and admin2 both pass,
and the level runs from 1 to 4 because LLdictionary.AppendGeoLines writes four.
An empty cell passes: the export reads it as every level.
The exporter honours the cell only when the whole of it reads, so a cell this
reports is a cell the export ignores, and every geo column goes out of it.
Parameters:
levelsText: String. What the "Admin levels" cell of one export row holds.
Returns: Boolean. True when the export can honour the cell.
IsLiteralChunk #
is-literal-chunk
Test whether a file-name chunk is a quoted literal
Signature:
Private Function IsLiteralChunk(ByVal chunk As String) As Boolean
Mirrors LLExport.IsLiteralTemplateChunk: the chunk is a literal when it starts AND ends with the same quote character. Testing for a quote anywhere in the chunk reported a single-quoted label as a missing variable.
Parameters:
chunk: String. One trimmed piece of the file-name template.
Returns: Boolean. True when the chunk is a literal.
Core checking routines
CheckDictionary #
check-dictionary
Validate dictionary sheet for structure, duplicates, and formulas
Signature:
Private Sub CheckDictionary()
Unprotects the dictionary and exports sheets, tidies the dictionary table, then reads its ten columns once into arrays and walks the rows bottom-up checking for: duplicate variable names, short variable names, missing main labels, missing sheet names, unknown controls, choice formula mismatches, formula validity, min/max validation types, format/type consistency, and export column alignment. Collects all findings into a single Checking object and re-protects sheets on exit. The Choices sheet is only read here, through the choices index and the category lists, so it is left protected.
CheckChoice #
check-choice
Inspect choices sheet for unused or inconsistent entries
Signature:
Private Sub CheckChoice()
Unprotects the choices sheet, builds a delimited index of the choice names actually referenced in dictionary controls, then flags unused choices, missing list names, missing ordering values, and missing labels. The three choices columns are read by name, so the check no longer depends on the distance between them. Collects all findings into a single Checking object and re-protects on exit.
CheckExports #
check-exports
Audit exports sheet for missing metadata and dictionary alignment
Signature:
Private Sub CheckExports()
Iterates over each export row, checking active exports for missing label, password, metadata, format, filename, and header values. Also validates that the corresponding dictionary column exists and is not empty, checks password/identifier consistency, and validates filename variable references. Every value of a row is read from that row, and the Export Number the row carries is what names the export in the message and picks the dictionary column. The old shape read the status by row position and the password by Export Number, so the two only agreed while the numbers ran 1..N in row order.
CheckTranslations #
check-translations
Review translations table for missing and duplicate labels
Signature:
Private Sub CheckTranslations()
Unprotects the translations sheet, updates the translations manager from the registry, then queries for missing and duplicate labels. Collects findings into a single Checking object and re-protects on exit.
CheckAnalysis #
check-analysis
Run analysis table checks across all sections
Signature:
Private Sub CheckAnalysis()
Resolves the analysis worksheet, removes empty rows through TidyAnalysisSheet, reports a failure there as a Checking entry, then delegates to CheckTable for each of the seven analysis sections. Caches the worksheet and WorksheetFunction references and passes them to CheckTable to avoid repeated lookups.
TidyAnalysisSheet #
tidy-analysis-sheet
Remove the empty analysis rows before the checks read the sheet
Signature:
Private Function TidyAnalysisSheet(ByVal analysisSheet As Worksheet) As String
The sheet is unprotected, trimmed and protected again. When any of the three fails, the sheet is protected in the handler and the reason is handed back so the caller can report it. Passwords.UnProtect stays quiet when the stored key does not open the sheet, and the row removal that follows then raises on a sheet that is still locked, so this failure is a real one for a user.
Parameters:
analysisSheet: Worksheet. The analysis worksheet.
Returns: String. The failure description, or an empty string on success.
CheckTable #
Evaluate a specific analysis section for structural errors.
Signature:
Private Sub CheckTable(ByVal partName As String, _
ByVal analysisSheet As Worksheet, _
ByVal workFun As WorksheetFunction)
Parameters:
partName: String. The section display name (e.g. "Global summary").analysisSheet: Worksheet. The cached analysis worksheet.workFun: WorksheetFunction. The cached worksheet function object.
PrintReport #
print-report
Publish all collected checkings to the reporting worksheet
Signature:
Private Sub PrintReport()
Resolves the __checkRep worksheet, clears its content, creates a CheckingOutput helper, and prints all collected Checking objects. Installs the worksheet change handler for filtering support and activates the report sheet. The clear needs no unprotect: __checkRep is deliberately outside the protection matrix and CheckingOutput unprotects it before it writes anyway. Only the change handler is allowed to fail quietly, because it writes into the VBA project and many machines block that. Activating the report is what the user pressed the button for, so a failure there is reported.
Message helpers
FormatMessage #
format-message
Format an error message by replacing numbered placeholders
Signature:
Private Function FormatMessage(ByVal messageTag As String, _
Optional ByVal valueOne As String = vbNullString, _
Optional ByVal valueTwo As String = vbNullString, _
Optional ByVal valueThree As String = vbNullString, _
Optional ByVal valueFour As String = vbNullString) As String
Parameters:
messageTag: String. The message template with %1..%4 placeholders.valueOne: Optional String. Replacement for %1.valueTwo: Optional String. Replacement for %2.valueThree: Optional String. Replacement for %3.valueFour: Optional String. Replacement for %4.
Returns: String. The formatted message.
FormulaChecker #
formula-checker
The one formula parser a run reuses
Signature:
Private Function FormulaChecker() As Formulas
Built with New rather than through Formulas.Create, because the factory seals the instance and a sealed instance refuses SetupFormula. Reuse is what keeps the LLVariables cache warm: a fresh parser per formula read the whole Variable Name column again, up to three times per dictionary row.
Returns: Formulas. The parser bound to this run's dictionary and formula store.
FormulaMessage #
formula-message
Validate a formula string and return a formatted error when invalid
Signature:
Private Function FormulaMessage(ByVal formValue As String, _
ByVal messageTag As String, _
Optional ByVal valueOne As String = vbNullString, _
Optional ByVal valueTwo As String = vbNullString, _
Optional ByVal formulaType As String = CONTEXT_LINELIST) As String
The reason is read in the context the formula was validated in. Asking for the default context instead made a formula that is invalid as a linelist formula and valid as an analysis one report "The formula seems correct" at the end of the line saying it will fail.
Parameters:
formValue: String. The formula expression to validate.messageTag: String. The message template to use on failure.valueOne: Optional String. Replacement for %1.valueTwo: Optional String. Replacement for %2.formulaType: Optional String. Formula type ("linelist" or "analysis"). Defaults to "linelist".
Returns: String. The formatted error message, or empty when the formula is valid.
ReplacePlaceholder #
replace-placeholder
Replace a single placeholder token in a template string
Signature:
Private Function ReplacePlaceholder(ByVal template As String, _
ByVal placeholder As String, _
ByVal replacement As String) As String
Parameters:
template: String. The template containing the placeholder.placeholder: String. The token to replace (e.g. "%1").replacement: String. The value to substitute.
Returns: String. The template with the placeholder replaced.
ThrowError #
throw-error
Raise a ProjectError-based exception
Signature:
Private Sub ThrowError(ByVal errNumber As ProjectError, ByVal errorMessage 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:
errNumber: ProjectError. The error code to raise.errorMessage: String. Human-readable description of the failure.
Throws:
- ProjectError Always raises the specified error.
Used in (12 file(s))
- LLExporter.cls
- LLdictionary.cls
- CaseWhen.cls
- ChoiceFormula.cls
- EventSetup.cls
- SetupPreparation.cls
- EventSetupWorkbook.bas
- SetupHelpers.bas
- TestLLExporter.bas
- TestLLdictionary.bas
- TestCaseWhen.bas
- TestSetupErrors.bas