ValueOfFormula

Turns a VALUE_OF custom formula written with dictionary variable names into an expression a workbook can hold, by filling in the sheet name and the column indices the lookup needs. The three arguments are the key variable, the lookup variable and the value variable. Create binds a dictionary, ValueOfExpression takes the formula in, Valid says whether the three variables exist and sit on one worksheet, FailureReason names what stopped it, and ConvertedFormula gives the Excel text. LookupSheetName, LookupColumnIndex and ValueColumnIndex answer the three parts on their own.

Depends on: BetterArray, LLdictionary, LLVariables, LLSheets

Version: 1.0 (2026-02-09)

Factory

Create #

create

Instantiate a VALUE_OF parser bound to the supplied dictionary

Signature:

Public Function Create(ByVal formula As String, _
                       ByVal dict As LLdictionary, _
                       Optional ByVal variables As LLVariables, _
                       Optional ByVal sheets As LLSheets) As ValueOfFormula

Validates that the dictionary is not Nothing, stores all dependencies, seals the instance, and returns it. Parsing is deferred until the first access to Valid, ConvertedFormula, or any result property: a caller can build the parser and prepare the dictionary afterwards. Optional variables and sheets helpers can be supplied to avoid redundant instantiation when the caller already holds them.

Parameters:

  • formula: String. Representation of the VALUE_OF call.
  • dict: LLdictionary. The dictionary exposing variables and sheets metadata.
  • variables: Optional LLVariables. Reuse an existing helper instance.
  • sheets: Optional LLSheets. Reuse an existing helper instance.

Returns: ValueOfFormula. A ready-to-inspect parser instance.

Throws:

  • ProjectError.ObjectNotInitialized When dict is Nothing.
Validation

Valid #

valid

Determine whether the VALUE_OF expression parsed successfully

Signature:

Public Property Get Valid() As Boolean

Parsed results and diagnostic properties.

Triggers lazy parsing on first access and returns the cached validity flag. True indicates that all three arguments were resolved against the dictionary and the lookup/value variables share the same sheet.

Returns: Boolean. True when parsing and dictionary lookups succeeded.

ConvertedFormula #

converted-formula

Retrieve the workbook-ready VALUE_OF expression

Signature:

Public Property Get ConvertedFormula() As String

Returns the converted expression with the sheet name and column indices resolved. Returns vbNullString when the formula is invalid.

Returns: String. Converted expression; vbNullString when invalid.

FailureReason #

failure-reason

Provide a descriptive message when parsing fails

Signature:

Public Property Get FailureReason() As String

Returns a human-readable explanation of the validation failure, and vbNullString when the formula is valid. ConvertedFormula answers with an empty string for a bad expression, and an empty string on its own reads as "no formula was written", so the caller reads this to say what happened. CaseWhen and ChoiceFormula carry the same property.

Returns: String. Description; vbNullString for valid formulas.

LookupSheetName #

lookup-sheet-name

Expose the resolved lookup worksheet name

Signature:

Public Property Get LookupSheetName() As String

Returns the worksheet name where the lookup and value variables reside. Available only after successful parsing.

Returns: String. Sheet name or vbNullString when unavailable.

LookupColumnIndex #

lookup-column-index

Expose the column index used to locate the matching value

Signature:

Public Property Get LookupColumnIndex() As Long

Returns the 1-based column index of the lookup variable within the dictionary. Zero when the formula is invalid.

Returns: Long. Lookup column index; zero when the formula is invalid.

ValueColumnIndex #

value-column-index

Expose the column index providing the returned value

Signature:

Public Property Get ValueColumnIndex() As Long

Returns the 1-based column index of the value variable within the dictionary. Zero when the formula is invalid.

Returns: Long. Value column index; zero when the formula is invalid.

Internal members (not exported)

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.

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. The class holds parse results, and a setter running behind them would leave answers that belong to another formula or another dictionary.

Parameters:

PublicAccessors

Dictionary #

dictionary

Retrieve the dictionary backing this instance

Signature:

Public Property Get Dictionary() As LLdictionary

Properties that expose internal state.

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

Returns: LLdictionary. The dictionary used during parsing.

Dictionary #

dictionary-set

Assign the dictionary and reset cached state

Signature:

Public Property Set Dictionary(ByVal dict As LLdictionary)

Replaces the dictionary reference, clears the lazily created variable and sheet helpers, and resets all parsed outcomes. The factory seals the instance, so this raises after construction.

Parameters:

Throws:

ValueOfExpression #

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.

ValueOfExpression #

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. The factory seals the instance, so this raises after construction.

Parameters:

Throws:

Parsing

EnsureParsed #

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. The parsed flag is set before any work is done, on purpose: a failure is cached the same way a success is, so a bad formula is parsed once.

ColumnIndexOf #

column-index-of

Read the dictionary column index of one variable

Signature:

Private Function ColumnIndexOf(ByVal vars As LLVariables, ByVal varName As String) As Long

LLVariables.Index raises ElementNotFound when the column index cell is empty and InvalidArgument when it holds text. Both mean the same thing here: the dictionary carries no usable column index for that variable. Catching the raise is what lets the caller report the column-index message and name the variable. Without it the raise reached the routine handler and came back as "Unable to parse VALUE_OF formula" plus the VBA text.

Parameters:

Returns: Long. The stored index, or 0 when there is none to read.

ExtractArguments #

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. Blank segments are KEPT here, unlike CaseWhen.SplitFormulaSegments and ChoiceFormula.SplitArguments, which drop them. That is what makes the count check catch VALUE_OF(a, , c). The three walks stay separate: a class cannot reach a standard module, so a shared copy has nowhere to live.

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

SanitisedFormula #

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. The text before the ( has to be the token in full. Without that second check VALUE_OFX(a, b, c) and VALUE_OF_EXTRA(a, b, c) both parsed as a VALUE_OF, because the first check only looks at the leading characters. ChoiceFormula carries the same pair of checks.

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

BuildConvertedFormula #

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. The output is itself a VALUE_OF call: it goes back into Formulas, which tokenises it and needs VALUE_OF to be in the function whitelist, and the workbook function that evaluates it reads this exact shape.

Parameters:

Returns: String. Workbook-ready VALUE_OF expression.

QuotedText #

quoted-text

Wrap a value in double quotes while escaping embedded quotes

Signature:

Private Function QuotedText(ByVal value As String) As String

Parameters:

Returns: String. Quoted representation suitable for Excel formulas.

Helpers

Initialise #

initialise

Assign dependencies and prime the parser state

Signature:

Public Sub Initialise(ByVal formula As String, _
                       ByVal dict As LLdictionary, _
                       ByVal variables As LLVariables, _
                       ByVal sheets As LLSheets)

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

Throws:

ResetState #

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.

ResetParsedOutcome #

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 #

fail

Record a parsing failure

Signature:

Private Sub Fail(ByVal message As String)

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

Parameters:

VariablesProvider #

variables-provider

Lazily create the LLVariables helper

Signature:

Private Function VariablesProvider() As LLVariables

Ensures the dictionary is ready, creates the LLVariables helper on first access, and caches it for subsequent calls. Formulas hands its own helper in, so that path builds nothing.

Returns: LLVariables. Helper for the current dictionary.

Throws:

SheetsProvider #

sheets-provider

Lazily create the LLSheets helper

Signature:

Private Function SheetsProvider() As LLSheets

Ensures the dictionary is ready, creates the LLSheets helper on first access, and caches it for subsequent calls. Formulas hands its own helper in, so that path builds nothing.

Returns: LLSheets. Helper for the current dictionary.

Throws:

EnsureDictionaryReady #

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:

ThrowError #

throw-error

Raise a project-specific error

Signature:

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

Wrapper around Err.Raise that standardises the source to the class constant CLASSNAME for consistent stack traces. The code is taken as a Long: a named enum type in a parameter position is the macOS trap.

Parameters:

Throws:

Used in (4 file(s))