LLFormat

Reads the design values of a linelist from the format table and applies them. DesignValue answers one value by name. ApplyFormat takes a FormatScope and writes the look that scope stands for onto a Worksheet, a Range, a Shape, a ListObject or a run of Characters. Import and Export move the format table between workbooks, and DesignName says which design the object reads.

THE DESIGN VALUES ARE READ ONCE

DesignValue reads a value from the table the first time it is asked and holds the answer for the life of the object. A caller that edits the format sheet by hand builds a new object to reach the new values, or calls Import, which drops every held answer.

Depends on: CustomTable, Checking, BetterArray

Version: 1.0 (2026-02-09)

Factory

Create #

create

Instantiate an LL format engine for a worksheet

Signature:

Public Function Create(ByVal formatSh As Worksheet, _
                       Optional ByVal designType As String = DEFAULT_DESIGN) As LLFormat

Validates the format worksheet, resolves the design column, and returns a fully initialised LLFormat instance.

Parameters:

formatSh: Worksheet. Worksheet containing the format specification table.
designType: String. Design column to use. Defaults to DEFAULT_DESIGN.

Returns: LLFormat. A fully initialised LLFormat instance.

Design Lookups

DesignValue #

design-value

Look up a design value or colour from the format table

Signature:

Public Function DesignValue(ByVal labelName As String, _
                             Optional ByVal returnColor As Boolean = True) As Variant

Retrieves either the fill colour (Interior.Color) or the raw cell value for the specified label row under the active design column. Returns a fallback when the label is not found and logs a diagnostic. The answer is cached for the life of the object; see the class @details.

Parameters:

labelName: String. Label text stored in the format table.
returnColor: Boolean. When True returns Interior.Color; otherwise the raw value. Defaults to True.

Returns: Variant. The colour (Long) or cell value.

Formatting

ApplyFormat #

apply-format

Apply a design-driven format to an Excel object

Signature:

Public Sub ApplyFormat(ByVal formattingObject As Variant, _
                        ByVal scope As Byte, _
                        Optional ByVal nbDecimals As Integer = 0)

Reads style metadata from the format table and applies it to the supplied object according to the given FormatScope constant. The object is sorted ONCE here, by its type, and then handed to the sub that formats that kind of object. Every branch used to open with its own TypeName test and its own Set, 31 times over. An object of the wrong kind for the requested scope still leaves quietly - callers rely on that silence.

Parameters:

formattingObject: Variant. The Excel object to format.
scope: Byte. FormatScope constant selecting the visual pattern.
nbDecimals: Integer. Decimal count for numeric formatting. Defaults to 0.

Import

Import #

import

Import formatting definitions from another worksheet

Signature:

Public Sub Import(ByVal inpsh As Worksheet)

Reads design columns from the source worksheet and merges them into the current format table via CustomTable.Import. Updates the active design name from the source DESIGNTYPE named range.

Parameters:

inpsh: Worksheet. Source worksheet containing the format table.

Export

Export #

export

Export the format table to another workbook

Signature:

Public Sub Export(ByVal toWkb As Workbook)

Copies the format table and cell styles to a new worksheet in the destination workbook. When a worksheet with the same name already exists, the call turns into an Import from that sheet.

Parameters:

toWkb: Workbook. Destination workbook.

Internal members (not exported)

Cache Management

ResetCaches #

reset-caches

Invalidate all cached references

Signature:

Private Sub ResetCaches()

EnsureFormatReady #

ensure-format-ready

Ensure caches are initialised and the worksheet is set

Signature:

Private Sub EnsureFormatReady()

CachedValue #

cached-value

Read one entry out of a keyed cache

Signature:

Private Function CachedValue(ByVal store As Collection, _
                             ByVal cacheKey As String, _
                             ByRef found As Boolean) As Variant

Parameters:

store: Collection. The cache to read.
cacheKey: String. Key of the entry.
found: Boolean (ByRef). True when the key was present.

Returns: Variant. The stored value, or Empty when the key is absent.

StoreCachedValue #

store-cached-value

Add one entry to a keyed cache

Signature:

Private Sub StoreCachedValue(ByVal store As Collection, _
                             ByVal cacheKey As String, _
                             ByVal cacheValue As Variant)

Parameters:

store: Collection. The cache to write.
cacheKey: String. Key of the entry.
cacheValue: Variant. Value to keep.

Format Table Access

TableOfSheet #

table-of-sheet

Retrieve the first ListObject of a worksheet

Signature:

Private Function TableOfSheet(ByVal sh As Worksheet) As ListObject

Parameters:

sh: Worksheet. The worksheet to read.

Returns: ListObject. The first table on the sheet.

HeaderOfTable #

header-of-table

Retrieve the header row of a format table

Signature:

Private Function HeaderOfTable(ByVal formatLo As ListObject) As Range

Parameters:

formatLo: ListObject. The format table.

Returns: Range. The header row.

EnsureFormatSheetReady #

ensure-format-sheet-ready

Validate a worksheet as a valid format source

Signature:

Private Sub EnsureFormatSheetReady(ByVal sh As Worksheet, ByRef resolvedDesign As String)

Parameters:

sh: Worksheet. The worksheet to validate.
resolvedDesign: String (ByRef). Falls back to DEFAULT_DESIGN when the requested design column is absent.

LogInfo #

log-info

Append a diagnostic entry to the internal checking object

Signature:

Private Sub LogInfo(ByVal label As String)

Parameters:

label: String. The message to record.

HarvestCollaboratorCheckings #

harvest-collaborator-checkings

Fold what a table adapter filed into this trace

Signature:

Private Sub HarvestCollaboratorCheckings(ByVal collaboratorChecks As Checking)

CustomTable keeps a store of its own, and nobody read it before this line. An import that aborted, or headers the export could not find, was filed and then dropped.

Parameters:

collaboratorChecks: Checking. What the adapter filed.

FormatTable #

format-table

Retrieve the first ListObject on the format worksheet

Signature:

Private Function FormatTable() As ListObject

Returns: ListObject. The format table.

HeaderRange #

header-range

Retrieve the header row range of the format table

Signature:

Private Function HeaderRange() As Range

Returns: Range. The header row.

EnsureLabelColumn #

ensure-label-column

Cache the column index of the Label column

Signature:

Private Sub EnsureLabelColumn()

FindColumnIndex #

find-column-index

Find the column index of a named header, or zero when it is absent

Signature:

Private Function FindColumnIndex(ByVal header As Range, ByVal columnName As String) As Long

Parameters:

header: Range. The header row to search.
columnName: String. Column caption to locate.

Returns: Long. The 1-based column index, or 0 when not found.

ColumnExists #

column-exists

Test whether a named column exists in a header range

Signature:

Private Function ColumnExists(ByVal header As Range, ByVal columnName As String) As Boolean

Parameters:

header: Range. The header row to search.
columnName: String. Column caption to test.

Returns: Boolean. True when the column is found.

ResolveColumnIndex #

resolve-column-index

Find the column index of a named header, raising when it is absent

Signature:

Private Function ResolveColumnIndex(ByVal header As Range, ByVal columnName As String) As Long

Parameters:

header: Range. The header row to search.
columnName: String. Column caption to locate.

Returns: Long. The 1-based column index.

DesignColumnIndex #

design-column-index

Resolve and cache the column index for a design name

Signature:

Private Function DesignColumnIndex(ByVal designName As String) As Long

Parameters:

designName: String. Name of the design column.

Returns: Long. The 1-based column index.

LabelRowIndex #

label-row-index

Resolve and cache the row index for a format label

Signature:

Private Function LabelRowIndex(ByVal labelName As String) As Long

The lookup tries an exact match first and then a partial one. Four of the labels this class asks for are stored in the format table with extra words around them - "gap for graphs" sits in "default row height gap for graphs", "dropdown interior color" in "Select dropdown interior color", "time series header font" in "Time series header font color" and "time series header interior" in "Time series header interior color". Partial matching has to stay for those four. The exact pass runs first so that a new row whose label merely CONTAINS an existing one leaves that older row alone.

Parameters:

labelName: String. Label text to locate in the Label column.

Returns: Long. The 1-based row index, or 0 when not found.

Public Interface

Wksh #

wksh

Retrieve the worksheet hosting the format table

Signature:

Public Property Get Wksh() As Worksheet

Returns: Worksheet. The host worksheet.

Wksh #

wksh-set

Assign the worksheet backing this formatter

Signature:

Public Property Set Wksh(ByVal sh As Worksheet)

Clears all caches when the worksheet changes.

Parameters:

sh: Worksheet. The new host worksheet.

DesignName #

design-name

Name of the active design column

Signature:

Public Property Get DesignName() As String

Returns: String. The active design name.

DesignName #

design-name-set

Change the active design column

Signature:

Public Property Let DesignName(ByVal design As String)

Resets cached lookups when the design changes.

Parameters:

design: String. The new design column name.

HasCheckings #

has-checkings

Whether the format object has logged diagnostic messages

Signature:

Public Property Get HasCheckings() As Boolean

Returns: Boolean. True when diagnostic entries exist.

CheckingValues #

checking-values

Retrieve the diagnostic log entries

Signature:

Public Property Get CheckingValues() As Checking

Returns: Checking. The checking instance, or Nothing.

Design Lookups

AnalysisBaseFontSize #

analysis-base-font-size

Base font size used by the analysis format scopes

Signature:

Private Function AnalysisBaseFontSize() As Variant

Returns: Variant. The design font size, or 11 when the design cell is blank.

Drawing Helpers

DrawLines #

draw-lines

Draw border lines around a range

Signature:

Private Sub DrawLines(ByVal rng As Range, _
                      Optional At As String = "All", _
                      Optional weight As Integer = xlHairline, _
                      Optional line As Integer = xlContinuous, _
                      Optional color As Variant = vbBlack)

Parameters:

rng: Range. Target range.
At: String. Edge selector (All, AvoidLeft, AvoidRight, etc.). Defaults to "All".
weight: Integer. Line weight constant. Defaults to xlHairline.
line: Integer. Line style constant. Defaults to xlContinuous.
color: Variant. Border colour. Defaults to vbBlack.

DrawBorder #

draw-border

Draw a rectangular border around a range

Signature:

Private Sub DrawBorder(ByVal rng As Range, Optional weight As Integer = xlThin, _
                       Optional color As Variant = vbBlack, _
                       Optional line As Long = xlContinuous)

Writes the four properties in the SAME order as DrawLines. Several scopes called DrawLines four times, once per edge, and now call this sub once. With line:=xlDouble the LineStyle write is what settles the final weight, so a different order would make those frames come out heavier.

Parameters:

rng: Range. Target range to border.
weight: Optional Integer. Line weight constant. Defaults to xlThin.
color: Optional Variant. Border colour. Defaults to vbBlack.
line: Optional Long. Line style constant. Defaults to xlContinuous.

FormatRange #

format-range

Apply fill, font, alignment, and number format to a range

Signature:

Private Sub FormatRange(ByVal rng As Range, _
                        Optional ByVal fillValue As String = "", _
                        Optional ByVal interiorColor As Variant = "", _
                        Optional ByVal fontColor As Variant = "", _
                        Optional ByVal isBold As Boolean = False, _
                        Optional ByVal Horiz As Integer = xlHAlignCenter, _
                        Optional ByVal Verti As Integer = xlVAlignCenter, _
                        Optional ByVal FontSize As Double = 0, _
                        Optional ByVal NumFormat As String = "", _
                        Optional ByVal wrap As String = vbNullString)

The Variant arguments are compared against vbNullString to mean "leave this property alone". That works because VBA sorts every number below every string, and several scopes pass vbNullString on purpose to skip a property.

Parameters:

rng: Range. Target range.
fillValue: Optional String. Cell value to write. Defaults to "".
interiorColor: Optional Variant. Interior fill colour. Defaults to "".
fontColor: Optional Variant. Font colour. Defaults to "".
isBold: Optional Boolean. Bold font. Defaults to False.
Horiz: Optional Integer. Horizontal alignment. Defaults to xlHAlignCenter.
Verti: Optional Integer. Vertical alignment. Defaults to xlVAlignCenter.
FontSize: Optional Double. Font size in points. Defaults to 0 (unchanged).
NumFormat: Optional String. Number format string. Defaults to "".
wrap: Optional String. When "wrap", enables text wrapping. Defaults to vbNullString.

FormatCharacters #

format-characters

Apply font styling to a Characters object

Signature:

Private Sub FormatCharacters(ByVal Char As Characters, _
                             Optional ByVal fontColor As Variant = "", _
                             Optional ByVal isBold As Boolean = False, _
                             Optional ByVal FontSize As Double = 0)

Parameters:

Char: Characters. The Characters object to style.
fontColor: Optional Variant. Font colour. Defaults to "".
isBold: Optional Boolean. Bold font. Defaults to False.
FontSize: Optional Double. Font size in points. Defaults to 0 (unchanged).

FormatShape #

format-shape

Apply fill and font styling to a Shape object

Signature:

Private Sub FormatShape(ByVal shp As Shape, _
                        Optional ByVal fontColor As Variant = vbNullString, _
                        Optional ByVal interiorColor As Variant = vbNullString, _
                        Optional ByVal isBold As Boolean = False, _
                        Optional ByVal FontSize As Double = 0)

Parameters:

shp: Shape. The Shape to style.
fontColor: Optional Variant. Text colour. Defaults to vbNullString.
interiorColor: Optional Variant. Fill colour. Defaults to vbNullString.
isBold: Optional Boolean. Bold font. Defaults to False.
FontSize: Optional Double. Font size in points. Defaults to 0 (unchanged).

RemoveGridLines #

remove-gridlines

Hide gridlines on a worksheet

Signature:

Private Sub RemoveGridLines(ByVal sh As Worksheet, Optional DisplayZeros As Boolean = False)

Parameters:

sh: Worksheet. Target worksheet.
DisplayZeros: Optional Boolean. When True, shows zero values. Defaults to False.

PreparePrintSheet #

prepare-print-sheet

Configure page setup for printing a worksheet

Signature:

Private Sub PreparePrintSheet(ByVal sh As Worksheet)

Parameters:

sh: Worksheet. Target worksheet to configure.

Formatting

ApplyWorksheetFormat #

apply-worksheet-format

Apply the worksheet-level format scopes

Signature:

Private Sub ApplyWorksheetFormat(ByVal sh As Worksheet, ByVal scope As Byte)

Parameters:

sh: Worksheet. The worksheet to format.
scope: Byte. FormatScope constant.

ApplyCharactersFormat #

apply-characters-format

Apply the label format scopes to a Characters object

Signature:

Private Sub ApplyCharactersFormat(ByVal Char As Characters, ByVal scope As Byte)

Parameters:

Char: Characters. The label text to format.
scope: Byte. FormatScope constant.

ApplyShapeFormat #

apply-shape-format

Apply the button format scopes to a Shape

Signature:

Private Sub ApplyShapeFormat(ByVal shp As Shape, ByVal scope As Byte)

Parameters:

shp: Shape. The button to format.
scope: Byte. FormatScope constant.

ApplyListObjectFormat #

apply-listobject-format

Apply the entry table format scopes to a ListObject

Signature:

Private Sub ApplyListObjectFormat(ByVal tableObject As ListObject, ByVal scope As Byte)

Parameters:

tableObject: ListObject. The entry table to format.
scope: Byte. FormatScope constant.

FormatMissingLook #

format-missing-look

The shared look of the missing and one-cell analysis scopes

Signature:

Private Sub FormatMissingLook(ByVal rng As Range, _
                              ByVal horizAlign As Long, _
                              ByVal drawInsideLines As Boolean)

Six scopes share this block: the same two design colours, the same FormatRange call apart from the horizontal alignment, and the same double frame around the cells.

Parameters:

rng: Range. Target range.
horizAlign: Long. Horizontal alignment constant.
drawInsideLines: Boolean. True to rule the inside of the block as well.

FormatSectionBody #

format-section-body

The shared tail of the section and sub-section scopes

Signature:

Private Sub FormatSectionBody(ByVal rng As Range, _
                              ByVal interiorColorValue As Variant, _
                              ByVal fontColorValue As Variant, _
                              ByVal fontSizeValue As Variant)

Parameters:

rng: Range. Target range.
interiorColorValue: Variant. Fill colour.
fontColorValue: Variant. Font colour.
fontSizeValue: Variant. Font size in points.

ApplyRangeFormat #

apply-range-format

Apply every range-level format scope

Signature:

Private Sub ApplyRangeFormat(ByVal baseRng As Range, _
                             ByVal scope As Byte, _
                             ByVal nbDecimals As Integer)

baseRng always holds the range the caller passed. rng is the working range and several scopes move it, so anything that has to measure from the original reads baseRng.

Parameters:

baseRng: Range. The range the caller passed.
scope: Byte. FormatScope constant.
nbDecimals: Integer. Decimal count for the CRF numeric scopes.

Export

ResolveWorksheet #

resolve-worksheet

Look up a worksheet by name without raising errors

Signature:

Private Function ResolveWorksheet(ByVal targetWorkbook As Workbook, ByVal sheetName As String) As Worksheet

Returns: Worksheet. The found worksheet, or Nothing.

ResolveFormatTable #

resolve-format-table

Locate a ListObject on the target sheet by name or index

Signature:

Private Function ResolveFormatTable(ByVal targetSheet As Worksheet, ByVal preferredName As String) As ListObject

Returns: ListObject. The found table, or Nothing.

CopyFormatTableStyles #

copy-format-table-styles

Copy cell-level styles between two format tables

Signature:

Private Sub CopyFormatTableStyles(ByVal sourceTable As ListObject, ByVal targetTable As ListObject)

Block at a time. Excel answers Null for a property whose value varies across a range, so a block styled the same all the way down takes six writes for the whole block, and a mixed block falls back to the cell loop. The header row is taken on its own, because it carries its own styling and would make every column read as mixed. The limit of this: the design columns hold a different colour on every row, which is the whole point of the format table, so those columns are still walked cell by cell. VBA offers a block format copy only through the clipboard. Copy plus PasteSpecial xlPasteFormats would do the whole table in one step, and the clipboard is the least reliable path on Mac Excel, so this code stays off it.

CopyStyles #

copy-styles

Copy the styles of one block, in one step when the block allows it

Signature:

Private Sub CopyStyles(ByVal sourceBlock As Range, ByVal targetBlock As Range)

Parameters:

sourceBlock: Range. Block to read.
targetBlock: Range. Block of the same shape to write.

CopyStylesAsBlock #

copy-styles-as-block

Copy a block of styles in a single write when the block is uniform

Signature:

Private Function CopyStylesAsBlock(ByVal sourceBlock As Range, _
                                   ByVal targetBlock As Range) As Boolean

Excel answers Null for a property whose value varies across a range, so reading the six properties off the whole block is both the test and the read.

Parameters:

sourceBlock: Range. Block to read.
targetBlock: Range. Block of the same shape to write.

Returns: Boolean. True when the whole block was copied in one step.

CopyStylesByCell #

copy-styles-by-cell

Copy a block of styles cell by cell

Signature:

Private Sub CopyStylesByCell(ByVal sourceBlock As Range, ByVal targetBlock As Range)

Both blocks are walked with a single Cells index, so the same loop serves a one-row block and a one-column block. Each cell fetches its Interior and its Font once, then takes three properties off each.

Parameters:

sourceBlock: Range. Block to read.
targetBlock: Range. Block of the same shape to write.

EnsureDesignTypeRange #

ensure-design-type-range

Create the DESIGNTYPE named range on the target sheet

Signature:

Private Sub EnsureDesignTypeRange(ByVal sourceSheet As Worksheet, _
                                  ByVal targetSheet As Worksheet, _
                                  ByVal targetTable As ListObject)

NamedRange #

named-range

Retrieve a worksheet-scoped named range, or Nothing when it is absent

Signature:

Private Function NamedRange(ByVal sh As Worksheet, ByVal rngName As String) As Range

Parameters:

sh: Worksheet. The worksheet to read.
rngName: String. The name to look up.

Returns: Range. The named range, or Nothing.

ThrowError #

throw-error

Raise a typed project error

Signature:

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

Seal #

seal

Seal the instance so setup setters can no longer be written

Signature:

Public Sub Seal()

GuardNotSealed #

guard-not-sealed

Guard a setup setter against writes after sealing

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

Parameters:

propName: String. Name of the property being guarded.

Used in (27 file(s))

AnalysisOutput.cls
CrossTable.cls
CustomPivotTable.cls
DesignerPreparation.cls
Buttons.cls
HiddenNames.cls
Graphs.cls
EventLinelist.cls
Linelist.cls
LinelistSpecs.cls
LLDataEntry.cls
SectionBuilder.cls
VarWriter.cls
SetupImport.cls
EventsDesignerCore.bas
InitTransfer.bas
TestAnalysisOutput.bas
TestCrossTable.bas
TestCustomPivotTable.bas
TestDesignerPreparation.bas
TestButtons.bas
TestLLFormat.bas
LLFormatTestFixture.bas
TestEventLinelistSheets.bas
TestLLDataEntry.bas
TestSectionBuilder.bas
TestVarWriter.bas