AnaTabIds

Records analysis table named ranges and graph series metadata into ListObjects on a hidden tracking worksheet. The tracking data is consumed during export to recreate the full analysis structure (tables and charts) on output worksheets. Manages three ListObject families (tab_ids_, graph_ids_, graph_formats_*) across four analysis scopes (normal, time series, spatial, spatio-temporal).

Depends on: CustomTable, Graphs, IGraphs, BetterArray, IFormulas, Named ranges for sheet names

Version: 2025-01-15

Instantiation

create #

Create a new AnaTabIds instance bound to a tracking worksheet

Signature:

Public Function Create(ByVal idsh As Worksheet, _
                       Optional ByVal check As Boolean = True) As IAnaTabIds

Factory method on the predeclared instance. Validates the worksheet reference and optionally checks that all 12 tracking ListObjects (3 prefixes x 4 scopes) and 4 sheet-name named ranges exist. Pass check as False to skip validation when the tracking sheet is being built incrementally.

Parameters:

idsh: Worksheet. The worksheet containing the tracking ListObjects and sheet-name named ranges.
check: Optional Boolean. When True, calls CheckRequirements to validate all expected infrastructure. Defaults to True.

Returns: IAnaTabIds. A fully initialised tracking instance.

Throws:

ProjectError.InvalidArgument When idsh is Nothing.
ProjectError.InvalidArgument When check is True and any required ListObject or named range is missing.

Depends on:

CustomTable
BetterArray

Table Tracking

add-table-infos #

Register table named ranges in the tracking ListObject

Signature:

Private Sub AddTableInfos(ByVal scope As Byte, _
                           ByVal tabId As String, _
                           ByVal tabRangesNames As BetterArray, _
                           Optional ByVal exportTable As String = "yes")

Methods for registering table named ranges for export. Writes entries into the scope-specific tab_ids_ ListObject. Each entry consists of the table identifier in column 1, the named range name in column 2, and the export flag in column 3. The range names are bulk- written via BetterArray.ToExcelRange. The ListObject is resized after appending. Exits immediately when tabRangesNames is empty.

Parameters:

scope: Byte. AnalysisIdsScope value selecting the tracking ListObject.
tabId: String. Identifier of the analysis table.
tabRangesNames: BetterArray. Named range names to register.
exportTable: Optional String. Export flag ("yes" or "no"). Defaults to "yes".

Graph Tracking

add-graph-info #

Register a graph series row in the tracking ListObject

Signature:

Private Sub AddGraphInfo(ByVal scope As Byte, _
                          ByVal tabId As String, _
                          ByVal graphId As String, _
                          ByVal seriesName As String, _
                          ByVal seriesType As String, _
                          ByVal seriesPos As String, _
                          ByVal seriesLabel As String, _
                          ByVal seriesColumnLabel As String, _
                          ByVal hardCodeLabels As Boolean, _
                          ByVal outRangeAddress As String, _
                          Optional ByVal prefix As String = vbNullString, _
                          Optional ByVal prefixOnly As Boolean = False)

Methods for registering graph series and formatting metadata. Writes one series entry into the scope-specific graph_ids_ ListObject. The 11 columns store: tabId, graphId, seriesName, seriesType, seriesPos, seriesLabel, seriesColumnLabel, hardCodeLabels (as CLng 0/1), outRangeAddress, prefix, and prefixOnly (as CLng 0/1). The method appends after the last populated row and resizes the ListObject.

Parameters:

scope: Byte. AnalysisIdsScope value selecting the tracking ListObject.
tabId: String. Identifier of the parent analysis table.
graphId: String. Identifier of the graph receiving this series.
seriesName: String. Named range name for the series data.
seriesType: String. Chart series type (e.g. "xlLine").
seriesPos: String. Axis position indicator for the series.
seriesLabel: String. Named range name for category labels.
seriesColumnLabel: String. Named range name for column header labels.
hardCodeLabels: Boolean. When True, labels are written as literals.
outRangeAddress: String. Cell address for graph placement on output.
prefix: Optional String. Admin-level prefix for spatial graphs. Defaults to vbNullString.
prefixOnly: Optional Boolean. When True, only the prefix is used as the label. Defaults to False.

Remarks:

Boolean parameters are stored as Long (0/1) via CLng for reliable cross-session persistence in Excel ListObject cells.

add-graph-format #

Register graph formatting metadata in the tracking ListObject

Signature:

Private Sub AddGraphFormat(ByVal scope As Byte, _
                            ByVal tabId As String, _
                            ByVal graphId As String, _
                            ByVal catTitle As String, _
                            ByVal valuesTitle As String, _
                            ByVal hardCodeLabels As Boolean, _
                            Optional ByVal heightFactor As Long = 1, _
                            Optional ByVal plotTitle As String = vbNullString)

Writes one format entry into the scope-specific graph_formats_ ListObject. The 7 columns store: tabId, graphId, catTitle, valuesTitle, hardCodeLabels (as CLng 0/1), heightFactor, and plotTitle. The method appends after the last populated row and resizes the ListObject. The graphId links this format entry to its corresponding series entries in graph_ids_.

Parameters:

scope: Byte. AnalysisIdsScope value selecting the tracking ListObject.
tabId: String. Identifier of the parent analysis table.
graphId: String. Identifier of the graph to format.
catTitle: String. Category (X) axis title.
valuesTitle: String. Value (Y) axis title.
hardCodeLabels: Boolean. When True, axis labels are written as literals.
heightFactor: Optional Long. Multiplier for graph height. Defaults to 1.
plotTitle: Optional String. Chart title. Defaults to vbNullString.

Range Transfer and Graph Writing

write-graphs #

Write all tracked graphs to the output worksheet

Signature:

Private Sub WriteGraphs(ByVal outsh As Worksheet, ByVal scope As Byte)

First calls WriteTableNames to transfer all named ranges, then iterates the graph_ids_ ListObject to build charts. A new IGraphs object is created at each graph boundary (detected when graphId changes). Each series row is processed by AddSeries. When the loop ends, the final graph is formatted via AddFormat. Format metadata is read from the graph_formats_ ListObject wrapped in an ICustomTable keyed by "graphId".

Parameters:

outsh: Worksheet. The output worksheet receiving the chart objects.
scope: Byte. AnalysisIdsScope value selecting the tracking tables.

Remarks:

The loop walks column 2 (graphId) of graph_ids_. The outRangeAddress is read from column 9 (offset 7 from column 2). Application.GoTo scrolls to the chart position, which is required for correct chart placement in some Excel versions.

Internal members (not exported)

Internal Properties

self #

Current object instance

Signature:

Public Property Get Self() As IAnaTabIds

Factory-support properties for construction and worksheet access. Convenience accessor so consuming code can fluently retrieve the interface reference from the predeclared Create method.

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

wksh-set #

Assign the tracking worksheet

Signature:

Public Property Set Wksh(ByVal sh As Worksheet)

Parameters:

sh: Worksheet. The worksheet to bind.

wksh #

Tracking worksheet reference

Signature:

Public Property Get Wksh() As Worksheet

Returns the worksheet that hosts the tracking ListObjects and sheet-name named ranges.

Returns: Worksheet. The bound tracking worksheet.

ListObject Access

actual-lo #

Resolve the tracking ListObject for a scope and prefix

Signature:

Private Property Get ActualLo(ByVal scope As Byte, _
                              Optional ByVal prefix As String = "tab_ids_") As ListObject

Routing helpers that resolve scope-specific ListObjects and worksheets. Maps the AnalysisIdsScope enum value to a suffix string (ts, sp, uba, sptemp) and concatenates it with the prefix to form the ListObject name. This is the central routing mechanism all tracking methods use.

Parameters:

scope: Byte. AnalysisIdsScope value selecting the analysis type.
prefix: Optional String. ListObject name prefix. Defaults to "tab_ids_". Pass "graph_ids_" or "graph_formats_" for other families.

Returns: ListObject. The resolved tracking ListObject.

Remarks:

If the scope does not match any known case, actName remains empty and the ListObject lookup will raise a runtime error.

actual-analysis-sheet #

Resolve the analysis output worksheet for a scope

Signature:

Private Property Get ActualAnalysisSheet(ByVal scope As Byte) As Worksheet

Reads the sheet-name named range corresponding to the scope from the tracking worksheet (RNG_SheetUAName, RNG_SheetTSName, RNG_SheetSPName, or RNG_SheetSPTempName) and returns the matching worksheet from the parent workbook.

Parameters:

scope: Byte. AnalysisIdsScope value selecting the output sheet.

Returns: Worksheet. The analysis output worksheet for the given scope.

Remarks:

If the named range value does not correspond to an existing worksheet, the Worksheets collection access raises a runtime error.

Range Transfer and Graph Writing

range-exists #

Check whether a named range exists on a worksheet

Signature:

Private Function RangeExists(ByVal sh As Worksheet, ByVal rngName As String) As Boolean

Helpers for transferring named ranges and rendering charts. Attempts to resolve the name via sh.Range(rngName) with error trapping. Returns True when the name resolves to a valid Range. Also picks up workbook-level names resolved through the worksheet.

Parameters:

sh: Worksheet. The worksheet to check.
rngName: String. The named range to look for.

Returns: Boolean. True when the named range exists.

transfer-range #

Copy a named range definition from source to output worksheet

Signature:

Private Sub TransferRange(ByVal inpsh As Worksheet, ByVal outsh As Worksheet, _
                           ByVal rngName As String)

Recreates a named range on the output worksheet using the same cell address as the source. This is the core mechanism for export: graphs and formulas that reference these names work correctly on the new sheet. Silently exits when the source range does not exist.

Parameters:

inpsh: Worksheet. The source worksheet owning the named range.
outsh: Worksheet. The destination worksheet for the new name.
rngName: String. The named range to transfer.

write-table-names #

Transfer all tracked named ranges to the output worksheet

Signature:

Private Sub WriteTableNames(ByVal outsh As Worksheet, ByVal scope As Byte)

Iterates the tab_ids_ ListObject for the scope and calls TransferRange for each named range name found in column 2. The source analysis sheet is resolved via ActualAnalysisSheet. The loop continues until an empty cell is hit in column 1.

Parameters:

outsh: Worksheet. The destination worksheet for the named ranges.
scope: Byte. AnalysisIdsScope value selecting the tracking table.

add-series #

Read one series entry and add it to a graph object

Signature:

Private Sub AddSeries(ByVal gr As IGraphs, ByVal cellRng As Range)

Reads series metadata from a row in the graph_ids_ ListObject and calls gr.AddSeries and gr.AddLabels. The cellRng points to the graphId cell (column 2); all other values are read as offsets. Boolean values are read with error trapping because they are stored as numeric 0/1.

Parameters:

gr: IGraphs. The chart object receiving the series.
cellRng: Range. The graphId cell in the current tracking row.

Remarks:

Offset numbering skips column 9 (outRangeAddress, offset 7) and jumps to offset 8 (prefix) and offset 9 (prefixOnly), matching the 11-column layout stored by AddGraphInfo.

add-format #

Apply formatting settings to a built graph

Signature:

Private Sub AddFormat(ByVal gr As IGraphs, ByVal grTab As ICustomTable, _
                       ByVal scope As Byte, ByVal graphId As String)

Reads format metadata from the graph_formats_ ICustomTable by graphId and calls gr.Format. Maps AnalysisIdsScope to GraphScope: TimeSeries and SpatioTemporal map to GraphScopeTimeSeries, Spatial maps to GraphScopeSpatial, and all others default to GraphScopeNormal.

Parameters:

gr: IGraphs. The chart object to format.
grTab: ICustomTable. Wrapper around graph_formats_ ListObject, keyed by "graphId".
scope: Byte. AnalysisIdsScope value for scope-to-GraphScope mapping.
graphId: String. The graph identifier for the format lookup.

Remarks:

heightFactor defaults to 1 and hardCodeLabels defaults to False when the stored values cannot be converted.

Validation

lo-exists #

Check whether a ListObject exists on a worksheet

Signature:

Private Function LoExists(ByVal sh As Worksheet, ByVal loName As String) As Boolean

Existence checks and factory validation. Uses the standard VBA error-trapping pattern to test for ListObject existence. Used by CheckRequirements to validate the tracking sheet.

Parameters:

sh: Worksheet. The worksheet to search.
loName: String. The ListObject name to look for.

Returns: Boolean. True when the ListObject exists.

sheet-exists #

Check whether a worksheet exists in a workbook

Signature:

Private Function SheetExists(ByVal wb As Workbook, ByVal sheetName As String) As Boolean

Uses the standard VBA error-trapping pattern to test for worksheet existence. Used by CheckRequirements to verify that the sheets referenced by the named ranges actually exist.

Parameters:

wb: Workbook. The workbook to search.
sheetName: String. The worksheet name to look for.

Returns: Boolean. True when the worksheet exists.

check-requirements #

Validate the tracking worksheet infrastructure

Signature:

Private Sub CheckRequirements(ByVal sh As Worksheet)

Checks for all 12 tracking ListObjects (tab_ids_uba/sp/ts/sptemp, graph_ids_uba/sp/ts/sptemp, graph_formats_uba/sp/ts/sptemp) and the 4 sheet-name named ranges (RNG_SheetUAName, RNG_SheetTSName, RNG_SheetSPName, RNG_SheetSPTempName). Also verifies that each named range value corresponds to an actual worksheet in the workbook.

Parameters:

sh: Worksheet. The tracking worksheet to validate.

Throws:

ProjectError.InvalidArgument When any ListObject, named range, or referenced worksheet is missing.

Error Handling

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.

Parameters:

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

Throws:

ProjectError. Always raises the specified error.

Interface Implementation

IAnaTabIds_AddGraphInfo #

Signature:

Private Sub IAnaTabIds_AddGraphInfo(ByVal scope As Byte, _
    ByVal tabId As String, ByVal graphId As String, _
    ByVal seriesName As String, ByVal seriesType As String, _
    ByVal seriesPos As String, ByVal seriesLabel As String, _
    ByVal seriesColumnLabel As String, ByVal outRangeAddress As String, _
    Optional ByVal hardCodeLabels As Boolean = False, _
    Optional ByVal prefix As String = vbNullString, _
    Optional ByVal prefixOnly As Boolean = False)

Delegated members satisfying the IAnaTabIds contract. See the corresponding Private members above for full documentation.

Used in (6 file(s))