GraphSpecs

Builds and stores chart series specifications for both simple (non-time-series) and complex (time series) analysis graphs. In simple mode, the class reads chart configuration directly from an ICrossTable and produces flat parallel arrays of series metadata (names, types, positions, labels). In complex mode, it reads from three setup ListObjects to produce a nested BetterArray collection of graph entries, each containing its own set of parallel series arrays plus a display title. Consumers create an instance via Create (simple) or CreateRangeSpecs (complex), call CreateSeries (or let Valid trigger it lazily), then read series data via the IGraphSpecs interface.

Depends on: ICrossTable, ITableSpecs, TableSpecs, ILinelistSpecs, BetterArray, Table title constants used to validate listobject order, Simple mode: one cross-table, Complex mode: 3 listobjects + output sheet + linelist specs, Series data (simple mode), Graph collection (complex mode), Validity cache

Version: 1.0 (2026-02-09)

Instantiation

create #

Create a GraphSpecs instance in simple mode from a cross-table

Signature:

Public Function Create(ByVal tabl As ICrossTable) As IGraphSpecs

Factory methods and Self accessor for the predeclared instance pattern. Factory method that creates a GraphSpecs object in simple mode for non-time-series cross-tables. Simple mode is used for univariate, bivariate, spatial, and spatio-temporal analysis tables. This method explicitly rejects TimeSeries tables (which require CreateRangeSpecs) and GlobalSummary tables (which do not support graphing). The returned IGraphSpecs instance stores a reference to the cross-table and can later produce chart series definitions via CreateSeries.

Parameters:

  • tabl: ICrossTable. The fully-built cross-table whose named ranges contain the data to graph. Must not be Nothing, ScopeTimeSeries, or ScopeGlobalSummary.

Returns: IGraphSpecs. An instance configured in simple mode (GraphTypeSimple) ready for series creation via CreateSeries.

Remarks:

  • After calling Create, you must call CreateSeries before accessing any series properties (SeriesName, SeriesType, etc.). The cross-table must already have its named ranges written to the worksheet before series data can be consumed.

Throws:

  • InvalidArgument When tabl is Nothing, ScopeTimeSeries, or ScopeGlobalSummary.

create-range-specs #

Create a GraphSpecs instance in complex mode for time series graphs

Signature:

Public Function CreateRangeSpecs(ByVal loTable As BetterArray, _
                                 ByVal outputSh As Worksheet, _
                                 ByVal lData As ILinelistSpecs) As IGraphSpecs

Factory method that creates a GraphSpecs object in complex mode for time series graphs. Complex mode requires exactly 3 ListObjects passed as a BetterArray: (1) the graph setup table defining series per graph ID with types, axis positions, and choice values; (2) the time series setup table mapping series IDs to table specifications; (3) the title labels table providing display titles per graph ID. All three ListObjects are validated via ValidateListObjects before construction. Complex mode produces a nested BetterArray collection of graph spec entries (one per unique graph ID) when CreateSeries is called later.

Parameters:

  • loTable: BetterArray. Exactly 3 ListObject entries in order: [graphLo, timeSeriesLo, titleLo]. Each must be a non-empty ListObject with the correct title label in its header area.
  • outputSh: Worksheet. The worksheet where time series graphs will be drawn.
  • lData: ILinelistSpecs. Linelist-level metadata (section names, sheet info) needed to resolve table specifications during series building.

Returns: IGraphSpecs. An instance configured in complex mode (GraphTypeComplex) ready for series creation via CreateSeries.

Remarks:

  • The 3 ListObjects must appear in the exact order validated by ValidateListObjects: graph table first, time series table second, title labels table third.

Throws:

  • InvalidArgument When loTable fails validation, lData is Nothing, or outputSh is Nothing.
  • ErrorUnexpectedState When ListObjects are in the wrong order.

CoreOperations

create-series #

Build all series from the stored specifications

Signature:

Public Sub CreateSeries()

Entry point for building all chart series definitions. Dispatches series building to the appropriate handler based on the current GraphType. For simple mode (GraphTypeSimple), delegates to BuildSimpleSeries which reads chart configuration directly from the cross-table's named ranges. For complex mode (GraphTypeComplex), delegates to BuildComplexSeries which iterates over unique graph IDs from the setup ListObjects and builds a nested BetterArray collection of graph spec entries. Must be called explicitly before accessing any series data properties or the SpecsLists collection.

Remarks:

  • Also called internally by the Valid property if the validity cache has not yet been populated. Calling it multiple times is safe but will rebuild all series data from scratch each time.

Internal members (not exported)

Instantiation

self #

Current object instance

Signature:

Public Property Get Self() As IGraphSpecs

Convenience accessor so consuming code can fluently retrieve the interface reference from the predeclared Create or CreateRangeSpecs method.

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


InternalSetters

table #

Cross-table reference for simple mode

Signature:

Public Property Get Table() As ICrossTable

Property accessors used only during factory construction. These are Public to support the With New pattern but are not part of the caller contract.

Returns: ICrossTable. The stored cross-table instance.


table-set #

Assign the cross-table reference

Signature:

Public Property Set Table(ByVal tabl As ICrossTable)

Parameters:


graph-type #

Current graph specifications mode

Signature:

Public Property Get GraphType() As GraphSpecsType

Returns: GraphSpecsType. GraphTypeSimple or GraphTypeComplex.


graph-type-set #

Assign the graph specifications mode

Signature:

Public Property Let GraphType(ByVal specType As GraphSpecsType)

Parameters:


lo-list #

ListObject collection for complex mode

Signature:

Public Property Get LoList() As BetterArray

Returns: BetterArray. The stored ListObject array.


lo-list-set #

Assign the ListObject collection

Signature:

Public Property Set LoList(ByVal loTable As BetterArray)

Parameters:


output-sheet #

Output worksheet for complex mode

Signature:

Public Property Get OutputSheet() As Worksheet

Returns: Worksheet. The stored output worksheet reference.


output-sheet-set #

Assign the output worksheet

Signature:

Public Property Set OutputSheet(ByVal sh As Worksheet)

Parameters:


linelist-specifications #

Linelist specifications for complex mode

Signature:

Public Property Get LinelistSpecifications() As ILinelistSpecs

Returns: ILinelistSpecs. The stored linelist specifications instance.


linelist-specifications-set #

Assign the linelist specifications

Signature:

Public Property Set LinelistSpecifications(ByVal lData As ILinelistSpecs)

Parameters:


SimpleMode

build-simple-series #

Build chart series for a non-time-series cross-table

Signature:

Private Sub BuildSimpleSeries()

Series building logic for non-time-series cross-tables. Reads the table scope and configuration from the cross-table's ITableSpecs to determine what kind of series to generate. The "flip" setting controls plot direction: when flip is "yes", horizontal bars ("hbar") are used; otherwise vertical bars ("bar") are the default. For spatio-temporal tables, the number of columns is overridden by the "n geo" setting (defaulting to 5 if not specified), and the section-level table ID (tabSecId) is captured for use as row category ranges. After initializing fresh series arrays, dispatches to BuildUnivariateSeries for ScopeUnivariate tables, or BuildMultiColumnSeries for ScopeBivariate, ScopeSpatial, and ScopeSpatioTemporal tables.

Remarks:


build-univariate-series #

Build chart series for a single-variable cross-table

Signature:

Private Sub BuildUnivariateSeries(ByVal tabId As String, _
                                   ByVal plotType As String, _
                                   ByVal hasPerc As Boolean)

Pushes one bar series referencing the VALUES_COL_1_ named range for the primary data, paired with the ROW_CATEGORIES_ range for axis labels and LABEL_COL_1_ for the legend entry. If the table has a percentage column and the plot direction is vertical bars (plotType = "bar"), an additional point series is pushed referencing PERC_COL_1_ on the right axis, allowing a percentage overlay on top of the value bars. Horizontal bars ("hbar") do not get the percentage overlay because point series on a horizontal chart do not render well.

Parameters:

Remarks:


build-multi-column-series #

Build chart series for multi-column cross-tables

Signature:

Private Sub BuildMultiColumnSeries(ByVal specs As ITableSpecs, _
                                    ByVal tabId As String, _
                                    ByVal tabSecId As String, _
                                    ByVal tabType As Byte, _
                                    ByVal nbCols As Long, _
                                    ByVal plotType As String)

Builds series for bivariate, spatial, or spatio-temporal tables. The graph mode is determined by the "graph" setting in the table specs: (1) "values" or "yes" pushes one bar series per column referencing VALUES_COL__ named ranges on the left axis; (2) "percentages" pushes one bar series per column referencing PERC_COL__ ranges (only if HasPercentage is True); (3) "both" pushes value bars on the left axis AND percentage points on the right axis for each column, creating a dual-axis overlay chart. For spatio-temporal tables, the row categories use the section-level range (ROW_CATEGORIES_ + tabSecId) instead of the table-level range, because spatio-temporal tables share row categories across all tables in the same section.

Parameters:

Remarks:


ComplexMode

build-complex-series #

Build the complete set of graph specifications for complex mode

Signature:

Private Sub BuildComplexSeries()

Time series graph building from setup ListObjects. Iterates over all unique graph IDs extracted by BuildGraphIdsList. For each graph ID, calls DefineGraphSpecs to populate the series arrays with that graph's series definitions, then PushGraph to package those arrays into a 7-element BetterArray entry appended to the graphsChartSpecs collection. The resulting collection can be retrieved via SpecsLists after completion.

Remarks:


define-graph-specs #

Define chart series for a single graph ID in complex mode

Signature:

Private Sub DefineGraphSpecs(ByVal graphId As String)

Reads all column values for the given graphId from the graph ListObject using GraphValues: series IDs, axis positions, percentage overrides, chart types, choice values, and display labels. Clears any previously buffered series data via ClearSeries. For each series ID, looks up the corresponding row in the time series ListObject to obtain a TableSpecs instance, which provides the table ID and section ID. The choice value is resolved to a named range name via TimeSeriesColumnName, then to the actual data range name via ResolveSeriesRangeName. If the "percentages" column indicates a percentage override, the resolved range name is replaced with its PERC_ equivalent. Each valid series is pushed via PushSeries and PushLabels, using the section-level ROW_CATEGORIES_ range for row labels.

Parameters:

Remarks:

Depends on:


resolve-series-range-name #

Resolve a choice value and label name into the data range name

Signature:

Private Function ResolveSeriesRangeName(ByVal choiValue As String, _
                                         ByVal tabId As String, _
                                         ByVal labName As String) As String

Maps a choice value to the correct data range name for chart series values using three rules: (1) If the choice value is "Total", returns TOTAL_COL_VALUES_ + tabId. (2) If the label name contains the prefix "COLUMN_CATEGORIES_", returns INTERIOR_VALUES_ + tabId. (3) For all other cases, replaces the LABEL prefix in the label name with VALUES (e.g., "LABEL_COL_2_xyz" becomes "VALUES_COL_2_xyz").

Parameters:

Returns: String. The fully-qualified named range name for the data values.


time-series-column-name #

Find the named range for a choice value within a time series table

Signature:

Private Function TimeSeriesColumnName(ByVal choiValue As String, _
                                       ByVal tabId As String) As String

Looks up a category label (e.g., a geographic area or demographic group) in the COLUMN_CATEGORIES_ range of the table on the output worksheet. If the choice value is "Total", returns the predefined TOTAL_LABEL_COL_ + tabId range name directly. For all other values, searches the COLUMN_CATEGORIES_ range for an exact match and returns the .Name.Name of the found cell. Returns an empty string if the choice value is empty or not found.

Parameters:

Returns: String. The full named range name string for the matching column, or vbNullString if no match is found.

Remarks:


SeriesDataManagement

init-series-arrays #

Create fresh BetterArray instances for all six series arrays

Signature:

Private Sub InitSeriesArrays()

Parallel array management for series names, types, positions, and labels. Creates six fresh BetterArray instances (all with LowerBound=1) to store the parallel series data arrays: seriesNames, seriesTypes, seriesPositions, seriesLabels, seriesColumnLabels, and seriesLabelPrefixes. These six arrays form matched parallel arrays where index N across all six describes one complete series entry. LowerBound is set to 1 to align with the 1-based indexing used throughout GraphSpecs and consumer code.

Remarks:


push-series #

Append one series entry to the series-related parallel arrays

Signature:

Private Sub PushSeries(ByVal chrtName As String, _
                        ByVal chrtType As String, _
                        ByVal chrtPos As String)

Appends one entry to seriesNames, seriesTypes, and seriesPositions. The series name is typically an Excel named range reference (e.g., "VALUES_COL_1_table1") that the chart builder uses as the data source. If seriesNames is Nothing (which occurs in complex mode after ClearSeries resets the arrays), this method lazy-initializes all six arrays via InitSeriesArrays before pushing.

Parameters:

Remarks:


push-labels #

Append one label entry to the label-related parallel arrays

Signature:

Private Sub PushLabels(ByVal chrtRowCategory As String, _
                        ByVal chrtColumnLab As String, _
                        Optional ByVal chrtLabPref As String = vbNullString)

Appends one entry to seriesLabels, seriesColumnLabels, and seriesLabelPrefixes. The row category is the named range containing category axis labels. The column label is the named range for the series legend entry. The optional prefix is a display string prepended to the series label in the chart legend, used in complex mode where a user-defined label augments the default column label. If seriesLabels is Nothing, lazy-initializes all six arrays via InitSeriesArrays.

Parameters:


clear-series #

Clear all six parallel series and label arrays

Signature:

Private Sub ClearSeries()

Calls .Clear on each BetterArray instance to reset them between complex mode graph IDs inside DefineGraphSpecs. After clearing, the arrays remain as instantiated (non-Nothing) objects with zero length. Uses On Error Resume Next because during the first call the arrays may still be Nothing.

Remarks:


push-graph #

Package buffered series data into a graph specification entry

Signature:

Private Sub PushGraph(ByVal graphId As String)

Packages the currently-buffered series data into a single graph specification entry and appends it to the graphsChartSpecs collection. Each entry is a BetterArray containing exactly 7 elements: (1) seriesNames clone, (2) seriesTypes clone, (3) seriesPositions clone, (4) seriesLabels clone, (5) seriesColumnLabels clone, (6) seriesLabelPrefixes clone, (7) the graph title string from GraphTitle. All BetterArray elements are cloned to ensure independence from the mutable series buffers. The graphsChartSpecs collection is lazy-initialized on first call.

Parameters:

Remarks:


ListObjectHelpers

graph-list-object #

Graph setup ListObject from the LoList collection

Signature:

Private Property Get GraphListObject() As ListObject

ListObject accessors and lookup utilities for complex mode. Returns the first ListObject in the LoList array, which is the graph setup table. This ListObject defines which series belong to each graph ID, including their chart types, axis positions, choice values, percentage overrides, and display labels.

Returns: ListObject. The graph setup table ("graph on time series").


time-series-list-object #

Time series setup ListObject from the LoList collection

Signature:

Private Property Get TimeSeriesListObject() As ListObject

Returns the second ListObject in the LoList array, which is the time series setup table. This ListObject maps each series ID to its full table specification row (variable name, section, time unit, etc.), enabling DefineGraphSpecs to construct a TableSpecs instance for each series.

Returns: ListObject. The time series definition table ("time series analysis").


titles-list-object #

Title labels ListObject from the LoList collection

Signature:

Private Property Get TitlesListObject() As ListObject

Returns the third ListObject in the LoList array, which provides display titles for each graph. Keyed by graph ID in column 3, with the title text in column 1. Used by GraphTitle to look up human-readable plot titles.

Returns: ListObject. The title labels table ("labels for time series graphs").


graph-title #

Look up the display title for a graph ID

Signature:

Private Function GraphTitle(ByVal graphId As String) As String

Searches the titles ListObject's third column for an exact, case-sensitive match of the graphId string. If found, returns the title text from the same row's first column (offset -2). If not found, returns an empty string.

Parameters:

Returns: String. The display title, or vbNullString if not found.


build-graph-ids-list #

Extract unique graph IDs from the graph setup ListObject

Signature:

Private Function BuildGraphIdsList() As BetterArray

Iterates through every data row in the graph ID column (skipping the header), checks whether each cell is non-empty and not already present in the accumulator array (using BetterArray.Includes for duplicate detection), and pushes new unique values. The resulting list preserves the order of first appearance.

Returns: BetterArray. A cloned array of unique graph ID strings with LowerBound=1.

Remarks:


graph-values #

Extract filtered column values from the graph setup ListObject

Signature:

Private Function GraphValues(ByVal colName As String, _
                              ByVal graphId As String) As BetterArray

Returns all values from a specified column, filtered to rows whose "graph id" matches the given graphId. Walks through the "graph id" column progressively: after finding each matching row, narrows the search range to start after the found row, preventing re-matches. Empty cell values are replaced with "&" as a placeholder to maintain array index alignment. Returns an empty BetterArray if the graphId is not found at all.

Parameters:

Returns: BetterArray. A cloned array (LowerBound=1) of extracted cell values. Empty cells are represented as "&".

Remarks:

Throws:


lo-column-index #

Look up a column index in a ListObject header by partial match

Signature:

Private Function LoColumnIndex(ByVal lo As ListObject, _
                                ByVal colName As String) As Long

Searches the HeaderRowRange for a partial, case-insensitive match of the given column name using Range.Find with LookAt:=xlPart. The returned index is relative to the ListObject (not the worksheet), calculated as the found cell's absolute column minus the header range's starting column plus 1. Returns -1 if the column name is not found in the header.

Parameters:

Returns: Long. The 1-based relative column index, or -1 if not found.

Remarks:


Validation

validate-list-objects #

Validate the BetterArray of ListObjects required by CreateRangeSpecs

Signature:

Private Sub ValidateListObjects(ByVal loTable As BetterArray)

ListObject validation for complex mode construction. Performs three levels of validation: (1) Count validation -- the array must contain exactly 3 elements. (2) Type and content validation -- each element must be a non-Nothing ListObject with a non-Nothing DataBodyRange. (3) Order validation -- the ListObjects must appear in the correct order, verified by reading the title label from 2 rows above each ListObject's header and comparing against the module-level constants TAB_ON_GRAPH_TIME_SERIES, TAB_ON_TIME_SERIES, and TAB_ON_TITLE_GRAPHS_TIME_SERIES.

Parameters:

Remarks:

Throws:


Properties

wksh #

Output worksheet where charts will be drawn

Signature:

Public Property Get Wksh() As Worksheet

Public accessors for series data, validity, and worksheet reference. Dispatches based on the current graph mode. In simple mode, returns Table.Wksh -- the worksheet containing the cross-table's named ranges. In complex mode, returns OutputSheet -- a dedicated output worksheet provided during factory construction. This abstraction allows consumers to call Wksh without knowing the mode.

Returns: Worksheet. The target worksheet for chart creation.


number-of-series #

Number of chart series in simple mode

Signature:

Public Property Get NumberOfSeries() As Long

Returns the length of the seriesNames BetterArray after CreateSeries has been called. Returns 0 in complex mode (GraphTypeComplex), because complex mode stores series data within the nested graphsChartSpecs collection. Also returns 0 if seriesNames is Nothing (CreateSeries not yet called).

Returns: Long. Count of series entries in simple mode, or 0.


number-of-graphs #

Number of distinct graphs in complex mode

Signature:

Public Property Get NumberOfGraphs() As Long

Each graph corresponds to one unique graph ID from the graph setup ListObject and has its own entry in the graphsChartSpecs collection. Returns 0 in simple mode (GraphTypeSimple) or if graphsChartSpecs is Nothing (CreateSeries not yet called or produced no valid graphs).

Returns: Long. Count of graph entries in complex mode, or 0.


series-name #

Named range reference for series data at a given index

Signature:

Public Property Get SeriesName(ByVal index As Long) As String

Returns the Excel named range string (e.g., "VALUES_COL_1_table1") that a chart builder uses as the data source for this series. Only valid in simple mode after CreateSeries has been called.

Parameters:

Returns: String. Named range reference for the series data.

Throws:


series-type #

Chart rendering type for a series at a given index

Signature:

Public Property Get SeriesType(ByVal index As Long) As String

Returns the chart type string controlling how the series is rendered. Common values include "bar" (vertical bars), "hbar" (horizontal bars), "point" (scatter for percentage overlays), and "line". Only valid in simple mode.

Parameters:

Returns: String. Chart rendering type.

Throws:


series-pos #

Axis position for a series at a given index

Signature:

Public Property Get SeriesPos(ByVal index As Long) As String

Returns "left" for the primary value axis or "right" for the secondary axis, typically used for percentage overlays. Only valid in simple mode.

Parameters:

Returns: String. Axis placement ("left" or "right").

Throws:


series-label #

Row category named range for a series at a given index

Signature:

Public Property Get SeriesLabel(ByVal index As Long) As String

Returns the named range providing category axis labels (e.g., "ROW_CATEGORIES_table1") displayed along the chart axis. Only valid in simple mode after CreateSeries has been called.

Parameters:

Returns: String. Named range for row category labels.

Throws:


series-column-label #

Column label named range for a series at a given index

Signature:

Public Property Get SeriesColumnLabel(ByVal index As Long) As String

Returns the named range providing the legend entry text (e.g., "LABEL_COL_2_table1") that identifies this series in the chart legend. Only valid in simple mode after CreateSeries has been called.

Parameters:

Returns: String. Named range for the legend entry.

Throws:


series-label-prefix #

Label prefix for a series at a given index

Signature:

Public Property Get SeriesLabelPrefix(ByVal index As Long) As String

Returns the optional display prefix prepended to the series legend entry. In simple mode this is always vbNullString. In complex mode (when accessed via SpecsLists), this contains the user-defined "label" value from the graph setup ListObject. Only valid in simple mode after CreateSeries has been called.

Parameters:

Returns: String. Prefix string or vbNullString when none is set.

Throws:


valid #

Whether the graph specifications were successfully built

Signature:

Public Property Get Valid() As Boolean

The result is cached after the first evaluation using the validityTested and validityResult flags. On the first call, invokes CreateSeries to build all series data, then determines validity: in simple mode always True (because BuildSimpleSeries always succeeds if the factory accepted the table); in complex mode True only if graphsChartSpecs was successfully created (at least one graph ID produced valid series definitions).

Returns: Boolean. True if series data is available for consumption.

Remarks:


specs-lists #

Nested collection of all graph specifications

Signature:

Public Property Get SpecsLists() As BetterArray

Returns a cloned copy of the graphsChartSpecs collection. Each element is a 7-element BetterArray representing one graph: [seriesNames, seriesTypes, seriesPositions, seriesLabels, seriesColumnLabels, seriesLabelPrefixes, titleString]. The clone ensures consumers cannot mutate internal state. Returns a fresh empty BetterArray if graphsChartSpecs is Nothing.

Returns: BetterArray. Cloned collection of graph spec entries, or empty.

Remarks:


graph-ids #

Unique graph identifiers for complex mode

Signature:

Public Property Get GraphIDs() As BetterArray

Returns a cloned list of all unique graph IDs, lazily built on first access by calling BuildGraphIdsList if the internal graphIdsList is Nothing. If CreateSeries has already been called, the cached list is reused. Each element is a string graph ID in the order of first appearance in the graph setup ListObject.

Returns: BetterArray. Cloned list of unique graph ID strings.

Remarks:


ErrorHandling

validate-series-index #

Validate that a series index is within bounds

Signature:

Private Sub ValidateSeriesIndex(ByVal index As Long)

Index validation and error raising helpers.

Parameters:

Throws:


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:


InterfaceImplementation

IGraphSpecs_NumberOfSeries #

Signature:

Private Property Get IGraphSpecs_NumberOfSeries() As Long

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


Used in (4 file(s))