DesignerPreparation
Prepares a designer workbook for ribbon interactions. The full preparation sequence imports translation tables from a user-selected workbook, creates a dropdown worksheet with standard dropdown lists (setup languages, interface languages, epiweek start, design values), hides internal worksheets (VeryHidden for __pass/__formula/__dropdowns, Hidden for translation sheets), seeds workbook- and worksheet-level HiddenNames flags, carries the designer version from the Dev worksheet into a workbook-level name, applies data validation to the T_Multi table on the GenerateMultiple worksheet, and registers hidden sheets with the development manager. Also provides methods to read and write ribbon checkbox flags persisted through workbook-level hidden names.
Depends on: HiddenNames, Development, OSFiles, CustomTable, BetterArray, DropdownLists
Factory
Create #
create
Create and configure a designer preparation helper
Signature:
Public Function Create(ByVal hostBook As Workbook) As DesignerPreparation
Factory method that creates a new DesignerPreparation instance, binds it to the supplied designer workbook via Configure, and returns the result. Called on the predeclared instance.
Parameters:
hostBook: Workbook. The designer workbook to prepare.
Returns: DesignerPreparation. A fully initialised instance ready for use.
Public API
Configure #
configure
Bind the helper to the designer workbook
Signature:
Public Sub Configure(ByVal hostBook As Workbook)
Stores a reference to the host workbook. Called once by the factory, which then seals the instance; a later call raises. Raises an error when the workbook is Nothing.
Parameters:
hostBook: Workbook. The designer workbook to bind to.
Throws:
- ProjectError.ObjectNotInitialized When the workbook is Nothing.
- ProjectError.SomethingWentWrong When the instance is sealed.
Prepare #
prepare
Execute all preparation tasks on the designer workbook
Signature:
Public Sub Prepare(ByRef devManager As Development, _
Optional ByVal translationsSource As Workbook = Nothing)
Runs the full preparation sequence:
- Prompts the user to select a translations workbook and imports translation tables into LinelistTranslation and DesignerTranslation.
- Creates the dropdown worksheet and registers all standard dropdown lists (setup languages, interface languages, epiweek start, design).
- Hides internal worksheets (VeryHidden for __pass/__formula/ __dropdowns, Hidden for LinelistTranslation, DesignerTranslation). __formatter and __check are left alone: the designer works with both.
- Seeds workbook-level HiddenNames flags (ribbon checks, language codes, last opened date).
- Copies the version from the Dev worksheet into the workbook-level RNG_DesignerVersion name.
- Seeds Geo worksheet-level HiddenNames flags (geo language, name, update status).
- Applies data validation to the T_Multi table on GenerateMultiple.
- Applies dropdown validation to the Main named ranges (RNG_LangSetup, RNG_DesignLL, RNG_LLForm). When devManager is provided, hidden sheets are also registered with the manager for deployment. When translationsSource is provided, the translation tables are read from that open workbook. When it is Nothing, the user is prompted to select the translations file. Tests hand a fixture workbook here so the sequence runs without a dialog.
Parameters:
devManager: Development. ByRef development manager instance, or Nothing.translationsSource: Optional Workbook. Open workbook holding the translation tables. Defaults to Nothing.
SetFlag #
set-flag
Persist a ribbon checkbox flag as Yes/No in hidden names
Signature:
Public Sub SetFlag(ByVal nameId As String, ByVal enabled As Boolean)
Writes a boolean value to the workbook-level hidden names store as "Yes" or "No". Ensures the store and default flags exist before writing.
Parameters:
nameId: String. The hidden name identifier for the flag (e.g. "chkAlert").enabled: Boolean. True to store "Yes", False to store "No".
GetFlag #
get-flag
Read a ribbon checkbox flag as a boolean from hidden names
Signature:
Public Function GetFlag(ByVal nameId As String, Optional ByVal defaultValue As Boolean = False) As Boolean
Reads the named value from the hidden names store and returns True when the stored value equals "Yes" (case-insensitive comparison). Falls back to the defaultValue when the name is missing.
Parameters:
nameId: String. The hidden name identifier for the flag.defaultValue: Optional Boolean. Value returned when the flag is missing. Defaults to False.
Returns: Boolean. True when the stored value is "Yes".
FormatterImported #
formatter-imported
Whether the designer holds a formatter loaded through the ribbon
Signature:
Public Property Get FormatterImported() As Boolean
Reads the workbook-level TAG_FORMATTER_IMPORTED flag. True means the styles import button was pressed for the setup file that is loaded now, so the generation ships the designer's __formatter sheet. False means the setup file owns the formatter.
Returns: Boolean. True when the flag reads "Yes".
FormatterImported #
formatter-imported-set
Set the formatter flag
Signature:
Public Property Let FormatterImported(ByVal imported As Boolean)
Writes "Yes" or "No" into the workbook-level TAG_FORMATTER_IMPORTED flag, creating the hidden name when the designer has none yet. The styles import writes True; loading a setup file writes False, so the designer's formatter wins only after an explicit styles import for the setup that is loaded now.
Parameters:
imported: Boolean. True to mark the designer's formatter as the live copy.
HostWorkbook #
host-workbook
The configured host workbook
Signature:
Public Property Get HostWorkbook() As Workbook
Returns the designer workbook that was bound via Configure. Raises an error when accessed before configuration.
Returns: Workbook. The host workbook.
Throws:
- ProjectError.ObjectNotInitialized When the workbook was not configured.
HiddenStore #
hidden-store
The workbook-level hidden names manager
Signature:
Public Property Get HiddenStore() As HiddenNames
Returns the HiddenNames instance lazily created from the host workbook. Used internally to persist and retrieve ribbon flag values.
Returns: HiddenNames. The hidden names manager.
Dropdowns #
dropdowns
The dropdown manager created during preparation
Signature:
Public Property Get Dropdowns() As DropdownLists
Returns the DropdownLists manager bound to the designer 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.
Preparation Helpers
ImportTranslations #
import-translations
Import translation tables from a translations workbook
Signature:
Public Sub ImportTranslations(Optional ByVal source As Workbook = Nothing)
Iterates through LinelistTranslation and DesignerTranslation sheets and imports matching ListObjects via CustomTable.Import. When source is Nothing, prompts the user to select an xlsx file and opens it read-only; the workbook opened here is also closed here. When source is provided, reads from that open workbook and leaves it open -- the clickImpTrans ribbon callback opens the file itself and hands it over that way, and tests hand a fixture workbook so the sequence runs without a dialog.
This body is the only translations import in the project. The preparation sequence and the ribbon button both come here, so the eight table names are written down once.
A sheet or table missing on either side is skipped through the resolve helpers, which probe and clear their own errors. The cleanup label only sees errors raised while the import itself is running, so a designer that lacks one sheet prepares without a raise.
Parameters:
source: Optional Workbook. Open workbook holding the translation tables, or Nothing.
EnsureGeoFlags #
ensure-geo-flags
Ensure the Geo HiddenNames flags exist
Signature:
Public Sub EnsureGeoFlags()
Creates worksheet-level HiddenNames on the Geo worksheet for language code, geobase name, metadata language, update status, translation state and form loaded state.
The five level labels (RNG_ADM1NAME through RNG_ADM4NAME and RNG_HFNAME) are created at WORKBOOK scope. LLGeo.CheckRequirements asks for them there, and CrossTable resolves them from a worksheet formula on the spatial analysis sheet, where a worksheet-scoped name would give #NAME?.
RNG_PastingGeoCol stays a real cell-based named range. It is a paste anchor at runtime and the paste needs a cell to land in.
Exits with no work when the host workbook has no Geo worksheet.
PUBLIC BECAUSE A HEADLESS BUILD NEEDS IT ON ITS OWN
Prepare is the ordinary caller and it opens a file dialog on its first step, so a headless run cannot reach this through it. Every name below is what LLGeo.CheckRequirements asks for, and a designer that has never been prepared with the current code carries none of them: LLGeo.Create then fails, the dictionary logs "geolines not append", and every geo variable stays one column instead of the twelve it expands into. Calling this first is what a build does to make the geo sheet readable, and it is idempotent -- every write is an EnsureName, so a designer that already carries them is left alone.
Internal members (not exported)
Instance Sealing
Seal #
seal
Seal the instance against further setup writes
Signature:
Public Sub Seal()
GuardNotSealed #
guard-not-sealed
Guard a setup-only member against post-seal writes
Signature:
Private Sub GuardNotSealed(ByVal memberName As String)
Parameters:
memberName: String. The member name for the error message.
Preparation Helpers
EnsureDropdowns #
ensure-dropdowns
Create the dropdown worksheet and register all standard dropdown lists
Signature:
Private Sub EnsureDropdowns()
Creates the dropdown manager if needed, then registers setup languages (empty placeholder), interface languages, epiweek start (1-7), and design values dropdown lists.
HideWorksheets #
hide-worksheets
Hide internal worksheets and register them with the dev manager
Signature:
Private Sub HideWorksheets(ByRef devManager As Development)
Sets __pass, __formula and __dropdowns to xlSheetVeryHidden. __formatter and __check are deliberately left as they are: __formatter carries the formats a user picks and __check carries the generation report, so a prepared designer keeps both on screen. Sets LinelistTranslation and DesignerTranslation to xlSheetHidden (visible in the VBA editor for developers). When a development manager is provided, all sheets are also registered via AddHiddenSheet for deployment. Silently skips any worksheet that does not exist.
Parameters:
devManager: Development. Development manager for deployment registration, or Nothing.
EnsureWorkbookFlags #
ensure-workbook-flags
Ensure all workbook-level HiddenNames flags exist
Signature:
Private Sub EnsureWorkbookFlags()
Creates workbook-level HiddenNames for ribbon checkbox flags, language codes, and the last opened date. Checkbox flags default to "Yes" (on). Language codes default to empty strings. Last opened date is set to the current date.
EnsureVersionName #
ensure-version-name
Copy the designer version into a workbook-level hidden name
Signature:
Private Sub EnsureVersionName()
The version is owner hand work: it is written once on the Dev worksheet, as the value name RNG_Version scoped to that sheet. This copies it into the workbook-scoped RNG_DesignerVersion name, which is where LLGeo reads it for the metadata sheet of every generated linelist.
The copy is what the metadata sees, so a version edited on the Dev sheet reaches a linelist once the designer is initialised again.
Three states leave the workbook name as it is: no Dev worksheet, no RNG_Version on it, and an empty RNG_Version. A designer built before the name existed is in one of them, and it is an ordinary designer rather than a broken one: LLGeo answers "(not found)" and the build carries on.
DevVersionText #
dev-version-text
Read the version the Dev worksheet carries
Signature:
Private Function DevVersionText(ByVal devSheet As Worksheet) As String
RNG_Version is a value name: the version text is in the definition itself, and QuickValue reads it without walking the whole Names collection of the sheet.
A Dev worksheet that points the name at a cell instead is read from that cell, so a version typed in the grid answers here too. Without that, the name would be parsed as text and the metadata sheet would carry an address.
Parameters:
devSheet: Worksheet. The Dev worksheet.
Returns: String. The version text, or an empty string when the sheet holds none.
EnsureLevelName #
ensure-level-name
Ensure one workbook-level hidden name holding a geo level label
Signature:
Private Sub EnsureLevelName(ByVal levelStore As HiddenNames, ByVal sh As Worksheet, _
ByVal nameId As String)
A Geo worksheet written before the level labels moved into HiddenNames carries each label in a cell-based named range of the same identifier. The cell is read first and its value seeds the hidden name, so the label the designer already shows survives the move.
Parameters:
levelStore: HiddenNames. The store over the workbook.sh: Worksheet. The Geo worksheet.nameId: String. The hidden name identifier.
Dropdown Helpers
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.
EnsureDropdownSheet #
ensure-dropdown-sheet
Ensure the dropdown worksheet exists and cache the reference
Signature:
Private Function EnsureDropdownSheet() As Worksheet
Resolves the dropdown worksheet from the host workbook. If not found, creates a new worksheet at the end and names it. Caches the reference for subsequent calls.
Returns: Worksheet. The dropdown worksheet.
RegisterAllDropdowns #
register-all-dropdowns
Register every standard dropdown list for designer worksheets
Signature:
Private Sub RegisterAllDropdowns()
Calls RegisterDropdown for each predefined dropdown: setup languages (empty placeholder for later population), interface languages (pre-populated with the five supported languages), epiweek start (numbers 1 through 7), and design values (matching LLFormat design constants).
RegisterDropdown #
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 to populate the dropdown.
Multi Validations
ApplyMultiValidations #
apply-multi-validations
Apply data validation to the T_Multi table on GenerateMultiple
Signature:
Private Sub ApplyMultiValidations()
Resolves the GenerateMultiple worksheet and the T_Multi ListObject, then wires the "language of the interface", "epiweek start", and "design" columns to their corresponding dropdown lists via CustomTable.SetValidation. Silently exits when the worksheet or table does not exist in the host workbook.
Main Validations
ApplyMainValidations #
apply-main-validations
Apply dropdown validation to named ranges on the Main worksheet
Signature:
Private Sub ApplyMainValidations()
Resolves the Main worksheet and applies data validation from the registered dropdown lists to three named ranges: RNG_LangSetup (setup language), RNG_DesignLL (design selection), and RNG_LLForm (language of linelist interface). Silently exits when the Main worksheet does not exist or individual named ranges are missing.
ApplyRangeValidation #
apply-range-validation
Apply dropdown validation to a single named range
Signature:
Private Sub ApplyRangeValidation(ByVal sh As Worksheet, ByVal rngName As String, _
ByVal drop As DropdownLists, ByVal dropName As String)
Resolves the named range from the worksheet and applies list validation using the specified dropdown. Silently exits when the named range does not exist on the worksheet.
Parameters:
sh: Worksheet. The worksheet containing the named range.rngName: String. The named range identifier.drop: DropdownLists. The dropdown manager with registered lists.dropName: String. The dropdown list name to use as validation source.
Internal Helpers
EnsureHiddenStore #
ensure-hidden-store
Lazily initialise the hidden names store from the host workbook
Signature:
Private Sub EnsureHiddenStore()
Creates the HiddenNames instance on first access by calling HiddenNames.Create with the host workbook. Subsequent calls are no-ops.
EnsureDefaultFlags #
ensure-default-flags
Ensure both default ribbon flags exist in the hidden store
Signature:
Private Sub EnsureDefaultFlags()
Calls EnsureName for chkAlert and chkInstruct with "Yes" as default, creating the hidden names if they do not already exist. All checks default to enabled per specification.
ResolveWorksheet #
resolve-worksheet
Resolve a worksheet by name from the host workbook
Signature:
Private Function ResolveWorksheet(ByVal sheetName As String) As Worksheet
Returns the worksheet with the given name from the host workbook, or Nothing when the sheet does not exist.
Parameters:
sheetName: String. The worksheet name to resolve.
Returns: Worksheet. The resolved worksheet, or Nothing.
ResolveSheetIn #
resolve-sheet-in
Resolve a worksheet by name from a given workbook
Signature:
Private Function ResolveSheetIn(ByVal book As Workbook, ByVal sheetName As String) As Worksheet
Returns the worksheet with the given name from the supplied workbook, or Nothing when the sheet does not exist. Probes and clears its own error, so the caller's error state stays clean.
Parameters:
book: Workbook. The workbook to resolve the sheet from.sheetName: String. The worksheet name to resolve.
Returns: Worksheet. The resolved worksheet, or Nothing.
ResolveTableIn #
resolve-table-in
Resolve a ListObject by name into a CustomTable
Signature:
Private Function ResolveTableIn(ByVal sh As Worksheet, ByVal tableName As String) As CustomTable
Returns a CustomTable over the named ListObject on the supplied worksheet, or Nothing when the table does not exist or cannot be wrapped. Probes and clears its own error, so the caller's error state stays clean.
Parameters:
sh: Worksheet. The worksheet holding the ListObject.tableName: String. The ListObject name to resolve.
Returns: CustomTable. The wrapped table, or Nothing.
ResolveNamedRange #
resolve-named-range
Resolve a named range from a worksheet
Signature:
Private Function ResolveNamedRange(ByVal sh As Worksheet, ByVal rngName As String) As Range
Returns the Range matching the given name on the supplied worksheet, or Nothing when the named range does not exist.
Parameters:
sh: Worksheet. The worksheet to resolve the range from.rngName: String. The named range identifier.
Returns: Range. The resolved range, or Nothing.
SetSheetVisibility #
set-sheet-visibility
Set the visibility of a worksheet by name
Signature:
Private Sub SetSheetVisibility(ByVal sheetName As String, ByVal visibility As Long)
Resolves the worksheet from the host workbook and sets its Visible property. Silently skips when the worksheet does not exist.
Parameters:
sheetName: String. The worksheet name.visibility: Long. The xlSheetVisibility constant to apply.
RegisterHiddenSheet #
register-hidden-sheet
Register a sheet with the development manager for deployment hiding
Signature:
Private Sub RegisterHiddenSheet(ByRef manager As Development, ByVal sheetName As String)
Wraps manager.AddHiddenSheet in error handling so a missing sheet does not abort the preparation sequence.
Parameters:
manager: Development. The development manager to register with.sheetName: String. The worksheet name to register.
EnsureGeoRange #
ensure-geo-range
Ensure a cell-based named range exists on the Geo worksheet
Signature:
Private Sub EnsureGeoRange(ByVal sh As Worksheet, ByVal rngName As String)
Checks whether the named range already exists on the worksheet. If not, creates the range in a free cell below the used area so it does not overlap with existing ListObjects or other content.
Parameters:
sh: Worksheet. The Geo worksheet.rngName: String. The named range identifier to create.
RaiseError #
raise-error
Raise a ProjectError-based exception
Signature:
Private Sub RaiseError(ByVal errorCode As ProjectError, ByVal messageText As String)
Wrapper around Err.Raise that standardises the source to CLASS_NAME.
Parameters:
errorCode: ProjectError. The error code to raise.messageText: String. Human-readable description of the failure.
Throws:
- ProjectError.
Always raises the specified error.
Used in (7 file(s))
- LLGeo.cls
- EventsDesignerAdvanced.bas
- EventsDesignerCore.bas
- EventsDesignerMulti.bas
- InitTransfer.bas
- HeadlessBuild.bas
- TestDesignerPreparation.bas