ValueOfFormula

PublicAccessors

self #

Current object instance

Signature:

Public Property Get Self() As IValueOfFormula

Properties that expose internal state and the interface pointer. Returns the current instance cast to its interface. Used internally by the factory method to return an IValueOfFormula reference.

Returns: IValueOfFormula. Reference to the current object.

dictionary #

Retrieve the dictionary backing this instance

Signature:

Public Property Get Dictionary() As ILLdictionary

Returns the ILLdictionary reference that was supplied at creation and is used during lazy parsing.

Returns: ILLdictionary. The dictionary used during parsing.

dictionary-set #

Assign the dictionary and reset cached state

Signature:

Public Property Set Dictionary(ByVal dict As ILLdictionary)

Replaces the dictionary reference, clears the lazily created variable and sheet helpers, and resets all parsed outcomes so the next access triggers a fresh evaluation.

Parameters:

• dict: ILLdictionary. The dictionary providing metadata.

Throws:

• ProjectError.ObjectNotInitialized When dict is Nothing.

value-of-expression #

Retrieve the stored VALUE_OF expression

Signature:

Public Property Get ValueOfExpression() As String

Returns the formula text as stored after trimming during assignment.

Returns: String. Expression as provided to the parser.

value-of-expression-set #

Store the VALUE_OF expression and reset cached results

Signature:

Public Property Let ValueOfExpression(ByVal formula As String)

Trims the incoming formula and stores it, then clears all cached parsing outcomes so the next access triggers a fresh parse.

Parameters:

• formula: String. Representing the VALUE_OF call.

Parsing

ensure-parsed #

Lazily parse the VALUE_OF expression and cache outcomes

Signature:

Private Sub EnsureParsed()

Lazy parsing engine and argument extraction. Executes the full parsing pipeline on first call: extracts three arguments, validates each against the dictionary, confirms sheet alignment, resolves column indices, and builds the converted formula. All outcomes are cached so subsequent calls are no-ops.

extract-arguments #

Split the VALUE_OF body into trimmed argument segments

Signature:

Private Function ExtractArguments() As BetterArray

Walks the sanitised body character by character, tracking quotation state and parenthesis depth. Splits on top-level commas and returns exactly three arguments for a well-formed VALUE_OF expression.

Returns: BetterArray. Up to three arguments; Nothing when malformed.

sanitised-formula #

Remove the VALUE_OF token and surrounding parenthesis

Signature:

Private Function SanitisedFormula() As String

Strips the VALUE_OF token, opening parenthesis, and closing parenthesis to expose the raw comma-delimited argument body.

Returns: String. The argument list; vbNullString when malformed.

build-converted-formula #

Compose the converted VALUE_OF expression

Signature:

Private Function BuildConvertedFormula(ByVal keyVar As String, _
                                       ByVal sheetName As String, _
                                       ByVal lookupIndex As Long, _
                                       ByVal valueIndex As Long) As String

Assembles the VALUE_OF output string with the key variable name, the quoted sheet name, and the two resolved column indices.

Parameters:

• keyVar: String. Variable supplying the lookup key range.
• sheetName: String. Worksheet name hosting the lookup table.
• lookupIndex: Long. Column index for the lookup key.
• valueIndex: Long. Column index for the return value.

Returns: String. Workbook-ready VALUE_OF expression.

quoted-text #

Wrap a value in double quotes while escaping embedded quotes

Signature:

Private Function QuotedText(ByVal value As String) As String

Parameters:

• value: String. The value to quote.

Returns: String. Quoted representation suitable for Excel formulas.

Helpers

initialise #

Assign dependencies and prime the parser state

Signature:

Public Sub Initialise(ByVal formula As String, _
                       ByVal dict As ILLdictionary, _
                       ByVal variables As ILLVariables, _
                       ByVal sheets As ILLSheets)

Initialisation, state management, and lazy dependency providers. Stores the dictionary, optional helpers, and formula, then resets all cached state so the parser is ready for lazy evaluation.

Parameters:

• formula: String. The VALUE_OF expression.
• dict: ILLdictionary. The dictionary providing metadata.
• variables: ILLVariables. Optional helper for variable lookups.
• sheets: ILLSheets. Optional helper for sheet validation.

reset-state #

Clear cached parsing outcomes

Signature:

Private Sub ResetState()

Marks the instance as unparsed and clears all result fields so the next property access triggers a full reparse.

reset-parsed-outcome #

Reset fields populated during parsing

Signature:

Private Sub ResetParsedOutcome()

Clears validity, converted formula, failure reason, sheet name, and column indices to their default (empty/zero) values.

fail #

Record a parsing failure

Signature:

Private Sub Fail(ByVal message As String)

Stores the failure message and resets all result fields to their invalid defaults. Called from EnsureParsed on every error path.

Parameters:

• message: String. Describing the failure.

variables-provider #

Lazily create the LLVariables helper

Signature:

Private Function VariablesProvider() As ILLVariables

Ensures the dictionary is ready, creates the LLVariables helper on first access, and caches it for subsequent calls.

Returns: ILLVariables. Helper for the current dictionary.

Throws:

• ProjectError.ObjectNotInitialized When the dictionary is Nothing.

sheets-provider #

Lazily create the LLSheets helper

Signature:

Private Function SheetsProvider() As ILLSheets

Ensures the dictionary is ready, creates the LLSheets helper on first access, and caches it for subsequent calls.

Returns: ILLSheets. Helper for the current dictionary.

Throws:

• ProjectError.ObjectNotInitialized When the dictionary is Nothing.

ensure-dictionary-ready #

Guard against missing dictionary references

Signature:

Private Sub EnsureDictionaryReady()

Raises a ProjectError when the dictionary is Nothing, preventing downstream NullReference issues in the variable and sheet providers.

Throws:

• ProjectError.ObjectNotInitialized When the dictionary is Nothing.

throw-error #

Raise a project-specific error

Signature:

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

Wrapper around Err.Raise that standardises the source to the class constant CLASSNAME for consistent stack traces.

Parameters:

• errNumber: ProjectError. The error code describing the failure.
• message: String. Error message.

Throws:

• ProjectError. Always raises the specified error.

Interface

IValueOfFormula_ConvertedFormula #

Signature:

Private Property Get IValueOfFormula_ConvertedFormula() As String

Delegated members satisfying the IValueOfFormula contract.

• Formulas.cls
• IValueOfFormula.cls
• ValueOfFormula.cls
• TestValueOfFormula.bas