SetupPreparation
Orchestrates the preparation workflow for setup workbooks by registering dropdown list objects, initialising the updated values registry, applying data validations to setup tables (Dictionary, Exports, Analysis), and configuring hidden and protected sheets. Consumed by the setup entry point to prepare all worksheet assets before the workbook is ready for use. Uses the predeclared factory pattern through the ISetupPreparation interface.
Factory
create #
Create an initialised SetupPreparation instance
Signature:
Public Function Create(ByVal hostBook As Workbook) As ISetupPreparation
Factory method on the predeclared instance. Validates the workbook is not Nothing via Configure, then returns the new instance through the ISetupPreparation interface.
Parameters:
hostBook: Workbook. The workbook hosting setup worksheets.
Returns: ISetupPreparation. A fully initialised instance ready for use.
Throws:
- ProjectError.ObjectNotInitialized When hostBook is Nothing.
Public API
configure #
Bind the preparation helper to a workbook hosting setup worksheets
Signature:
Public Sub Configure(ByVal hostBook As Workbook)
Stores the workbook reference and resets all cached worksheet and manager objects to Nothing, forcing lazy re-initialisation on next access.
Parameters:
hostBook: Workbook. The workbook owning the setup assets.
Throws:
- ProjectError.ObjectNotInitialized When hostBook is Nothing.
prepare #
Execute the full preparation workflow
Signature:
Public Sub Prepare(ByRef manager As IDevelopment)
Runs the complete preparation sequence: registers all dropdown list objects, initialises the updated values registry, applies data validations to the Dictionary, Exports, and Analysis tables, then registers hidden and protected sheets via the supplied development manager.
Parameters:
manager: IDevelopment. Development manager providing hidden/protected sheet registration.
ensure-dropdowns #
Ensure the dropdown manager has registered all default dropdown lists
Signature:
Public Sub EnsureDropdowns()
Creates the dropdown manager if needed, then calls RegisterAllDropdowns to populate every standard dropdown list on the dropdown worksheet. Safe to call multiple times; the manager creation is a no-op when already initialised.
ensure-updated-registry #
Initialise the updated values registry and register watched tables
Signature:
Public Sub EnsureUpdatedRegistry()
Creates the update watcher if needed, clears any existing registry data, registers the watched setup sheets (Dictionary, Choices, Analysis, Exports), and resets all status tags to "yes" (the default ready state).
dropdowns #
Dropdown manager for external callers
Signature:
Public Property Get Dropdowns() As IDropdownLists
Returns the IDropdownLists manager bound to the dropdown worksheet. Lazily initialises the manager on first access if it was not already created by EnsureDropdowns or Prepare.
Returns: IDropdownLists. The dropdown manager instance.
updated-watcher #
Updated values watcher for external callers
Signature:
Public Property Get UpdatedWatcher() As IUpdatedValues
Returns the IUpdatedValues watcher bound to the registry worksheet. Lazily initialises the watcher on first access if it was not already created by EnsureUpdatedRegistry or Prepare.
Returns: IUpdatedValues. The update watcher instance.
host-workbook #
Workbook hosting setup assets
Signature:
Public Property Get HostWorkbook() As Workbook
Returns the cached workbook reference stored during Configure. Delegates to EnsureWorkbook, which raises an error when the instance has not been configured.
Returns: Workbook. The bound workbook.
Throws:
- ProjectError.ObjectNotInitialized When the instance has not been configured.
Internal members (not exported)
Factory
self #
Current object instance
Signature:
Public Property Get Self() As ISetupPreparation
Returns: ISetupPreparation. The current instance cast to the interface.
ISetupPreparation implementation
ISetupPreparation_Configure #
Signature:
Private Sub ISetupPreparation_Configure(ByVal hostBook As Workbook)
Delegated members satisfying the ISetupPreparation contract. See the corresponding Public members above for full documentation.
Preparation helpers
reset-caches #
Reset cached references after reconfiguring the helper
Signature:
Private Sub ResetCaches()
ensure-dropdown-manager #
Ensure the dropdown manager is initialised for the target workbook
Signature:
Private Sub EnsureDropdownManager()
Exits immediately when the manager is already initialised. Otherwise resolves or creates the dropdown worksheet, then creates a DropdownLists instance bound to it with the standard dropdown prefix.
ensure-updated-values-watcher #
Ensure the updated values watcher is initialised for the registry worksheet
Signature:
Private Sub EnsureUpdatedValuesWatcher()
Exits immediately when the watcher is already initialised. Otherwise resolves or creates the registry worksheet, then creates an UpdatedValues instance bound to it.
reset-updated-registry #
Reset the updated values registry prior to registering watched tables
Signature:
Private Sub ResetUpdatedRegistry()
apply-validations-and-updates #
Apply data validation rules to all setup tables
Signature:
Private Sub ApplyValidationsAndUpdates()
Ensures the dropdown manager is initialised, then delegates to ApplyDictionaryValidations, ApplyExportsValidations, and ApplyAnalysisValidations to wire each table column to its dropdown list.
ensure-hidden-sheets #
Register internal worksheets as hidden via the development manager
Signature:
Private Sub EnsureHiddenSheets(ByRef manager As IDevelopment)
Calls manager.AddHiddenSheet for each internal worksheet constant (updated, variables, formatter, pass, formula, dev).
Parameters:
manager: IDevelopment. Development manager to register with.
Throws:
- ProjectError.ObjectNotInitialized When manager is Nothing.
ensure-protected-sheets #
Register user-facing worksheets as protected via the development manager
Signature:
Private Sub EnsureProtectedSheets(ByRef manager As IDevelopment)
Calls manager.AddProtectedSheet for each user-facing worksheet, specifying whether filtering and sorting are allowed on the protected sheet.
Parameters:
manager: IDevelopment. Development manager to register with.
Throws:
- ProjectError.ObjectNotInitialized When manager is Nothing.
register-all-dropdowns #
Register every standard dropdown list for setup worksheets
Signature:
Private Sub RegisterAllDropdowns()
Calls RegisterDropdown for each predefined dropdown (yes/no, formats, variable status/type/control, sheet type, export options, analysis parameters, geo variables, chart types, etc.). The dropdown names use the double-underscore prefix convention.
register-dropdown #
Register a single dropdown by converting the values into a BetterArray
Signature:
Private Sub RegisterDropdown(ByVal dropdownName As String, ByVal values As Variant)
Ensures the dropdown manager is initialised, converts the supplied Variant array into a BetterArray (1-based), and adds it to the dropdown manager under the given name.
Parameters:
dropdownName: String. The name to register the dropdown under.values: Variant. An array of values or a single value to populate the dropdown.
register-watched-sheets #
Register watched columns for every relevant setup sheet
Signature:
Private Sub RegisterWatchedSheets()
Iterates over the list of watched sheet names, resolves each to a worksheet in the host workbook, and registers it with the update watcher via AddSheet. Raises an error when a required sheet is missing.
Throws:
- ProjectError.ElementNotFound When a watched sheet does not exist.
watched-sheet-names #
Return the list of setup sheets whose tables should be watched for updates
Signature:
Private Function WatchedSheetNames() As BetterArray
Returns: BetterArray. Sheet names (Dictionary, Choices, Analysis, Exports).
reset-registry-statuses #
Reset registry statuses to the default value after registration
Signature:
Private Sub ResetRegistryStatuses()
Worksheet helpers
ensure-dropdown-sheet #
Ensure the dropdown worksheet exists and cache the reference
Signature:
Private Function EnsureDropdownSheet() As Worksheet
Returns: Worksheet. The dropdown worksheet.
ensure-registry-sheet #
Ensure the registry worksheet exists and cache the reference
Signature:
Private Function EnsureRegistrySheet() As Worksheet
Returns: Worksheet. The registry worksheet.
ensure-workbook #
Return the cached workbook reference, raising when missing
Signature:
Private Function EnsureWorkbook() As Workbook
Returns: Workbook. The host workbook.
Throws:
- ProjectError.ObjectNotInitialized When the instance has not been configured.
resolve-worksheet #
Resolve an existing worksheet by name without creating it
Signature:
Private Function ResolveWorksheet(ByVal sheetName As String) As Worksheet
Parameters:
sheetName: String. The worksheet name to look up.
Returns: Worksheet. The resolved worksheet, or Nothing when not found.
resolve-or-create-worksheet #
Resolve a worksheet if it exists, otherwise create one
Signature:
Private Function ResolveOrCreateWorksheet(ByVal sheetName As String, _
Optional ByVal visibility As XlSheetVisibility, _
Optional ByVal addVisibility As Boolean = True) As Worksheet
Looks up the worksheet by name in the host workbook. When not found, appends a new worksheet at the end and assigns the given name. Optionally applies the supplied visibility state.
Parameters:
sheetName: String. The worksheet name to look up or create.visibility: Optional XlSheetVisibility. Visibility state to apply when addVisibility is True.addVisibility: Optional Boolean. When True, applies the visibility parameter. Defaults to True.
Returns: Worksheet. The resolved or newly created worksheet.
apply-dictionary-validations #
Apply data validation rules to the dictionary worksheet tables
Signature:
Private Sub ApplyDictionaryValidations(ByVal drop As IDropdownLists)
Resolves the Dictionary worksheet and its Tab_Dictionary ListObject, then wires each column (sheet type, status, variable type, format, control, etc.) to its corresponding dropdown list via SetValidation.
Parameters:
drop: IDropdownLists. The dropdown manager providing validation ranges.
apply-exports-validations #
Apply export worksheet validations
Signature:
Private Sub ApplyExportsValidations(ByVal drop As IDropdownLists)
Resolves the Exports worksheet and its Tab_Export ListObject, then wires each column (status, file format, password, etc.) to its dropdown list.
Parameters:
drop: IDropdownLists. The dropdown manager providing validation ranges.
apply-analysis-validations #
Apply validations to all analysis tables and named ranges
Signature:
Private Sub ApplyAnalysisValidations(ByVal drop As IDropdownLists)
Resolves the Analysis worksheet, applies the table-switcher validation to the RNG_SelectTable named range, then wires every analysis table (global summary, univariate, bivariate, time series, graph, spatial, spatio-temporal specs, spatio-temporal) to their respective dropdown lists.
Parameters:
drop: IDropdownLists. The dropdown manager providing validation ranges.
require-worksheet #
Retrieve a worksheet or raise if unavailable
Signature:
Private Function RequireWorksheet(ByVal sheetName As String) As Worksheet
Parameters:
sheetName: String. The worksheet name to resolve.
Returns: Worksheet. The resolved worksheet.
Throws:
- ProjectError.ElementNotFound When the worksheet does not exist.
require-list-object #
Retrieve a ListObject from a worksheet or raise when missing
Signature:
Private Function RequireListObject(ByVal sh As Worksheet, ByVal listName As String) As ListObject
Parameters:
sh: Worksheet. The worksheet to search.listName: String. The ListObject name to find.
Returns: ListObject. The resolved ListObject.
Throws:
- ProjectError.ElementNotFound When the ListObject does not exist.
require-custom-table #
Return a CustomTable helper for the requested ListObject
Signature:
Private Function RequireCustomTable(ByVal sh As Worksheet, ByVal listName As String) As ICustomTable
Parameters:
sh: Worksheet. The worksheet hosting the ListObject.listName: String. The ListObject name to wrap.
Returns: ICustomTable. A CustomTable wrapper for the ListObject.
resolve-named-range #
Resolve a named range within a worksheet
Signature:
Private Function ResolveNamedRange(ByVal sh As Worksheet, ByVal rangeName As String) As Range
Parameters:
sh: Worksheet. The worksheet to search.rangeName: String. The named range to resolve.
Returns: Range. The resolved range, or Nothing when not available.
normalize-text #
Normalise a string for case-insensitive comparisons
Signature:
Private Function NormalizeText(ByVal valueText As String) As String
Parameters:
valueText: String. The text to normalise.
Returns: String. The trimmed, lowercased text.
Error handling
throw-error #
Raise a project-scoped error with the supplied message
Signature:
Private Sub ThrowError(ByVal errNumber As ProjectError, ByVal message As String)
Wrapper around Err.Raise that standardises the source to "SetupPreparation", providing a consistent stack trace across all methods in this class.
Parameters:
errNumber: ProjectError. The error code to raise.message: String. Human-readable description of the failure.
Throws:
- ProjectError Always raises the specified error.