CrossTableFormula

Internal Properties

self #

Current object instance cast to the interface.

Signature:

Public Property Get Self() As ICrossTableFormula

Properties used during factory construction and internal wiring. Convenience accessor so the Create factory can return the newly constructed object through the ICrossTableFormula interface.

Returns: ICrossTableFormula. The current instance.

table-get #

ICrossTable backing store.

Signature:

Public Property Get Table() As ICrossTable

Returns the cross-table object that this formula writer targets. The cross-table provides the worksheet reference, named ranges, and layout metadata needed to place formulas.

Returns: ICrossTable. The bound cross-table instance.

table-set #

Assign the ICrossTable backing store.

Signature:

Public Property Set Table(ByVal tabl As ICrossTable)

Parameters:

• tabl: ICrossTable. The cross-table to bind to this formula writer.

form-data-get #

IFormulaData backing store.

Signature:

Public Property Get formData() As IFormulaData

Returns the formula-data object that supplies dictionary-based lookups for variable names, linelist ranges, and formula templates during generation.

Returns: IFormulaData. The bound formula-data instance.

form-data-set #

Assign the IFormulaData backing store.

Signature:

Public Property Set formData(ByVal fData As IFormulaData)

Parameters:

• fData: IFormulaData. The formula-data source to bind.

Shortcut Properties

specifications #

ITableSpecs shortcut for the cross-table specifications.

Signature:

Private Property Get Specifications() As ITableSpecs

Convenience accessors that delegate to the cross-table or its specifications, reducing repetitive chaining throughout the class. Provides a shortcut to the ITableSpecs object associated with this cross-table. ITableSpecs encapsulates all configuration metadata for a table: its row/column variable names, summary function, table scope, missing/total/percentage flags, and the section it belongs to. This property avoids repeatedly chaining Table.Specifications throughout the class methods.

Returns: ITableSpecs. The specification dictionary for the underlying cross-table.

summary-function #

Summary function name from the table specifications.

Signature:

Private Property Get SummaryFunction() As String

Returns the summary function name string from the table specifications. The summary function determines the type of aggregation Excel will perform in the generated formulas. Common values include "N" (count non-empty), "N()" (count all), "SUM", "MEAN", "MEDIAN", and "MIN"/"MAX". This value drives formula generation logic throughout the class, especially in AddNonEmptyTableConditions where "N"/"N()" use a different operator pattern.

Returns: String. A value such as "N", "N()", "SUM", "MEAN", etc.

id #

Unique identifier string for this cross-table.

Signature:

Private Property Get ID() As String

Returns the unique identifier string for this cross-table. The table ID is used extensively as a suffix for named ranges on the analysis worksheet (e.g., "ROW_CATEGORIES_", "VALUES_COL_1_", "TOTAL_ROW_VALUES_"). Every named range that belongs to this table is suffixed with this ID, making it the primary key for locating table regions on the worksheet.

Returns: String. The table identifier unique within the analysis workbook.

section-id #

Section identifier for the group this table belongs to.

Signature:

Private Property Get SectionId() As String

Returns the section identifier for the group of tables this cross-table belongs to. A section is a logical grouping of related tables on the analysis worksheet (e.g., all time-series tables sharing the same date controls). The section ID is the table ID of the first (anchor) table in the section. It is used to reference shared named ranges like "FIRST_VALUE_START_TIME_" and "TOTAL_ROW_" that are defined once per section rather than per table.

Returns: String. The anchor table ID for this table section.

table-type #

AnalysisTableScope byte value indicating the cross-table kind.

Signature:

Private Property Get TableScope() As Byte

Returns the AnalysisTableScope byte enum value indicating what kind of cross-table this is. The table scope determines which branch of the AddFormulas Select Case is executed: ScopeGlobalSummary, ScopeUnivariate, ScopeBivariate, ScopeTimeSeries, ScopeSpatioTemporal, or ScopeSpatial. Each type has fundamentally different formula structures, named range layouts, and condition-building logic.

Returns: Byte. One of the AnalysisTableScope enum constants.

Remarks:

• Returns Byte rather than the enum type for macOS VBA compatibility.

table-formula #

Lazy-loaded IFormulas parsing engine for this table.

Signature:

Private Property Get TableFormula() As IFormulas

Returns the IFormulas object for this table, creating it on first access via lazy initialisation. The IFormulas object (created by Formulas.Create) is the core formula-parsing engine: it knows how to translate the table summary function and variable dictionary into concrete Excel formula strings for both linelist-scope and analysis-scope contexts. Once created, the instance is cached in this.forml so subsequent calls reuse the same object without re-parsing.

Returns: IFormulas. The formula parsing engine providing ParsedLinelistFormula and ParsedAnalysisFormula methods.

Remarks:

• The IFormulas object is initialised with the table specification dictionary, the IFormulaData reference, and the summary function string. This is intentionally lazy because not all code paths need the formula engine (e.g., if Valid is False).

Range and Formula Helpers

range-exists #

Check whether a named range exists on the cross-table worksheet.

Signature:

Private Function RangeExists(ByVal rngName As String) As Boolean

Utility functions for range existence checks, formula validation, string literals, percentage formulas, and conditional wrapping. Attempts to resolve a named range on the cross-table worksheet using On Error Resume Next to silently handle missing ranges. This is used before writing formulas to optional table regions (e.g., "MISSING_TOTAL_", "MISSING_MISSING_", "TOTAL_TOTAL_") that may or may not be present depending on the table missing/total configuration.

Parameters:

• rngName: String. The named range to look up (e.g., "MISSING_TOTAL_T001").

Returns: Boolean. True if the named range resolves to a valid Range object.

failed-formula #

Test whether a generated Excel formula string is syntactically valid.

Signature:

Private Function FailedFormula(ByVal frmName As String) As Boolean

Attempts to write the formula as an array formula into cell(1,1) of the cross-table worksheet, then immediately clears that cell. If the assignment succeeds without error, the formula is considered valid and FailedFormula returns False. If an error occurs (e.g., invalid function name, bad range reference, or formula syntax error), execution jumps to the Fail label and returns True. This acts as a safety net to prevent writing broken formulas into the actual table cells.

Parameters:

• frmName: String. The Excel formula string to test (without the leading "= " prefix).

Returns: Boolean. True if the formula raised an error (is broken), False if it succeeded.

Remarks:

• Application.DisplayAlerts is temporarily disabled and restored afterward to suppress any Excel warning dialogs during the test. Cell(1,1) is always cleared regardless of success or failure to avoid leaving test artifacts on the worksheet.

emp-str #

Return the Excel formula literal for an empty string.

Signature:

Private Function EmpStr() As String

Returns a two-character string consisting of two double-quote characters (Chr(34) & Chr(34)), representing the Excel formula literal for an empty string (""). This is used extensively when building condition expressions that test whether a cell is empty. Using Chr(34) avoids VBA double-quote escaping issues and keeps the formula-building code readable.

Returns: String. The literal text consisting of two Chr(34) characters.

non-emp-str #

Return the Excel formula literal for "not empty" criteria.

Signature:

Private Function NonEmpStr() As String

Returns a four-character string representing the Excel formula literal "<>" wrapped in double-quote characters (Chr(34) & "<>" & Chr(34)). This is the COUNTIFS criteria for "not empty" when used with the "N" or "N()" summary functions, which need a comparison operator inside the criteria string.

Returns: String. The literal "<>" wrapped in double-quote characters for Excel formula use.

percentage #

Build an IF(ISERR(...)) guarded percentage formula string.

Signature:

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

Builds an Excel formula string that computes a percentage (numerator / denominator) with an ISERR guard to handle division-by-zero or other errors gracefully. The generated formula follows the pattern: IF(ISERR(num/denom), "", num/denom). This ensures that when the denominator is zero or the ranges are empty, the cell displays blank instead of a #DIV/0! error. Address references use mixed absolute/relative addressing so the formula can be auto-filled down rows while keeping the denominator column fixed.

Parameters:

• denomRng: Range. The range whose address is used as the denominator in the division.
• numRng: Range. The range whose address is used as the numerator.
• rowabs: Optional Boolean. When True, the denominator row reference is absolute. Defaults to True.

Returns: String. The complete IF(ISERR(...)) Excel formula text without "= " prefix.

condition #

Wrap a formula in an IF guard that blanks when a condition cell is empty.

Signature:

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

Wraps a formula value inside an IF guard that returns an empty string when a specified condition cell is blank. The generated pattern is: IF(conditionCell = "", "", formulaValue). This is primarily used in time-series and spatio-temporal tables where formula cells should display blank when the corresponding time period label cell is empty (i.e., the row falls outside the user selected date range). Without this guard, formulas would show misleading zero/error values for unused time period rows.

Parameters:

• rngCondition: Range. The cell tested for blankness.
• formulaValue: String. The Excel formula string to evaluate when rngCondition is not blank.
• rowabs: Optional Boolean. Controls whether rngCondition uses an absolute row reference. Defaults to False.

Returns: String. The complete IF(...="","",formulaValue) Excel formula text.

Condition Management

add-table-conditions #

Append a variable-name / condition-test pair to the accumulators.

Signature:

Private Sub AddTableConditions(ByVal varName As String, ByVal conditionName As String)

Methods that maintain the parallel condVars/condTests accumulator arrays used to build multi-criteria COUNTIFS/SUMPRODUCT formulas. Appends a variable name and its corresponding condition test string to the parallel accumulator arrays (condVars and condTests). These arrays are consumed by CurrentExcelFormula to build multi-criteria COUNTIFS/SUMPRODUCT formulas. Each call adds one criteria pair. The BetterArray objects are lazily initialised on first use with a 1-based lower bound. If varName is vbNullString, nothing is pushed (silent no-op).

Parameters:

• varName: String. The linelist variable name identifying the column the condition applies to.
• conditionName: String. The criteria string for the formula (e.g., "= $A3", "= """, "<>""").

Remarks:

• condVars and condTests always have the same length and are consumed in parallel. Call ClearTableConditions to reset them before building a new formula.

clear-table-conditions #

Clear or trim conditions from the accumulator arrays.

Signature:

Private Sub ClearTableConditions(Optional ByVal removeNumber As Long = 0)

Clears conditions from the condVars/condTests accumulator arrays. When called with removeNumber = 0 (the default), all accumulated conditions are cleared, resetting the formula builder to a blank state. When called with a positive removeNumber, only the last N condition pairs are popped off the arrays, leaving earlier conditions intact. This selective removal is useful when a base set of conditions is shared across multiple formulas and only the last few criteria need to change between iterations.

Parameters:

• removeNumber: Optional Long. Number of condition pairs to remove from the end. Defaults to 0 (clear all).

Remarks:

• If the BetterArray objects have not been initialised yet (Nothing), they are created fresh and removeNumber is forced to 0 to avoid popping from an empty array.

add-non-empty-table-conditions #

Add a "non-empty" filter condition adapted to the summary function.

Signature:

Private Sub AddNonEmptyTableConditions(ByVal varName As String)

Adds a "non-empty" filter condition for a variable, automatically adapting the condition operator to the current summary function. For count-based functions ("N" and "N()"), the condition uses the equality operator with the NonEmpStr literal ("= <>"), because COUNTIFS interprets "<>" as "not blank" inside a criteria string. For all other summary functions (SUM, MEAN, etc.), the condition uses the inequality operator with EmpStr ("<>"""), which is the standard SUMPRODUCT-style not-equal-to-empty test.

Parameters:

• varName: String. The linelist variable name to add the non-empty filter for.

Excel Formula Builder

current-excel-formula #

Assembled Excel formula from the current condition accumulators.

Signature:

Private Property Get CurrentExcelFormula(Optional ByVal scope As Byte = 2, _
                                         Optional ByVal prefix As String = "f") As String

Central entry point that assembles the final Excel formula string from the accumulated condition pairs. Builds and returns the complete Excel formula string from the currently accumulated condition pairs (condVars/condTests). This is the central formula-generation entry point that all table-type branches call after setting up their conditions. It operates in two modes: (1) Linelist scope calls ParsedLinelistFormula for unfiltered formulas; (2) Analysis scope wraps the accumulated condVars and condTests into a FormulaCondition object and calls ParsedAnalysisFormula for COUNTIFS/SUMPRODUCT formulas with all criteria. After generation, the formula is validated via FailedFormula, returning a fallback error placeholder if parsing failed.

Parameters:

• scope: Optional Byte. FormulaScopeLinelist (1) for unfiltered formulas or FormulaScopeAnalysis (2) for conditional formulas. Defaults to 2.
• prefix: Optional String. Table name prefix for the linelist reference. Defaults to "f".

Returns: String. The Excel formula text without "= " prefix, or an error placeholder if parsing failed.

Remarks:

• If condVars or condTests is Nothing when scope is analysis, execution jumps to Fail immediately. Always call AddTableConditions before requesting an analysis-scope formula.

Number Format Helper

add-rng-format #

Apply an Excel NumberFormat to a range based on a format keyword.

Signature:

Private Sub AddRngFormat(ByVal formatValue As String, ByVal rng As Range)

Translates human-readable format keywords into Excel NumberFormat strings. Translates configuration-friendly format keywords into proper Excel custom number format strings and applies them to the target range. Supported keywords include "integer", "round0", "roundN", "percentage0", "percentageN", "euros", "dollars", and "text". Any unrecognised string is treated as a custom Excel NumberFormat and applied directly inside On Error Resume Next. If formatValue is vbNullString, the sub exits without modifying the range.

Parameters:

• formatValue: String. The format keyword or custom Excel NumberFormat string.
• rng: Range. The range to apply the number format to.

Remarks:

• For "roundN" and "percentageN" keywords, the numeric suffix is extracted via Replace and CLng conversion. If the suffix is not a valid number, nbDecimals defaults to 1.

Error Handling

throw-error #

Raise a project error with a standardised source name.

Signature:

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

Standardised error-raising helper for all methods in this class. Wrapper around Err.Raise that standardises the source to CLASS_NAME, providing a consistent stack trace across all methods in this class.

Parameters:

• errNumber: Long. The error code to raise (typically a ProjectError constant).
• message: String. Human-readable description of the failure.

Throws:

• ProjectError. Always raises the specified error.

Interface Implementation

ICrossTableFormula_Valid #

Signature:

Private Property Get ICrossTableFormula_Valid() As Boolean

Delegated members satisfying the ICrossTableFormula contract. See the corresponding Public/Private members above for full documentation.

• AnalysisOutput.cls
• CrossTableFormula.cls
• ICrossTableFormula.cls
• TestCrossTableFormula.bas