FormulaData

PublicAccessors

self #

Current object instance

Signature:

Public Property Get Self() As IFormulaData

Properties that expose the internal state and worksheet ranges. Convenience accessor that returns the current instance cast to its interface. Used internally by the factory method to hand back an IFormulaData reference from the predeclared Create pattern.

Returns: IFormulaData. The current instance cast to the interface.

wksh #

Worksheet hosting the lookup tables

Signature:

Public Property Get Wksh() As Worksheet

Returns the worksheet reference stored during creation. All cached lookups originate from ListObjects on this worksheet.

Returns: Worksheet. The worksheet reference stored at creation.

wksh-set #

Assign the worksheet hosting the lookup tables

Signature:

Public Property Set Wksh(ByVal formWksh As Worksheet)

Replaces the worksheet reference and invalidates all cached lookups so they are rebuilt from the new worksheet on the next access.

Parameters:

• formWksh: Worksheet. The worksheet providing formula metadata.

formula-range #

Data body range containing allowed formulas

Signature:

Public Property Get FormulaRange() As Range

Resolves the ENG column of the T_XlsFonctions ListObject and returns its DataBodyRange. Callers can use this range to inspect the raw worksheet data backing the function whitelist.

Returns: Range. The data body range covering the ENG column of the formulas table.

Throws:

• ProjectError.ElementNotFound When the table or column cannot be resolved.

special-characters-range #

Data body range containing allowed separator characters

Signature:

Public Property Get SpecialCharactersRange() As Range

Resolves the TEXT column of the T_ascii ListObject and returns its DataBodyRange. Provides direct access to the characters that the tokeniser treats as delimiters.

Returns: Range. The data body range covering the TEXT column of the characters table.

Throws:

• ProjectError.ElementNotFound When the table or column cannot be resolved.

PrivateHelpers

initialise #

Perform one-time initialisation of the instance

Signature:

Public Sub Initialise(ByVal formWksh As Worksheet)

Initialisation, validation, and cache management utilities. Stores the worksheet reference, clears any stale caches, and eagerly builds both function and character lookups so the instance is ready for immediate use after creation.

Parameters:

• formWksh: Worksheet. The worksheet providing lookup sources.

validate-worksheet #

Ensure the worksheet contains the required list objects and columns

Signature:

Private Sub ValidateWorksheet(ByVal targetSheet As Worksheet)

Guards against Nothing references and delegates to ValidateListObject for each of the two required tables (formulas and characters). Raises an error at the first missing element encountered.

Parameters:

• targetSheet: Worksheet. The worksheet to validate.

Throws:

• ProjectError.ObjectNotInitialized When targetSheet is Nothing.
• ProjectError.ElementNotFound When a required table or column is missing.
• ProjectError.ErrorUnexpectedState When a column contains no data.

validate-list-object #

Confirm the presence of a specific ListObject and column

Signature:

Private Sub ValidateListObject(ByVal hostSheet As Worksheet, _
                               ByVal tableName As String, _
                               ByVal columnName As String)

Attempts to locate the named ListObject and column on the host worksheet using guarded On Error Resume Next blocks. Raises a ProjectError when any component is missing or the column has no data.

Parameters:

• hostSheet: Worksheet. The worksheet containing the table.
• tableName: String. Name of the ListObject to inspect.
• columnName: String. Header expected within the table.

Throws:

• ProjectError.ElementNotFound When the table or column is absent.
• ProjectError.ErrorUnexpectedState When the column DataBodyRange is Nothing.

resolve-column-range #

Resolve and return the data body range for a column

Signature:

Private Function ResolveColumnRange(ByVal tableName As String, _
                                    ByVal columnName As String) As Range

Looks up the named table and column on the stored worksheet and returns its DataBodyRange. Raises an error when the range cannot be resolved, which typically indicates the table structure was modified after the instance was created.

Parameters:

• tableName: String. ListObject name.
• columnName: String. ListColumn header.

Returns: Range. The data body range containing the column data.

Throws:

• ProjectError.ElementNotFound When the column cannot be resolved.

reset-caches #

Reset cached lookups so they reload on next use

Signature:

Private Sub ResetCaches()

Sets the three cache references (functions, characters, group functions) to Nothing. The next call to any lookup method will trigger a lazy rebuild from the worksheet.

build-caches #

Build in-memory caches from the worksheet tables

Signature:

Private Sub BuildCaches()

Reads the formula and character ranges into BetterArray instances and seeds the grouped-function registry with default mappings. Function names are uppercased for case-insensitive matching while character tokens are stored verbatim.

ensure-functions #

Ensure the function lookup is initialised and return it

Signature:

Private Function EnsureFunctions() As BetterArray

Lazily rebuilds all caches when the function BetterArray is Nothing, then returns the function lookup for immediate use.

Returns: BetterArray. Collection keyed by uppercase function names.

ensure-characters #

Ensure the character lookup is initialised and return it

Signature:

Private Function EnsureCharacters() As BetterArray

Lazily rebuilds all caches when the character BetterArray is Nothing, then returns the character lookup for immediate use.

Returns: BetterArray. Collection keyed by literal characters.

build-lookup #

Convert a column of cells into a lookup array

Signature:

Private Function BuildLookup(ByVal sourceRange As Range, _
                             ByVal normaliseCase As Boolean) As BetterArray

Iterates over each cell in the source range, skips empty values, optionally uppercases the entry, and pushes only distinct values into the resulting BetterArray. The array uses a zero-based lower bound for consistent indexing across all caches.

Parameters:

• sourceRange: Range. The cells containing the values to cache.
• normaliseCase: Boolean. True to uppercase entries before storing.

Returns: BetterArray. Collection containing the distinct values.

GroupFunctions

initialise-group-functions #

Seed the grouped-formula registry with recognised mappings

Signature:

Private Sub InitialiseGroupFunctions()

Maintains an in-memory registry of grouped formulas along with the canonical Excel aggregation function each token maps to. Ensures the registry exists and registers the seven built-in grouped formulas (SUMIFS, COUNTIFS, NIFS, MINIFS, MAXIFS, MEDIANIFS, MEANIFS) together with their corresponding Excel aggregators and native flags.

ensure-group-registry #

Guarantee the grouped-formula registry exists and is seeded

Signature:

Private Sub EnsureGroupRegistry()

Creates a new zero-based BetterArray when the registry is Nothing. Called before any read or write operation on the grouped-function list to prevent Nothing-reference errors.

register-group-function #

Register a grouped formula token and its Excel aggregator

Signature:

Private Sub RegisterGroupFunction(ByVal functionName As String, _
                                  ByVal aggregatorName As String, _
                                  Optional ByVal useNative As Boolean = False)

Normalises both names to uppercase, removes any prior mapping for the same token, and stores a three-element array (token, aggregator, useNative flag). Also ensures that both the token and its aggregator appear in the main function cache so the tokeniser accepts them.

Parameters:

• functionName: String. Grouped token as it appears in setup formulas.
• aggregatorName: String. Canonical Excel aggregator to generate.
• useNative: Optional Boolean. When True the output uses the native *IFS function directly. Defaults to False.

ensure-function-token #

Insert a token into the Excel-function cache when absent

Signature:

Private Sub EnsureFunctionToken(ByVal functionToken As String)

Normalises the token to uppercase, creates the function cache if needed, and appends the token only when it is not already present. This ensures grouped function names are accepted by the tokeniser.

Parameters:

• functionToken: String. Token to guarantee in the lookup.

remove-group-function-mapping #

Remove any existing mapping for the provided grouped token

Signature:

Private Sub RemoveGroupFunctionMapping(ByVal functionName As String)

Iterates the registry in reverse order and removes entries whose first element matches the normalised function name. Reverse iteration prevents index shifting issues when removing from a BetterArray.

Parameters:

• functionName: String. Grouped formula token to clear.

ErrorHandling

throw-error #

Raise a consistent error for this class

Signature:

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

Centralised error-raising helper for consistent ProjectError usage. Wrapper around Err.Raise that standardises the source to "FormulaData", providing a consistent stack trace across all methods in this class.

Parameters:

• errNumb: Long. ProjectError code describing the failure.
• errorMessage: String. Human-readable explanation.

Throws:

• ProjectError. Always raises the specified error.

Interface

IFormulaData_SpecialCharacterIncludes #

Signature:

Private Function IFormulaData_SpecialCharacterIncludes(ByVal specialChar As String) As Boolean

Delegated members satisfying the IFormulaData contract. See the corresponding Private members above for full documentation.

• AnalysisOutput.cls
• CrossTableFormula.cls
• FormulaData.cls
• Formulas.cls
• IFormulaData.cls
• ILinelistSpecs.cls
• LinelistSpecs.cls
• VarWriter.cls
• ISetupErrors.cls
• SetupErrors.cls
• TestCrossTableFormula.bas
• TestFormulaData.bas
• TestFormulas.bas
• FormulaDataStub.cls
• LinelistSpecsWorkbookStub.cls
• LLVarContextSpecsStub.cls
• TableSpecsLinelistStub.cls