LLdictionary

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 = DEFAULTNUMBEROFEXPORTS) As ILLdictionary

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.

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

Returns: An ILLdictionary instance

Depends on:

  • DataSheet
  • Checking
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.

Self #

self

Current object instance

Signature:

Public Property Get Self() As ILLdictionary

Convenience accessor so consuming code can fluently retrieve the instance returned by Create without repeatedly typing Set x = New LLdictionary.

Returns: ILLdictionary

Data Elements

Data #

data

DataSheet backing store

Signature:

Public Property Get Data() As IDataSheet

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: IDataSheet

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

ResetColumnCaches #

reset-export-cache

Signature:

Private Sub ResetColumnCaches()

PersistTotalExports #

persist-exports

Signature:

Private Sub PersistTotalExports(ByVal totalCount As Long)

PersistTotalExportsOnSheet #

persist-exports-sheet

Signature:

Private Sub PersistTotalExportsOnSheet(ByVal sheet As Worksheet, ByVal totalCount As Long)

StoredTotalExportsFromSheet #

stored-exports

Signature:

Private Function StoredTotalExportsFromSheet(ByVal sheet As Worksheet) As Long

HiddenNameStore #

hidden-name-store

Signature:

Private Function HiddenNameStore(Optional ByVal sheet As Worksheet) As IHiddenNames

TrySheetNameDefinition #

try-sheet-name

Signature:

Private Function TrySheetNameDefinition(ByVal sheet As Worksheet, ByVal nameId As String) As Name

ExtractSimpleName #

extract-name

Signature:

Private Function ExtractSimpleName(ByVal qualifiedName As String) As String

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

Returns: Long

DictEndColumn #

endcol

Last data column

Signature:

Private Property Get DictEndColumn() As Long

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

Column Configuration

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.

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, ignoring case-sensitive duplicates to reflect how dictionary metadata is typically interpreted.

Parameters:

Returns: BetterArray

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.

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

AddRows #

addrows

Append rows via listobject

Signature:

Public Sub AddRows()

Adds rows to the listobject backing the dictionary while preserving table formatting. When no listobject is available the action is logged for traceability.

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)

RemoveRows #

removerows

Remove rows via listobject

Signature:

Public Sub RemoveRows()

Removes rows from the dictionary listobject using the shared ManageRows helper. Logs a note when a listobject is not present.

Specialised Views

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

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.

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.

Parameters:

EnsureRequiredColumns #

Ensure required columns are present in the dictionary

Signature:

Private Sub EnsureRequiredColumns()

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.

Parameters:

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.

Parameters:

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.

Parameters:

Translate #

translate

Translate dictionary labels

Signature:

Public Sub Translate(ByVal TransObject As ITranslationObject)

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

Parameters:

Used in (71 file(s))