TableSpecs

create #

Create a TableSpecs instance from a setup row

Signature:

Public Function Create(ByVal headerRng As Range, _
                       ByVal tableSpecsRng As Range, _
                       ByVal dict As ILLdictionary) As ITableSpecs

Factory method that creates a new TableSpecs instance representing one row from an analysis setup ListObject. Each row in the setup table defines a single analysis table (e.g. a univariate frequency table or a time series). The TableSpecs object wraps that row and exposes properties to query its scope, validity, navigation to neighboring rows, and display flags (total, missing, percentage, graph). Category lookups (RowCategories, ColumnCategories) are resolved later at call time by passing a LinelistSpecs object. Validation is performed immediately via CheckRequirements before the instance is populated. After property injection, InitCache scans the header and data row once to build column name and cell value caches, and eagerly resolves the table scope.

Parameters:

• headerRng: Range. The header row Range of the analysis setup ListObject. Column names in this range are used by Value() and ColumnIndex() to locate fields such as "row", "column", "total", "section", etc.
• tableSpecsRng: Range. A single data-row Range from the same ListObject. Must be below headerRng and have the same number of columns.
• dict: ILLdictionary. The linelist dictionary that provides variable definitions, existence checks, and control type lookups needed for validation and spatial type detection.

Returns: ITableSpecs. A fully initialised TableSpecs instance ready for use.

Throws:

• InvalidArgument When headerRng is Nothing.
• InvalidArgument When tableSpecsRng is Nothing.
• InvalidArgument When dict is Nothing.
• InvalidArgument When headerRng and tableSpecsRng have different column counts.
• InvalidArgument When headerRng.Row is not above tableSpecsRng.Row.
• InvalidArgument When headerRng.Row is less than 1.

---

init-cache #

Populate all eager caches from header and data ranges

Signature:

Public Sub InitCache()

One-time cache population called from the factory. Scans the header range once to build parallel BetterArray caches of column names and cell values (Tier 1 + Tier 3). Also eagerly resolves the table scope from the cell above the header (Tier 2). Must be called exactly once after HeaderRange, TableRange, and DictionaryObject have been assigned. Lazy caches (ValidTable, IsNewSection) are NOT populated here; they are computed on first access.

---

table-scope #

Analysis table scope for this specification row

Signature:

Public Property Get TableScope() As AnalysisTableScope

Type identification and identity properties for each table spec. Returns the cached AnalysisTableScope enum value. The scope is computed once eagerly during InitCache by reading the type label cell above the header range. On subsequent calls the cached Long value is returned directly without any worksheet access.

Returns: AnalysisTableScope. The table scope category.

---

table-id #

Unique identifier for this analysis table

Signature:

Public Property Get TableId() As String

Generates a unique string identifier by combining a short type-based prefix with the row offset from the header. The prefix is derived from the TableScope: "GS" for GlobalSummary, "UA" for Univariate, "BA" for Bivariate, "TS" for TimeSeries, "SA" for Spatial, and "SPT" for SpatioTemporal. The row offset is computed as TableRange.Row minus HeaderRange.Row, producing a stable integer that uniquely locates this row within its analysis block. The resulting format is "prefix_tabN", for example "UA_tab3".

Returns: String. The unique table identifier such as "GS_tab1" or "BA_tab5".

---

table-section-id #

Identifier of the first table in this section

Signature:

Public Property Get TableSectionId() As String

Returns the TableId of the first table in the current section group by walking backward through the Previous chain. If this table is itself the start of a new section (IsNewSection = True) or is a GlobalSummary table, it returns its own TableId immediately. Otherwise it delegates to Previous.TableSectionId, which continues walking backward until a section boundary is reached. All tables within the same section share a common section identifier.

Returns: String. The TableId of the first table in this section.

Remarks:

• This is a recursive property. For very long sections with many rows, it will traverse one row at a time via Previous. GlobalSummary tables short-circuit immediately because they are standalone.

---

dictionary #

Linelist dictionary bound to this specification

Signature:

Public Property Get Dictionary() As ILLdictionary

Returns the ILLdictionary reference stored at construction time. The dictionary provides variable existence checks, control type lookups, and spatial prefix detection used throughout validation and category resolution.

Returns: ILLdictionary. The linelist dictionary instance.

---

spatial-table-types #

Spatial analysis sub-type

Signature:

Public Property Get SpatialTableScopes() As String

Determines the spatial sub-type of a Spatial or SpatioTemporal table by checking whether the relevant variable has a health-facility or administrative geography prefix in the dictionary. For Spatial tables, the variable comes from the "row" field; for SpatioTemporal tables, the spatial variable is in the "column" field. The method prepends "hf_" or "adm1_" to the variable name and checks whether that prefixed variable exists in the dictionary. Returns "hf" for health facility, "geo" for administrative geography, or vbNullString if neither prefix is found.

Returns: String. The spatial sub-type ("hf", "geo", or empty string).

Remarks:

• This property is meaningful only for Spatial and SpatioTemporal table scopes. Calling it on other table scopes will still execute but will likely return vbNullString since those variables typically lack geo/hf prefixes.

---

valid-table #

Whether this table has a valid configuration

Signature:

Public Property Get ValidTable() As Boolean

Properties that validate table configuration and detect section boundaries. Returns the cached validation result. On first access, validates whether this table specification row contains all required fields and references valid variables for its analysis type. Each table scope has different validation rules: GlobalSummary requires non-empty "label" and "function" fields; Univariate requires the "row" variable to be a choice variable; Bivariate requires both "row" and "column" to be choice variables; TimeSeries requires the "row" variable to be of type "date"; Spatial requires a geographic prefix in the dictionary; SpatioTemporal requires a geographic prefix on the column variable and a date-type row variable. Invalid table specs are skipped during navigation (Previous, NextSpecs).

Returns: Boolean. True if the table specification meets all requirements for its type.

Depends on:

• LLVariables

---

is-new-section #

Whether this table begins a new section group

Signature:

Public Property Get IsNewSection() As Boolean

Returns the cached section-boundary result. On first access, determines whether this table specification row begins a new section within the analysis setup block. Sections group related tables together. The method checks the "section" column: if the current row's section value differs from the previous row's section value, or if this is the first data row (where the previous row is the header), the table is considered a new section. GlobalSummary tables are never considered new sections regardless of their section value, because each GlobalSummary table is standalone and does not participate in section grouping.

Returns: Boolean. True if this table starts a new section.

Remarks:

• The comparison uses the cell at row offset 0 (prevCell = tRng.Cells(0, sectIndex)), which in Excel Range addressing corresponds to the row immediately above the current row. Section values are compared with exact string equality (case-sensitive).

---

has-total #

Whether totals are needed for computation

Signature:

Public Property Get HasTotal() As Boolean

Properties controlling optional table features: totals, missing, percentage, and graphs. Determines whether a total row/column is needed for this table. The logic varies by table scope: GlobalSummary and SpatioTemporal always return False; Univariate and Bivariate always return True; TimeSeries returns True when the user explicitly requested total="yes" or when percentage is set to "row" or "column" (because percentage formulas need the total column as a denominator); Spatial returns True only when a column variable is specified. This is distinct from TotalRequested: HasTotal can be True even when the user did not ask for totals, solely because percentage needs them.

Returns: Boolean. True if a total row/column should be created.

---

total-requested #

Whether total was explicitly requested by the user

Signature:

Public Property Get TotalRequested() As Boolean

Returns True only when the user explicitly wrote "yes" in the "total" column of the setup row. This is the companion to HasTotal for the visibility logic: when HasTotal is True but TotalRequested is False, the total column is created for percentage computation but hidden from the user's view. If TotalRequested is True, the total column is both created and visible.

Returns: Boolean. True if the "total" field equals "yes".

Remarks:

• For Univariate and Bivariate tables, TotalRequested is not consulted because HasTotal is unconditionally True and totals are always shown. This property is primarily relevant for TimeSeries and Spatial types.

---

has-missing #

Whether missing-data rows or columns are included

Signature:

Public Property Get HasMissing() As Boolean

Determines whether a "missing" row or column should be included in the output table. The logic varies by table scope: GlobalSummary and SpatioTemporal always return False; Univariate returns True when missing="yes"; Bivariate returns True when missing="row", "column", or "all"; TimeSeries and Spatial return True when missing="yes" and a column variable is present (without a column variable, there is no categorical axis to show missing values on).

Returns: Boolean. True if missing data should be shown in the output table.

---

has-percentage #

Whether percentage display is enabled

Signature:

Public Property Get HasPercentage() As Boolean

Determines whether percentage values should be computed and displayed for this table. The logic varies by table scope: GlobalSummary and SpatioTemporal always return False; Univariate returns True when percentage="yes"; Bivariate returns True when percentage="row", "column", or "total"; TimeSeries returns True when percentage="row" or "column" and HasTotal is True; Spatial returns True when percentage="yes" and HasTotal is True. The interplay between HasPercentage and HasTotal for TimeSeries is intentional: requesting percentage implicitly forces HasTotal=True since percentage needs the total.

Returns: Boolean. True if percentage values should be included.

---

has-graph #

Whether a graph should be created for this table

Signature:

Public Property Get HasGraph() As Boolean

Determines whether a chart/graph should be generated alongside the output table. The logic varies by table scope: GlobalSummary always returns False; Univariate, TimeSeries, Spatial, and SpatioTemporal return True when graph="yes"; Bivariate returns True when graph="percentage", "values", or "both", letting the user choose whether the chart shows raw counts, percentages, or both. The actual graph rendering is handled downstream by the chart builder; this property only signals whether one should be created.

Returns: Boolean. True if a graph should be created for this table.

---

previous #

Previous valid specification in the same section

Signature:

Public Property Get Previous() As ITableSpecs

Linked-list navigation between sibling specifications. Returns the nearest valid table specification that precedes this one within the same section. Starting from the row immediately above the current table range, it walks backward one row at a time until it finds a valid table or encounters a section boundary (IsNewSection on the candidate). The walk stops at the header row to prevent reading outside the data area. This property is used by TableSectionId to recursively find the first table in a section.

Returns: ITableSpecs. The nearest valid preceding table in the same section.

Remarks:

• Callers must check IsNewSection before accessing Previous. Each candidate row examined creates a new TableSpecs instance, so performance degrades with many consecutive invalid rows.

Throws:

• InvalidArgument When IsNewSection is True, because there is no previous table in the same section for a section-starting table.

---

next-specs #

Next valid specification following an anchor range

Signature:

Public Property Get NextSpecs(ByVal anchorRng As Range) As ITableSpecs

Returns the next valid table specification after this row by walking forward through the setup ListObject. Starting from the row immediately below the current table range, it creates a TableSpecs for that row. If the row is invalid, it continues advancing one row at a time until it finds a valid table or passes the anchor range. Unlike Previous, NextSpecs does not stop at section boundaries. If no valid table is found within the data area, the property returns Nothing.

Parameters:

• anchorRng: Range. A Range representing the last row of the setup ListObject. The forward walk will not advance past this row.

Returns: ITableSpecs. The next valid table after this row, or Nothing if no valid table exists between this row and the anchor range.

Remarks:

• This property is the primary iteration mechanism for processing all tables in an analysis block. Callers typically start with the first table and call NextSpecs repeatedly until Nothing is returned.

---

value #

Retrieve a value from the specification row by column name

Signature:

Public Function Value(ByVal colName As String) As String

Value retrieval and category lookups from the specification row. Retrieves the cached cell value from this table specification row for a given column name. The column is located by scanning the cached column names array for a case-insensitive substring match (replicating the original Range.Find with xlPart and MatchCase:=False). Once found, the corresponding value is returned from the parallel cellValues cache without any worksheet access. If the column name does not exist in the header, the method returns vbNullString.

Parameters:

• colName: String. The column name to search for in the header (partial, case-insensitive).

Returns: String. The cell value, or vbNullString if the column is not found.

---

row-categories #

Row variable categories from the dictionary

Signature:

Public Property Get RowCategories(ByRef lData As Object) As BetterArray

Returns a BetterArray of category values for the row variable of this table. When lData is a LinelistSpecs instance, the categories are retrieved from it based on the variable name in the "row" field. When lData is any other type (or Nothing), returns an empty BetterArray. Internally delegates to CategoriesData("row", lData).

Parameters:

• lData: Object. A LinelistSpecs instance providing category lookups, or any other Object (categories will be empty).

Returns: BetterArray. The category strings for the row variable, or an empty BetterArray if the row variable is not found or lData is not LinelistSpecs.

---

column-categories #

Column variable categories from the dictionary

Signature:

Public Property Get ColumnCategories(ByRef lData As Object) As BetterArray

Returns a BetterArray of category values for the column variable of this table. When lData is a LinelistSpecs instance, the categories come from it based on the variable name in the "column" field. When lData is any other type (or Nothing), returns an empty BetterArray. For SpatioTemporal tables, the column categories are empty placeholder strings whose count is determined by the "n geo" field in the setup row (defaulting to 5). These placeholders are filled in later at runtime with actual geographic location names. Internally delegates to CategoriesData("column", lData).

Parameters:

• lData: Object. A LinelistSpecs instance providing category lookups, or any other Object (categories will be empty).

Returns: BetterArray. The category strings for the column variable. For SpatioTemporal, contains N empty strings. Returns an empty BetterArray if the column variable is not found or not specified.

---