CrossTableFormula

Internal Properties

Seal #

seal

Seal the instance against further setup writes.

Signature:

Public Sub Seal()

Properties used during factory construction and internal wiring.

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.

Table #

table-get

CrossTable backing store.

Signature:

Public Property Get Table() As CrossTable

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: CrossTable. The bound cross-table instance.

Table #

table-set

Assign the CrossTable backing store.

Signature:

Public Property Set Table(ByVal tabl As CrossTable)

Parameters:

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

formData #

form-data-get

FormulaData backing store.

Signature:

Public Property Get formData() As FormulaData

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

Returns: FormulaData. The bound formula-data instance.

formData #

form-data-set

Assign the FormulaData backing store.

Signature:

Public Property Set formData(ByVal fData As FormulaData)

Parameters:

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

Shortcut Properties

Specifications #

specifications

TableSpecs shortcut for the cross-table specifications.

Signature:

Private Property Get Specifications() As TableSpecs

Convenience accessors that delegate to the cross-table or its specifications, reducing repetitive chaining throughout the class.

Provides a shortcut to the TableSpecs object associated with this cross-table. TableSpecs 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: TableSpecs. The specification dictionary for the underlying cross-table.

SummaryFunction #

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.

IsCountFunction #

is-count-function

Whether the summary function counts rows.

Signature:

Private Property Get IsCountFunction() As Boolean

The two count spellings take a different formula shape from every other summary function: a total is a sum of counts and a non-empty test is written as a COUNTIFS criteria. Six places asked this question with the same pair of string tests.

Returns: Boolean. True for "N" and "N()".

ID #

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.

SectionId #

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.

TableScope #

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.

TableFormula #

table-formula

Lazy-loaded Formulas parsing engine for this table.

Signature:

Private Property Get TableFormula() As Formulas

Returns the Formulas object for this table, creating it on first access via lazy initialisation. The Formulas 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: Formulas. The formula parsing engine providing ParsedLinelistFormula and ParsedAnalysisFormula methods.

Remarks:

• The Formulas object is initialised with the table specification dictionary, the FormulaData 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).

Formula Builder

Builder #

builder

The builder this class writes its formulas through.

Signature:

Private Property Get Builder() As FormulaBuilder

The one place a formula is assembled and written. SpatialTables writes through one of these too, so the two classes agree on how a formula is made, on the 255 character limit of an array formula, and on what a refusal looks like in the report.

Created on first use, because it carries the table identifier and the entries both travel into every message it files.

Returns: FormulaBuilder. The builder of this instance.

Range and Formula Helpers

RangeExists #

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, string literals, percentage formulas, and conditional wrapping.

Attempts to resolve a named range on the cross-table worksheet, answering False when the name is absent. This is used before writing formulas to optional table regions (e.g., "MISSING_TOTAL_", "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.

ResolvedRange #

resolved-range

Resolve a named range on the cross-table worksheet, or answer Nothing.

Signature:

Private Function ResolvedRange(ByVal rngName As String) As Range

One resolution serves both the question and the answer. Twelve places asked RangeExists and then resolved the same name a second time, so each optional region cost two lookups.

Parameters:

• rngName: String. The named range to look up.

Returns: Range. The range, or Nothing when the name is absent.

EmpStr #

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.

NonEmpStr #

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 #

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 #

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.

Writing Formulas

WriteFormula #

write-formula

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

Signature:

Private 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 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 the class needs. A rejected formula leaves the cell empty and files a check carrying the address and the message Excel gave.

A formula over 255 characters cannot go through Range.FormulaArray at all, so it is entered through a stub and Range.Replace. See the class description.

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:

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

WritePercentageTwin #

write-percentage-twin

Write the percentage column that sits beside one value column.

Signature:

Private Sub WritePercentageTwin(ByVal valueRng As Range, ByVal denomRng As Range, _
                                ByVal lastCell As Range, ByVal rowabs As Boolean)

A table with percentages carries one percentage column immediately right of every value column, including the ones heading the Missing and the Total columns. The formula is written into the first cell and copied down to the last cell the caller names, because a bivariate table fills its percentage column down to the total row while a temporal one stops at the value block.

Parameters:

• valueRng: Range. The value column the percentage is computed from.
• denomRng: Range. The cell holding the denominator.
• lastCell: Range. The last cell of the percentage column to fill.
• rowabs: Boolean. True to hold the denominator row fixed.

PercentageDenominator #

percentage-denominator

Resolve the denominator one percentage column divides by.

Signature:

Private Function PercentageDenominator(ByVal tabNames As AnalysisRanges, _
                                       ByVal percentageValue As String, _
                                       ByVal totalRow As Long, _
                                       ByVal valueColumn As Long) As Range

The setup sheet offers three percentages and each names a different denominator: a row percentage divides by the total column of the same row, a column percentage by the total row of the same column, and a total percentage by the cell where the two cross. Three arms of AddFormulas held the same Select Case.

Parameters:

• tabNames: AnalysisRanges. The names of this table.
• percentageValue: String. The percentage setting of the table.
• totalRow: Long. The row holding the totals.
• valueColumn: Long. The column the percentage belongs to.

Returns: Range. The denominator cell, or Nothing when the setting names none.

PercentageRowAbsolute #

percentage-row-absolute

Whether the percentage denominator holds its row fixed.

Signature:

Private Function PercentageRowAbsolute(ByVal percentageValue As String) As Boolean

Parameters:

• percentageValue: String. The percentage setting of the table.

Returns: Boolean. False for a row percentage, True for the other two.

Condition Management

AddTableConditions #

add-table-conditions

Add one criterion to the formula being built.

Signature:

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

The criteria of the formula being built. The builder holds them, and it starts a new set on the first criterion added after a formula was built, so an arm that forgets to clear cannot carry the criteria of the previous cell.

One criterion is a variable name and the test applied to it, and the two travel as a pair into the COUNTIFS or SUMPRODUCT formula CurrentExcelFormula asks for. An empty variable name is ignored, which is how a caller passes an absent column.

Parameters:

• varName: String. The linelist variable name identifying the column the condition applies to.
• conditionName: String. The criteria string for the formula.

ClearTableConditions #

clear-table-conditions

Empty the criteria set before a new formula is built.

Signature:

Private Sub ClearTableConditions()

Every arm opens with this and says what it is about to build. The builder also clears for itself, so a forgotten call costs nothing. The routine used to take a count of pairs to pop off the end, and all sixty-odd call sites took the default, so that branch was unreachable.

AddNonEmptyTableConditions #

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 Text

CurrentExcelFormula #

current-excel-formula

Assembled Excel formula from the current condition accumulators.

Signature:

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

Central entry point that asks the builder for the formula text the current criteria describe.

Builds and returns the complete Excel formula string from the currently criteria the arm has added. 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 reads the whole column and ignores the criteria; (2) Analysis scope builds a COUNTIFS or SUMPRODUCT formula carrying every criterion.

The text is handed back as the parser built it. Excel judges it when WriteFormula assigns it.

Parameters:

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

Returns: String. The Excel formula text without "=" prefix, or an empty string when the parser produced none.

Number Format Helper

AddRngFormat #

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.

Validation Lists

AddListValidation #

add-list-validation

Point a cell at a workbook-wide choice list.

Signature:

Private Sub AddListValidation(ByVal targetName As String, ByVal listName As String)

The three dropdowns a table offers its reader.

The time unit control, the administrative unit control and the population divisor each take their values from one list shared by every table on the sheet. The three writes used to sit inside blanket error handlers, so a table whose dropdown was never created looked exactly like one whose dropdown works.

Parameters:

• targetName: String. The name of the cell carrying the dropdown.
• listName: String. The name of the choice list the dropdown reads.

Checkings

Checks #

checks

The report entries of this instance.

Signature:

Private Property Get Checks() As Checking

What the class reports back to the generation report. The report used to show the analysis phase clean while a table carried a cell Excel had refused. The formula builder and the spatial table builder file into the same entries, so one harvest carries everything written for the table.

Returns: Checking. The entries, created on first use.

LogInfo #

log-info

File one diagnostic entry.

Signature:

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

Checking.Add raises on a duplicate key, and AnalysisOutput pours the entries of several classes over several tables into one report, so the key names the class, the table and a counter. A bare counter collided with the cross-table first entry on the same table.

Parameters:

• message: String. The diagnostic message text.
• scope: Optional Byte. The checking scope (default 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)

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.

• AnalysisOutput.cls
• CrossTable.cls
• FormulaBuilder.cls
• SpatialTables.cls
• FormulaCondition.cls
• Formulas.cls
• Graphs.cls
• TestCrossTable.bas
• TestCrossTableFormula.bas
• TestFormulaBuilder.bas
• TestSpatialTables.bas