LLdictionary

Manages the "Dictionary" worksheet, the sheet that describes every variable of a linelist. Create binds the worksheet through a DataSheet. ColumnExists, VariableExists, UniqueValues and DataRange read it. AddColumn, InsertColumn, RemoveColumn and RenameColumn work on its columns, and ManageRows, InsertRows and DeleteRows on its rows. Sort orders the sheet, Clean empties it, Translate rewrites its labels, and Import and Export move it between workbooks. SpecialVars, ChoicesVars, GeoVars and TimeVars answer the variables of one kind.

PREPARATION

Prepare adds the helper columns a build reads: table names, column indices, CRF metadata, visibility and the geo rows. It runs once per sheet and Prepared says whether it has run. TotalNumberOfExports carries the number of export columns the sheet holds. The geo object Prepare is handed is typed Object and reached at run time, so LLGeo stays outside the compile dependencies of this class.

HOW THE SHEET IS READ

A row loop reads its block into a Variant once, works in memory, and writes the block back once.

WHAT IS REPORTED

Entries filed while a member runs leave through HasCheckings and CheckingValues.

Depends on: BetterArray, Checking, CustomTable, DataSheet, HiddenNames, TranslationObject

Instantiation

Create #

create

Create a dictionary object

Signature:

Public Function Create(ByVal dictWksh As Worksheet, ByVal dictStartRow As Long, _
                       ByVal dictStartColumn As Long, _
                       Optional ByVal numberOfExports As Long = 0) As LLdictionary

Creates a linelist dictionary wrapper around the supplied worksheet. The routine initialises a backing DataSheet so all subsequent access goes through a consistent abstraction and callers interact with a predeclared instance.

numberOfExports defaults to 0, which means "keep whatever the worksheet already stores". Fourteen of the twenty production call sites omit the argument, and a default of 20 wrote 20 over the real count and persisted it, so a workbook holding 3 exports came back claiming 20. SetupErrors then looped 1 To 20 over a three-row status table and reported notes about exports that are not there. The four call sites that pass a number still decide.

Parameters:

  • dictWksh: Worksheet hosting the dictionary data
  • dictStartRow: Long. Header row of the dictionary
  • dictStartColumn: Long. First column of the dictionary
  • numberOfExports: Optional Long. Expected number of export columns. Zero reads
  • the: stored value.

Returns: An LLdictionary instance

Depends on:

  • DataSheet
  • Checking
Preparation

Sort #

sort

Sort the dictionary by sheet and main section

Signature:

Public Sub Sort()

Reached from the designer Sort button on the setup Dictionary sheet, through EventSetup.SortTables.

The clean afterwards keeps the generated columns. The three helper columns the sort writes ("main section sort", "main section index" and its number_ helper) sit outside the allowed list, so the plain clean already removes them. Passing True here also deleted table name, column index, visibility, crf index, crf choices, crf status and list auto, so a sort on a prepared dictionary undid the preparation.

Internal members (not exported)

Instantiation

InitialiseTotalExports #

init-exports

Initialise total export counter

Signature:

Public Sub InitialiseTotalExports(ByVal requestedTotal As Long)

Loads the stored number of export columns from the worksheet when available. When no persisted value exists, the routine falls back to the requested input and persists it so subsequent sessions pick up the same configuration. A requested total of zero means "use what is stored".

Data Elements

Data #

data

DataSheet backing store

Signature:

Public Property Get Data() As DataSheet

Expose the lazily initialised DataSheet wrapper used for all worksheet interactions. Consumers rely on this to perform advanced queries without directly manipulating Excel ranges.

Returns: DataSheet

Seal #

seal

Prevent further changes to creation-only setters

Signature:

Public Sub Seal()

GuardNotSealed #

guard-not-sealed

Guard creation-only setters after sealing

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

TotalNumberOfExports #

exports

Number of export columns

Signature:

Public Property Get TotalNumberOfExports() As Long

Number of export slots currently configured for the dictionary. This mirrors the legacy behaviour where designer workflows expect a bounded set of export columns ready for mapping.

Returns: Long

TotalNumberOfExports #

exports-set

Signature:

Public Property Let TotalNumberOfExports(ByVal numberOfExports As Long)

Persist the number of configured export columns while defaulting back to the class constant when an invalid value is provided.

Parameters:

ExportCounterManagement

ApplyTotalExports #

apply-exports

Normalise and persist export counters

Signature:

Private Sub ApplyTotalExports(ByVal requestedTotal As Long, _
                              Optional ByVal persist As Boolean = True, _
                              Optional ByVal suppressInvalidLog As Boolean = False)

Centralises validation, caching and persistence of the export counter so every entry point (creation, import, manual setter) behaves consistently. The helper optionally suppresses warnings for legacy workbooks that do not yet store the hidden counter.

NormaliseTotalExports #

normalise-exports

Signature:

Private Function NormaliseTotalExports(ByVal requestedTotal As Long, ByVal suppressInvalidLog As Boolean) As Long

PersistTotalExports #

persist-exports

Signature:

Private Sub PersistTotalExports(ByVal totalCount As Long)

Persist the export counter into the hidden name store of the dictionary worksheet.

PersistTotalExportsOn #

persist-exports-on

Signature:

Private Sub PersistTotalExportsOn(ByVal targetSheet As Worksheet, ByVal totalCount As Long)

Persist the export counter onto a worksheet this object does not own. Used when the counter is written into an exported workbook.

WriteTotalExports #

write-exports

Signature:

Private Sub WriteTotalExports(ByVal store As HiddenNames, ByVal totalCount As Long)

StoredTotalExports #

stored-exports

Export counter held on the dictionary worksheet

Signature:

Private Function StoredTotalExports() As Long

StoredTotalExportsFromSheet #

stored-exports-from

Export counter held on a foreign worksheet

Signature:

Private Function StoredTotalExportsFromSheet(ByVal sourceSheet As Worksheet) As Long

HiddenNameStore #

hidden-name-store

Hidden name store of the dictionary worksheet

Signature:

Private Function HiddenNameStore() As HiddenNames

The store is built once and kept. The host worksheet is fixed at creation, so the second test only guards the case where the backing DataSheet was replaced before the object was sealed.

Wksh #

wksh

Host worksheet

Signature:

Public Property Get Wksh() As Worksheet

Expose the worksheet currently backing this dictionary. Primarily used by helpers that need to perform sheet-level operations such as adding columns or listobjects.

Returns: Worksheet

StartRow #

startrow

Header row index

Signature:

Private Property Get StartRow() As Long

Returns: Long

StartColumn #

startcol

First column index

Signature:

Private Property Get StartColumn() As Long

Returns: Long

DictEndRow #

endrow

Last data row

Signature:

Private Property Get DictEndRow() As Long

One End(xlUp) scan per call, so callers read it once and hold the number rather than asking inside a loop.

Returns: Long

DictEndColumn #

endcol

Last data column

Signature:

Private Property Get DictEndColumn() As Long

One End(xlToLeft) scan per call. Same rule as DictEndRow.

Returns: Long

DataRange #

datarange

Dictionary range accessor

Signature:

Public Property Get DataRange(Optional ByVal colName As String = "__all__", _
                              Optional ByVal includeHeaders As Boolean = False) As Range

Central entry point to retrieve ranges from the dictionary, either by column name or the full table. The helper delegates to the underlying DataSheet so filtering or header inclusion behaves consistently everywhere.

Parameters:

Returns: Range

Block Readers And Writers

ColumnValues #

column-values

Read a single-column Range as a 1-based list of values

Signature:

Private Function ColumnValues(ByVal target As Range) As Variant

Small helpers that move a whole column between the worksheet and a plain Variant array. They exist so every row loop in this class costs one read and one write instead of one crossing per cell.

Range.Value hands back a plain value for one cell and a two-dimensional array otherwise. This flattens both cases so callers see one shape.

Parameters:

Returns: Variant. A 1-based one-dimensional array of cell values.

RowValues #

row-values

Read a single-row Range as a 1-based list of values

Signature:

Private Function RowValues(ByVal target As Range) As Variant

Parameters:

Returns: Variant. A 1-based one-dimensional array of cell values.

SheetColumnValues #

sheet-column-values

Read one worksheet column between two rows

Signature:

Private Function SheetColumnValues(ByVal columnNumber As Long, _
                                   ByVal firstRow As Long, _
                                   ByVal lastRow As Long) As Variant

Parameters:

Returns: Variant. A 1-based one-dimensional array of cell values.

WriteSheetColumn #

write-sheet-column

Write a 1-based list of values back into one worksheet column

Signature:

Private Sub WriteSheetColumn(ByVal columnNumber As Long, ByVal firstRow As Long, ByRef values As Variant)

An element left Empty clears its cell, which is how the routines below leave a row untouched without writing an empty string into it. An empty string would count as content for End(xlUp) and move the last data row.

Parameters:

TextOf #

text-of

Read one cell value as text without raising

Signature:

Private Function TextOf(ByVal cellValue As Variant) As String

A cell holding an error value (#N/A and the like) or Null makes CStr raise a type mismatch. Such a value can never match a header or a control tag, so it reads as empty text.

Parameters:

Returns: String

ListPosition #

list-position

Find a value in a plain String list

Signature:

Private Function ListPosition(ByRef values() As String, ByVal usedCount As Long, _

BetterArray.Includes copies the whole internal array on every call, so a linear scan over a local array is cheaper inside a row loop.

Parameters:

Returns: Long. The 1-based position, or zero when the value is absent.

Column Configuration

EnsureColumnsInitialized #

ensure-columns

Build the allowed and generated column lists

Signature:

Private Sub EnsureColumnsInitialized()

Both lists come from constants that never move, so they are built once per object and never rebuilt. Export columns are answered by the "export" prefix test in ColumnExists; pushing export 1 ... export N into the list as well changed no answer, and it made the list depend on the export counter.

EnsureFormatColumns #

ensure-format-columns

Ensure the conditional formatting columns exist

Signature:

Private Sub EnsureFormatColumns()

ColumnExists #

columnexists

Test if a column exists

Signature:

Public Function ColumnExists(ByVal colName As String, Optional ByVal checkValidity As Boolean = False) As Boolean

Checks whether a column header is present in the dictionary and optionally verifies it belongs to the known schema. Recording the attempt helps capture incomplete dictionaries during setup.

The lookup lower-cases its argument and searches with matchCase False. LLExport.ExportColumnName builds "Export 3" with a capital and relies on that, so both sides have to keep it true.

Parameters:

Returns: Boolean

ColumnIndex #

columnindex

Column index lookup

Signature:

Private Function ColumnIndex(ByVal colName As String, Optional ByVal inDataRange As Boolean = False) As Long

Parameters:

Returns: Long

UniqueValues #

uniquevalues

Unique values in a column

Signature:

Public Function UniqueValues(ByVal colName As String) As BetterArray

Collects a distinct list of values for the requested column. The column is read in one crossing and the duplicates are dropped in memory.

Parameters:

Returns: BetterArray

SanitizeSearchTerm #

sanitize-search-term

Escape the characters Excel reads as a search pattern

Signature:

Private Function SanitizeSearchTerm(ByVal searchText As String) As String

Range.Find treats *, ?, [, ] and # as patterns, and a tilde as the escape. TableSpecs asks about composed names such as "adm1_" & rowVar, so a user variable name carrying one of those characters would match the wrong row. LLVariables and LLSheets each hold their own copy of this routine; folding the three into one place belongs to the session that owns those two files.

Parameters:

Returns: String. The escaped term.

VariableExists #

variableexists

Variable presence test

Signature:

Public Function VariableExists(ByVal varName As String) As Boolean

Tests the dictionary for a specific variable identifier by scanning the "variable name" column. Used to guard operations that may attempt to insert duplicate entries.

LookIn and SearchOrder are named because Range.Find reuses whatever the last search anywhere in the application used, the user's own Ctrl+F included. The match stays case-sensitive: LLVariables.Contains answers the same way and TableSpecs reads both.

Parameters:

Returns: Boolean

AddColumn #

addcolumn

Append a column at the end

Signature:

Public Sub AddColumn(ByVal colName As String)

Adds a new column header after the current data block. No data is populated, allowing callers to later fill values or formulas as required.

Parameters:

InsertColumn #

insertcolumn

Insert a column relative to another

Signature:

Public Sub InsertColumn(ByVal colName As String, ByVal After As String)

Inserts a column immediately after the specified anchor, ensuring the header is merged with surrounding formatting when available so designer tables retain their layout.

Parameters:

RemoveColumn #

removecolumn

Remove a column by name

Signature:

Public Sub RemoveColumn(ByVal colName As String)

Deletes the specified column from the dictionary, logging diagnostics when the column cannot be located to aid troubleshooting in designer flows.

Parameters:

RenameColumn #

Rename a dictionary column header

Signature:

Public Sub RenameColumn(ByVal currentName As String, _
                        ByVal newName As String, _
                        Optional ByVal strictSearch As Boolean = True, _
                        Optional ByVal matchCase As Boolean = False)

Row Operations

ManageRows #

managerows

Add or remove rows on the dictionary listobject

Signature:

Public Sub ManageRows(Optional ByVal del As Boolean = False)

The dictionary listobject is taken as the first one on the worksheet, because this entry point has no selection to start from. InsertRows and DeleteRows work from the caller's selection and reach the table through it. Not every dictionary is table-backed; with no ListObject the attempt is logged.

Parameters:

InsertRows #

Insert rows relative to a target selection

Signature:

Public Sub InsertRows(ByVal targetCell As Range, _
                      Optional ByVal insertShift As Boolean = False)

Uses the worksheet selection height to determine how many rows to insert in the dictionary listobject. When the selection falls outside the table the request is logged and ignored to keep the structure stable.

Parameters:

DeleteRows #

Delete rows intersecting the supplied selection

Signature:

Public Sub DeleteRows(ByVal targetCell As Range, _
                      Optional ByVal includeIds As Boolean = True, _
                      Optional ByVal forceShift As Boolean = False)

ResolveRowTarget #

resolve-row-target

Resolve the listobject behind a selection

Signature:

Private Function ResolveRowTarget(ByVal targetCell As Range, ByVal actionLabel As String) As ListObject

Specialised Views

ResolveSpecialVars #

resolve-special-vars

Filter variables on a tag column

Signature:

Private Function ResolveSpecialVars(ByVal firstCondition As String, _
                                    Optional ByVal secondCondition As String = vbNullString, _
                                    Optional ByVal conditionName As String = "Control") As BetterArray

Shared filtering routine behind ChoicesVars, GeoVars, and TimeVars. The function inspects the control column (or a custom column) and returns the variables that match the supplied criteria. Only variables living on horizontal sheets (hlist2D) are relevant for these helpers.

The three columns are read as three blocks and matched in memory. EventSetup asks for five different filters back to back, so this used to walk the same three columns cell by cell five times.

SpecialVars #

specialvars

Filtered variable list by criteria

Signature:

Public Property Get SpecialVars(ByVal firstCondition As String, _
                                Optional ByVal secondCondition As String = vbNullString, _
                                Optional ByVal conditionName As String = "Control") As BetterArray

Returns variables that match one or two control values within a chosen column. Only considers variables on horizontal sheets to align with designer expectations.

Parameters:

ChoicesVars #

choicesvars

Choice variables list

Signature:

Public Property Get ChoicesVars() As BetterArray

Shortcut returning variables controlled by manual or formula-driven choice lists.

Returns: BetterArray

GeoVars #

geovars

Geographic variables list

Signature:

Public Property Get GeoVars() As BetterArray

Convenience accessor for variables whose controls drive geographic selections (geo/hf).

Returns: BetterArray

TimeVars #

timevars

Time variables list

Signature:

Public Property Get TimeVars() As BetterArray

Returns variables tied to dates by filtering through the Variable Type column.

Returns: BetterArray

Preparation

ClearPreparedCache #

clear-prepared

Drop the held preparation answer

Signature:

Private Sub ClearPreparedCache()

Called by every routine that adds, removes or renames a column, or moves the last data row, because Prepared reads six column names, two forbidden ones and the font colour of the cell below the data.

Prepared #

prepared

Dictionary preparation flag

Signature:

Public Property Get Prepared() As Boolean

Indicates whether all helper columns have been generated for designer workflows. The heuristic mirrors the legacy implementation by inspecting specific markers instead of tracking explicit state.

A True answer is held on the object. VBA evaluates every operand of And, so one read costs eight column searches plus a font read, and Formulas.ParsedLinelistFormula reaches it once per token in every formula in the workbook through LLSheets.VariableAddress. That hot path always runs on a prepared dictionary, so holding True is where the whole win is.

A False answer is read again every time. Preparation is what turns False into True, and another object over the same worksheet can be the one that runs it, so a held False could go stale with nothing to drop it. Every routine here that can turn True back into False drops the held answer itself.

Returns: Boolean

Prepare #

prepare

Prepare dictionary for designer workflows

Signature:

Public Sub Prepare(Optional ByVal preservedSheetNames As BetterArray, _
                   Optional ByVal geoObject As Object, _
                   Optional ByVal tablePrefix As String = "table")

Extends the dictionary with all helper metadata required by the designer: table names, CRF indices, geo duplications, and visibility columns. The method guards against repeated execution by checking the Prepared flag and logs issues discovered during enrichment.

The order of operations is fixed: exported workbooks depend on it. The sheet is sorted twice, once by AppendNumberColumn to group the rows by sheet and again inside AddMainSectionSort to order each sheet by main section. The second sort numbers its groups in first-seen order, so it needs the rows already grouped by sheet. Both sorts stay.

Parameters:

EnsureRequiredColumns #

Ensure required columns are present in the dictionary

Signature:

Private Sub EnsureRequiredColumns()

ValidDictionaryColumn #

valid-dictionary-column

Decide in memory whether a header belongs to the dictionary

Signature:

Private Function ValidDictionaryColumn(ByVal columnName As String, _
                                       ByRef knownHeaders() As String, _

Same rule as ColumnExists with checkValidity set: the header must sit in the dictionary header row, and it must either be in the allowed list or start with the word "export". The two log messages match the ones ColumnExists writes, so a cleaned workbook still reports the same notes.

Parameters:

Returns: Boolean. True when the column is kept.

Clean #

clean

Remove unexpected columns

Signature:

Public Sub Clean(Optional ByVal removeAddedColumns As Boolean = False)

Deletes columns that fall outside the supported dictionary schema. When the optional argument is True, helper columns introduced during preparation are removed as well so the dictionary reverts to the user-provided structure.

The header row is read once and every decision is taken in memory. Asking the worksheet for each header and then searching the worksheet again for its name cost two crossings a column.

The walk starts at the dictionary start column. It used to start at column 1, so for a dictionary that does not begin in column A the columns to its left were read as headers and deleted.

This deletes ENTIRE worksheet columns. Anything else living on the Dictionary sheet to the right of the table goes with them.

Parameters:

GoodSheetNames #

good-sheet-names

Suffix sheet names that clash with reserved names

Signature:

Private Sub GoodSheetNames(ByVal preservedSheetNames As BetterArray)

The sheet-name column is read once and written back only when a name really moved, so a dictionary with nothing to rename keeps its cells untouched.

NormaliseVariableName #

normalise-variable-name

Clean one variable name in plain VBA

Signature:

Private Function NormaliseVariableName(ByVal rawName As String) As String

Reproduces the three worksheet functions this used to call, with no crossing into Excel: U+00A0 NO-BREAK SPACE becomes a space, codes below 32 are dropped (worksheet CLEAN), the ends are trimmed and runs of spaces inside the text collapse to one (worksheet TRIM does that as well as trimming), then the remaining spaces become underscores.

AscW answers a NEGATIVE number above code point 32767, so the control-code test guards on zero as well.

Parameters:

Returns: String. The normalised identifier.

UniqueVarNames #

unique-var-names

Normalise variable names and force uniqueness

Signature:

Private Sub UniqueVarNames()

Trims whitespace, replaces spaces with underscores, and appends an underscore when two rows end up with the same identifier.

Every short name is collected and reported in ONE message, and nothing is written when one is found. Raising on the first bad row left the rows above normalised and the rows below untouched, so the user was told about one row and handed a half-rewritten dictionary.

AddVisibility #

add-visibility

Derive the visibility and CRF status columns

Signature:

Private Sub AddVisibility()

The logic mirrors the designer wizard so that prepared dictionaries behave the same whether produced manually or through automation. The status and control columns are read as two blocks and the two answers are written as two blocks.

The status decides both columns, and then the CONTROL has the last word on the CRF one: a formula, a choice_formula, a case_when and a list_auto read "always hidden" there whatever their status is.

A row whose status is filled in with something this dictionary does not know keeps both cells empty and the value is logged. A row with no status at all keeps them empty and is passed over in silence, since there is nothing there for anybody to have got wrong.

AppendGeoLines #

append-geo-lines

Expand every geo control variable into twelve rows

Signature:

Private Sub AppendGeoLines(ByVal geoObject As Object)

Each "geo" control variable becomes 12 contiguous rows: 4 admin levels (adm1-4) with geo1-4 controls 4 p-codes (pcode_adm1-4) hidden, formula-driven 4 concatenations (concat_adm1-4) hidden, formula-driven

The original geo row is modified in place to become adm1 and 11 new rows are inserted right after it. DictEndRow() is read on every pass ON PURPOSE, so the search range follows the rows this routine keeps inserting. Do not turn this into a single block read.

geoObject.GeoNames(...) is read per level; LLGeo holds those in its own cache, so the repeat costs nothing here.

AppendHFLines #

append-hf-lines

Give every health facility variable its hidden p-code row

Signature:

Private Sub AppendHFLines()

A health facility variable occupies TWO worksheet columns, and until now it only ever wrote one. AppendColumnIndex reserves the pair -- its Case "hf" advances the next variable by 2 -- and LLSpatial.HF_COLUMN_SPAN agrees the block is two wide. Nothing filled the second. LLSheets sizes the data table to max(variable count, largest column index), so the table stretched over a header cell nobody had written and Excel named that column "Column 1" itself. A phantom column in every linelist carrying a health facility.

This is the geo expansion cut to its smallest form: one inserted row rather than eleven, hidden the same way, controlled by "formula" the same way. The p-code is looked up on the facility column ALONE, matched against hf_concat and read out of hf_pcode, both of which LLGeo publishes off T_HF beside the four admin pairs.

The variable name is used exactly as the dictionary holds it. The geo branch rewrites its own row to adm1_<name> and can build prefixed names from what it started with; no such rewrite happens to a health facility row, so the name in the cell is the name VarWriter puts in the header, and it is the only spelling that will match the column at run time.

AppendNumberColumn #

append-number-column

Number each distinct group and sort the dictionary on it

Signature:

Private Sub AppendNumberColumn(Optional ByVal onColName As String = "sheet name", _
                               Optional ByVal tablePrefix As String = "table", _
                               Optional ByVal newColumn As String = "table name")

Assigns an incrementing number to each distinct sheet (or user-defined group) so the dictionary can later produce stable table names, sorts the whole block on that number, then writes the visible name column as a formula.

The source column index is resolved ONCE. It used to be resolved inside the row loop, and DataSheet.ColumnIndex is a header scan plus a search, so an N-row pass cost N searches.

The name column stays a formula write: the formula is written, autofilled and then flattened on purpose (sortRange.Value = sortRange.Value).

AddMainSectionSort #

add-main-section-sort

Add the main section sort helper and order the sheet on it

Signature:

Private Sub AddMainSectionSort()

Writes a formula column holding " -

", or the row number when the main section is empty, then numbers and sorts on it through AppendNumberColumn. The formula write and its autofill stay as they are.

AppendColumnIndex #

append-column-index

Compute the final column order used by the designer

Signature:

Private Sub AppendColumnIndex()

Sheet-level grouping is respected and gaps are inserted for geo/hf variables that expand into several columns. The three source columns are read as three blocks and the answer is written as one block.

AppendCRFLineIndex #

append-crf-line-index

Populate the CRF indices

Signature:

Private Sub AppendCRFLineIndex()

Form designers map dictionary variables to the rows shown in CRF-style layouts. The walk goes through sections, subsections and controls to insert spacing exactly like the interactive designer.

Five columns are read as five blocks and the two answers are written as two blocks. This was the heaviest loop in the class: five range reads and up to two writes per row.

A row that needs no choices marker keeps its cell Empty. Writing an empty string there would count as content for the last-row scan.

Data Exchange

Import #

import

Import dictionary data

Signature:

Public Sub Import(ByVal fromWksh As Worksheet, _
                  ByVal fromStartRow As Long, _
                  ByVal fromStartcol As Long, _
                  Optional ByVal clearSheet As Boolean = False)

Replaces the current dictionary content with data copied from another worksheet. The incoming data is wrapped in a temporary DataSheet so the existing cleaning and formatting routines can be reused.

With clearSheet True the whole worksheet is cleared. Sheet-level hidden names survive a Cells.Clear, and the export counter is one of them, which is why the source counter is read first.

Parameters:

  • fromWksh: Source worksheet
  • fromStartRow: Long. Start row of the source data
  • fromStartcol: Long. Start column of the source data
  • clearSheet: Optional Boolean. Clear the whole host worksheet first.

Export #

export

Export dictionary data

Signature:

Public Sub Export(ByVal toWkb As Workbook, _
                  Optional ByVal exportType As String = "__all__", _
                  Optional ByVal addListObject As Boolean = True, _
                  Optional ByVal Hide As Long = xlSheetHidden)

Copies the dictionary into the destination workbook with optional filtering, table creation, and sheet visibility adjustments to mirror the interactive export workflow.

The prepared marker is written from the TARGET worksheet and it is written whatever addListObject says. The copy always lands on the first cell of the target, so taking the row from the source put the marker four rows below the data for every setup workbook, whose dictionary starts at row 5, and Prepared then answered False on the copy. LLExporter.AddDictionary writes the same marker itself, at the same cell, because it could not rely on this one.

Parameters:

  • toWkb: Destination workbook
  • exportType: Optional String. Filter by column name. Defaults to "all"
  • addListObject: Optional Boolean. Create a listobject in the destination.
  • Defaults: to True
  • Hide: Optional Long. Visibility setting applied after export. Defaults to
  • xlSheetHidden

Translate #

translate

Translate dictionary labels

Signature:

Public Sub Translate(ByVal TransObject As TranslationObject)

Delegates the translation of user-facing columns (labels, notes, sections) to the provided translation object while protecting formula columns from direct text replacement.

Parameters:

  • TransObject: TranslationObject responsible for translations

Checkings

LogInfo #

log-info

Record one event in the shared checking object

Signature:

Private Sub LogInfo(ByVal label As String, Optional ByVal scope As Byte = checkingNote)

All noteworthy events (missing columns, normalisation steps, etc.) are piped into the shared checking object so setup and designer screens can surface diagnostics to the user. The host worksheet name is read only when the checking object is built; it used to cost two crossings on every logged line, and Clean can log once per deleted column.

HarvestCollaboratorCheckings #

harvest-collaborator-checkings

Fold what a collaborator filed into this dictionary's entries

Signature:

Private Sub HarvestCollaboratorCheckings(ByVal collaboratorChecks As Checking)

DataSheet and CustomTable each keep a store of their own, and until this line nobody read either of them. An import that aborted, or a column name a table could not find, was filed and then dropped.

Parameters:

  • collaboratorChecks: Checking. What the collaborator filed.

MergeCollaboratorCheckings #

merge-collaborator-checkings

Take the data sheet's entries before the report is read

Signature:

Private Sub MergeCollaboratorCheckings()

The data sheet lives as long as the dictionary does, so its entries are folded in when the report is asked for. The flag keeps the fold to once: the caller reads HasCheckings and then CheckingValues, and appending twice would double every line.

HasCheckings #

has-checkings

True once something was logged

Signature:

Public Property Get HasCheckings() As Boolean

Returns: Boolean

CheckingValues #

checking-values

The logged events

Signature:

Public Property Get CheckingValues() As Checking

Returns: Checking

Errors

ThrowError #

throw-error

Raise a ProjectError and record it

Signature:

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

The message is logged BEFORE the raise. Logging afterwards meant the line never ran, so every raise from this class was missing from the generation report, which is the one place the user sees why a build stopped.

Used in (57 file(s))