FormulaBuilder

Holds the criteria of one analysis formula, asks the parser for the formula text and writes it into a cell. AddCondition adds one criterion, ConditionCount answers how many are held and ClearConditions drops them. ExcelFormula gives the text, WriteFormula puts it in a cell, FillDown copies it down a range and ApplyFormat sets the number format. Percentage and Condition build the two smaller formulas, and EmptyText and NotEmptyText give the strings an empty cell shows. CrossTableFormula and SpatialTables both build their formulas through one of these.

THE CRITERIA

A formula is built from the criteria added since the last one was built. The first AddCondition after a build starts a new set. ClearConditions empties the set at any point.

EXCEL IS THE JUDGE OF A FORMULA

WriteFormula assigns the formula and traps what Excel raises. A refused formula leaves the cell empty and files a check naming the cell.

THE 255 CHARACTER LIMIT ON FORMULAARRAY

Range.FormulaArray refuses a formula longer than 255 characters, and an analysis formula over three criteria passes that on ordinary variable names. Those go in through a short stub formula and Range.Replace, which carries no such limit and keeps the cell an array formula. When the host refuses that too, the formula is entered as an ordinary formula and a warning is filed.

WHERE THE CHECKS GO

The owning class hands its own Checking to Create, so everything filed here reaches the generation report through that owner. A key carries the owner name, the table and an F. Checking.Add raises on a duplicate key.

Depends on: Formulas, FormulaCondition, Checking, BetterArray

Version: 2026-07-30

Instantiation

Create #

create

Create a builder for one owner and one table.

Signature:

Public Function Create(ByVal ownerName As String, ByVal contextId As String, _
                       ByVal checks As Checking) As FormulaBuilder

Factory method on the predeclared instance. The owner name and the table identifier travel into every message and every checking key, so a reader of the generation report knows which class wrote which cell.

Parameters:

ownerName: String. The class the builder works for.
contextId: String. The table identifier the formulas belong to.
checks: Checking. The report entries are filed into this.

Returns: FormulaBuilder. A builder with an empty criteria set.

Throws:

ProjectError.ObjectNotInitialized When checks is Nothing.

Seal #

seal

Seal the instance against further setup writes.

Signature:

Public Sub Seal()

OwnerName #

owner-name

The class this builder works for.

Signature:

Public Property Get OwnerName() As String

Returns: String. The owner class name.

OwnerName #

owner-name-set

Assign the owner class name.

Signature:

Public Property Let OwnerName(ByVal ownerValue As String)

Parameters:

ownerValue: String. The owner class name.

ContextId #

context-id

The table identifier the formulas belong to.

Signature:

Public Property Get ContextId() As String

Returns: String. The table identifier.

ContextId #

context-id-set

Assign the table identifier.

Signature:

Public Property Let ContextId(ByVal idValue As String)

Parameters:

idValue: String. The table identifier.

Checkings #

checkings

The report entries are filed into this.

Signature:

Public Property Get Checkings() As Checking

Returns: Checking. The owner report.

Checkings #

checkings-set

Assign the report the entries are filed into.

Signature:

Public Property Set Checkings(ByVal checks As Checking)

Parameters:

checks: Checking. The owner report.

Criteria

AddCondition #

add-condition

Add one criterion to the formula being built.

Signature:

Public Sub AddCondition(ByVal varName As String, ByVal conditionName As String)

The criteria of the formula being built. condVars and condTests are two arrays of the same length, read in step by FormulaCondition.

The first criterion added after a formula was built starts a new set. An empty variable name is ignored, which is how a caller passes an absent column.

Parameters:

varName: String. The linelist variable the criterion reads.
conditionName: String. The criteria text, such as "= $A3" or "<>""".

ClearConditions #

clear-conditions

Empty the criteria set.

Signature:

Public Sub ClearConditions()

ConditionCount #

condition-count

How many criteria the current formula carries.

Signature:

Public Property Get ConditionCount() As Long

Returns: Long. The number of criteria added since the last build.

Formula Text

EmptyText #

empty-text

The Excel literal for an empty string.

Signature:

Public Property Get EmptyText() As String

Two double quote characters. Written with Chr(34) so the VBA source carries no doubled quotes of its own.

Returns: String. The two characters.

NotEmptyText #

not-empty-text

The COUNTIFS criteria for a cell that holds something.

Signature:

Public Property Get NotEmptyText() As String

The two characters "<>" inside a pair of double quotes. COUNTIFS reads that as "not blank", which is what a count summary function needs.

Returns: String. The quoted operator.

Percentage #

percentage

Build a percentage guarded against a zero denominator.

Signature:

Public Function Percentage(ByVal denomRng As Range, ByVal numRng As Range, _
                           Optional ByVal rowabs As Boolean = True) As String

The shape is IF(ISERR(num/denom), "", num/denom), so an empty denominator shows a blank cell. The denominator column is held absolute, so the formula survives a copy down the rows.

Parameters:

denomRng: Range. The cell the division divides by.
numRng: Range. The cell the division divides.
rowabs: Optional Boolean. True to hold the denominator row fixed. Defaults to True.

Returns: String. The formula text with no leading "=".

Condition #

condition

Blank a formula when its guard cell is empty.

Signature:

Public Function Condition(ByVal rngCondition As Range, ByVal formulaValue As String, _
                          Optional ByVal rowabs As Boolean = False) As String

The shape is IF(guard = "", "", formula). A time period row the user date range leaves unused has an empty label cell, and every value beside it reads blank.

Parameters:

rngCondition: Range. The guard cell.
formulaValue: String. The formula text to keep when the guard holds something.
rowabs: Optional Boolean. True to hold the guard row fixed. Defaults to False.

Returns: String. The guarded formula text, empty when formulaValue is empty.

ExcelFormula #

excel-formula

Ask the parser for the formula the current criteria describe.

Signature:

Public Property Get ExcelFormula(ByVal formObject As Formulas, _
                                 Optional ByVal scope As Byte = FormulaScopeAnalysis, _
                                 Optional ByVal prefix As String = "f") As String

The analysis scope wraps the criteria into a FormulaCondition and asks for a COUNTIFS or SUMPRODUCT formula. The linelist scope reads the whole column and ignores the criteria. The text comes back as the parser built it, and Excel judges it when WriteFormula assigns it.

Parameters:

formObject: Formulas. The parsed summary function.
scope: Optional Byte. FormulaScopeAnalysis or FormulaScopeLinelist. Defaults to the analysis scope.
prefix: Optional String. The table name prefix of the linelist reference. Defaults to "f".

Returns: String. The formula text with no leading "=".

Writing Formulas

WriteFormula #

write-formula

Write one formula into one cell and report what Excel says.

Signature:

Public Function WriteFormula(ByVal target As Range, ByVal frm As String, _
                             Optional ByVal asArray As Boolean = True) As Boolean

The one way a formula reaches a cell. Excel accepts it or raises, and a raise is reported through the owner checkings.

Assigns the formula and traps the assignment. Excel rejects a malformed formula with error 1004 at assignment time, which makes it the only judge needed. A refused formula leaves the cell empty and files a check carrying the address and the message Excel gave.

Parameters:

target: Range. The cell the formula belongs in.
frm: String. The formula text with no leading "=".
asArray: Optional Boolean. True to enter the formula as an array formula. Defaults to True.

Returns: Boolean. True when the formula reached the cell.

FillDown #

fill-down

Copy the formula of one cell over a range.

Signature:

Public Sub FillDown(ByVal sourceCell As Range, ByVal destination As Range)

AutoFill needs a destination taller than one cell and raises 1004 otherwise. A value column is one cell tall whenever the table has a single category row, which a univariate table with one category and a spatial table with a geo count of one both produce, so the guard is on the ordinary path.

Parameters:

sourceCell: Range. The cell holding the formula to copy.
destination: Range. The range to fill, including the source cell.

Number Format

ApplyFormat #

apply-format

Apply a number format to a range from a setup keyword.

Signature:

Public Sub ApplyFormat(ByVal formatValue As String, ByVal rng As Range)

Translates the keywords the setup workbook offers into Excel number formats: "integer", "round0", "roundN", "percentage0", "percentageN", "euros", "dollars" and "text". Anything else is handed to Excel as a number format of its own, because the setup sheet allows free text there.

Parameters:

formatValue: String. The keyword or an Excel number format.
rng: Range. The range to format.

Internal members (not exported)

Instantiation

GuardNotSealed #

guard-not-sealed

Guard a setup setter against post-creation writes.

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

Parameters:

propName: String. The property name for the error message.

Formula Text

HarvestConditionCheckings #

harvest-condition-checkings

Take what the condition reported into the owner report

Signature:

Private Sub HarvestConditionCheckings(ByVal cond As FormulaCondition)

The keys are rewritten under this builder's own shape, because FormulaCondition counts from 0 and one owner report holds the entries of many conditions.

Parameters:

cond: FormulaCondition. The condition just built.

Writing Formulas

WriteLongArrayFormula #

write-long-array-formula

Enter an array formula longer than 255 characters.

Signature:

Private Sub WriteLongArrayFormula(ByVal target As Range, ByVal frm As String)

Range.FormulaArray refuses the assignment, so a short stub goes in first and Range.Replace swaps the real text in. Replace edits the cell the way a user would and carries no length limit, and the cell stays an array formula. Range.Replace is called as a statement and the cell is read back to see whether the swap happened. Its return value is a Variant that this host does not always fill, and testing it with Not raised a type mismatch that read as Excel refusing the formula.

When the host refuses the swap as well, the formula goes in as an ordinary formula and a warning is filed carrying the step that refused it. Excel with dynamic arrays gives the same answer either way; an older Excel returns the first value of the range where the aggregate belongs, so the warning is the reader only sign of it.

Parameters:

target: Range. The cell the formula belongs in.
frm: String. The formula text with no leading "=".

Checkings

LogInfo #

log-info

File one entry into the owner report.

Signature:

Private Sub LogInfo(ByVal message As String, _
                    Optional ByVal scope As Byte = checkingSuccess)

Checking.Add raises on a duplicate key, so the key carries the owner name, the table and a counter. The F tells a reader the entry came from the builder rather than from the owner itself, and it is what lets two owners file into one report.

Parameters:

message: String. The message text.
scope: Optional Byte. The checking scope. Defaults to checkingSuccess.

Error Handling

ThrowError #

throw-error

Raise a project error with a standardised source name.

Signature:

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

Parameters:

errNumber: Long. The error code to raise.
message: String. Human-readable description of the failure.

Throws:

ProjectError. Always raises the specified error.

Used in (3 file(s))

CrossTableFormula.cls
SpatialTables.cls
TestFormulaBuilder.bas