Linelist

Facade for the output linelist workbook. Prepare adds every worksheet the linelist needs, prepares the temporary sheets with their hidden names and ListObjects, formats the admin sheet and transfers the VBA code, and SaveLL writes the .xlsb the user receives. AddOutputSheet adds one sheet, SheetExists and SheetNames answer what is there, Wksh, PrintWksh and CRFWksh reach a data entry sheet and its two companions, and Dropdown and Pivots answer the two managers the class builds on first use. LinelistSpecs.Prepare has to have run first: it is what creates the output workbook and points the domain managers at it.

The class uses VB_Creatable = True, so callers build one with Set ll = Linelist.Create(specs) over a New Linelist.

SHEET NAMES AND THEIR SCOPE

A data entry sheet can carry two companions, and each companion is the same name under a prefix: print_ for the printable copy, crf_ for the case report form. ScopedName is the one place that puts a prefix on a name and cuts the result to what Excel accepts, and AddOutputSheet, ResolveWksh and the SheetExists calls all go through it. A sheet created under a shortened name is found again under the same shortened name.

A name the cut actually shortens is filed as a report entry, once per name, so the user learns which sheet came out under a different name.

WHAT THIS CLASS REPORTS

Prepare files its own entries and folds in the ones CodeTransfer filed, so HasCheckings and CheckingValues answer over both. clickGenerate flushes them into the generation report right after Prepare returns.

Depends on: LinelistSpecs, LLdictionary, LLSheets, TranslationObject, LLFormat, DropdownLists, CustomPivotTable, TemporaryRepos, Buttons, CodeTransfer, Passwords, HiddenNames, AnaTabIds, ShowHideStore, AnalysisRanges, Checking, BetterArray

Version: 1.4 (2026-08-01)

Factory

Create #

create

Instantiate a Linelist facade bound to specifications

Signature:

Public Function Create(ByVal specs As LinelistSpecs) As Linelist

Instance creation via VB_Creatable = True factory pattern.

Validates that the supplied specifications object is not Nothing, creates a new Linelist instance via New Linelist, binds the specifications through the Friend property, and returns an Linelist interface reference.

Parameters:

  • specs: LinelistSpecs. The linelist specifications providing dictionary, translations, design format, passwords, and all other setup data required to build the output workbook.

Returns: Linelist. A ready-to-use linelist facade.

Throws:

  • ProjectError.ObjectNotInitialized When specs is Nothing.
Public properties

LinelistData #

linelist-data

Specifications backing this linelist

Signature:

Public Property Get LinelistData() As LinelistSpecs

Worksheet resolution and collaborator accessors exposed through the Linelist contract.

Returns the LinelistSpecs instance that was supplied during factory construction, providing access to dictionary, choices, translations, and all other setup data.

Returns: LinelistSpecs. The linelist specifications.

Dictionary #

dictionary

Dictionary from the specifications

Signature:

Public Property Get Dictionary() As LLdictionary

Convenience accessor that delegates to specs.Dictionary, avoiding the extra indirection at call sites.

Returns: LLdictionary. The linelist dictionary.

SheetNames #

sheet-names

The dictionary's unique sheet names

Signature:

Public Property Get SheetNames() As BetterArray

One walk of the dictionary's sheet name column, kept for the whole build. Prepare, TransferAllCode and clickGenerate each derived this list on their own, and every walk reads the whole column.

Returns: BetterArray. The unique sheet names of the dictionary.

SheetInfoManager #

sheet-info-manager

The LLSheets over the dictionary, shared by the whole build

Signature:

Public Property Get SheetInfoManager() As LLSheets

One LLSheets instance over the specifications dictionary, built on first read and kept for the whole build. TransferAllCode and the sheet-building loop of clickGenerate each created their own over the same dictionary, so every row resolution and every cache slot was computed twice.

Returns: LLSheets. The sheet information manager of the dictionary.

Wksh #

wksh

Worksheet from the output workbook by name

Signature:

Public Property Get Wksh(ByVal sheetName As String) As Worksheet

Returns the worksheet matching the given name from the output workbook. Delegates to ResolveWksh with scope 1 (no prefix). Raises an error if the sheet does not exist.

Parameters:

  • sheetName: String. The worksheet name to look up.

Returns: Worksheet. The resolved worksheet.

Throws:

  • ProjectError.InvalidArgument When the sheet does not exist.

PrintWksh #

print-wksh

Printed companion worksheet by base name

Signature:

Public Property Get PrintWksh(ByVal sheetName As String) As Worksheet

Returns the printed companion worksheet (prefixed with "print_") from the output workbook. Used for HList sheets that have a printable duplicate. Delegates to ResolveWksh with scope 2.

Parameters:

  • sheetName: String. The base sheet name (without the print_ prefix).

Returns: Worksheet. The printed companion worksheet.

Throws:

  • ProjectError.InvalidArgument When the sheet does not exist.

CRFWksh #

crf-wksh

CRF companion worksheet by base name

Signature:

Public Property Get CRFWksh(ByVal sheetName As String) As Worksheet

Returns the CRF companion worksheet (prefixed with "crf_") from the output workbook. Used for case report form layouts. Delegates to ResolveWksh with scope 3.

Parameters:

  • sheetName: String. The base sheet name (without the crf_ prefix).

Returns: Worksheet. The CRF companion worksheet.

Throws:

  • ProjectError.InvalidArgument When the sheet does not exist.

dropdown

Dropdown lists manager for the output workbook

Signature:

Public Property Get Dropdown(Optional ByVal scope As Byte = 1) As DropdownLists

Returns the DropdownLists instance backed by the appropriate list sheet. Scope 1 uses the temporary _dropdown_lists sheet with the "dropdown" header prefix; scope 2 uses the custom choice sheet with no prefix.

Each scope is built once and kept. DropdownLists accumulates its report entries in instance state, and this property used to hand back a fresh object every time, so every warning the data-entry build recorded went to a throwaway instance and clickGenerate's phase 2b flush pushed nothing. Keeping the instance also takes a worksheet lookup and an object construction out of the per-sheet build loop, which asks for both scopes on every sheet.

Parameters:

  • scope: Optional Byte. 1 = standard dropdowns, 2 = custom choice dropdowns. Defaults to 1.

Returns: DropdownLists. The dropdown lists manager.

Throws:

  • ProjectError.InvalidArgument When the scope is neither 1 nor 2.

Pivots #

pivots

Pivot tables manager for the output workbook

Signature:

Public Property Get Pivots() As CustomPivotTable

Returns the CustomPivotTable instance backed by the translated custom pivot table sheet. It is built once and kept, for the reason the dropdown managers are: the class holds the metadata of the blocks it has stacked, and a fresh instance per call starts empty.

Returns: CustomPivotTable. The pivot tables manager.

HasCheckings #

has-checkings

Whether this linelist has anything to report

Signature:

Public Property Get HasCheckings() As Boolean

Answers True once an entry has been filed. Prepare files a warning for every sheet name it had to shorten, and folds in the entries CodeTransfer filed for the components it replaced in the output workbook.

Returns: Boolean. True when there is something to read.

CheckingValues #

checking-values

The report entries of this linelist

Signature:

Public Property Get CheckingValues() As Checking

Answers the Checking object holding the entries, and Nothing while none have been filed. clickGenerate flushes it into the generation report right after Prepare returns.

Returns: Checking. The entries, or Nothing.

SheetExists #

sheet-exists

Check whether a sheet exists in the output workbook

Signature:

Public Function SheetExists(ByVal sheetName As String) As Boolean

Tests whether a worksheet with the given name exists in the output workbook without raising an error. The name is taken as it stands: callers that work with a scope pass the answer of ScopedName.

Parameters:

  • sheetName: String. The worksheet name to test.

Returns: Boolean. True when the sheet exists, False otherwise.

AddOutputSheet #

add-output-sheet

Add a worksheet to the output workbook

Signature:

Public Sub AddOutputSheet(ByVal sheetName As String, _
                          Optional ByVal Hide As Long = xlSheetVeryHidden, _
                          Optional ByVal sheetScope As Byte = 1, _
                          Optional ByVal beforeSheet As Worksheet)

Creates a worksheet in the output workbook under the name ScopedName builds from the base name and the scope. When beforeSheet is supplied the new sheet is inserted immediately before it; otherwise the sheet is appended at the default position.

A sheet that is already there is left alone. Template workbooks ship with some of these sheets already made, and rebuilding one would throw away what the template author put in it.

Parameters:

  • sheetName: String. The base name for the new worksheet.
  • Hide: Optional Long. Excel visibility constant. Defaults to xlSheetVeryHidden.
  • sheetScope: Optional Byte. 1 = no prefix, 2 = print_ prefix, 3 = crf_ prefix. Defaults to 1.
  • beforeSheet: Optional Worksheet. An existing worksheet of the output workbook to insert before. Defaults to Nothing (append at default position).
Lifecycle

Prepare #

prepare

Build the full linelist output workbook from specifications

Signature:

Public Sub Prepare()

Build, save, and error-manage the output workbook.

Runs the whole build of the output workbook:

  1. Takes the temporary repository the specifications created.
  2. Resolves the Geo worksheet once. Every sheet this routine adds goes in before it, and the anchor is passed down as a worksheet.
  3. Writes the two language hidden names and every translated message and sheet name onto the output workbook.
  4. Adds one data-entry worksheet per dictionary sheet name.
  5. Adds the six analysis and custom worksheets.
  6. Adds the six temporary worksheets and prepares them with their hidden names and ListObjects.
  7. Moves the instruction sheet to the front (template workbooks only).
  8. Adds and formats the admin sheet (non-template only).
  9. Transfers the VBA components into the workbook.
  10. Folds the code transfer's report entries into this instance's.

The default worksheet Workbooks.Add made is dropped by LinelistSpecs.Prepare, before any exporter writes to the workbook.

Throws:

  • ProjectError.ErrorUnexpectedState When the specifications have not been prepared.

SaveLL #

save-ll

Save the output workbook as .xlsb with password protection

Signature:

Public Sub SaveLL()

Saves the output workbook in .xlsb format with the configured password, protects the workbook structure via Passwords.Protect, activates an appropriate visible sheet (admin or instruction), closes the workbook, and cleans up temporary files via TemporaryRepos.Reset.

Both cached references to the workbook are dropped once it is closed. This instance held one and LinelistSpecs held the other, and a read of either after the close raised an automation error. The read that mattered was ErrorManage, reached from clickGenerate's cleanup, so the failure landed inside the error handler and hid the error that got it there.

ErrorManage #

error-manage

Display an error dialog for incomplete linelist operations

Signature:

Public Sub ErrorManage(Optional ByVal textMessage As String = vbNullString)

Shows a message box with the error details and asks the user whether they want to view the incomplete linelist workbook or close it without saving. If the user chooses Yes the workbook's Application is made visible; otherwise the workbook is closed without saving.

A workbook SaveLL has already closed leaves nothing to show, and the message says so. This routine is reached from a caller's error handler, so it has to finish whatever state the build left behind.

Parameters:

  • textMessage: Optional String. Error description to display. Defaults to vbNullString.

DiscardBuild #

discard-build

Close the incomplete output workbook and drop the references to it

Signature:

Public Sub DiscardBuild()

Closes the output workbook of a build that stopped before SaveLL, with its changes thrown away, and drops both cached references to it: the one this instance holds and the one LinelistSpecs holds. The multi generation driver calls it after a failed row, so the loop moves to the next row with no workbook left open behind it; ErrorManage routes its close branch here. When there is no output workbook the call exits quietly.

Internal members (not exported)

Bindings

BinSpecs #

bin-specs

Bind the linelist specifications to this instance

Signature:

Friend Property Set BinSpecs(ByVal value As LinelistSpecs)

Friend setters used by the factory to inject dependencies.

Called by Create to inject the LinelistSpecs dependency into the backing UDT. Exposed as Friend to prevent external callers from resetting specifications after construction.

Parameters:

Private - Workbook management

OutputWkb #

output-wkb

Return the output workbook from LinelistSpecs

Signature:

Private Property Get OutputWkb() As Workbook

Internal helpers for reaching the output workbook and for turning a base sheet name plus a scope into the name Excel actually carries.

On first access, retrieves the output workbook that LinelistSpecs.Prepare created and caches it locally.

The class used to record Worksheets(1).Name here so Prepare could delete it later. First access is well after every exporter has written to the workbook, and LLGeo inserts its two sheets near the front, so the name recorded was a real, populated sheet. LinelistSpecs.Prepare drops the default worksheet now, holding the reference it took before the first exporter ran.

Returns: Workbook. The output workbook.

Throws:

ScopedName #

scoped-name

The worksheet name a base name carries under a scope

Signature:

Private Function ScopedName(ByVal sheetName As String, ByVal sheetScope As Byte) As String

Puts the scope's prefix on the base name and cuts the result to the 31 characters Excel accepts. Every caller that creates a sheet and every caller that looks one up goes through here, which is what keeps the two sides agreeing.

A cut that shortens the name is filed as a report entry, once per resulting name, so a build finishes and the user reads which sheets came out shorter.

Parameters:

Returns: String. The worksheet name to create or to look up.

ReportShortName #

report-short-name

File one report entry for a name that had to be shortened

Signature:

Private Sub ReportShortName(ByVal fullName As String, ByVal shortName As String)

The same name is scoped many times during a build, so the entry is filed once and the names already filed are kept in a keyed Collection.

Parameters:

AlreadyReported #

already-reported

Whether a shortened name has been filed already

Signature:

Private Function AlreadyReported(ByVal shortName As String) As Boolean

A Collection raises on a key it does not hold, which is how a miss is read.

Parameters:

Returns: Boolean. True when the name has been filed.

ResolveWksh #

resolve-wksh

Resolve a worksheet from the output workbook with scope prefix

Signature:

Private Sub ResolveWksh(ByVal sheetName As String, ByVal sheetScope As Byte, _
                        ByRef result As Worksheet)

Builds the worksheet name through ScopedName, verifies the sheet exists, and returns a reference through the ByRef result parameter.

Parameters:

Throws:

GeoAnchor #

geo-anchor

The Geo worksheet every added sheet goes in before

Signature:

Private Function GeoAnchor() As Worksheet

Prepare used to pass the name "Geo" down to more than twenty AddOutputSheet calls, and each one resolved it again. A missing anchor made AddOutputSheet fall back to a bare Worksheets.Add, which inserts before whatever sheet is active and gives a different final sheet order on every run. The anchor is resolved once here and a missing one stops the build.

Returns: Worksheet. The Geo worksheet of the output workbook.

Throws:

DictionarySheetNames #

dictionary-sheet-names

The dictionary's sheet names, walked once per build

Signature:

Private Function DictionarySheetNames() As BetterArray

Prepare and TransferAllCode both need this list and each used to derive it, which walks the whole dictionary.

Returns: BetterArray. The unique sheet names of the dictionary.

DictionarySheetInfo #

dictionary-sheet-info

The dictionary's sheet manager, built on first read

Signature:

Private Function DictionarySheetInfo() As LLSheets

The dictionary is meaningful once specs.Prepare has run, and a build on first read keeps an early ask safe.

Returns: LLSheets. The sheet information manager of the dictionary.

BuildDropdown #

build-dropdown

Build the dropdown manager of one scope

Signature:

Private Function BuildDropdown(ByVal scope As Byte) As DropdownLists

Parameters:

Returns: DropdownLists. The manager over the sheet that scope reads.

Private - Prepare helpers

PrepareTemporarySheets #

prepare-temporary-sheets

Set up hidden names and ListObjects on temporary sheets

Signature:

Private Sub PrepareTemporarySheets()

Internal routines called by Prepare to set up temporary sheets, the admin sheet, and code transfer.

Performs three setup tasks on the output workbook:

  1. Creates workbook-level hidden names for the global flags: update list auto, import done, geo update manual, show all optionals, export init, number of exports and epi week start.
  2. Creates four ListObjects on the import report sheet for tracking sheets and variables not imported/found.
  3. Asks AnaTabIds to build its registry table on the analysis sheet. That class reads the columns, so it writes the headers.

PrepareAnalysisLists #

prepare-analysis-lists

Add the choice lists every analysis sheet shares

Signature:

Private Sub PrepareAnalysisLists()

ONE LIST FOR THE WHOLE WORKBOOK

The time units a temporal table can group by are one workbook-level dropdown on the list sheet. Every temporal table binds its own time unit cell to it, and GetAgg in CustomLinelistFunctions reads the same list to turn the chosen label back into a time unit.

Each analysis sheet used to build a copy of its own, which is why there were two spellings of the same five words. A reader that named one of them answered wrongly on the sheet carrying the other, and answered nothing at all in a workbook that carried neither.

The words come from the message translations, so the list is written in the language of the linelist. The dropdown is added once; DropdownLists refuses a second one under the same name and files the reason.

InitialiseWorkbookNames #

initialise-workbook-names

Check the two language selections before the build starts

Signature:

Private Sub InitialiseWorkbookNames()

The two language codes reach the output workbook through InitTransfer.SyncDesignerLanguageNames and ExportDesignerHiddenNames, during specs.Prepare. This routine used to write them a second time, deriving the interface code with a bare Split on "-" where the transfer guards the split with InStr. Both answer the same string for every input, including an empty one, so the second write added nothing.

What it does add is the check: an empty language selection is a mistake the user can fix, and the build service says nothing about it. That check is what is left here.

Throws:

InitialiseTranslationNames #

initialise-translation-names

Create the translated message and sheet names on the output workbook

Signature:

Private Sub InitialiseTranslationNames()

Calls InitialiseHiddenNames to create all RNG_* translated message and sheet names on the output workbook.

A second copy of the two language codes used to be written onto the translation worksheet here. LLTranslation reads its languages from the workbook store, so that copy was written and never read.

PrepareAdmin #

prepare-admin

Format the admin sheet and add Advanced/Export buttons

Signature:

Private Sub PrepareAdmin()

Applies the linelist design format to the admin sheet, then creates two large-scope buttons (Advanced and Export) with translated labels. After placing the buttons, re-suppresses UI updates and activates the admin sheet. Finally protects the active sheet via Passwords.Protect with the "_active" scope marker.

Remarks:

TransferAllCode #

transfer-all-code

Transfer all VBA code components to the output workbook

Signature:

Private Sub TransferAllCode(ByVal transfer As CodeTransfer, _
                            ByVal sheetNames As BetterArray)

Moves the VBA the generated linelist runs on, in five steps:

  1. 36 class modules. ChoiceKeys, ImportMetadata, ImportReport and CheckingOutput joined the list with the import work: LLExporter types ChoiceKeys, LLImporter types the first three, ImportChecking types CheckingOutput and F_ImportRep reads ImportReport, so a linelist without them fails project compilation. LLLog joined with the user log: EventLinelist holds one and the button and form modules write their outcome lines through it. SectionMap, GeoFormCache and AnalysisRanges joined last: EventsLinelistButtons types SectionMap, GeoModule and the F_Geo code-behind call GeoFormCache statically, and GeoModule names AnalysisRanges. None of the three is reached by a bare procedure call, so the call scan reported the set closed while a generated linelist could not compile.
  2. 5 standard modules, carrying the linelist toolbar and its helpers. NO FormLogic MODULE IS TRANSFERRED. Every one of them uses Me and declares control event handlers, which only compile inside a form, and the merged .frm files already carry that code in the code module where it belongs. The two calls into it are written qualified -- F_Export.SetupExportForm and F_ExportMig.HandleAnalysisExport -- so they run the form's copy and need no standard module at all. Transferring the four of them was what cost a delivered linelist its compile.
  3. The ribbon module. A template workbook already carries EventsLinelistRibbon, so its code text is overwritten; otherwise the module is imported.
  4. 10 user forms.
  5. The workbook code module, then EventLinelistSheet into the code module of every hlist2D data-entry worksheet.

Two free scans check the three lists below, and they read different things. scripts/devtools/transferred-code-scan.R reads CALLS: a transferred module invoking a Public procedure defined outside the set. scripts/devtools/linelist-components-scan.R reads NAMES: a type, a predeclared class or an enum the set mentions and does not carry. Run both after any change here.

Parameters:

Checkings

Checks #

checks

The report entries of this instance

Signature:

Private Property Get Checks() As Checking

What this class reports into the generation report. clickGenerate reads HasCheckings right after Prepare and flushes CheckingValues.

Returns: Checking. The entries, created on first use.

LogCheck #

log-check

File one report entry

Signature:

Private Sub LogCheck(ByVal checkKey As String, ByVal message As String, _
                     Optional ByVal scope As Byte = checkingNote)

Checking.Add raises on a duplicate key, and one report carries the entries of several classes, so the key names this class and the thing the entry is about.

Parameters:

Private - Error handling

ThrowError #

throw-error

Raise a project error with class context

Signature:

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

Centralised error-raising helper.

Wraps Err.Raise with CLASS_NAME as the source, providing consistent error attribution across all methods in this class.

Parameters:

Used in (59 file(s))