TimeSeriesGraphs

The chart definitions of every time series graph, read from the three graph setup tables. One entry per graph identifier, each holding its series and its display title. Count answers how many graphs there are, Series gives the SeriesBuffer of one graph and Title its title. AnalysisOutput walks the entries, draws one chart each and puts the title in the cell above it.

THE THREE SETUP TABLES ARE IDENTIFIED BY NAME

The three names are byte-identical in the six workbooks measured on 2026-07-31, so this class compares names. An earlier version read a caption a fixed number of rows above each header row. That gap is four rows in .mock/setup_mock.xlsb, src/bin/setup/setup.xlsb and setup_dev.xlsb, and two rows in every file under releases/.

THE GRAPHS ARE BUILT ON THE FIRST READ

Count builds the entries the first time it is asked and holds the answer, and so do Series and Title. A Count of 0 says nothing could be drawn, and the reasons are in the report. HasCheckings is read after Count.

EVERY SKIP IS REPORTED

This class builds fewer series than the setup asked for in eight cases: a setup table with a column missing, a graph with no series row, a series row naming no series, a series identifier absent from the time series table, a series matching the header row, a choice value with no column on the output sheet, a column carrying no named range, and a graph identifier with no title row. A graph that ends with no series is left out and named.

Depends on: SeriesBuffer, TableSpecs, LinelistSpecs, Checking, BetterArray

Version: 1.0 (2026-07-31)

Instantiation

Create #

create

Create the time series graph builder from the three setup tables

Signature:

Public Function Create(ByVal loTable As BetterArray, _
                       ByVal outputSh As Worksheet, _
                       ByVal lData As LinelistSpecs) As TimeSeriesGraphs

The three ListObjects come in the order the validation checks: the graph setup table, which says which series belong to each graph identifier; the time series table, which maps a series identifier to its table specification row; and the titles table, which gives a display title per graph identifier.

Parameters:

loTable: BetterArray. Exactly 3 ListObject entries in order: [Tab_Graph_TimeSeries, Tab_TimeSeries_Analysis, Tab_Label_TSGraph]. Each must carry at least one data row.
outputSh: Worksheet. The worksheet the time series charts are drawn on, which is also where the column category ranges are read from.
lData: LinelistSpecs. The linelist specifications, read for the dictionary that TableSpecs needs.

Returns: TimeSeriesGraphs. A builder ready for Count.

Throws:

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

Reading

Count #

count

How many charts the setup tables ask for

Signature:

Public Property Get Count() As Long

Builds on the first read and keeps the answer. A graph that ended with no series is left out, so this counts the charts that can actually be drawn.

Returns: Long. The number of graphs.

Series #

series

The series of one graph

Signature:

Public Property Get Series(ByVal index As Long) As SeriesBuffer

Parameters:

index: Long. 1-based position, from 1 to Count.

Returns: SeriesBuffer. One entry per series, in plot order.

Throws:

ProjectError.InvalidArgument When index is outside the collection.

Title #

title

The display title of one graph

Signature:

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

Parameters:

index: Long. 1-based position, from 1 to Count.

Returns: String. The title, or an empty string when the graph has no title row.

Throws:

ProjectError.InvalidArgument When index is outside the collection.

Wksh #

wksh

The worksheet the charts are drawn on

Signature:

Public Property Get Wksh() As Worksheet

Returns: Worksheet. The output worksheet.

Checking

HasCheckings #

has-checkings

Whether anything was reported while the graphs were built

Signature:

Public Property Get HasCheckings() As Boolean

Returns: Boolean. True when at least one entry was filed.

CheckingValues #

checking-values

The report of this build

Signature:

Public Property Get CheckingValues() As Checking

Returns: Checking. The store, or Nothing when nothing was filed.

Internal members (not exported)

Instantiation

Seal #

seal

Seal the instance against further setup writes

Signature:

Public Sub Seal()

Called by the factory after the last setup write. Once sealed, the creation-only setters raise through ThrowError.

GuardNotSealed #

guard-not-sealed

Guard a setup setter against post-creation writes

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

Parameters:

propName: String. The property name reported in the error message.

LoList #

lo-list-set

Assign the three setup tables

Signature:

Public Property Set LoList(ByVal loTable As BetterArray)

Resolves the three tables into fields of their own. They are read inside loops, and each read used to go through the array again.

Parameters:

loTable: BetterArray. The 3-element ListObject array.

OutputSheet #

output-sheet-set

Assign the output worksheet

Signature:

Public Property Set OutputSheet(ByVal sh As Worksheet)

Parameters:

sh: Worksheet. The worksheet to store.

LinelistSpecifications #

linelist-specifications-set

Assign the linelist specifications

Signature:

Public Property Set LinelistSpecifications(ByVal lData As LinelistSpecs)

Parameters:

lData: LinelistSpecs. The linelist specifications to store.

Building

BuildGraphs #

build-graphs

Build one entry per graph identifier

Signature:

Private Sub BuildGraphs()

Walks the graph identifiers of the setup table in the order they first appear. A graph that ends with no series is reported and left out, because a chart with no series has no axes and Graphs.Format used to raise 1004 on one.

PushGraph #

push-graph

Append one finished graph to the collection

Signature:

Private Sub PushGraph(ByVal buffer As SeriesBuffer, ByVal graphTitle As String)

The buffer and the title are appended together, in this one routine, so the two lists cannot come apart.

Parameters:

buffer: SeriesBuffer. The series of the graph.
graphTitle: String. Its display title.

DefineGraph #

define-graph

Build the series of one graph identifier

Signature:

Private Function DefineGraph(ByVal graphId As String) As SeriesBuffer

Reads the six columns of the graph setup table for the rows carrying this identifier: the series identifiers, the axis sides, the percentage overrides, the chart types, the choice values and the display labels. Each series identifier is looked up in the time series table to get a TableSpecs, which gives the table identifier and its section. The choice value resolves to a column name through TimeSeriesColumnName, and that to a data range name through ResolveSeriesRangeName. A row asking for percentages takes the percentage twin of that range.

Parameters:

graphId: String. The graph identifier the rows are filtered by.

Returns: SeriesBuffer. The series of that graph, empty when none resolved.

Depends on:

TableSpecs

ResolveSeriesRangeName #

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

Three rules. The total answers TOTAL_COL_VALUES_ and the table identifier. A label name carrying the COLUMN_CATEGORIES_ prefix answers INTERIOR_VALUES_ and the table identifier. Everything else replaces LABEL with VALUES in the label name, so "LABEL_COL_2_xyz" becomes "VALUES_COL_2_xyz".

Parameters:

choiValue: String. The choice value from the graph setup table.
tabId: String. The table identifier every named range carries.
labName: String. The label range name TimeSeriesColumnName resolved.

Returns: String. The named range holding the data of the series.

TimeSeriesColumnName #

time-series-column-name

Find the named range of a choice value inside a time series table

Signature:

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

Looks the choice value up in the COLUMN_CATEGORIES_ range of the table on the output worksheet and answers the name of the cell that carries it. The total answers TOTAL_LABEL_COL_ and the table identifier directly. An empty answer means the series is skipped, and every route to one files an entry.

Parameters:

choiValue: String. The category value to look up.
tabId: String. The table identifier every named range carries.
graphId: String. The graph being built, named in the report entries.

Returns: String. The named range of the matching column, or an empty string.

Remarks:

The COLUMN_CATEGORIES_ read is guarded. The table row it belongs to may have been skipped during the table pass, and then the name was never created; reading it raised 1004 and took every remaining time series chart of the sheet with it, because the loop above has no handler.

SetupTables

GraphTitle #

graph-title

Look up the display title of a graph identifier

Signature:

Private Function GraphTitle(ByVal graphId As String) As String

Reading the three graph setup ListObjects.

Walks the identifier column of the titles table and answers the title cell of the matching row. Both columns are found by header name, and the identifier is matched without regard to case and outer spaces, because the two tables are typed by two hands and a case difference used to give a chart with an empty title and a navigation entry reading "Go to graph: ".

Parameters:

graphId: String. The graph identifier to look up.

Returns: String. The display title, or an empty string when there is no row.

BuildGraphIdsList #

build-graph-ids-list

Read the graph identifiers of the setup table

Signature:

Private Function BuildGraphIdsList() As BetterArray

Walks the identifier column, keeps every value it has not seen, and holds the order of first appearance.

Returns: BetterArray. The identifiers, LowerBound = 1. Empty when the table has no data row or no identifier column.

Remarks:

The duplicate test uses BetterArray.Includes, which scans. The graph setup table carries fewer than a hundred rows.

GraphValues #

graph-values

Read one column of the graph setup table, for one graph

Signature:

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

Answers the values of one column, for the rows whose graph identifier matches, in row order. The walk reads the identifier column and the wanted column of the same row, so the six lists DefineGraph reads always line up by index. An empty cell answers an empty string.

Parameters:

colName: String. Header of the column to read.
graphId: String. The graph identifier the rows are filtered by.

Returns: BetterArray. The values, LowerBound = 1. Empty when the table has no data row, no identifier column, or no column of that name.

Remarks:

This used to be driven by Range.Find with a search range narrowed on every iteration. Find wraps around by default, and none of the calls named LookIn, so the search inherited whatever the last search in the application used, the user's own Ctrl+F dialog included.

LoColumnIndex #

lo-column-index

Look up a column index in a ListObject header by name

Signature:

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

Walks the header row once. A header equal to the search term wins outright; a header that contains it is the fallback, which is what lets "axis" find "Y-Axis" and "type" find "Chart type". When more than one header carries the term the first is used and the ambiguity is reported, because a renamed or an added column would otherwise change what the chart plots with nothing said.

Parameters:

lo: ListObject. The ListObject whose header row is searched.
colName: String. The header text to search for. Matched without regard to case or outer spaces.

Returns: Long. The 1-based index relative to the ListObject, or -1 when no header carries the term.

Validation

ValidateListObjects #

validate-list-objects

Validate the three ListObjects the factory is given

Signature:

Private Sub ValidateListObjects(ByVal loTable As BetterArray)

Three levels. The array holds exactly three entries. Each entry is a ListObject carrying at least one data row. And each one is the table its position expects, which is settled by comparing its name against Tab_Graph_TimeSeries, Tab_TimeSeries_Analysis and Tab_Label_TSGraph.

Parameters:

loTable: BetterArray. The ListObject collection to validate.

Remarks:

The order used to be read off a caption two rows above the header row. That gap is two rows in a release file and four rows in every development file, so the check passed for one family and refused the other.

Throws:

ProjectError.InvalidArgument When loTable is Nothing, holds the wrong number of entries, holds something that is not a ListObject, or holds an empty one.
ProjectError.ErrorUnexpectedState When the ListObjects are in the wrong order.

ValidateIndex #

validate-index

Refuse a graph position the collection does not hold

Signature:

Private Sub ValidateIndex(ByVal index As Long)

Parameters:

index: Long. The position to test.

Throws:

ProjectError.InvalidArgument When index is outside the collection.

Checking

SpecsId #

specs-id

A name for this instance that no other instance shares

Signature:

Private Function SpecsId() As String

The report of everything this builder decided to leave out.

AnalysisOutput merges the entries of every collaborator into one report, and Checking.Add raises on a duplicate key, so a key made of a bare counter collides as soon as two instances file an entry. One instance serves one output worksheet, so the sheet names it.

Returns: String. The identifier every key of this instance carries.

LogInfo #

log-info

Record a diagnostic entry in the internal checking store

Signature:

Private Sub LogInfo(ByVal label As String, _
                    Optional ByVal scope As Byte = checkingNote)

Creates the Checking store on first use and appends the message under a key naming this class, this worksheet and a counter.

Parameters:

label: String. The message to record.
scope: Optional Byte. Severity from CheckingLogType. Defaults to checkingNote.

ErrorHandling

CellText #

cell-text

Read one cell value as text

Signature:

Private Function CellText(ByVal cellValue As Variant) As String

CStr on an Error variant raises runtime error 13, and the analysis setup sheet carries formula columns, so an ordinary edit can produce one. An error value, a Null and an empty cell all answer an empty string here.

Parameters:

cellValue: Variant. One value read out of a worksheet range.

Returns: String. The text of the cell, or an empty string.

IsTotalChoice #

is-total-choice

Whether a choice value asks for the total column

Signature:

Private Function IsTotalChoice(ByVal choiValue As String) As Boolean

The value comes out of a setup cell, so it is matched trimmed and without regard to case. It used to be compared against the literal "Total", which a cell holding " total " failed.

Parameters:

choiValue: String. The choice value read from the graph setup table.

Returns: Boolean. True when the value names the total.

IsPercentageChoice #

is-percentage-choice

Whether a graph setup row asks for percentages

Signature:

Private Function IsPercentageChoice(ByVal percValue As String) As Boolean

Parameters:

percValue: String. The "plot values or percentages" cell of the row.

Returns: Boolean. True when the row asks for percentages.

ThrowError #

throw-error

Raise a ProjectError-based exception

Signature:

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

Parameters:

errNumber: Long. The error code to raise.
message: String. Human-readable description of the failure.

Throws:

ProjectError. Always raises the specified error.

Used in (5 file(s))

AnalysisOutput.cls
GraphSeries.cls
TestGraphSeries.bas
TestSeriesBuffer.bas
TestTimeSeriesGraphs.bas