SetupPreparation

Prepares a setup workbook for use. Configure binds the workbook and Prepare runs the whole sequence: it registers the dropdown lists, rebuilds the update registry, applies the data validations of the Dictionary, Exports and Analysis tables, and hands Development the sheets to hide and to protect. EnsureDropdowns and ResetUpdatedRegistry run those two steps on their own, Dropdowns answers the dropdown manager and HostWorkbook the workbook.

RUNNING PREPARE MORE THAN ONCE

Prepare runs again on a workbook it has already prepared, and each step has its own answer to a second run:

  • the 28 dropdowns are written again, so a new format or a new control value reaches an old workbook. Seven placeholder lists are left as they are; see RegisterAllDropdowns.
  • the update registry is dropped and rebuilt in full; see ResetUpdatedRegistry.
  • the 56 validations are applied again.
  • a sheet that already exists keeps its visibility; see ResolveOrCreateWorksheet.
  • the hidden and protected sheet lists are handed to Development, which skips a name it already holds.
Factory

Create #

create

Create an initialised SetupPreparation instance

Signature:

Public Function Create(ByVal hostBook As Workbook) As SetupPreparation

Factory method on the predeclared instance. Validates the workbook is not Nothing via Configure, then returns the new instance.

Parameters:

  • hostBook: Workbook. The workbook hosting setup worksheets.

Returns: SetupPreparation. A fully initialised instance ready for use.

Throws:

  • ProjectError.ObjectNotInitialized When hostBook is Nothing.
Public API

Configure #

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 #

prepare

Execute the full preparation workflow

Signature:

Public Sub Prepare(ByRef manager As Development)

Runs the complete preparation sequence: registers all dropdown list objects, rebuilds 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.

The manager is tested first so a missing one stops the run before any worksheet is written. RibbonDev.EnsureDevelopment answers Nothing on any failure and its caller passes that straight through, so this is a case a user reaches.

Parameters:

  • manager: Development. Development manager providing hidden/protected sheet registration.

Throws:

  • ProjectError.ObjectNotInitialized When manager is Nothing.

EnsureDropdowns #

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 again: the manager is built once, and a list that is already on the sheet is written with its current values.

ResetUpdatedRegistry #

reset-updated-registry

Drop every registry table and build it again from the watched sheets

Signature:

Public Sub ResetUpdatedRegistry()

This is a teardown. DeleteUp removes every registry ListObject on the registry sheet, then the watched setup sheets (Dictionary, Choices, Analysis, Exports) are registered again and every status tag is written back as "yes". A registry that was already there is lost, so this is the wrong call for topping one up. Two of the three callers press a ribbon button that means "start again"; the third is Prepare.

dropdowns

Dropdown manager for external callers

Signature:

Public Property Get Dropdowns() As DropdownLists

Returns the DropdownLists manager bound to the dropdown worksheet. Lazily initialises the manager on first access if it was not already created by EnsureDropdowns or Prepare.

Returns: DropdownLists. The dropdown manager instance.

HostWorkbook #

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)

Preparation helpers

ResetCaches #

reset-caches

Reset cached references after reconfiguring the helper

Signature:

Private Sub ResetCaches()

EnsureDropdownManager #

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.

EnsureUpdatedValuesWatcher #

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.

ApplyValidationsAndUpdates #

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.

EnsureHiddenSheets #

ensure-hidden-sheets

Register internal worksheets as hidden via the development manager

Signature:

Private Sub EnsureHiddenSheets(ByRef manager As Development)

Calls manager.AddHiddenSheet for each internal worksheet constant (updated, variables, formatter, pass, formula, dev).

__checkRep is left off this list on purpose. SetupErrors publishes its report there, so the sheet has to stay visible and writable. EventSetup.ProtectSetupSheet returns early for it by name for the same reason.

Parameters:

Throws:

EnsureProtectedSheets #

ensure-protected-sheets

Register user-facing worksheets as protected via the development manager

Signature:

Private Sub EnsureProtectedSheets(ByRef manager As Development)

Calls manager.AddProtectedSheet for each user-facing worksheet, specifying whether filtering and sorting are allowed on the protected sheet.

Parameters:

Throws:

RegisterAllDropdowns #

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.

PLACEHOLDER LISTS

Seven lists are seeded here with two empty strings and filled later by EventSetup.AddOrUpdateDrop from the workbook's own content: __geo_vars, __hfonly_vars, __geoonly_vars, __choice_vars, __time_vars, __graphs_titles and __series_titles. They pass allowRefresh:=False so a second Prepare leaves what the user has in them. Every other list is owned by this routine and is written again on each Prepare.

RegisterDropdown #

register-dropdown

Write one dropdown, adding it or refreshing what is already there

Signature:

Private Sub RegisterDropdown(ByVal dropdownName As String, ByVal values As Variant, _
                             Optional ByVal allowRefresh As Boolean = True)

Ensures the dropdown manager is initialised, converts the supplied Variant array into a BetterArray (1-based), then writes it. DropdownLists.Add exits when the name is already on the sheet, so a workbook prepared once would keep its old lists for ever. A list this class owns is sent to Update instead, which is what makes a second Prepare pick up a new format or a new control value.

Parameters:

RegisterWatchedSheets #

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:

WatchedSheetNames #

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).

ResetRegistryStatuses #

reset-registry-statuses

Reset registry statuses to the default value after registration

Signature:

Private Sub ResetRegistryStatuses()

Worksheet helpers

EnsureDropdownSheet #

ensure-dropdown-sheet

Ensure the dropdown worksheet exists and cache the reference

Signature:

Private Function EnsureDropdownSheet() As Worksheet

Returns: Worksheet. The dropdown worksheet.

EnsureRegistrySheet #

ensure-registry-sheet

Ensure the registry worksheet exists and cache the reference

Signature:

Private Function EnsureRegistrySheet() As Worksheet

Returns: Worksheet. The registry worksheet.

EnsureWorkbook #

ensure-workbook

Return the cached workbook reference, raising when missing

Signature:

Private Function EnsureWorkbook() As Workbook

Returns: Workbook. The host workbook.

Throws:

ResolveWorksheet #

resolve-worksheet

Resolve an existing worksheet by name without creating it

Signature:

Private Function ResolveWorksheet(ByVal sheetName As String) As Worksheet

Parameters:

Returns: Worksheet. The resolved worksheet, or Nothing when not found.

ResolveOrCreateWorksheet #

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 = xlSheetVisible) As Worksheet

Looks up the worksheet by name in the host workbook. When not found, appends a new worksheet at the end, assigns the given name and applies the supplied visibility.

The visibility is written on creation alone. A sheet that is already there keeps whatever state the workbook left it in, so a prepared workbook whose __variables sheet is hidden stays hidden after another Prepare. Nothing in this class hides these sheets again, and Development.Deploy is what makes them very hidden at deploy time.

Parameters:

Returns: Worksheet. The resolved or newly created worksheet.

ApplyDictionaryValidations #

apply-dictionary-validations

Apply data validation rules to the dictionary worksheet tables

Signature:

Private Sub ApplyDictionaryValidations(ByVal drop As DropdownLists)

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:

ApplyExportsValidations #

apply-exports-validations

Apply export worksheet validations

Signature:

Private Sub ApplyExportsValidations(ByVal drop As DropdownLists)

Resolves the Exports worksheet and its Tab_Export ListObject, then wires each column (status, file format, password, etc.) to its dropdown list.

Parameters:

ApplyAnalysisValidations #

apply-analysis-validations

Apply validations to all analysis tables and named ranges

Signature:

Private Sub ApplyAnalysisValidations(ByVal drop As DropdownLists)

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.

TWO GAPS ON THE SPATIO-TEMPORAL TABLE

"flip coordinates" is wired here the way it is on the spatial table, and a setup workbook whose Tab_SpatioTemporal_Analysis has no such column gets nothing: CustomTable.SetValidation resolves the name through DataRange, which answers Nothing, and the call ends without a word. The column belongs in the workbook; the line stays so it works once the column is there.

"Section (select)" and "Spatial type" on the same table are left alone. Tab_SpatioTemporal_Specs owns the spatial type for the pair, and the section is chosen from the specs table.

Parameters:

RequireWorksheet #

require-worksheet

Retrieve a worksheet or raise if unavailable

Signature:

Private Function RequireWorksheet(ByVal sheetName As String) As Worksheet

Parameters:

Returns: Worksheet. The resolved worksheet.

Throws:

RequireListObject #

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:

Returns: ListObject. The resolved ListObject.

Throws:

RequireCustomTable #

require-custom-table

Return a CustomTable helper for the requested ListObject

Signature:

Private Function RequireCustomTable(ByVal sh As Worksheet, ByVal listName As String) As CustomTable

Parameters:

Returns: CustomTable. A CustomTable wrapper for the ListObject.

ResolveNamedRange #

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:

Returns: Range. The resolved range, or Nothing when not available.

Error handling

ThrowError #

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:

Throws:

Used in (9 file(s))