Graphs

Wraps one Excel ChartObject on an analysis worksheet. Add draws the chart at a caller-supplied Range, AddSeries queues one data series from a named range, AddLabels gives that series its category and legend labels, and Format draws the queue and applies the look the GraphScope calls for. Entries filed along the way leave through HasCheckings and CheckingValues.

THE PHASE ORDER

Add, then AddSeries and AddLabels once per series, then Format. All four callers drive it that way: AnalysisOutput builds a chart per table and a chart per time series graph, and AnaTabIds replays both from its tracking tables when a linelist is exported.

THE SERIES ARE QUEUED AND DRAWN IN ONE CALL

AddSeries and AddLabels write to a queue and leave the chart alone. CommitSeries unions the queued ranges and hands the chart one SetSourceData, then walks the series it made to give each its type, its axis and its labels. Format calls CommitSeries; a caller that reads the chart before it formats calls it itself.

A SERIES THAT FAILS TAKES ITS LABELS WITH IT

AddSeries answers a missing named range by filing a warning and queueing no series. The labels that follow belong to that series, so AddLabels reads the flag AddSeries leaves and skips them.

EVERY SIZE THIS CLASS APPLIES COMES FROM ONE FUNCTION

SizeForScope holds the whole policy: the width coefficient that stretches a time series chart, the height coefficient that grows a spatial one, the plot area box and the legend position. It is arithmetic over two numbers, so a test reaches it through Format with no chart to automate.

Depends on: Checking

Version: 1.1 (2026-07-31)

Instantiation

Create #

create

Create a Graph object bound to a worksheet and position

Signature:

Public Function Create(ByVal sh As Worksheet, ByVal posRng As Range, _
                       Optional ByVal grName As String = vbNullString) As Graphs

Graph objects simplify the manipulation of Excel graphs. They are based on a basic graphic grammar with the ability to stack graphs on top of each other. At its core, a graph object is created with a worksheet and a range where to write the graph. Values to be plotted should be in a named range on the worksheet used to instantiate the class.

The position range has to sit on the worksheet the values come from. The class draws the chart on the parent of the range and reads every named range off the worksheet, so two different sheets would draw a chart in one place and feed it from another, with the legend references qualified by the wrong sheet name.

Parameters:

  • sh: Worksheet. The worksheet containing the values to be plotted.
  • posRng: Range. The cell or range determining where to position the graph.
  • grName: Optional String. Display name for the graph. Defaults to vbNullString.

Returns: Graphs. A ready-to-use graph instance.

Throws:

  • ProjectError.ObjectNotInitialized When sh is Nothing.
  • ProjectError.ObjectNotInitialized When posRng is Nothing.
  • ProjectError.InvalidArgument When posRng sits on another worksheet.

Depends on:

  • Checking
Operations

Add #

add

Create an empty chart on the worksheet

Signature:

Public Sub Add()

Chart creation, series attachment, labelling, and formatting.

Creates an empty graph on a worksheet, ready for data to be attached to the graph. The graph is empty at creation, so the legend and the plot area are left to Format, which owns both and runs once the series exist.

The frame comes from Shapes.AddChart2 rather than ChartObjects.Add. AddChart2 builds the chart in one host call where the older one builds a frame and then takes its style, its type and its plot area in several. It plots whatever range is selected when it runs, so the series it invents are deleted here and the chart starts empty either way.

AddSeries #

addseries

Attach a data series from a named range

Signature:

Public Sub AddSeries(ByVal rngName As String, ByVal chrtType As String, _
                    Optional ByVal axisPos As String = vbNullString)

One graph can have multiple series added. Series are referenced using named ranges on the graph object worksheet. If the named range does not exist, the method files a warning, queues no series, and marks the attempt as failed so the labels that follow are skipped too. If no chart exists yet, Add is called lazily before the first series is queued.

The series is queued, not drawn. CommitSeries hands the whole queue to the chart in one SetSourceData call, and Format calls it. A caller that wants to read the chart before it formats calls CommitSeries itself.

Parameters:

  • rngName: String. Named range containing the series values.
  • chrtType: String. Chart type: "line", "point", "bar", or "hbar". Defaults to "bar" for unknown values.
  • axisPos: Optional String. Axis position: "right" adds a secondary axis; any other value uses the primary axis. Defaults to vbNullString.

CommitSeries #

commit-series

Draw every queued series in one call to the chart

Signature:

Public Sub CommitSeries()

The queued ranges are the columns of one analysis table, so their union is a single Source Excel splits back into the same series, in the same order. One SetSourceData replaces the two data writes each series used to cost: the older path added a series from its own Source and then wrote the values over it, because SeriesCollection.Add guesses how to split a Source whose cells are mostly empty and a column with data in its last row alone came out as a one-cell series.

Excel still does the splitting here, so the count it made is checked against the count that was asked for. When the two differ, or SetSourceData refuses the Source outright, the series are attached one at a time and the values pinned range by range. The look of a series -- its type, its axis, its labels -- is applied afterwards either way.

Format calls this. A caller that reads the chart before it formats calls it itself. Calling it twice costs nothing: the queue is empty the second time.

Layout

HeightInRows #

height-in-rows

How many worksheet rows a chart of this scope covers

Signature:

Public Function HeightInRows(ByVal sh As Worksheet, ByVal scope As Byte, _
                             ByVal heightFactor As Long) As Long

A caller that draws its charts after its tables still has to leave room for them while the tables are placed, and the chart is not there to be measured yet. This answers the height the chart WILL have, in rows of the sheet it will sit on, from the same SizeForScope policy Format applies.

The scope is sanitised here rather than through KnownScope, because that one files a checking and this is called on the predeclared instance, which owns no report. An unknown scope takes the standard layout, the same answer.

The row height comes from the sheet. Analysis output sheets are left at the standard height -- the row-height rules in LLFormat belong to the HList print and pivot sheets -- so a division answers the same row a drawn chart would reach. One row is added rather than rounding, so the reservation is never short.

Parameters:

  • sh: Worksheet. The sheet the chart will be drawn on.
  • scope: Byte. A GraphScope value.
  • heightFactor: Long. The geographic unit count, 1 or more.

Returns: Long. The number of rows to reserve, or 0 when sh is Nothing.

Checking

HasCheckings #

has-checkings

Determine whether diagnostics have been captured

Signature:

Public Property Get HasCheckings() As Boolean

Returns True when at least one LogInfo call has been made, indicating that diagnostic information is available.

Returns: Boolean. True when internal checks exist.

CheckingValues #

checking-values

Retrieve the collected checking entries

Signature:

Public Property Get CheckingValues() As Checking

Returns the internal Checking store when at least one entry has been recorded. Returns Nothing otherwise.

Returns: Object. The Checking instance with logged diagnostics.

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:

Wksh #

wksh

Host worksheet for the graph

Signature:

Public Property Get Wksh() As Worksheet

Returns the worksheet reference stored during creation. All named ranges for series data reside on this worksheet.

Returns: Worksheet. The worksheet hosting the chart.

Wksh #

wksh-set

Assign the host worksheet

Signature:

Public Property Set Wksh(ByVal sh As Worksheet)

Parameters:

StartRange #

start-range

Starting cell or range for the graph position

Signature:

Public Property Get StartRange() As Range

Returns the Range that determines the top-left anchor point of the chart on the worksheet.

Returns: Range. The positioning range.

StartRange #

start-range-set

Assign the starting position range

Signature:

Public Property Set StartRange(ByVal strtRng As Range)

Parameters:

Name #

name-set

Assign the graph display name

Signature:

Public Property Let Name(ByVal grName As String)

Parameters:

Name #

name

Retrieve the graph display name

Signature:

Public Property Get Name() As String

Returns: String. The graph name.

GraphElements

graphChart #

graph-chart

Retrieve the internal ChartObject

Signature:

Private Property Get graphChart() As ChartObject

Internal chart object state.

Returns the ChartObject created by Add. Nothing until Add is called, and Nothing again after Format removes an empty frame.

Returns: ChartObject. The underlying Excel chart object.

Operations

PendingSeries #

pending-series

The queue of series waiting to be drawn

Signature:

Private Function PendingSeries() As Collection

Returns: Collection. The queue, created empty on first use.

ChartTypeOf #

chart-type-of

The Excel chart type one series name stands for

Signature:

Private Function ChartTypeOf(ByVal chrtType As String) As XlChartType

Parameters:

Returns: XlChartType. The matching type, clustered columns for anything else.

AddLabels #

addlabels

Assign category and legend labels to the current series

Signature:

Public Sub AddLabels(ByVal catName As String, _
                     ByVal lblName As String, _
                     Optional ByVal prefix As String = vbNullString, _
                     Optional ByVal prefixOnly As Boolean = False, _
                     Optional ByVal hardCodeLabels As Boolean = True)

Sets the category axis values and legend entry for the most recently added series. The optional prefix is prepended to the legend name, separated by " - " unless prefixOnly is True. When hardCodeLabels is False, the legend entry references the named range dynamically rather than using a literal string.

The labels belong to the series added just before them, so a failed AddSeries skips them. Writing them anyway put series n's category axis and legend entry onto series n-1.

Parameters:

InSheetOrder #

in-sheet-order

Whether the queued ranges run left to right down the sheet

Signature:

Private Function InSheetOrder(ByVal rngs As Collection) As Boolean

A Source is plotted in the order the sheet holds it, and the styling that follows walks the series by index against the queue. The two agree only while the queue is in sheet order, and the analysis tables name their columns that way: CrossTable writes VALUES_COL_1, PERC_COL_1, VALUES_COL_2 across the table as GraphSeries queues them.

A queue that arrives in some other order is not wrong, it just cannot be read back by index, so those series are attached one at a time instead. Nothing here guesses: it compares the column and the row each range starts at.

Parameters:

Returns: Boolean. True when each range starts at or after the one before it.

PointsMatch #

points-match

Whether the chart plotted a whole column and not part of one

Signature:

Private Function PointsMatch(ByVal co As ChartObject, ByVal firstRng As Range) As Boolean

The series count alone does not say the split was right. A column holding one number in its last row and nothing above it comes back as a one-point series, and one series is what was asked for, so the count agrees while the chart shows a single bar over the first period. The points of the first series are counted against the cells of its range, which is the same layout every other series of the chart has.

A range wider than one column is left alone: its points were never meant to match its cells, and a count that far off is caught before this runs.

Parameters:

Returns: Boolean. True when the first series carries one point per cell.

AttachOneByOne #

attach-one-by-one

Give the chart one series per range, values pinned

Signature:

Private Sub AttachOneByOne(ByVal co As ChartObject, ByVal rngs As Collection)

The fallback of CommitSeries. Whatever Excel made of the single Source goes, and each range becomes an empty series with its values written once.

Parameters:

StyleSeries #

style-series

Give every drawn series its type, its axis and its labels

Signature:

Private Sub StyleSeries(ByVal co As ChartObject, ByVal live As Collection)

The Series objects are taken before the first type is written. Turning one series into a scatter moves it between chart groups, and an index read after that can answer a different series than the one the queue describes.

Parameters:

ApplySeriesLabels #

apply-series-labels

Write the category values, the legend entry and the data labels

Signature:

Private Sub ApplySeriesLabels(ByVal srs As Series, ByRef entry As Variant)

Parameters:

LegendText #

legend-text

Build the literal legend entry of a series

Signature:

Private Function LegendText(ByVal prefix As String, ByVal prefixOnly As Boolean, _
                            ByVal hasLabelRange As Boolean, _
                            ByVal lblRng As Range) As String

Reads the label cell only on the branch that uses it. Every simple-mode series asks for a reference instead, so the read used to be paid for and thrown away.

Parameters:

Returns: String. The text to write as the series name.

Format #

format

Apply layout and formatting to the chart

Signature:

Public Sub Format(Optional ByVal valuesTitle As String = vbNullString, _
                  Optional ByVal catTitle As String = vbNullString, _
                  Optional ByVal plotTitle As String = vbNullString, _
                  Optional ByVal secondAxisTitle As String = vbNullString, _
                  Optional ByVal scope As Byte = GraphScopeNormal, _
                  Optional ByVal heightFactor As Long = 1, _
                  Optional ByVal hardCodeLabels As Boolean = True)

Formats are defined based on the scope of the graph. For time series, graphs are larger; for spatio-temporal analyses, they are wider. The scope controls dimension coefficients, axis gridline colours, legend placement, and plot area reverse ordering. When hardCodeLabels is False, axis titles and the chart title reference named ranges dynamically rather than using literal strings.

A chart with no series has no axes, so reading them raised 1004 and took the whole chart pass of a sheet with it. Format removes such a frame and files the reason.

Parameters:

Layout

KnownScope #

known-scope

Answer a scope this class knows how to lay out

Signature:

Private Function KnownScope(ByVal scope As Byte) As Byte

The scope-to-dimensions policy, and the six steps Format applies.

AnalysisOutput maps its own four analysis scopes onto these three before it calls, and AnaTabIds does the same when it replays a chart. A number outside the three used to fall through every branch and take the standard layout with nothing said about it.

Parameters:

Returns: Byte. The scope given, or GraphScopeNormal when it is unknown.

SizeForScope #

size-for-scope

Answer the chart and plot-area dimensions for one scope

Signature:

Private Function SizeForScope(ByVal scope As Byte, _
                              ByVal heightFactor As Long) As TGraphLayout

Holds every number that depends on the graph scope. The width coefficient stretches a time series graph. The height coefficient grows the chart with the number of geographic units it shows.

Both coefficients are fractional and both were declared As Long once. 1.75 was stored as 2, so every time series chart came out 14 per cent larger than the code asks for, and a spatial chart of five geographic units got 0.48 rounded down to zero, which is a chart of no height at all.

The height curve anchors at one geographic unit and each further unit adds 8 per cent, so five units give 1.32 and twenty give 2.52. Every count a user can set moves the chart, and the bars keep their thickness as the count rises. The floor answers a count below 1, which the callers already clamp away.

Parameters:

Returns: TGraphLayout. The dimensions to apply.

ReadyToFormat #

ready-to-format

Whether the chart can take a layout

Signature:

Private Function ReadyToFormat(ByVal co As ChartObject) As Boolean

A chart with zero series has no axes, and reading Axes on one raises 1004. An empty bordered frame on the analysis sheet reads as a broken chart, and the export would faithfully draw it again, so the frame goes.

Parameters:

Returns: Boolean. True when there is a chart carrying at least one series.

ApplyChartFrame #

apply-chart-frame

Size the chart and draw its border

Signature:

Private Sub ApplyChartFrame(ByVal co As ChartObject, ByRef layout As TGraphLayout)

Parameters:

FormatCategoryAxis #

format-category-axis

Format the category axis and write its title

Signature:

Private Sub FormatCategoryAxis(ByVal ax As Axis, ByVal catTitle As String, _
                               ByVal hardCodeLabels As Boolean, _
                               ByRef layout As TGraphLayout)

Parameters:

FormatValueAxis #

format-value-axis

Format the primary value axis and write its title

Signature:

Private Sub FormatValueAxis(ByVal ax As Axis, ByVal valuesTitle As String, _
                            ByVal hardCodeLabels As Boolean)

Parameters:

FormatChartTitle #

format-chart-title

Write the chart title, when one was asked for

Signature:

Private Sub FormatChartTitle(ByVal cht As Chart, ByVal plotTitle As String, _
                             ByVal hardCodeLabels As Boolean)

Parameters:

FormatSecondaryAxis #

format-secondary-axis

Format the secondary value axis

Signature:

Private Sub FormatSecondaryAxis(ByVal ax As Axis, ByVal secondAxisTitle As String)

The scale runs from 0 to 1 with a tick every tenth, which is what a fraction needs. CrossTableFormula is what decides that the percentage columns hold fractions.

Parameters:

ApplyPlotArea #

apply-plot-area

Colour and place the plot area

Signature:

Private Sub ApplyPlotArea(ByVal plotBox As PlotArea, ByRef layout As TGraphLayout)

Parameters:

ApplyLegend #

apply-legend

Place the legend

Signature:

Private Sub ApplyLegend(ByVal cht As Chart, ByRef layout As TGraphLayout)

Format owns the legend position. Add used to set it as well, on a chart that had no series and so may have had no legend at all.

Parameters:

SetTitleFromName #

set-title-from-name

Write a title as literal text or as a reference to a cell

Signature:

Private Sub SetTitleFromName(ByVal titleObj As Object, ByVal nameOrText As String, _
                             ByVal hardCoded As Boolean)

Formula is the property the object model gives for a reference, so that is what a dynamic title is written through. Caption was measured on 2026-07-31 doing the same thing on this host: it parses "= 'Analysis'!$C$7" and binds the title to the cell, which is what the class relied on before. TestGraphs holds that measurement.

A caller asking for a reference against a name the worksheet does not carry is told about it, because that combination means the caller expected the name to be there.

Parameters:

Helpers

TryRange #

try-range

Resolve a named range on the host worksheet, answering whether it exists

Signature:

Private Function TryRange(ByVal rngName As String, ByRef outRng As Range) As Boolean

Private utility functions for range resolution.

The class used to ask twice for every name it wanted: once to learn whether the name resolved and once to read it, and the address helper asked twice more. Labelling one series cost seven resolutions of two names. A miss costs more than a hit, because a miss is a trapped COM error, and the export path asks for an empty name on every time series series it replays.

Parameters:

Returns: Boolean. True when the name resolved.

QualifiedAddress #

qualified-address

Return the full worksheet-qualified address of a range

Signature:

Private Function QualifiedAddress(ByVal rng As Range) As String

The address is in the format 'SheetName'!$A$1, which a chart formula takes.

Parameters:

Returns: String. Worksheet-qualified address.

Checking

GraphKey #

graph-key

A name for this chart that no other chart shares

Signature:

Private Function GraphKey() As String

Logging and validation support via the Checking interface.

AnalysisOutput builds one chart per table and one per time series graph, and the caller of a checking merges them all into one report. Checking.Add raises on a duplicate key, so a key made of a bare counter took the whole generation down on the second chart that filed anything. The display name is used when the caller gave one; otherwise the cell the chart sits on names it, and every chart of a sheet sits on a cell of its own.

Returns: String. The identifier used in every key this instance files.

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)

Lazily creates the Checking instance on first use and appends the message under a key naming this class, this chart and a counter. The checking store is exposed through HasCheckings and CheckingValues.

Parameters:

ErrorHandling

ThrowError #

throw-error

Raise a ProjectError-based exception

Signature:

Private Sub ThrowError(ByVal errNumb As Long, ByVal errorMessage As String)

Centralised error-raising helper.

Wrapper around Err.Raise that standardises the source for consistent stack traces across all methods in this class.

Parameters:

Throws:

Used in (9 file(s))