CrossTable

The CrossTable class builds and formats structured analysis output tables on an Excel worksheet. It supports six table scopes: univariate, bivariate, time series, spatial, spatio-temporal, and global summary. Each cross-table occupies a rectangular region with row categories, column categories, named ranges, and visual formatting. Consumers interact through the ICrossTable interface, and AnalysisOutput orchestrates the build sequence by calling Build followed by Format for each table specification.

Depends on: ITableSpecs, ILinelistSpecs, ITranslationObject, ILLVariables, LLVariables, ILLFormat, IHiddenNames, HiddenNames, BetterArray

Version: 1.1 (2026-02-11)

Factory

create #

Create a CrossTable instance from table specifications.

Signature:

Public Function Create(ByVal specs As ITableSpecs, _
                       ByVal outputWksh As Worksheet, _
                       ByRef lData As ILinelistSpecs) As ICrossTable

Factory method that creates and returns a new ICrossTable instance from the given table specifications. A cross-table is a structured analysis output (univariate, bivariate, time series, spatial, spatio-temporal, or global summary) that is built on an output worksheet and contains row categories, column categories, named ranges, and formatting. This method validates all three required dependencies, then injects them into a fresh CrossTable instance via the PredeclaredId/New pattern and returns the interface reference.

Parameters:

specs: ITableSpecs. An ITableSpecs object representing one row from the analysis setup sheet.
outputWksh: Worksheet. The Excel Worksheet where this cross-table will physically write its headers, row labels, column labels, data placeholders, and named ranges.
lData: ILinelistSpecs. The linelist specifications object that provides the translation object for locale-aware labels and is passed to ITableSpecs.RowCategories / ColumnCategories for category resolution at build time.

Returns: ICrossTable. A fully initialized ICrossTable ready for Build or individual step calls.

Throws:

InvalidArgument When specs is Nothing.
InvalidArgument When outputWksh is Nothing.
InvalidArgument When lData is Nothing.

Internal Properties

wksh #

Output worksheet where the table is built.

Signature:

Public Property Get Wksh() As Worksheet

Returns the Worksheet reference where this cross-table writes its headers, row labels, column labels, and data cells.

Returns: Worksheet. The output worksheet.

specifications #

Table specifications used to build this cross-table.

Signature:

Public Property Get Specifications() As ITableSpecs

Returns the ITableSpecs instance that defines the configuration for this cross-table. Provides access to table scope, variable names, category lists, and validation flags.

Returns: ITableSpecs. The backing table specification.

Range Access

header-range #

Header range of the table on the output worksheet.

Signature:

Public Property Get HeaderRange() As Range

Properties that return Range objects for structural regions of the table. Returns the Range object spanning the column header row of this cross-table. The range starts at (StartRow, StartColumn + 1) and extends to (StartRow, EndColumn), covering all column category cells but excluding the row-label column at StartColumn. This range is used by NameRanges to create the COLUMN_CATEGORIES_ named range, by Format to apply header styling, and by ColumnRange to search for a specific column value.

Returns: Range. The header row range excluding the leftmost row-label cell.

Remarks:

EndColumn must be set (by AddColumns/BivariateColumns) before this property is called. If EndColumn is 0, the returned range will be invalid.

rows-categories-range #

Row categories range with optional filtering.

Signature:

Public Property Get RowsCategoriesRange(Optional ByVal includeHeaders As Boolean = True, _
                                         Optional ByVal onlyCategories As Boolean = False) As Range

Returns a Range covering the row category cells of this cross-table in column C (STANDARD_START_COL). The range can optionally include or exclude the header label row, and can be trimmed to only the "pure" category rows (excluding Total and Missing footer rows). The start row varies by table scope: for bivariate, time series, spatial, and spatio-temporal tables, the categories begin at StartRow + 1; for univariate tables, they begin at StartRow itself. If includeHeaders is False, an additional row is skipped. The end row also varies based on the onlyCategories flag, trimming footer rows for time series or tables with HasTotal.

Parameters:

includeHeaders: Optional Boolean. When True (default), includes the row variable label row. Defaults to True.
onlyCategories: Optional Boolean. When True, excludes Total and Missing footer rows. Defaults to False.

Returns: Range. A single-column Range in column C spanning the requested row categories.

Remarks:

Raises an error for ScopeGlobalSummary because global summary tables do not have row categories.

Throws:

ErrorUnexpectedState When the table scope is ScopeGlobalSummary.

row-range #

Row data range for a specific row value.

Signature:

Public Property Get RowRange(ByVal rowVal As String, _
                              Optional ByVal includeHeaders As Boolean = True) As Range

Finds a row in the cross-table by its category label value and returns a horizontal Range spanning that entire row from the start column to the end column. The search is performed within RowsCategoriesRange using an exact case-sensitive match via ValueExists/Find. If the value is found, the returned range includes either the full row from StartColumn (with the row-label cell) or from StartColumn + 1 (data cells only), depending on the includeHeaders parameter. If the value is not found, Nothing is returned.

Parameters:

rowVal: String. The exact string value to search for in the row categories column.
includeHeaders: Optional Boolean. When True (default), the returned range starts at StartColumn. Defaults to True.

Returns: Range. A single-row Range, or Nothing if rowVal is not found.

Remarks:

The search uses RowsCategoriesRange() with default parameters, so it searches ALL row category cells including footer rows.

column-range #

Column data range for a specific column value.

Signature:

Public Property Get ColumnRange(ByVal colVal As String, _
                                 Optional ByVal onlyCategories As Boolean = False, _
                                 Optional ByVal includeHeaders As Boolean = False) As Range

Finds a column in the cross-table by its header label value and returns a vertical Range spanning that entire column from the first data row to the end row. The search is performed within HeaderRange using an exact case-sensitive match via ValueExists/Find. The start row of the returned range varies by table scope. The end row can be trimmed to exclude footer rows when onlyCategories is True. The includeHeaders parameter extends the range one row upward to include the column header cell. Returns Nothing if colVal is not found.

Parameters:

colVal: String. The exact string value to search for in the header row.
onlyCategories: Optional Boolean. When True, excludes Total and Missing footer rows. Defaults to False.
includeHeaders: Optional Boolean. When True, includes the column-label row. Defaults to False.

Returns: Range. A single-column Range, or Nothing if colVal is not found in the header range.

Remarks:

Callers must check for Nothing before using the result.

AddRows

addrows #

Add row headers and row category labels to the worksheet.

Signature:

Private Sub AddRows()

Populate row categories on the output worksheet. Populates the row category labels in the leftmost column (STANDARD_START_COL) of this cross-table on the output worksheet. The row content depends entirely on the table scope: ScopeGlobalSummary writes a single label cell; ScopeUnivariate writes the row variable's main label then all row categories with optional Missing and Total; ScopeBivariate is similar but offset by one extra row; ScopeTimeSeries and ScopeSpatioTemporal create a fixed-size grid of NB_ROWS_TIME_SERIES (56) rows only for new sections; ScopeSpatial writes the geo-level label then reserves blank rows for dynamic geo data plus Missing. After writing, this sub sets EndRow for subsequent steps.

Remarks:

For temporal tables, AddRows is only called once per section (the first table or the de-facto first table). Subsequent tables in the same temporal section reuse the row structure from the first table and only add columns.

AddColumns

addcolumns #

Add column headers and column category labels to the worksheet.

Signature:

Private Sub AddColumns()

Populate column headers on the output worksheet. Writes column headers and category labels to the output worksheet. The column structure depends on the table scope: ScopeGlobalSummary creates two fixed columns; ScopeUnivariate creates one data column plus an optional percentage column; ScopeBivariate delegates to BivariateColumns then writes the column variable label; ScopeTimeSeries and ScopeSpatioTemporal delegate to BivariateColumns then construct a composite TimeSeriesHeader string; ScopeSpatial delegates entirely to BivariateColumns. After writing, this sub sets EndColumn and NumberOfColumns.

Remarks:

For global summary tables, the two-column header is created only once and shared by all subsequent global summary rows. The COLGS_SET named range acts as a guard to prevent duplicate creation.

AddHeader

addheader #

Add the table title and header to the worksheet.

Signature:

Private Sub AddHeader()

Create section headers, titles, and date/geo controls. Inserts the structural header infrastructure above the data area of this cross-table: section titles, table titles, date/time input controls for temporal tables, and geo-level selection controls for spatial tables. This is the first step called by Build and may insert rows into the worksheet (shifting existing content down) to make room for the header. Behavior varies by table scope: univariate and bivariate write a title and optional section header; global summary writes a shared label once; temporal tables insert date controls and optional geo-level inputs; spatial tables create admin-unit or health-facility dropdowns. This sub also initializes the allRangeNames BetterArray.

Remarks:

Row insertions shift the entire sheet down, which is why AddHeader must run before AddRows, AddColumns, and NameRanges. For temporal tables, the date controls are unlocked (Locked = False) so the user can interactively change dates at runtime.

NameRanges

name-ranges #

Create all named ranges for the table data regions.

Signature:

Private Sub NameRanges()

Create all structural named ranges for the table. Creates all structural named ranges for this cross-table after the data area (rows and columns) has been populated. These named ranges serve as stable references for formula generation, VBA-driven data updates, and export tracking. The method creates ENDTABLE_, ROW_CATEGORIES_, time-period markers, per-column VALUES_COL_ and LABEL_COL_ ranges, Missing/Total/Percent row and column ranges, intersection cells, INTERIOR_VALUES_, OUTER_VALUES_ (spatial only), and COLUMN_CATEGORIES_. All created range names are accumulated in the allRangeNames BetterArray for retrieval via NamedRangesList.

Remarks:

Exits immediately for ScopeGlobalSummary (which has no row categories or column structure beyond the two fixed columns).

Throws:

ErrorUnexpectedState When NumberOfColumns is 0, indicating AddColumns was not called.

Format

format #

Apply formatting to the table using design specifications.

Signature:

Private Sub Format(ByVal desFormat As ILLFormat)

Apply visual formatting to all structural regions of the cross-table. Applies comprehensive visual formatting to every part of the cross-table using the design format object (ILLFormat). This is the final step after Build completes the structural layout. Formatting is applied in order: section header, table title, spatial dropdown, column headers, row and column categories, interior values, Total/Missing rows and columns, single cells, info cells, hidden cells, percentage columns, whole table borders, and end-table gap. Bug #1 fix: for temporal tables where HasTotal is True but TotalRequested is False, hides the Total column entirely since it is needed internally for percentage computation but should be invisible to the user.

Parameters:

desFormat: ILLFormat. The formatting specification providing ApplyFormat for each scope.

Remarks:

Format must be called after Build because it relies on named ranges being present. Some operations are idempotent, guarded by HiddenNames like ROWGS_FORMATSET and MERGED_.

Build

build #

Build the complete cross-table on the worksheet.

Signature:

Private Sub Build()

Orchestrate the complete table construction sequence. Orchestrates the complete construction of this cross-table by calling the four build steps in the correct order: AddHeader (inserts rows), AddRows (populates row categories and sets EndRow), AddColumns (populates column headers and sets EndColumn/NumberOfColumns), and NameRanges (creates all named ranges). The order is critical because AddHeader may insert rows that shift content down. After Build completes, the table is structurally complete and ready for Format and formula generation.

Remarks:

Build does NOT call Format. Formatting is applied separately by the caller (typically the analysis builder) after all tables in a section have been built.

Internal members (not exported)

Internal Properties

self #

Current object instance.

Signature:

Public Property Get Self() As ICrossTable

Properties used during factory instantiation and internal wiring. Convenience accessor so consuming code can fluently retrieve the interface reference from the predeclared Create method.

Returns: ICrossTable. The current instance cast to the interface.

wksh-set #

Assign the output worksheet.

Signature:

Public Property Set Wksh(ByVal sh As Worksheet)

Parameters:

sh: Worksheet. The target worksheet to assign.

specifications-set #

Assign the table specifications.

Signature:

Public Property Set Specifications(ByVal specs As ITableSpecs)

Parameters:

specs: ITableSpecs. The table specification to assign.

linelist-data #

Linelist specifications providing translations and categories.

Signature:

Public Property Get LinelistData() As ILinelistSpecs

Returns the ILinelistSpecs instance stored at construction time. Used to extract the translation object and to pass as the lData parameter when calling specs.RowCategories(lData) and specs.ColumnCategories(lData).

Returns: ILinelistSpecs. The linelist specifications.

linelist-data-set #

Assign the linelist specifications.

Signature:

Public Property Set LinelistData(ByVal lData As ILinelistSpecs)

Parameters:

lData: ILinelistSpecs. The linelist specifications to assign.

translations #

Translation object for locale-aware labels.

Signature:

Public Property Get Translations() As ITranslationObject

Returns the ITranslationObject extracted from the stored linelist specifications. Resolves translated strings such as "Total", "Missing", "Percent", and date-unit names.

Returns: ITranslationObject. The translation object.

number-of-columns #

Number of data columns in the table.

Signature:

Private Property Get NumberOfColumns() As Long

Returns the count of data columns excluding the row-label column. For bivariate and time series tables this equals the number of column categories plus optional total and missing columns.

Returns: Long. The data column count.

number-of-columns-set #

Assign the number of data columns.

Signature:

Private Property Let NumberOfColumns(ByVal col As Long)

Parameters:

col: Long. The data column count to store.

named-ranges-list #

List of named range names created by this table.

Signature:

Private Property Get NamedRangesList() As BetterArray

Returns a cloned BetterArray containing all the named range names that were accumulated during the build process. Used by AnaTabIds to register the ranges for export tracking.

Returns: BetterArray. The collection of named range names.

Position Properties

range-exists #

Check whether a named range exists on the output worksheet.

Signature:

Private Function RangeExists(ByVal rngName As String) As Boolean

Properties that compute or retrieve the table position on the worksheet. Checks whether a named range with the given name is defined and reachable on the current output worksheet. This is used extensively throughout the class to decide whether structural infrastructure (section headers, start rows, end-table markers, column setups, etc.) has already been created by a previous table in the same section, or whether it needs to be created for the first time. The check uses On Error Resume Next to silently swallow the error that Excel raises when a name does not exist, then inspects whether the resulting Range reference is Nothing.

Parameters:

rngName: String. The string name of the named range to look up, e.g. "STARTROW_T3" or "ENDTABLE_T5".

Returns: Boolean. True if the named range exists and resolves to a valid Range on the output worksheet; False otherwise.

Remarks:

This does not distinguish between workbook-scoped and worksheet-scoped names. If a workbook-level name exists but does not refer to a range on this sheet, the lookup may still succeed. All names in this class are created via Cell.Name which produces workbook-scoped names.

start-row #

Starting row of the table on the output worksheet.

Signature:

Private Property Get StartRow() As Long

Computes or retrieves the starting row for this cross-table on the output worksheet. The logic has three branches depending on whether the named range STARTROW_{tableId} already exists (reuse), the table is a new section or non-temporal type (compute fresh), or the table is a non-new-section temporal table (inherit from previous table). For new sections and non-temporal tables the start row is computed by finding the last used row in column C via End(xlUp), then adding vertical padding that varies by table scope. For non-new-section temporal tables the start row is inherited from the Previous cross-table so that all temporal tables in a section are laid out side-by-side. Bug #3 fallback: if the previous table was never built, the code computes a fresh start row as if this were a new section.

Returns: Long. The 1-based row number on the output worksheet where this table begins.

Remarks:

This property is called many times during a single Build cycle (by StartColumn, AddRows, AddColumns, AddHeader, NameRanges, Format). After the first call creates the STARTROW_ named range, subsequent calls are cheap lookups.

previous #

Previous cross-table in the same section.

Signature:

Private Property Get Previous() As ICrossTable

Creates and returns an ICrossTable instance representing the table that immediately precedes this one within the same analysis section. This is used by temporal table scopes (time series and spatio-temporal) where multiple tables share the same row structure and are laid out side-by-side in columns. The previous table's start row and end column are needed to position the current table correctly. The method delegates to the Create factory, passing the Previous spec from the current table's ITableSpecs, along with the same output worksheet and translation object.

Returns: ICrossTable. The preceding cross-table, or Nothing when this is the first table.

Remarks:

Each call creates a new CrossTable instance; the result is not cached. For temporal tables, this property is typically called once during StartRow resolution and should not be called in tight loops.

start-column #

Starting column of the table on the output worksheet.

Signature:

Private Property Get StartColumn() As Long

Computes or retrieves the starting column for this cross-table on the output worksheet. For most table scopes and for the first table in a temporal section, the start column is the constant STANDARD_START_COL (column C, index 3). For non-new-section temporal tables (time series and spatio-temporal), the start column is determined by finding the last used column in the row immediately below StartRow via End(xlToLeft). This produces the side-by-side horizontal layout where each successive temporal table in a section is placed to the right of the previous one. The computed column is persisted as the named range STARTCOL_{tableId} so that subsequent calls are idempotent.

Returns: Long. The 1-based column number where this table's row category labels begin.

Remarks:

The STARTCOL_ named range is stored at (StartRow - 1, strtCol + 1), meaning the actual named range cell is one column to the right and one row above the logical start column. When reading back, the code subtracts 1 from the named range's column to recover the true start column.

end-row-set #

Assign the ending row index.

Signature:

Private Property Let EndRow(ByVal rw As Long)

Parameters:

rw: Long. The 1-based ending row index.

end-column-set #

Assign the ending column index.

Signature:

Private Property Let EndColumn(ByVal col As Long)

Parameters:

col: Long. The 1-based ending column index.

end-row #

Last row used by the table on the worksheet.

Signature:

Private Property Get EndRow() As Long

Returns the 1-based row index of the bottommost cell occupied by this cross-table. Used to compute the start row for the next stacked table.

Returns: Long. The ending row index.

end-column #

Last column used by the table on the worksheet.

Signature:

Private Property Get EndColumn() As Long

Returns the 1-based column index of the rightmost cell occupied by this cross-table. Used by sibling tables and formatting routines to avoid overlapping output.

Returns: Long. The ending column index.

Range Access

time-series-header #

Header label for time series tables.

Signature:

Private Property Get TimeSeriesHeader() As String

Returns the composite header text for a time-series or spatio-temporal table. This text is a human-readable string like "Cases -- Notification date -- Region" built from the summary label, the time variable label, and optionally the column variable label, separated by horizontal-line Unicode characters (U+2500). It is stored in the STARTCOL_ named-range cell on the worksheet and used by the GoTo navigation dropdown so the user can jump to this table's section.

Returns: String. The cached header text string, or an empty string if not yet set.

Remarks:

This value is populated during AddColumns for temporal tables only. Non-temporal table scopes never set this property.

time-series-header-set #

Store the composite header text for temporal tables.

Signature:

Private Property Let TimeSeriesHeader(ByVal headerText As String)

Parameters:

headerText: String. The full header string to cache.

value-exists #

Check whether a value exists in a given range.

Signature:

Private Function ValueExists(ByVal rng As Range, ByVal searchValue As String) As Boolean

Checks whether a specific string value exists anywhere within the given Range by performing an exact, case-sensitive, whole-cell match using Excel's Range.Find method. This is used internally by RowRange and ColumnRange to verify that a category label is present before attempting to locate its row or column position.

Parameters:

rng: Range. The Range to search within, typically RowsCategoriesRange or HeaderRange.
searchValue: String. The exact string to look for (case-sensitive, whole-cell match).

Returns: Boolean. True if at least one cell in rng contains exactly searchValue; False otherwise.

Remarks:

Uses xlWhole and MatchCase:=True, so "total" will NOT match "Total". This is important because translated labels may have specific capitalization.

Helpers

percent-label #

Build a display label for percentage columns.

Signature:

Private Function PercentLabel(ByVal percentVal As String, _
                               Optional ByVal percentType As String = "all") As String

Private helper methods that support the public API. Builds a display label for percentage columns by appending a directional Unicode arrow to the translated percentage string. The arrow indicates the direction of the percentage computation: a horizontal double arrow (U+2194) for row percentages and a vertical double arrow (U+2195) for column percentages. When the percentage type is "all" or any other value, no arrow is appended.

Parameters:

percentVal: String. The translated percentage label text.
percentType: Optional String. The percentage direction: "row", "column", or "all". Defaults to "all".

Returns: String. The formatted percentage label string with or without a directional arrow.

bivariate-columns #

Write column headers for bivariate, temporal, and spatial tables.

Signature:

Private Sub BivariateColumns(ByVal startRw As Long, ByVal specs As ITableSpecs, _
                              ByVal trans As ITranslationObject, ByVal sh As Worksheet)

Shared column-building logic used by bivariate, time series, spatial, and spatio-temporal table scopes. This sub writes the column category headers and column-label sub-headers to the output worksheet, starting at StartColumn + 1. It handles two layout scenarios: when column categories exist, they are written to the header row with optional percentage interleaving; when column categories are empty, a single summary column is created. This sub also sets NumberOfColumns and EndColumn.

Parameters:

startRw: Long. The starting row for column headers.
specs: ITableSpecs. The ITableSpecs providing column categories and flags.
trans: ITranslationObject. The ITranslationObject providing translated strings.
sh: Worksheet. The output Worksheet where the column headers are written.

Remarks:

NumberOfColumns counts only the "pure" data columns, not including Missing or Total. However, EndColumn includes all columns written.

Bug #3 Helper

is-de-facto-new-section #

Determine whether this table is a de-facto new section.

Signature:

Private Function IsDeFactoNewSection() As Boolean

Detect de-facto new sections when the previous table was never built. Determines whether this table should be treated as a de-facto new section even though its ITableSpecs.IsNewSection returns False. This situation arises from Bug #3: when the previous table in the same section was invalid and was therefore skipped during the build process, the section infrastructure was never created. The logic checks whether the previous table's ENDTABLE_ named range exists; if not, this table must act as the section anchor. The result is cached after the first computation since IsDeFactoNewSection is called multiple times during a single Build cycle.

Returns: Boolean. True if this table must assume the section-anchor role; False otherwise.

Remarks:

For tables where specs.IsNewSection is True, this function always returns False. The typical usage pattern is: "If specs.IsNewSection() Or IsDeFactoNewSection() Then ...".

Error Handling

throw-error #

Raise a ProjectError-based exception.

Signature:

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

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.
message: String. Human-readable description of the failure.

Throws:

ProjectError. Always raises the specified error.

Interface Implementation

ICrossTable_AddRows #

Signature:

Private Sub ICrossTable_AddRows()

Delegated members satisfying the ICrossTable contract. See the corresponding Public members above for full documentation.

Used in (11 file(s))