VarWriter

Writes a single dictionary variable to one or more linelist worksheets. For HList layers the writer targets the main sheet, a printed companion, and a CRF companion; for VList layers only the main sheet is written. Each invocation of WriteVariable applies label text, number formatting, formulas, dropdown choices, data validation, and conditional formatting to the resolved cell range. The class caches dictionary, variables, and sheets accessors for performance across repeated calls.

Depends on: ILinelistSpecs, ILLdictionary, ILLVariables, ILLSheets, ILLFormat, ITranslationObject, IFormulaData, IFormulas, IDropdownLists, ILLGeo, Formulas, LLVariables, LLSheets, BetterArray

Version: 1.0 (2026-02-10)

Instantiation

create #

Create a new VarWriter for the given layer and dependencies

Signature:

Public Function Create(ByVal layer As Byte, _
                       ByVal specs As ILinelistSpecs, _
                       ByVal wksh As Worksheet, _
                       Optional ByVal printWksh As Worksheet = Nothing, _
                       Optional ByVal crfWksh As Worksheet = Nothing, _
                       Optional ByVal dropdownObj As IDropdownLists = Nothing, _
                       Optional ByVal customDropdownObj As IDropdownLists = Nothing) As IVarWriter

Factory method that instantiates and configures a VarWriter. The caller must supply a valid ILinelistSpecs and target Worksheet at minimum. Optional parameters allow attaching printed and CRF companion worksheets (used only for HList layers) and dropdown list managers. Raises an error if specs or wksh is Nothing.

Parameters:

  • layer: Byte. VarWriterLayer enum value (HList=1, VList=2). Use Byte for macOS compatibility.
  • specs: ILinelistSpecs. The linelist specifications object providing dictionary, format, translations, etc.
  • wksh: Worksheet. The target main worksheet.
  • printWksh: Optional Worksheet. Printed companion worksheet (HList only). Defaults to Nothing.
  • crfWksh: Optional Worksheet. CRF companion worksheet (HList only). Defaults to Nothing.
  • dropdownObj: Optional IDropdownLists. Dropdown lists manager. Defaults to Nothing.
  • customDropdownObj: Optional IDropdownLists. Custom dropdown lists manager (for choice_custom). Defaults to Nothing.

Returns: IVarWriter. A fully initialised writer bound to the given layer and worksheets.

Throws:

  • VBA.vbObjectError + 516 if specs or wksh is Nothing.
Value Lookup

value-of #

Read a dictionary column value for the current variable

Signature:

Public Property Get ValueOf(ByVal colName As String) As String

Looks up the value stored in the specified dictionary column for the variable that was most recently passed to WriteVariable. This is the primary way all other methods retrieve variable metadata such as labels, types, formats, and control information.

Parameters:

  • colName: String. The dictionary column name to look up.

Returns: String. The column value for the current variable.

Entry Point

write-variable #

Write one variable to the target worksheet(s)

Signature:

Public Sub WriteVariable(ByVal varName As String)

Main public method called once per variable. It stores the variable name, resets the cached cell ranges, then dispatches to WriteMainSheet. For HList layers it additionally dispatches to WritePrintSheet and WriteCRFSheet for the companion worksheets.

Parameters:

  • varName: String. The variable name as it appears in the dictionary.
Internal members (not exported)

Instantiation

self #

Return the current instance cast to the IVarWriter interface

Signature:

Public Property Get Self() As IVarWriter

Used by the factory pattern so that the With New block can return an interface-typed reference instead of the concrete class.

Returns: IVarWriter. The current instance.

Internal Setters

internal-specs #

Assign the linelist specifications object

Signature:

Public Property Set InternalSpecs(ByVal specs As ILinelistSpecs)

Properties used by the factory only during instantiation.

internal-wksh #

Assign the target main worksheet

Signature:

Public Property Set InternalWksh(ByVal wksh As Worksheet)

internal-layer #

Assign the writer layer (HList or VList)

Signature:

Public Property Let InternalLayer(ByVal layer As Byte)

internal-print-wksh #

Assign the printed companion worksheet

Signature:

Public Property Set InternalPrintWksh(ByVal wksh As Worksheet)

internal-crf-wksh #

Assign the CRF companion worksheet

Signature:

Public Property Set InternalCRFWksh(ByVal wksh As Worksheet)

internal-dropdown #

Assign the dropdown lists manager

Signature:

Public Property Set InternalDropdown(ByVal dropdownObj As IDropdownLists)

internal-custom-dropdown #

Assign the custom dropdown lists manager

Signature:

Public Property Set InternalCustomDropdown(ByVal dropdownObj As IDropdownLists)

Cached Accessors

dictionary #

Return the cached dictionary object

Signature:

Private Property Get Dictionary() As ILLdictionary

Lazy-loaded accessors for frequently used dependency objects.

Lazily loads the ILLdictionary from specs on first access and caches it in the TVarWriter UDT. Subsequent calls return the cached instance without re-querying specs, which improves performance when writing many variables in sequence.

Returns: ILLdictionary. The linelist dictionary.

variables #

Return the cached variables helper

Signature:

Private Property Get Variables() As ILLVariables

Creates an LLVariables wrapper around the dictionary on first access. The wrapper provides convenient lookup methods such as Value and Contains that are used throughout the writer.

Returns: ILLVariables. The variables helper object.

sheets-object #

Return the cached sheets helper

Signature:

Private Property Get SheetsObject() As ILLSheets

Creates an LLSheets wrapper around the dictionary on first access. The wrapper provides VariableAddress and SheetInfo lookups used when building conditional-formatting formulas and resolving table anchors.

Returns: ILLSheets. The sheets helper object.

design-format #

Return the design format object from specs

Signature:

Private Property Get DesignFormat() As ILLFormat

Delegates directly to specs.DesignFormat on every call because the format object is lightweight and does not benefit from caching.

Returns: ILLFormat. The design format provider.

trans #

Return the translation object from specs

Signature:

Private Property Get Trans() As ITranslationObject

Delegates directly to specs.TransObject on every call. Used to fetch translated UI messages such as MSG_Calculated and MSG_CustomChoice.

Returns: ITranslationObject. The translation provider.

formula-data-obj #

Return the formula data object from specs

Signature:

Private Property Get FormulaDataObj() As IFormulaData

Delegates directly to specs.FormulaDataObject on every call. Provides the lookup tables and metadata needed by the Formulas parser.

Returns: IFormulaData. The formula data provider.

Cell Range Resolution

var-range #

Resolve the main cell range for the current variable

Signature:

Private Property Get VarRange() As Range

Computes the target cell from the column index dictionary value and the layer-specific start position. For HList the cell is in a column at HLIST_START_ROW + 1; for VList it is in a row at VLIST_START_COL. The result is cached until WriteVariable resets it for the next variable.

Returns: Range. The main cell range, or Nothing if the index is invalid.

var-print-range #

Resolve the printed companion cell range (HList only)

Signature:

Private Property Get VarPrintRange() As Range

Returns the cell on the printed companion worksheet that corresponds to the current variable. Uses the same column index as VarRange but targets the printWksh. Returns Nothing when no printed worksheet is attached.

Returns: Range. The printed companion cell range, or Nothing.

var-crf-range #

Resolve the CRF companion cell range (HList only)

Signature:

Private Property Get VarCRFRange() As Range

Returns the cell on the CRF companion worksheet at the row specified by the "crf index" dictionary value. Returns Nothing when no CRF worksheet is attached or when the crf index is zero or missing.

Returns: Range. The CRF companion cell range, or Nothing.

Sheet Dispatch

write-main-sheet #

Dispatch all writing steps for the main worksheet

Signature:

Private Sub WriteMainSheet()

Orchestrates the sequence of writing operations on the main worksheet: label, formula, type formatting, choices, validation, and conditional formatting. The exact set of conditional formatting steps depends on the active layer (HList applies geo, standard, and unique formatting; VList applies standard formatting only).

write-print-sheet #

Write variable label to the printed companion sheet (HList only)

Signature:

Private Sub WritePrintSheet()

Delegates to AddLabel with usePrinted=True so that the label is written to the printed companion worksheet instead of the main sheet. Exits silently when no printed worksheet is attached.

write-crf-sheet #

Write variable data to the CRF companion sheet (HList only)

Signature:

Private Sub WriteCRFSheet()

Resolves the CRF cell range and delegates to AddCRFLabel, AddCRFType, and AddCRFChoices. Exits silently when no CRF worksheet is attached or when the variable has no CRF index.

Label Writing

add-label #

Write variable label, sublabel, name, notes, and formatting

Signature:

Private Sub AddLabel(Optional ByVal usePrinted As Boolean = False)

Writes the main label, sublabel, variable name, notes, control info, and design formatting to the target cell range. For HList layers this includes header cells, named ranges for table anchors, comments for notes, hidden control rows, geo-specific formatting, editable labels, and column hiding. For VList layers the layout is row-based with adjacent cells for control info and label formatting.

Parameters:

Type Formatting

add-type #

Apply Excel number format based on variable type and format

Signature:

Private Sub AddType()

Reads the variable type (text, date, decimal, integer) and optional format string from the dictionary, then sets the NumberFormat property on the main cell range. Supports built-in formats such as round, percentage, euros, and dollars as well as custom format strings.

Formula Application

add-formula #

Parse and apply a formula to the variable cell

Signature:

Private Sub AddFormula()

Handles formula, case_when, and choice_formula control types. Parses the control details string through the Formulas class, validates the result, and writes the Excel formula to the cell. If the parsed formula fails a test application it is replaced with a failure placeholder string. Also applies calculated-formula design formatting to the cell and its header.

Choice Setup

add-choices #

Set up dropdown validation for choice, list, and geo variables

Signature:

Private Sub AddChoices()

Handles choice_manual, choice_multiple, choice_custom, list_auto, and geo1 control types. For each type it populates a BetterArray of categories, registers them with the appropriate IDropdownLists manager, and calls SetValidation to apply dropdown data validation to the cell. The choice_custom branch additionally creates a hyperlink from the label cell to the dropdown worksheet and adds a return link. The geo1 branch sets up admin1 through admin4 dropdown cascades.

Data Validation

add-validation #

Add min/max data validation to the variable cell

Signature:

Private Sub AddValidation()

Reads min and max values from the dictionary, converts them to Excel validation formulas via the ExcelFormula helper, and applies whole-number, date, or decimal validation with the configured alert style. Both OS-local and standard formula variants are attempted to ensure cross-platform compatibility. Text variables are skipped.

Conditional Formatting

add-conditional-formatting #

Add expression-based conditional formatting

Signature:

Private Sub AddConditionalFormatting()

Reads the formatting condition variable name from the dictionary, resolves its address, and adds an xlExpression format condition that highlights the cell when the condition variable equals 1. The interior colour, font colour, bold, and italic properties are copied from the formatting values cell in the dictionary.

add-unique-conditional-formatting #

Add unique/duplicate conditional formatting (HList only)

Signature:

Private Sub AddUniqueConditionalFormatting()

When the dictionary marks a variable with unique=yes, adds a duplicate- values format condition that highlights duplicate entries with a red interior. This is typically used for patient IDs or other fields that must be unique across records.

add-geo-conditional-formatting #

Add geo pcode conditional formatting (HList only)

Signature:

Private Sub AddGeoConditionalFormatting()

For geo-type variables, adds a format condition that highlights the cell in orange when the corresponding pcode variable is empty but the geo variable itself is not. This alerts users that a geographic name was entered but the pcode lookup failed.

CRF Companion

add-crf-label #

Write CRF label for the current variable

Signature:

Private Sub AddCRFLabel(ByVal crfRng As Range)

Writes the main label and sublabel to the CRF row, applies main-label and sublabel character formatting, and hides the row for formula-based or hidden variables. Also sets the table anchor named range on the first CRF row.

Parameters:

add-crf-type #

Apply CRF-specific type formatting

Signature:

Private Sub AddCRFType(ByVal crfRng As Range)

Maps the variable type and format to a CRF design scope constant (such as CRFText, CRFDate, CRFDecimal) and applies the corresponding visual format to the CRF cell. Skips choice_manual variables because their formatting is handled by AddCRFChoices instead.

Parameters:

add-crf-choices #

Write CRF choice headers and value cells

Signature:

Private Sub AddCRFChoices(ByVal crfRng As Range)

For choice_manual variables, retrieves the short-label categories from specs, writes optional column headers above the CRF row, and applies CRFChoiceValues formatting to each value cell. The headers are only written when the "crf choices" dictionary value is "yes".

Parameters:

Formula Helpers

failed-formula #

Test if a formula fails when applied to a cell

Signature:

Private Function FailedFormula(ByVal frmName As String) As Boolean

Writes the formula to cell A1 of the main worksheet as a test. If the assignment raises an error the function returns True, indicating the formula is invalid. The cell is cleared after the test regardless of outcome. Application.DisplayAlerts is temporarily suppressed during the test.

Parameters:

Returns: Boolean. True if the formula raised an error, False otherwise.

excel-formula #

Convert a formula value to an Excel-compatible format for validation

Signature:

Private Function ExcelFormula(ByVal formVal As String, _
                              ByVal validationTypes As Long, _
                              Optional ByVal useOS As Boolean = False) As Variant

Parses the given formula string through the Formulas class, optionally converts it to an OS-local formula via OSFormula, and then converts the result to the appropriate VBA type (CInt, CDate, or CLng) based on the validation type when no formula literals are present. Returns Empty if the formula is invalid or fails the FailedFormula test.

Parameters:

Returns: Variant. The converted formula value, or Empty if invalid.

os-formula #

Convert a formula to OS-specific local format

Signature:

Private Function OSFormula(ByVal formVal As String) As String

On macOS with VList layers, returns the formula as-is because FormulaLocal is unreliable. On Windows or for HList layers, temporarily writes the formula to cell A1, reads back FormulaLocal, and restores the previous cell content. This ensures validation formulas use the correct locale- aware separator characters.

Parameters:

Returns: String. The OS-local formula string.

Error Handling

throw-error #

Raise a descriptive VBA error from this class

Signature:

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

Raises a custom VBA error with number vbObjectError + 516 and the class name as the source. Called by Create when required arguments are Nothing.

Parameters:

Throws:

Interface Implementation

IVarWriter_WriteVariable #

Signature:

Private Sub IVarWriter_WriteVariable(ByVal varName As String)

Delegated members satisfying the IVarWriter contract.

Used in (4 file(s))