LLFormat

Reads design information from the LL format table and exposes helpers to apply those styles while caching lookups for performance. Supports import/export of format definitions across workbooks. Each FormatScope constant maps to a specific visual pattern applied to Worksheets, Ranges, Shapes, ListObjects, or Characters.

Depends on: CustomTable, Checking, BetterArray, ProjectError

Version: 1.0 (2026-02-09)

Factory

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 ILLFormat

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: ILLFormat. A fully initialised LLFormat instance.

Design Lookups

design-value #

Look up a design value or colour from the format table

Signature:

Private 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.

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

apply-format #

Apply a design-driven format to an Excel object

Signature:

Private 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.

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 formatting definitions from another worksheet

Signature:

Private 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 the format table to another workbook

Signature:

Private 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, imports from it instead of exporting.

Parameters:

toWkb: Workbook. Destination workbook.

Internal members (not exported)

Cache Management

reset-caches #

Invalidate all cached references

Signature:

Private Sub ResetCaches()

ensure-format-ready #

Ensure caches are initialised and the worksheet is set

Signature:

Private Sub EnsureFormatReady()

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.

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.

format-table #

Retrieve the first ListObject on the format worksheet

Signature:

Private Function FormatTable() As ListObject

Returns: ListObject. The format table.

header-range #

Retrieve the header row range of the format table

Signature:

Private Function HeaderRange() As Range

Returns: Range. The header row.

ensure-label-column #

Cache the column index of the Label column

Signature:

Private Sub EnsureLabelColumn()

resolve-column-index #

Find the column index of a named header

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.

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.

label-row-index #

Resolve and cache the row index for a format label

Signature:

Private Function LabelRowIndex(ByVal labelName As String) As Long

Parameters:

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

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

Factory

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.

Public Interface

self #

Retrieve this formatter as an interface

Signature:

Public Property Get Self() As ILLFormat

Returns: ILLFormat. Self-reference.

wksh #

Retrieve the worksheet hosting the format table

Signature:

Public Property Get Wksh() As Worksheet

Returns: Worksheet. The host worksheet.

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.

design-name #

Name of the active design column

Signature:

Public Property Get DesignName() As String

Returns: String. The active design name.

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.

has-checkings #

Whether the format object has logged diagnostic messages

Signature:

Public Property Get HasCheckings() As Boolean

Returns: Boolean. True when diagnostic entries exist.

checking-values #

Retrieve the diagnostic log entries

Signature:

Public Property Get CheckingValues() As IChecking

Returns: IChecking. The checking instance, or Nothing.

Design Lookups

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.

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)

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.

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)

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.

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).

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).

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.

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.

Export

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.

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.

copy-format-table-styles #

Copy cell-level styles between two format tables

Signature:

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

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)

has-named-range #

Test whether a worksheet-scoped named range exists

Signature:

Private Function HasNamedRange(ByVal sh As Worksheet, ByVal rngName As String) As Boolean

Returns: Boolean. True when the named range is found.

throw-error #

Raise a typed project error

Signature:

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

Used in (25 file(s))