CrossTable

Builds and formats one analysis output table on a worksheet. Six table scopes are handled: univariate, bivariate, time series, spatial, spatio-temporal and global summary. Build lays the table out and Format gives it its look. StartRow, EndRow, EndColumn, NumberOfColumns, HeaderRange, RowRange, ColumnRange, RowsCategoriesRange and TimeSeriesHeader answer where the table sits and what it holds. AnalysisOutput calls Build and then Format for each table specification.

THE LINELIST DATA PARAMETER IS TYPED Object

Create takes lData as an Object. The class asks it for TransObject and hands it on to TableSpecs.RowCategories and ColumnCategories, which type the same parameter as Object and dispatch on TypeName. AnalysisOutput passes a LinelistSpecs, and the Object type keeps the LinelistSpecs closure outside the compile dependencies of this class.

THE FOUR BUILD PHASES RUN IN ONE ORDER

AddHeader inserts rows and shifts the sheet, AddRows sets EndRow, AddColumns sets EndColumn and NumberOfColumns, and NameRanges reads all three. Build runs them in that order and records how far it has gone. NameRanges and Format refuse a table whose earlier phases have not run. Between AddHeader and AddRows the layout is fixed: the start row is held and the start column is written onto the sheet.

EACH OF THE THREE LARGE PHASES IS A DISPATCHER

AddHeader, NameRanges and Format each carry one Select Case over the table scope and hand the work to a small private routine, the way VarWriter does.

WHAT IS RESOLVED ONCE PER TABLE

The table identifier, the table scope, the range-name builder, the spatial type, the translation provider and the variable reader are read once and held for the life of the instance.

Depends on: AnalysisRanges, TableSpecs, TranslationObject, LLVariables, LLFormat, HiddenNames, BetterArray, Checking, The four build phases, in the order Build runs them. AddHeader inserts rows, AddRows sets EndRow, AddColumns sets EndColumn and NumberOfColumns, and, NameRanges needs all three, so the order is load-bearing. RequirePhase is what, says so to the compiler-less reader and to the caller that gets it wrong., The prefix lists Format walks. Each one was a BetterArray cleared and refilled, per table, and each list is the same for every table of a scope, so they are, written once here and split on use., Supplies the unique key every Checking.Add needs. It counts within one, instance, which is all uniqueness requires: each instance owns its own, Checking, and AnalysisOutput harvests them per table.

Version: 1.2 (2026-07-31)

Factory

Create #

create

Create a CrossTable instance from table specifications.

Signature:

Public Function Create(ByVal specs As TableSpecs, _
                       ByVal outputWksh As Worksheet, _
                       ByVal lData As Object, _
                       Optional ByVal previousTable As CrossTable, _
                       Optional ByVal outputNames As HiddenNames) As CrossTable

Factory method that creates and returns a new CrossTable 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: TableSpecs. An TableSpecs 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: Object. The linelist specifications object that provides the translation object for locale-aware labels and is passed to TableSpecs.RowCategories / ColumnCategories for category resolution at build time. Production passes a LinelistSpecs. The type is Object so that this class carries no compile dependency on it; the two members it needs, TransObject and Categories, are resolved at run time.
  • previousTable: Optional CrossTable. The table this one continues, when the caller already holds it. A caller that walks the setup rows in order builds every ancestor anyway, so handing the last one in saves this instance rebuilding it.
  • outputNames: Optional HiddenNames. The metadata reader of the output worksheet, when the caller already holds one. Creating a reader walks every tracked name on the sheet, so a caller writing many tables hands one reader to all of them. Left out, this instance builds its own on first use.

Returns: CrossTable. A fully initialized CrossTable 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 #

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 #

specifications

Table specifications used to build this cross-table.

Signature:

Public Property Get Specifications() As TableSpecs

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

Returns: TableSpecs. The backing table specification.

Position Properties

ReserveChartSpace #

reserve-chart-space

Hold rows for a chart that has not been drawn yet.

Signature:

Public Sub ReserveChartSpace(ByVal bottomRow As Long)

ComputeStartRow places a table below the charts already on the sheet, which it learns by reading them. A caller that draws its charts after all its tables has no chart to be read at that moment, so it says here how far down the chart will reach and the next table is placed below that line all the same. The answer is held in the same worksheet name ClearOfCharts reads, and the larger of the two wins, so a reservation and a drawn chart cannot undercut each other.

Parameters:

  • bottomRow: Long. The last row the chart will cover.
Range Access

HeaderRange #

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.

RowsCategoriesRange #

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.

RowRange #

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

ColumnRange #

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 FindLabelCell. 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 #

addrows

Add row headers and row category labels to the worksheet.

Signature:

Public 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 #

addcolumns

Add column headers and column category labels to the worksheet.

Signature:

Public 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 #

addheader

Add the table title and header to the worksheet.

Signature:

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

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

NameRanges #

name-ranges

Create all named ranges for the table data regions.

Signature:

Public 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_. Every name is created on the worksheet itself, which is where the export reads them back from.

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 #

format

Apply formatting to the table using design specifications.

Signature:

Public Sub Format(ByVal desFormat As LLFormat)

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 (LLFormat). 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: LLFormat. 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

Build the complete cross-table on the worksheet.

Signature:

Public 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

Seal #

seal

Seal the instance against further setup writes.

Signature:

Public Sub Seal()

Properties used during factory instantiation and internal wiring.

Also resolves the two facts every phase of the build reads first -- the table identifier and the table scope -- and binds the range-name builder to the identifier. Neither can change while this instance lives, so each is read once here instead of once per phase.

PreviousTable #

previous-table-set

Assign the table this one continues.

Signature:

Public Property Set PreviousTable(ByVal tabl As CrossTable)

Parameters:

OutputNames #

output-names-set

Assign the metadata reader of the output worksheet.

Signature:

Public Property Set OutputNames(ByVal namesObj As HiddenNames)

Parameters:

MetadataNames #

metadata-names

The metadata reader of the output sheet, built when nobody handed one in.

Signature:

Private Function MetadataNames() As HiddenNames

Returns: HiddenNames. The reader bound to the output worksheet.

GuardNotSealed #

guard-not-sealed

Guard a setup setter against post-creation writes.

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

Parameters:

Wksh #

wksh-set

Assign the output worksheet.

Signature:

Public Property Set Wksh(ByVal sh As Worksheet)

Parameters:

Specifications #

specifications-set

Assign the table specifications.

Signature:

Public Property Set Specifications(ByVal specs As TableSpecs)

Parameters:

LinelistData #

linelist-data

Linelist specifications providing translations and categories.

Signature:

Public Property Get LinelistData() As Object

Returns the linelist specifications object 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: Object. The linelist specifications.

LinelistData #

linelist-data-set

Assign the linelist specifications.

Signature:

Public Property Set LinelistData(ByVal lData As Object)

Parameters:

Translations #

translations

Translation object for locale-aware labels.

Signature:

Public Property Get Translations() As TranslationObject

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

Returns: TranslationObject. The translation object.

NumberOfColumns #

number-of-columns

Number of data columns in the table.

Signature:

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

NumberOfColumns #

number-of-columns-set

Assign the number of data columns.

Signature:

Private Property Let NumberOfColumns(ByVal col As Long)

Parameters:

Cached Facts

TableId #

table-id

Identifier of the table being built.

Signature:

Private Property Get TableId() As String

Every phase of a build opened by re-deriving the same handful of facts, and none of them can change while one instance lives. They are resolved once and held here, the way VarWriter holds its collaborators.

Returns: String. The table identifier, such as "TS_tab3".

TableScope #

table-scope

Analysis scope of the table being built.

Signature:

Private Property Get TableScope() As Byte

Returns: Byte. A member of AnalysisTableScope.

RangeNames #

range-names

Range-name builder bound to this table's identifier.

Signature:

Private Property Get RangeNames() As AnalysisRanges

Returns: AnalysisRanges. The builder.

SpatialType #

spatial-type

Whether the spatial variable is geographic or a health facility.

Signature:

Private Property Get SpatialType() As String

Resolved on first use rather than at creation, because the answer costs a dictionary probe and the four non-spatial scopes never ask for it. An empty answer is a real answer here, so a separate flag records that the probe ran.

Returns: String. "geo", "hf", or an empty string.

Variables #

variables

Variable reader over the dictionary of this build.

Signature:

Private Property Get Variables() As LLVariables

AddRows and AddColumns each built one of these, and TableSpecs builds a third for every category lookup. One per table is enough.

Returns: LLVariables. The variable reader.

ValueOf #

value-of

One setup column of the row this table is built from.

Signature:

Private Property Get ValueOf(ByVal colName As String) As String

Every read of the setup row goes through here. TableSpecs.Value scans its cached header names, so the cost is one linear scan per call, and putting the twenty-odd call sites behind one name is what makes a per-table memo a single edit later.

Parameters:

Returns: String. The cell value, or an empty string.

Position Properties

RangeExists #

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:

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

Remarks:

NamedRange #

named-range

Resolve a named range on the output worksheet, or Nothing.

Signature:

Private Function NamedRange(ByVal rngName As String) As Range

The answer to "does this name exist" and the answer to "give me the range" were two separate name resolutions at every call site of RangeExists that went on to read the range. Format alone did that about fifty times per table. This answers both questions with one resolution.

Parameters:

Returns: Range. The range, or Nothing when the name is absent or owned by another worksheet.

Remarks:

StartRow #

start-row

Starting row of the table on the output worksheet.

Signature:

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

WHY THE CACHE IS FILLED AFTER AddHeader AND NOT AT CREATION

AddHeader inserts rows above the table, which moves it down the sheet. Excel moves the STARTROW_ name with it; a Long held from before the insert would be stale. Nothing inserts rows above a table once its own header is written, so from that point on the value is fixed for the life of the instance.

CacheStartRow #

cache-start-row

Resolve the start row once and hold it.

Signature:

Private Sub CacheStartRow()

ComputeStartRow #

compute-start-row

Read or compute the start row from the worksheet.

Signature:

Private Function ComputeStartRow() As Long

Returns: Long. The 1-based row this table begins at.

Throws:

ClearOfCharts #

clear-of-charts

Push a candidate start row below the charts already on the sheet.

Signature:

Private Function ClearOfCharts(ByVal candidate As Long) As Long

The last used cell of column C says where the tables end and nothing about the charts, which hang right of their table and reach lower whenever the table is short. A table placed off the used cells alone started level with the chart of the table before, and its own chart was drawn over that one. The charts of a temporal sheet are drawn after every table of the sheet, so this walk finds nothing there and the temporal layout stands as it was.

Parameters:

Returns: Long. The first row below every chart, or the candidate when it already clears them.

Remarks:

Previous #

previous

Previous cross-table in the same section.

Signature:

Public Property Get Previous() As CrossTable

Creates and returns an CrossTable 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 TableSpecs, along with the same output worksheet and translation object.

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

Remarks:

WHY THIS PROPERTY USED TO COST O(n) PER CALL

It built a fresh CrossTable every time it was read, and StartRow reads Previous.StartRow, which reads Previous again. For a temporal section of n tables, resolving the last table's start row constructed O(n) cross-tables and walked O(n) specification chains, and StartRow is read from six places. Holding the instance collapses that to one construction per table.

AND WHY A HANDED-IN ANCESTOR IS CHECKED BEFORE IT IS USED

AnalysisOutput walks the setup rows in order and keeps the last table it built, so it can hand the ancestor in and save this instance building it again. The two can disagree: a row that passes validation and then fails inside Build is still the nearest valid row above, so the caller's last table is one further up. The identifiers are compared and a mismatch falls back to building the real one.

StartColumn #

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 filled column in the row immediately below StartRow, hidden columns included. 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:

THIS PROPERTY WRITES NOTHING

It used to name STARTCOL_ on the worksheet, so reading it from a consumer turned into writing names onto tables the caller had not built -- and, through StartRow, into walking the whole ancestor chain. EnsureStartColumn is the assignment half and Build calls it once, straight after AddHeader. A read before that answers what the assignment would store.

ComputeStartColumn #

compute-start-column

Work out which column this table starts in.

Signature:

Private Function ComputeStartColumn() As Long

A temporal table continuing a section sits to the right of the one before it, which is what the scan of the label row finds. Every other table, and the table that opens a temporal section, starts at the standard column.

WHY THE SCAN IS NOT End(xlToLeft)

Range.End SKIPS HIDDEN COLUMNS, and HideUnrequestedTotals hides the total pair of every temporal table whose percentage forced a total the user never asked for. The scan then answered the column BEFORE that pair, so the next table of the section was laid two columns too far left, on top of the hidden total. Its title strip overlapped the strip before it, Excel welded the two merges into one and a whole run of tables ended up under a single title; its COLUMN_CATEGORIES_ range overlapped too, which is what left the time series charts looking for column headings that had been overwritten. The walk below reads values and is blind to whether a column is hidden.

Returns: Long. The 1-based start column.

LastFilledColumn #

last-filled-column

The rightmost filled cell of one row, hidden columns included.

Signature:

Private Function LastFilledColumn(ByVal rowNum As Long) As Long

Reads the row in one crossing and walks it backwards. A cell counts as filled when it is not empty, which is the rule End(xlToLeft) applies too: the space BivariateColumns writes under a summary-only column counts, and so does a cell holding an error value.

Parameters:

Returns: Long. The 1-based column of the rightmost filled cell, or 0 when the row holds nothing.

EnsureStartColumn #

ensure-start-column

Fix this table's start column on the worksheet.

Signature:

Private Sub EnsureStartColumn()

Writes STARTCOL_ once. It has to run before AddColumns, because the row scan behind a continuation table's column answers something else as soon as that table's own columns are on the sheet, and because AddColumns writes the column label into the named cell. Build calls it straight after AddHeader; AddRows and AddColumns call it too, so a caller driving the phases one at a time gets the same sheet.

EndRow #

end-row-set

Assign the ending row index.

Signature:

Private Property Let EndRow(ByVal rw As Long)

Parameters:

EndColumn #

end-column-set

Assign the ending column index.

Signature:

Private Property Let EndColumn(ByVal col As Long)

Parameters:

EndRow #

end-row

Last row used by the table on the worksheet.

Signature:

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

EndColumn #

end-column

Last column used by the table on the worksheet.

Signature:

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

THE CONTRACT, PINNED HERE BECAUSE IT IS DECIDED HERE

EndColumn is INCLUSIVE and it is the last column WRITTEN. The total and missing columns count toward it. NumberOfColumns stays outside it and stops at the data columns. Every assignment below obeys this -- StartColumn + labData.Length for the bivariate and temporal arms, StartColumn + 2 for the global summary pair, StartColumn + colData.Length for the spatial arm -- because labData and colData already carry whatever total and missing labels the table asked for.

So a reader walking the value columns wants < EndColumn only if it means to stop before the totals, and <= EndColumn if it means to include them. The formula writer currently uses < tabEndColumn for bivariate and <= tabEndColumn for time series and spatial, which cannot be right for all three against one definition. The loops live in CrossTableFormula and are fixed there. Stating the contract here is the half that belongs to this class.

Returns: Long. The ending column index, inclusive of total and missing columns.

Range Access

TimeSeriesHeader #

time-series-header

Header label for time series tables.

Signature:

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

TimeSeriesHeader #

time-series-header-set

Store the composite header text for temporal tables.

Signature:

Private Property Let TimeSeriesHeader(ByVal headerText As String)

Parameters:

FindLabelCell #

find-label-cell

Find the cell holding a label in a given range.

Signature:

Private Function FindLabelCell(ByVal rng As Range, ByVal searchValue As String) As Range

Exact, case-sensitive, whole-cell match through Excel's Range.Find, so "total" will NOT match "Total"; translated labels carry their own capitalization. RowRange and ColumnRange read the row or the column off the answer. Each used to ask twice, once to know the label was there and once for where, and every Find is a host search over the category band.

Parameters:

Returns: Range. The first cell holding exactly searchValue, or Nothing.

AddRows

WriteTemporalRowGrid #

write-temporal-row-grid

Write the fixed row block a temporal section shares.

Signature:

Private Sub WriteTemporalRowGrid(ByVal startRw As Long, ByVal startCol As Long)

The block is NB_ROWS_TIME_SERIES rows tall: a Period label at the top, blank rows for the periods themselves, then Total and Missing. It used to be built by pushing 52 empty strings one at a time into a BetterArray, in a loop whose bounds had to be kept in step with the constant by hand. The size comes from the constant now and the block reaches the sheet in one crossing.

THE TOTAL AND MISSING LABELS ARE ALWAYS WRITTEN

RowsCategoriesRange trims the two footer rows BY COUNT, so dropping a label would move every category range of the section up by one row and take every total formula with it. Whether the user SEES the Total row is decided in Format, which hides it when no table of the section asked for one -- issue #338.

Parameters:

Helpers

PercentLabel #

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:

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

BivariateColumns #

bivariate-columns

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

Signature:

Private Sub BivariateColumns(ByVal startRw As Long, ByVal specs As TableSpecs, _
                              ByVal trans As TranslationObject, 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:

Remarks:

AddHeader

AddTitleAndSectionHeader #

add-title-and-section-header

Write the title of a univariate or bivariate table.

Signature:

Private Sub AddTitleAndSectionHeader()

A table that opens a section also gets three rows inserted above it for the section label. The insert shifts the sheet down and Excel carries the names with it, which is why the local start row is read before the insert and the section cell is addressed through the title name afterwards.

AddGlobalSummaryHeader #

add-global-summary-header

Write the shared global summary label, once per sheet.

Signature:

Private Sub AddGlobalSummaryHeader()

Every global summary row of a sheet shares one label, so ROWGS_SET is both the name of the cell and the guard that stops the second row writing it again.

AddTemporalHeader #

add-temporal-header

Open a temporal section and write the controls it carries.

Signature:

Private Sub AddTemporalHeader()

Runs for the table that opens the section only. A spatio-temporal section needs room for its geographic input rows on top of the date controls, which is what the taller insert is for.

AddSpatioTemporalGeoInputs #

add-spatio-temporal-geo-inputs

Write the geographic input rows of a spatio-temporal section.

Signature:

Private Function AddSpatioTemporalGeoInputs(ByVal sectionRng As Range, _
                                            ByVal nGeo As Long) As Range

One input cell per geographic unit, each named so the formula writer can point at it. The tag comes from the dictionary probe, which is what the formula writer uses to reference these same cells. It used to come from the "spatial type" column of this row, and that column is never filled: the setup workbook validates it on the spatio-temporal specification table and the analysis table only ever receives the geo dropdown. So the read returned empty, every table was tagged geographic, and a health facility table's label formulas pointed at INPUTSPTHF_ names that were never created.

Parameters:

Returns: Range. The anchor the date controls hang from.

AddTemporalDateControls #

add-temporal-date-controls

Write the start date, time unit and end date controls of a section.

Signature:

Private Sub AddTemporalDateControls(ByVal anchorRng As Range)

Parameters:

AddSpatialHeader #

add-spatial-header

Write the dropdowns and the title of a spatial table.

Signature:

Private Sub AddSpatialHeader()

Two of the three lists are shared by every spatial table of the sheet and are built once. The third is this table's own dropdown, geographic or health facility, and the title and section labels are inserted above it.

Throws:

NameRanges

NameEndTable #

name-end-table

Mark the row under the table, which is what the next one measures from.

Signature:

Private Sub NameEndTable()

NameCategoryRows #

name-category-rows

Name the row category block and hand it back.

Signature:

Private Function NameCategoryRows() As Range

A table that opens a section, and every non-temporal table, owns its category block and names it. A temporal table continuing a section shares the labels the anchor wrote, so it derives the block from its own ENDTABLE_ marker instead and names nothing.

Returns: Range. The category block the value columns hang off.

Throws:

NameValueColumns #

name-value-columns

Name every value column and its label cell.

Signature:

Private Sub NameValueColumns(ByVal catRng As Range)

The label row sits one row above the values for every scope but the univariate one, where it sits on the same row. That answer is the same for every column of a table, so it is read once above the loop.

Parameters:

Throws:

NameTotalsAndMissing #

name-totals-and-missing

Name the Total, Missing and Percent rows and columns.

Signature:

Private Sub NameTotalsAndMissing()

Each of the three is found by its translated label, so a table that carries none of them names none of them. The range just named is kept and offset for the percentage twin instead of being resolved back by name.

NameIntersections #

name-intersections

Name the cells where a total or missing row meets such a column.

Signature:

Private Sub NameIntersections()

NameInteriorValues #

name-interior-values

Name the block of data cells, and the outer block a spatial table adds.

Signature:

Private Sub NameInteriorValues()

The two end columns are read as ranges and the block is built from them. Splitting an address on a colon reads the second half of a one-cell address that has no colon in it, and a value column is one cell tall whenever the table has one category row: one category and no missing row for a univariate table, or a geo count of one for a spatial one. This range is required -- Format and CrossTableFormula both read it, and twenty-odd names are already on the sheet by the time it is built, so a raise here left the table half-named.

NameColumnCategories #

name-column-categories

Name the header row of the table.

Signature:

Private Sub NameColumnCategories()

Format

FormatSectionAndTitle #

format-section-and-title

Format the section label and the table title.

Signature:

Private Sub FormatSectionAndTitle(ByVal desFormat As LLFormat, ByVal hn As HiddenNames)

The guard on the second arm is the table scope. ROWGS_SET, the name it used to read, belongs to the global summary and is written on one sheet, but this arm is reached by any table that does not start a section, on any of the four analysis sheets. Asking whether the name resolved meant the time series builder formatted a range on the normal sheet and then stamped ROWGS_FORMATSET, a worksheet-scoped flag, onto its own -- so the flag described a sheet it was not written on. Tightening RangeExists closes the same hole from the other side; this states the intent.

Parameters:

FormatSpatialDropdown #

format-spatial-dropdown

Format the geo or health facility dropdown of a spatial table.

Signature:

Private Sub FormatSpatialDropdown(ByVal desFormat As LLFormat)

Parameters:

FormatHeaders #

format-headers

Format the header row of the table.

Signature:

Private Sub FormatHeaders(ByVal desFormat As LLFormat, ByVal hn As HiddenNames)

Parameters:

FormatCategories #

format-categories

Format the category rows and columns, and merge the column labels.

Signature:

Private Sub FormatCategories(ByVal desFormat As LLFormat, ByVal hn As HiddenNames)

Parameters:

FormatInteriorValues #

format-interior-values

Format the block of data cells.

Signature:

Private Sub FormatInteriorValues(ByVal desFormat As LLFormat)

Parameters:

FormatBandsAndCells #

format-bands-and-cells

Format the total and missing bands, the single cells, the info cells

Signature:

Private Sub FormatBandsAndCells(ByVal desFormat As LLFormat)

Four walks over four constant prefix lists. Each entry used to cost two name resolutions, one to ask whether it existed and one to read it, and about half the entries of any list do not exist for the scope in hand. NamedRange answers both questions at once.

Parameters:

GroupToFormat #

group-to-format

Hold a range against the format it takes, to be drawn with its like

Signature:

Private Sub GroupToFormat(ByVal groups As Collection, ByVal rng As Range, _
                          ByVal scope As Byte, ByVal desFormat As LLFormat)

Formatting is what the analyses stage spends its time on, and the spending is per CALL, not per cell: one ApplyFormat writes about five properties through FormatRange and up to forty more through the two border walks, whether it is handed one cell or twenty. Measured on the measles build, this one pass cost 5.34s of the 17.5s the tables take, and 5.25s of that was the calls -- the same pass with the calls taken out and its twenty-eight name lookups left in ran in 0.09s.

So the ranges are held here and drawn a scope at a time, which turns about twenty calls per table into about eight. Excel formats each area of a range on its own -- TestLLFormat proves it frames every area of a union -- so a grouped call leaves the same table behind as the calls it replaces.

ORDER IS KEPT, AND THAT IS WHAT THE INTERSECT IS FOR

Two of these lists name the same cell: POPFACT_ and POPFACTLABEL_ are single cells AND hidden cells, and each info cell takes two scopes in a row. Where formats overlap, the last one written is the one seen, so a range landing on a held group of another scope draws everything held first and starts the queue again. A range that touches nothing simply joins its own scope's group.

Parameters:

DrawGroupedFormats #

draw-grouped-formats

Draw every held group and empty the queue

Signature:

Private Sub DrawGroupedFormats(ByVal groups As Collection, ByVal desFormat As LLFormat)

Parameters:

FormatPercentageColumns #

format-percentage-columns

Format every percentage column and the corner cells beside them.

Signature:

Private Sub FormatPercentageColumns(ByVal desFormat As LLFormat)

Parameters:

FormatWholeTable #

format-whole-table

Draw the outline of the table and the gap under it.

Signature:

Private Sub FormatWholeTable(ByVal desFormat As LLFormat)

Parameters:

HideUnrequestedTotals #

hide-unrequested-totals

Hide the total row and column of a temporal table that asked for neither.

Signature:

Private Sub HideUnrequestedTotals()

For time series and spatio-temporal tables HasTotal can be True while the user asked for nothing: percentage="row" needs the total column as its denominator, so the column has to exist and the user still has no reason to see it.

THE ROW IS THE OTHER HALF OF THE SAME ANSWER -- ISSUE #338

AddRows writes the Total and Missing labels of a temporal section unconditionally, because the row grid is a fixed NB_ROWS_TIME_SERIES block with exactly two footer rows and RowsCategoriesRange trims them BY COUNT. So the row cannot be dropped and it is hidden instead, the way the column is. Without this a table with "Add total" left empty still showed a Total row, which is what the field reported.

The rows belong to the section rather than to one table, so the anchor -- the table that wrote them -- is what hides them, and any later table of the same section that does ask for a total shows them again. Nothing hides a row a table has asked for, whatever order the section is built in. Every table of the section names the same sheet row through its own TOTAL_ROW_, so the name is always this table's.

Build

MarkPhase #

mark-phase

Record that a build phase has run.

Signature:

Private Sub MarkPhase(ByVal reached As Byte)

Parameters:

RequirePhase #

require-phase

Refuse a phase whose inputs have not been written yet.

Signature:

Private Sub RequirePhase(ByVal completed As Byte, ByVal caller As String)

The four phases have to run in order and nothing said so. AddHeader inserts rows and shifts the sheet, AddRows sets EndRow, AddColumns sets EndColumn and NumberOfColumns, and NameRanges needs all three. Calling them out of order used to give a table built at row zero or a name over an empty block, several frames from the mistake.

Parameters:

Throws:

Checkings

LogInfo #

Signature:

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

Diagnostic logging for cross-table building. Adds a trace entry to the internal Checking instance, initialising it lazily on first use.

Parameters:

Error Handling

ThrowError #

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:

Throws:

Used in (19 file(s))