Graphs

Simplifies the creation and formatting of Excel ChartObject instances on analysis worksheets. The class wraps a single chart, allowing callers to attach data series from named ranges, assign category and legend labels, and apply scope-dependent formatting (time series, spatial, or standard). Charts are positioned at a caller-supplied Range and sized according to the GraphScope enum. Consumers interact through the IGraphs interface.

Depends on: Checking

Version: 1.0 (2026-02-09)

Instantiation

create #

Create a Graph object bound to a worksheet and position

Signature:

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

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.

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: IGraphs. A ready-to-use graph instance.

Throws:

ProjectError.ObjectNotInitialized When sh is Nothing.
ProjectError.ObjectNotInitialized When posRng is Nothing.

Depends on:

Checking

Operations

add #

Create an empty chart on the worksheet

Signature:

Private 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. The width and height of the graph object may change depending on the scope in the future.

addseries #

Attach a data series from a named range

Signature:

Private Sub AddSeries(ByVal rngName As String, ByVal chrtType As String, _
                    Optional 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 logs a warning and exits silently. If no chart exists yet, Add is called lazily before attaching the series.

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.

Checking

has-checkings #

Determine whether diagnostics have been captured

Signature:

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

checking-values #

Retrieve the collected checking entries

Signature:

Private Property Get CheckingValues() As Object

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

Returns: Object. The IChecking instance with logged diagnostics.

Internal members (not exported)

Instantiation

self #

Current object instance

Signature:

Public Property Get Self() As IGraphs

Returns the current instance cast to its interface. Used internally by the factory method to return an IGraphs reference.

Returns: IGraphs. Reference to the current object.

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

Assign the host worksheet

Signature:

Public Property Set Wksh(ByVal sh As Worksheet)

Parameters:

sh: Worksheet. The worksheet where graph data resides.

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.

start-range-set #

Assign the starting position range

Signature:

Public Property Set StartRange(ByVal strtRng As Range)

Parameters:

strtRng: Range. The cell or range used to position the graph.

name-set #

Assign the graph display name

Signature:

Public Property Let Name(ByVal grName As String)

Parameters:

grName: String. The graph name.

name #

Retrieve the graph display name

Signature:

Public Property Get Name() As String

Returns: String. The graph name.

GraphElements

graph-chart #

Retrieve the internal ChartObject

Signature:

Private Property Get graphChart() As ChartObject

Internal chart object and series index state. Returns the ChartObject created by Add. Nothing until Add is called.

Returns: ChartObject. The underlying Excel chart object.

series-index #

Latest series index

Signature:

Private Property Get seriesIndex() As Long

Tracks the 1-based index of the most recently added series. Incremented by AddSeries and used by AddLabels to target the correct series entry.

Returns: Long. Current series index.

series-index-set #

Update the latest series index

Signature:

Private Property Let seriesIndex(ByVal ind As Long)

Parameters:

ind: Long. New index value.

Operations

addlabels #

Assign category and legend labels to the current series

Signature:

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

Parameters:

catName: String. Named range for category axis labels.
lblName: String. Named range for the legend entry text.
prefix: Optional String. Display prefix prepended to the legend name. Defaults to vbNullString.
prefixOnly: Optional Boolean. When True, uses only the prefix as the legend entry. Defaults to False.
hardCodeLabels: Optional Boolean. When True, legend text is literal; when False, references the named range. Defaults to True.

format #

Apply layout and formatting to the chart

Signature:

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

Parameters:

valuesTitle: Optional String. Title for the value axis (Y-axis). Defaults to vbNullString.
catTitle: Optional String. Title for the category axis (X-axis). Defaults to vbNullString.
plotTitle: Optional String. Title displayed above the chart. Defaults to vbNullString.
secondAxisTitle: Optional String. Title for the secondary value axis. Defaults to vbNullString.
scope: Optional Byte. GraphScope value controlling layout sizing. Defaults to GraphScopeNormal.
heightFactor: Optional Long. Multiplicative factor applied to chart height. Defaults to 1.
hardCodeLabels: Optional Boolean. When True, axis titles are literal text. Defaults to True.

Helpers

range-exist #

Test whether a named range exists on the host worksheet

Signature:

Private Function RangeExist(ByVal rngName As String) As Boolean

Private utility functions for range validation and address resolution. Uses On Error Resume Next to attempt resolving the named range. Returns True when the range is valid and non-Nothing.

Parameters:

rngName: String. Named range to check.

Returns: Boolean. True when the named range exists.

rng-address #

Return the full worksheet-qualified address of a named range

Signature:

Private Function RngAddress(ByVal rngName As String) As String

Resolves the named range on the host worksheet and returns its address in the format 'SheetName'!$A$1. Returns vbNullString when the range does not exist.

Parameters:

rngName: String. Named range to resolve.

Returns: String. Worksheet-qualified address or vbNullString.

Checking

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)

Logging and validation support via the IChecking interface. Lazily creates the Checking instance on first use and appends the message using a monotonically increasing counter key. The checking store is exposed through HasCheckings and CheckingValues.

Parameters:

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

ErrorHandling

show-debug #

Print debug information to the Immediate window

Signature:

Private Sub ShowDebug()

Centralised error-raising and debug helpers. Outputs basic debug information including the graph name.

throw-error #

Raise a ProjectError-based exception

Signature:

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

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

Parameters:

errNumb: Long. ProjectError code describing the failure.
errorMessage: String. Descriptive error message.

Throws:

ProjectError. Always raises the specified error.

Interface

IGraphs_Add #

Signature:

Private Sub IGraphs_Add()

Delegated members satisfying the IGraphs contract.

Used in (6 file(s))