Formulas

PublicAccessors

self #

Return the interface pointer for the current instance

Signature:

Public Property Get Self() As IFormulas

Exposes configured dependencies, cached evaluation results, and parsed Excel expressions. Returns the current instance cast to its interface. Used internally by the factory method to return an IFormulas reference.

Returns: IFormulas. Reference to the current object.

dictionary #

Retrieve the underlying dictionary used during parsing

Signature:

Public Property Get Dictionary() As ILLdictionary

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

Returns: ILLdictionary. Dictionary backing metadata lookups.

dictionary-set #

Assign a new dictionary and reset dependent caches

Signature:

Public Property Set Dictionary(ByVal dict As ILLdictionary)

Replaces the current dictionary and invalidates all lazily resolved dependencies so subsequent property accesses trigger a fresh parse.

Parameters:

• dict: ILLdictionary. Variable metadata provider.

Throws:

• ProjectError.ObjectNotInitialized When dict is Nothing.

data #

Retrieve the formula configuration catalogue

Signature:

Public Property Get Data() As IFormulaData

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

Returns: IFormulaData. Configuration catalogue.

data-set #

Update the formula configuration catalogue and invalidate caches

Signature:

Public Property Set Data(ByVal formData As IFormulaData)

Replaces the current formula data and invalidates all cached parsing results so subsequent accesses trigger a fresh evaluation.

Parameters:

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

Throws:

• ProjectError.ObjectNotInitialized When formData is Nothing.

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.

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 so subsequent accesses trigger a fresh evaluation.

Parameters:

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

PrivateHelpers

initialise #

Store incoming dependencies and prepare caches

Signature:

Public Sub Initialise(ByVal dict As ILLdictionary, _
                       ByVal formData As IFormulaData, _
                       ByVal setupForm As String)

Internal utilities for dependency management, cache invalidation, and token resolution. Assigns the dictionary, formula data, and setup expression, then invalidates all caches and registers the default custom formulas recognised during analysis parsing.

Parameters:

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

reset-dependencies #

Clear lazily instantiated dependencies and invalidate token caches

Signature:

Private Sub ResetDependencies()

Sets variables and sheets providers to Nothing and re-initialises the custom formulas list when absent. Triggers InvalidateCaches to ensure downstream properties reparse.

register-default-custom-formulas #

Seed built-in custom formulas recognised by analysis parsing

Signature:

Private Sub RegisterDefaultCustomFormulas()

Replaces the custom formulas list and registers MEAN, N, and N() as known tokens that receive special handling during analysis formula construction.

register-custom-formula #

Add a custom formula identifier if not already registered

Signature:

Private Sub RegisterCustomFormula(ByVal customFormula As String)

Initialises the custom formulas list when absent, then pushes the token if it is not already present.

Parameters:

• customFormula: String. Token representing a custom formula.

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.

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.

variables #

Lazily instantiate the LLVariables helper from the dictionary

Signature:

Private Function Variables() As ILLVariables

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

Returns: ILLVariables. Bound to the current dictionary.

sheet-provider #

Lazily instantiate the LLSheets helper for address resolution

Signature:

Private Function SheetProvider() As ILLSheets

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

Returns: ILLSheets. Bound to the current dictionary.

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

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

evaluate-formula #

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

Signature:

Private Sub EvaluateFormula(ByVal formulaType 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:

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

GroupFormula

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 ILLVariables, _
                                       ByVal dataSource As IFormulaData) 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: ILLVariables. Helper exposing dictionary metadata.
• dataSource: IFormulaData. Configuration describing grouped functions.

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

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.

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

Parameters:

• functionName: String. Token under inspection.

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

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.

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.

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 ILLSheets) 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: ILLSheets. Provider for address resolution.

Returns: String. Excel reference targeting the grouped variable.

Tokenisation

tokenise-formula #

Break the converted formula into tokens while validating parentheses and literals

Signature:

Private Function TokeniseFormula(ByVal formula As String, _
                                 ByVal formulaType 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 IFormulaData configuration. Tracks parenthesis depth and quotation state. Each extracted chunk is validated through AppendToken.

Parameters:

• formula: String. Cleaned setup expression.
• formulaType: String. Context (analysis/simple).
• 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.

append-token #

Validate and append the provided chunk to the tokens collection

Signature:

Private Function AppendToken(ByVal chunk As String, _
                             ByRef formulaType As String, _
                             ByRef tokens As BetterArray, _
                             ByRef variableTokens As BetterArray, _
                             ByRef vars As ILLVariables, _
                             ByRef dataSource As IFormulaData, _
                             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.

Parameters:

• chunk: String. Candidate token.
• formulaType: String. Context (analysis/simple).
• tokens: BetterArray. Recipient for all tokens.
• variableTokens: BetterArray. Recipient for variable-only tokens.
• vars: ILLVariables. Metadata provider.
• dataSource: IFormulaData. Formula metadata provider.
• HasSetupVariables: Boolean. Flag capturing literal presence.
• failureReason: String. Explanation when parsing fails.

Returns: Boolean. True when the chunk is accepted.

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.

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, strips control characters via Application.WorksheetFunction.Clean, and trims whitespace.

Parameters:

• value: String. Token to clean.

Returns: String. Trimmed and cleaned representation.

parsed-custom-formula #

Delegate parsing to custom functions when present in analysis context

Signature:

Private Function ParsedCustomFormula(ByVal customFunction As String, _
                                     ByVal formCond As IFormulaCondition, _
                                     Optional ByVal TableName As String = vbNullString, _
                                     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 IFormulaCondition.

Parameters:

• customFunction: String. Identifier of the custom formula.
• formCond: IFormulaCondition. Provides condition logic.
• TableName: Optional String. Table name resolved for variables.
• tablePrefix: Optional String. Prefix for structured references.

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

remove-empty-invocation-tokens #

Strip trailing empty parentheses after replacing custom formulas

Signature:

Private Sub RemoveEmptyInvocationTokens(ByRef tokens As BetterArray, _
                                        ByVal functionIndex As Long)

When a custom formula token is replaced inline, the following "(" and ")" tokens may become redundant. This helper removes them when they immediately follow the replaced token.

Parameters:

• tokens: BetterArray. Tokens list to mutate.
• functionIndex: Long. Index of the custom function token.

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.

Parameters:

• value: String. Predicate fragment.

Returns: String. Sanitised predicate suitable for COUNTIFS.

quoted #

Wrap a value in Excel double quotes

Signature:

Private Function Quoted(ByVal value As String) As String

Prepends and appends a double-quote character to the supplied value.

Parameters:

• value: String. Text to wrap.

Returns: String. Quoted representation.

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.

Returns: String. Converted formula ready for tokenising.

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

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

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.

Parameters:

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

Throws:

• ProjectError. Always raises the specified error.

Interface

IFormulas_Varlists #

Signature:

Private Property Get IFormulas_Varlists(Optional ByVal formulaType As String = "analysis") As BetterArray

Delegated members satisfying the IFormulas contract.

formula-table-list #

Signature:

Private Property Get IFormulas_FormulaTableList() As BetterArray

• AnaTabIds.cls
• CrossTableFormula.cls
• ISpatialTables.cls
• SpatialTables.cls
• Formulas.cls
• IFormulas.cls
• LinelistSpecs.cls
• VarWriter.cls
• SetupErrors.cls
• TestFormulas.bas
• TestSpatialTables.bas