Formulas

Factory

Seal #

seal

Prevent further changes to setup-only properties

Signature:

Public Sub Seal()

Marks the instance as sealed so guarded setters raise when invoked after construction. Called by the factory before returning. The class holds a parse result, and a setter running behind it would leave tokens that belong to another expression.

GuardNotSealed #

guard-not-sealed

Reject writes to setup-only properties after sealing

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

Raises an error when a guarded setter is invoked on a sealed instance.

Parameters:

• propName: String. Name of the property being guarded.

PublicAccessors

Dictionary #

dictionary

Retrieve the underlying dictionary used during parsing

Signature:

Public Property Get Dictionary() As LLdictionary

Exposes configured dependencies, cached evaluation results, and parsed Excel expressions.

Returns the LLdictionary previously supplied during Create or Initialise. Used by downstream helpers to resolve variable metadata.

Returns: LLdictionary. Dictionary backing metadata lookups.

Dictionary #

dictionary-set

Assign a new dictionary and reset dependent caches

Signature:

Public Property Set Dictionary(ByVal dict As LLdictionary)

Replaces the current dictionary and invalidates all lazily resolved dependencies. The factory seals the instance, so this raises after construction.

Parameters:

• dict: LLdictionary. Variable metadata provider.

Throws:

• ProjectError.ObjectNotInitialized When dict is Nothing.
• ProjectError.SomethingWentWrong When the instance is sealed.

Data #

data

Retrieve the formula configuration catalogue

Signature:

Public Property Get Data() As FormulaData

Returns the FormulaData describing available Excel tokens, operators, and special characters used during tokenisation.

Returns: FormulaData. Configuration catalogue.

Data #

data-set

Update the formula configuration catalogue and invalidate caches

Signature:

Public Property Set Data(ByVal formData As FormulaData)

Replaces the current formula data and invalidates all cached parsing results. The factory seals the instance, so this raises after construction.

Parameters:

• formData: FormulaData. Catalogue of allowed operators and functions.

Throws:

• ProjectError.ObjectNotInitialized When formData is Nothing.
• ProjectError.SomethingWentWrong When the instance is sealed.

SetupFormula #

setup-formula

Retrieve the original setup pseudo-formula

Signature:

Public Property Get SetupFormula() As String

Returns the raw expression as captured from the setup sheet during Create or the last assignment.

Returns: String. Raw expression.

SetupFormula #

setup-formula-set

Store the setup pseudo-formula and reset computed caches

Signature:

Public Property Let SetupFormula(ByVal setupForm As String)

Replaces the stored expression and invalidates all cached parsing results. The factory seals the instance, so this raises after construction.

Parameters:

• setupForm: String. Pseudo-formula captured from setup.

Throws:

• ProjectError.SomethingWentWrong When the instance is sealed.

PrivateHelpers

Initialise #

initialise

Store incoming dependencies and prepare caches

Signature:

Public Sub Initialise(ByVal dict As LLdictionary, _
                       ByVal formData As FormulaData, _
                       ByVal setupForm As String)

Internal utilities for dependency management, cache invalidation, and token resolution.

Assigns the dictionary, formula data, and setup expression, then drops the lazily built helpers and invalidates the parse cache once. It stays Public because the factory calls it on a second instance, and it is guarded: after the factory seals that instance, this raises.

Parameters:

• dict: LLdictionary. Variable metadata provider.
• formData: FormulaData. Catalogue of allowed tokens and operators.
• setupForm: String. Pseudo-formula to parse.

Throws:

• ProjectError.SomethingWentWrong When the instance is sealed.

IsCustomFormula #

is-custom-formula

Determine whether a token is one of the custom analysis formulas

Signature:

Private Function IsCustomFormula(ByVal token As String) As Boolean

MEAN, N and N() are the whole set and they are compiled in. The match is case-sensitive, which is what the old list lookup did. AppendToken accepts these in the analysis context only, and ParsedAnalysisFormula tests the same three. Both tests are needed.

Parameters:

• token: String. Candidate token.

Returns: Boolean. True when the token is a custom analysis formula.

InvalidateCaches #

invalidate-caches

Reset cached parsing results and computed flags

Signature:

Private Sub InvalidateCaches()

Clears the token list, variable list, validity flags, and group context so the next access triggers a full reparse from the stored setup expression.

ResetGroupContext #

reset-group-context

Clear any grouped-formula metadata captured during previous evaluations

Signature:

Private Sub ResetGroupContext()

Resets all fields of the TGroupContext UDT to their default values.

VariablesProvider #

variables-provider

Lazily instantiate the LLVariables helper from the dictionary

Signature:

Private Function VariablesProvider() As LLVariables

Creates the LLVariables wrapper on first access and caches it for subsequent calls.

Returns: LLVariables. Bound to the current dictionary.

SheetProvider #

sheet-provider

Lazily instantiate the LLSheets helper for address resolution

Signature:

Private Function SheetProvider() As LLSheets

Creates the LLSheets wrapper on first access and caches it for subsequent calls.

Returns: LLSheets. Bound to the current dictionary.

EvaluationContext #

evaluation-context

Map the three context names onto the two real contexts

Signature:

Private Function EvaluationContext(ByVal formulaType As String) As String

"analysis" is one context and everything else is the other. Production passes "linelist", the class passes itself "simple", and the public defaults are "analysis". Every branch of EvaluateFormula asks the same question, so holding three names made the same expression parse twice for the same answer.

Parameters:

• formulaType: String. Context name as the caller wrote it.

Returns: String. CONTEXT_ANALYSIS or CONTEXT_LINELIST.

EnsureEvaluation #

ensure-evaluation

Guarantee that a parsing/validation run exists for the requested context

Signature:

Private Function EnsureEvaluation(ByVal formulaType As String) As Boolean

Checks whether the cached evaluation matches the requested context. When a mismatch is detected or no evaluation has been performed, a full EvaluateFormula pass is triggered.

Parameters:

• formulaType: String. Context (analysis/linelist/simple).

Returns: Boolean. True when the cached evaluation is valid.

EvaluateFormula #

evaluate-formula

Parse the setup expression, populate tokens, and capture validation results

Signature:

Private Sub EvaluateFormula(ByVal context As String)

Converts the stored setup formula through custom function handlers, then tokenises the result while validating parentheses and literals. Short-circuits for single-variable or single-custom-function expressions. Delegates grouped formula detection to GroupContexExtractionSucceed.

Parameters:

• context: String. CONTEXT_ANALYSIS or CONTEXT_LINELIST.

GroupFormula

GroupContexExtractionSucceed #

group-context-extraction

Analyse the tokenised expression and record grouped metadata when relevant

Signature:

Private Function GroupContexExtractionSucceed(ByVal tokens As BetterArray, _
                                       ByVal variableTokens As BetterArray, _
                                       ByVal vars As LLVariables, _
                                       ByVal dataSource As FormulaData) As Boolean

Detects grouped formulas, validates arguments, and prepares metadata for downstream builders.

Inspects the root function name to determine whether the formula targets grouped evaluation. When grouped, validates that exactly three variable arguments are present and that the first and third variables belong to the same table. Records the captured metadata in the TGroupContext UDT for BuildGroupedFormula.

Parameters:

• tokens: BetterArray. Tokenised formula.
• variableTokens: BetterArray. Variable names in encounter order.
• vars: LLVariables. Helper exposing dictionary metadata.
• dataSource: FormulaData. Configuration describing grouped functions.

Returns: Boolean. True when grouped context is captured or not required.

RootFunctionName #

root-function-name

Retrieve the entry token corresponding to the root function

Signature:

Private Function RootFunctionName(ByVal tokens As BetterArray) As String

Returns the first token from the tokenised expression, which is expected to be the function name when the formula is function-based.

Parameters:

• tokens: BetterArray. Tokenised expression.

Returns: String. Root token or empty string when unavailable.

IsGenericGroupFunction #

is-generic-group-function

Determine whether a function uses the GROUP prefix for grouped evaluation

Signature:

Private Function IsGenericGroupFunction(ByVal functionName As String) As Boolean

Returns True when the token starts with "GROUP" (case-insensitive). A token such as GROUPING_X is read as generic and its aggregator fails the whitelist check with a clear message, which is the wanted behaviour.

Parameters:

• functionName: String. Token under inspection.

Returns: Boolean. True when the token starts with "GROUP".

ExtractGenericAggregator #

extract-generic-aggregator

Parse the aggregator portion from a GROUP-prefixed function

Signature:

Private Function ExtractGenericAggregator(ByVal functionName As String) As String

Splits the function name at the underscore separator and returns the trailing portion as the aggregator token. When no underscore is present, strips the "GROUP" prefix directly.

Parameters:

• functionName: String. Grouped token (e.g. GROUP_SUM or GROUPS_SUM).

Returns: String. Aggregator token to apply when building the Excel formula.

BuildGroupedFormula #

build-grouped-formula

Construct the grouped Excel expression using captured metadata

Signature:

Private Function BuildGroupedFormula(ByVal useTableName As Boolean, _
                                     ByVal tablePrefix As String) As String

Uses the TGroupContext UDT to resolve variable ranges and emit the appropriate aggregation expression. Native *IFS functions produce direct SUMIFS/COUNTIFS calls; non-native functions wrap the result in an IF-based array formula.

Parameters:

• useTableName: Boolean. True to emit structured references.
• tablePrefix: String. Prefix applied to structured references.

Returns: String. Grouped Excel formula ready for consumption.

GroupedDataRange #

grouped-data-range

Resolve the appropriate range reference for a grouped variable

Signature:

Private Function GroupedDataRange(ByVal variableName As String, _
                                  ByVal useTableName As Boolean, _
                                  ByVal tablePrefix As String, _
                                  ByVal sheets As LLSheets) As String

Emits either a structured table reference or a sheet-level address depending on the useTableName flag.

Parameters:

• variableName: String. Variable identifier.
• useTableName: Boolean. True to emit structured references.
• tablePrefix: String. Prefix applied to structured references.
• sheets: LLSheets. Provider for address resolution.

Returns: String. Excel reference targeting the grouped variable.

Tokenisation

TokeniseFormula #

tokenise-formula

Break the converted formula into tokens while validating parentheses and literals

Signature:

Private Function TokeniseFormula(ByVal formula As String, _
                                 ByVal context As String, _
                                 ByVal tokens As BetterArray, _
                                 ByVal variableTokens As BetterArray, _
                                 ByRef HasSetupVariables As Boolean, _
                                 ByRef failureReason As String) As Boolean

Breaks expressions into tokens, validates each chunk, and handles custom formula expansion.

Walks the formula character by character, splitting on special characters defined by the FormulaData configuration. Tracks parenthesis depth and quotation state. Each extracted chunk is validated through AppendToken. The walk reads one character at a time. CaseWhen, ChoiceFormula and ValueOfFormula hold the same shape of walk over their own bodies. What made this loop expensive was the three Excel calls inside CleanString and the array copy inside the character lookup, and both of those are gone.

Parameters:

• formula: String. Cleaned setup expression.
• context: String. CONTEXT_ANALYSIS or CONTEXT_LINELIST.
• tokens: BetterArray. Recipient for all tokens.
• variableTokens: BetterArray. Recipient for variable-only tokens.
• HasSetupVariables: Boolean. Flag capturing literal presence.
• failureReason: String. Explanation when tokenisation fails.

Returns: Boolean. True when parsing succeeds.

AppendToken #

append-token

Validate and append the provided chunk to the tokens collection

Signature:

Private Function AppendToken(ByVal chunk As String, _
                             ByRef context As String, _
                             ByRef tokens As BetterArray, _
                             ByRef variableTokens As BetterArray, _
                             ByRef vars As LLVariables, _
                             ByRef dataSource As FormulaData, _
                             ByRef HasSetupVariables As Boolean, _
                             ByRef failureReason As String) As Boolean

Classifies the chunk as a grouped function, known variable, boolean literal, registered Excel formula, custom formula, numeric literal, or quoted string. Unknown chunks cause a validation failure. The grouped-function test runs on the FIRST token only. That is what stops SUM(SUMIFS(...)) from being read as a grouped formula.

Parameters:

• chunk: String. Candidate token.
• context: String. CONTEXT_ANALYSIS or CONTEXT_LINELIST.
• tokens: BetterArray. Recipient for all tokens.
• variableTokens: BetterArray. Recipient for variable-only tokens.
• vars: LLVariables. Metadata provider.
• dataSource: FormulaData. Formula metadata provider.
• HasSetupVariables: Boolean. Flag capturing literal presence.
• failureReason: String. Explanation when parsing fails.

Returns: Boolean. True when the chunk is accepted.

IsQuotedString #

is-quoted-string

Determine whether the supplied value is wrapped in quotes

Signature:

Private Function IsQuotedString(ByVal value As String) As Boolean

Returns True when the string starts and ends with a double-quote character and has a length of at least two.

Parameters:

• value: String. Candidate literal.

Returns: Boolean. True when the string is quoted.

IsNumericLiteral #

is-numeric-literal

Determine whether the supplied chunk is an English-form numeric literal

Signature:

Private Function IsNumericLiteral(ByVal chunk As String) As Boolean

Accepts digits around at most one dot. The convention keeps setup formulas in English, and Range.Formula reads US syntax on every host, so the English form is the one form a literal may take here. IsNumeric would read the chunk in the HOST's regional separators, and on a comma machine it refused every dot literal a setup file carried. A sign never reaches this test: '+' and '-' are separator characters, so they split off before the chunk arrives.

Parameters:

• chunk: String. Candidate literal.

Returns: Boolean. True when the chunk holds at least one digit and nothing besides digits and a single dot.

CleanString #

clean-string

Normalise the incoming token by removing control characters and duplicate spaces

Signature:

Private Function CleanString(ByVal value As String) As String

Replaces non-breaking spaces, drops control characters, squeezes runs of spaces to one, and trims the ends. This is what the worksheet SUBSTITUTE, CLEAN and TRIM trio did, written in plain VBA: the three of them crossed into Excel once each, and this runs once per token of every formula in the workbook. AscW answers a negative number above code point 32767, so the control-code test guards on zero. Worksheet TRIM squeezes runs of spaces INSIDE the text as well as at the ends, so that is reproduced.

Parameters:

• value: String. Token to clean.

Returns: String. Trimmed and cleaned representation.

ParsedCustomFormula #

parsed-custom-formula

Delegate parsing to custom functions when present in analysis context

Signature:

Private Function ParsedCustomFormula(ByVal customFunction As String, _
                                     ByVal formCond As FormulaCondition, _
                                     Optional ByVal tablePrefix As String = vbNullString) As String

Handles MEAN (mapped to AVERAGE), N/N() (mapped to COUNTIFS), and passes through unknown custom identifiers unchanged. The COUNTIFS expansion builds criteria pairs from the FormulaCondition. The table always comes from the FormulaCondition. It used to be carried from whichever variable token came first, so the same expression gave two answers depending on the order of its tokens.

Parameters:

• customFunction: String. Identifier of the custom formula.
• formCond: FormulaCondition. Provides condition logic.
• tablePrefix: Optional String. Prefix for structured references.

Returns: String. Excel fragment produced by the custom handler.

EmptyInvocationFollows #

empty-invocation-follows

Report whether the two tokens after a custom formula are an empty ()

Signature:

Private Function EmptyInvocationFollows(ByVal functionIndex As Long) As Boolean

When a custom formula token is replaced inline, the "(" and ")" tokens behind it become redundant and are skipped.

Parameters:

• functionIndex: Long. Index of the custom function token.

Returns: Boolean. True when the next two tokens are "(" then ")".

ClearCountIf #

clear-count-if

Remove equality fragments from COUNTIF-style conditions

Signature:

Private Function ClearCountIf(ByVal value As String) As String

Sanitises predicate fragments by stripping equality operators and normalising less-than and greater-than comparisons for COUNTIFS compatibility. FormulaCondition builds the fragments this rewrites, so the two have to keep agreeing on their shape.

Parameters:

• value: String. Predicate fragment.

Returns: String. Sanitised predicate suitable for COUNTIFS.

ConvertedSetupFormula #

converted-setup-formula

Convert higher-level setup directives into plain Excel expressions

Signature:

Private Function ConvertedSetupFormula() As String

Detects CASE_WHEN, CHOICE_FORMULA, and VALUE_OF prefixes in the stored setup expression and delegates to the corresponding parser class. Returns the original expression when no prefix matches. The expression is trimmed first. The three prefix tests anchor at position 1, so a leading space sent the raw CASE_WHEN text to the tokeniser, which reported it as an unknown token. When a converter answers with an empty string, its own failure message is held so the caller can report what really went wrong.

Returns: String. Converted formula ready for tokenising.

SetInvalid #

set-invalid

Record an invalidation reason and log the associated checking entry

Signature:

Private Sub SetInvalid(ByVal message As String, Optional ByVal scope As Byte = checkingError)

Stores the failure message, marks the formula as invalid, and logs the message through the internal Checking store.

Parameters:

• message: String. Explanation of the validation failure.
• scope: Optional Byte. Severity level persisted in Checking.

Checkings

LogCheck #

log-check

Record a diagnostic entry within the internal checking store

Signature:

Private Sub LogCheck(ByVal label As String, _
                     Optional ByVal scope As Byte = checkingNote)

Aggregates informational and error traces emitted during parsing.

Lazily creates the Checking instance on first use, then appends the labelled entry with the given severity scope.

Parameters:

• label: String. Message to log.
• scope: Optional Byte. Severity indicator.

ErrorHandling

ThrowError #

throw-error

Raise a VBA error with the class signature

Signature:

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

Centralises the error raising pattern for consistent ProjectError usage.

Wrapper around Err.Raise that standardises the source to the class constant CLASSNAME for consistent stack traces. The message goes through as written, the same as the other five classes in this folder.

Parameters:

• errNumb: Long. ProjectError code.
• errorMessage: String. Descriptive message.

Throws:

• ProjectError. Always raises the specified error.

• CrossTableFormula.cls
• FormulaBuilder.cls
• SpatialTables.cls
• LLdictionary.cls
• LLSheets.cls
• CaseWhen.cls
• ChoiceFormula.cls
• FormulaCondition.cls
• FormulaData.cls
• ValueOfFormula.cls
• Checking.cls
• TranslationObject.cls
• LinelistSpecs.cls
• VarWriter.cls
• SetupErrors.cls
• TestCrossTableFormula.bas
• TestFormulaBuilder.bas
• TestSpatialTables.bas
• TestLLVariablesExtra.bas
• TestFormulas.bas
• TestVarWriter.bas