Analysis

Manages the Analysis configuration worksheet of a setup workbook, and the copy of that worksheet the designer places inside a generated linelist. AddRows, InsertRows, DeleteRows, RemoveRows and ManageRows work on the rows, Sort orders the tables, Import and Export move the sheet between workbooks, and Translate rewrites the analysis labels. Each instance is bound to one worksheet and hands its table work to CustomTable. Entries filed along the way leave through HasCheckings and CheckingValues.

WHERE THE ANALYSIS OUTPUT LIVES

The tables a field user reads at run time are built by AnalysisOutput, CrossTable and TableSpecs. This class handles the configuration sheet. A linelist instance of this class receives one call, Translate, from LinelistSpecs.

THE NINE REQUIRED TABLES

Create checks that the worksheet carries the nine ListObjects an analysis definition is written in, and every member reads its rows from one of them.

TABLE NAMES ARE MATCHED WITHOUT REGARD TO CASE

Excel matches sheet and table names without regard to case, so this class does the same everywhere. The required-table list is walked with vbTextCompare and the table settings are dispatched on a lower-cased name. The shipped workbook writes "Tab_Global_Summary" where the list says "Tab_global_summary".

THE TEN INSTRUCTION STRINGS ARE HELD TWICE

ManageRows matches the ten instruction strings the user picks from the dropdown in cell RNG_SelectTable. SetupPreparation holds the same ten strings, because it is the class that fills that dropdown. Change one list and change the other.

Depends on: CustomTable, Checking, TranslationObject, BetterArray, ListObject names used in analysis worksheets, Named range holding the row-management instruction on the setup Analysis sheet

Version: 2026-07-29

Instantiation

Create #

create

Create an Analysis instance bound to a worksheet

Signature:

Public Function Create(ByVal hostsheet As Worksheet) As Analysis

Factory method on the predeclared instance. Validates that the worksheet is not Nothing and binds a new Analysis to it. Callers interact with the returned Analysis instance. The required-tables cache is filled lazily on the instance that needs it.

Parameters:

  • hostsheet: Worksheet. The worksheet hosting the analysis tables.

Returns: Analysis. A fully initialised analysis controller.

Throws:

  • ProjectError.ObjectNotInitialized When hostsheet is Nothing.

Depends on:

  • CustomTable
  • Checking
RowManagement

AddRows #

addrows

Add rows based on worksheet header selection

Signature:

Public Sub AddRows()

Add, insert, delete, and remove rows in analysis ListObjects.

Delegates to ManageRows in append mode to add rows to each target analysis table based on the instruction held in the RNG_SelectTable cell of the worksheet.

InsertRows #

insertrows

Insert rows based on the supplied selection

Signature:

Public Sub InsertRows(ByVal targetCell As Range, _
                       Optional ByVal insertShift As Boolean = True)

Determines which analysis table hosts the selection by calling ResolveSelectionTable, retrieves the table settings (prefix, id column, minimum count), and delegates to CustomTable.InsertRowsAt to mirror the selection height inside that table. Worksheet row insertion is enabled by default to protect stacked ListObjects.

Parameters:

  • targetCell: Range. Anchor describing the rows to mirror.
  • insertShift: Optional Boolean. When True, worksheet rows are inserted ahead of the resize. Defaults to True.

DeleteRows #

deleterows

Delete rows intersecting the selection

Signature:

Public Sub DeleteRows(ByVal targetCell As Range, _
                       Optional ByVal forceShift As Boolean = True)

Locates the analysis table hosting the selection via ResolveSelectionTable, retrieves its settings, and delegates to CustomTable.DeleteRowsAt. Identifier columns are cleared for every table that configures one, and forceShift controls whether worksheet rows are shifted up after deletion.

Parameters:

  • targetCell: Range. Anchor describing the rows to delete.
  • forceShift: Optional Boolean. When True, worksheet rows are shifted up after deletion. Defaults to True.

RemoveRows #

removerows

Remove rows while preserving minimum entries

Signature:

Public Sub RemoveRows()

Calls ManageRows in deletion mode to trim each analysis table down to its required minimum row count.

DataExchange

Import #

import

Import analysis tables from an external worksheet

Signature:

Public Sub Import(ByVal sourcesheet As Worksheet)

Copies each ListObject and its associated headers from the source worksheet into the managed worksheet. Tables the source does not carry are cleared first; the rest are rewritten by CustomTable.Import with strict column matching. Source columns that found no home in the host table are reported, and so are required tables missing from the source. The worksheet is sorted at the end, which is also where the host worksheet is checked for missing tables.

Parameters:

  • sourcesheet: Worksheet. The worksheet providing the source analysis tables.

Sort #

sort

Sort analysis ListObjects and enforce minimum rows

Signature:

Public Sub Sort()

Resolves the nine required tables in one pass, reports the ones the worksheet does not carry, then sorts each table by its primary key column (Section or Graph ID), applies secondary sorts where needed (Table order, Graph order), and trims trailing rows via CustomTable.RemoveRows. The identifier column of each table is handed over, so rows that move are renumbered to match their new position.

Export #

export

Export the analysis worksheet to a workbook

Signature:

Public Sub Export(ByVal sourcewb As Workbook, Optional ByVal Hide As Long = xlSheetHidden)

Duplicates the managed worksheet into the provided workbook. If a sheet with the same name already exists it is cleared and reused; otherwise a new sheet is created. The requested visibility is applied either way. Each ListObject is copied by value and the header rows above it are transferred with font formatting, up to four rows or up to the top of the sheet, whichever comes first.

Parameters:

  • sourcewb: Workbook. The workbook receiving the exported worksheet.
  • Hide: Optional Long. Excel visibility constant applied to the exported sheet. Defaults to xlSheetHidden.

Translate #

translate

Translate analysis labels using the provided engine

Signature:

Public Sub Translate(ByVal TransObject As TranslationObject)

Iterates through all ListObjects on the analysis worksheet and translates known textual columns (Section, Table Title, Graph Title, Summary label, Choices, Label, Section (select)) through the translation object. The Summary function column is translated with formula awareness enabled. Each header row is read once and matched in memory, so a column a table does not carry costs nothing. Logs a note on completion and a warning when the translator is Nothing.

Parameters:

  • TransObject: TranslationObject. The translation engine to use.
Internal members (not exported)

Instantiation

Wksh #

wksh

Backing analysis worksheet

Signature:

Public Property Get Wksh() As Worksheet

Returns the worksheet that stores the analysis tables manipulated by this class. Set during construction via the Create factory.

Returns: Worksheet. The bound analysis worksheet.

Wksh #

wksh-set

Assign the backing worksheet

Signature:

Public Property Set Wksh(ByVal hostsheet As Worksheet)

Stores the worksheet reference used by all subsequent operations. Called internally by the Create factory.

Parameters:

RowManagement

ManageRows #

managerows

Central row-management dispatcher

Signature:

Public Sub ManageRows(Optional ByVal del As Boolean = False)

Reads the instruction the user picked in the RNG_SelectTable cell of the analysis worksheet and determines which ListObject(s) to target. When del is False, rows are appended via CustomTable.AddRows, which renumbers the identifier column itself. When del is True, rows are removed via CustomTable.RemoveRows down to the required minimum. Falls back to processing all known tables when the instruction text does not match a specific table name.

Parameters:

InstructionCell #

instruction-cell

Resolve the cell carrying the row-management instruction

Signature:

Private Function InstructionCell(ByVal sh As Worksheet) As Range

The rest of the setup reaches this cell through the RNG_SelectTable named range, so this class asks for the name first and falls back to A1 when the workbook carries no such name. A name that resolves to another worksheet is refused, because a workbook-scoped name answers from any sheet.

Parameters:

Returns: Range. The single cell holding the instruction.

ResolveSelectionTable #

resolve-selection-table

Resolve the ListObject that hosts a worksheet selection

Signature:

Private Function ResolveSelectionTable(ByVal targetCell As Range, ByVal actionLabel As String) As ListObject

Validates that the target cell belongs to the analysis worksheet and intersects a ListObject. Logs a warning and returns Nothing when any validation check fails. Used by InsertRows and DeleteRows to identify the target table before delegating to CustomTable.

Parameters:

Returns: ListObject. The hosting ListObject, or Nothing when not found.

GetTableSettings #

table-settings

Derive prefix, identifier column, and minimum row counts

Signature:

Private Sub GetTableSettings(ByVal loName As String, _
                             ByRef prefix As String, _
                             ByRef idColumn As String, _
                             ByRef minimumCount As Long)

Populates the three ByRef outputs based on the ListObject name. Only TIMESERIES, TITLEGRAPHTIMESERIES, GRAPHTIMESERIES, and SPATIOTEMPORAL have non-default settings; all other tables use empty strings and zero. The name is lower-cased before it is matched, because a table name read back from a worksheet can carry any spelling of the same name.

Parameters:

DataExchange

Clean #

clean

Clean the analysis tables on the worksheet

Signature:

Private Sub Clean(Optional ByVal keepTables As Collection = Nothing)

Import, export, translate, sort, and clean operations.

Iterates through the ListObjects on the analysis worksheet and delegates to CustomTable.Clean for each one. Tables listed in keepTables are left alone: Import passes the tables it is about to rewrite, and CustomTable.Import clears each of those itself.

Parameters:

TableHeaderNames #

table-header-names

Read one table's header row into an array

Signature:

Private Function TableHeaderNames(ByVal csTab As CustomTable) As Variant

One worksheet crossing per table. A single-column table answers a scalar, so the value is wrapped into a one-element array and every caller reads the same shape.

Parameters:

Returns: Variant. A zero-based array of header texts.

HasHeader #

has-header

Test a header name against one table's header row

Signature:

Private Function HasHeader(ByVal headerList As Variant, ByVal colName As String) As Boolean

Whole-string match without regard to case, the same rule CustomTable.DataRange applies when strictSearch is True.

Parameters:

Returns: Boolean. True when the table carries the column.

Checking

LogInfo #

log-info

Log an informational or warning message

Signature:

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

Logging, validation, and error-raising support.

Adds a trace entry to the internal Checking instance, initialising it on first use with a name derived from the class and worksheet. Each call increments the check counter.

Parameters:

HarvestCollaboratorCheckings #

harvest-collaborator-checkings

Fold what a table adapter filed into this trace

Signature:

Private Sub HarvestCollaboratorCheckings(ByVal collaboratorChecks As Checking)

CustomTable keeps a store of its own, and nobody read it before this line. An import that aborted, or a column name the adapter could not find, was filed and then dropped.

Parameters:

LogDroppedColumns #

log-dropped-columns

Report the source columns an import could not place

Signature:

Private Sub LogDroppedColumns(ByVal hostTab As CustomTable, ByVal loName As String)

CustomTable records every source column that found no matching header in the target table. Without this, a column renamed between two setup versions is dropped in silence.

Parameters:

HasCheckings #

hascheckings

Whether trace entries were recorded

Signature:

Public Property Get HasCheckings() As Boolean

Provides a quick way for callers to know if diagnostics are available. Returns True when at least one trace entry has been logged via LogInfo.

Returns: Boolean. True when trace entries exist.

CheckingValues #

checkingvalues

Retrieve collected trace entries

Signature:

Public Property Get CheckingValues() As Checking

Returns the underlying Checking object when traces are available. Returns Nothing when no traces have been logged.

Returns: Checking. The Checking instance, or Nothing.

ThrowError #

throw-error

Raise a ProjectError-based exception

Signature:

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

Wrapper around Err.Raise that standardises the source to CLASSNAME, providing a consistent stack trace across all methods in this class.

Parameters:

Throws:

Helpers

EnsureRequiredTables #

ensure-required-tables

Populate the required-tables cache

Signature:

Private Sub EnsureRequiredTables()

Private helper methods supporting the public API.

Lazily initialises the requiredTablesCaches BetterArray with the names of all nine ListObjects expected on an analysis worksheet. Subsequent calls are no-ops once the cache is populated. The push order decides the order Import, Sort and EnsureAnalysisTables work in, so it is fixed.

IsRequiredTable #

is-required-table

Test a table name against the required-tables cache

Signature:

Private Function IsRequiredTable(ByVal loName As String) As Boolean

The comparison ignores case, because Excel does. A binary comparison here once made Import skip the whole global summary section without a word.

Parameters:

Returns: Boolean. True when the name is one of the nine required tables.

IsValidListObject #

is-valid-list-object

Check if a ListObject is valid for this analysis

Signature:

Private Function IsValidListObject(ByVal Lo As ListObject) As Boolean

Returns True when the ListObject name appears in the required-tables cache and the corresponding ListObject exists on the host worksheet. Used by Import to skip unrecognised tables from the source worksheet.

Parameters:

Returns: Boolean. True when the ListObject is valid for this analysis.

ResolveAnalysisTables #

resolve-analysis-tables

Resolve the required tables of the host worksheet in one pass

Signature:

Private Function ResolveAnalysisTables() As Collection

Nine lookups, once. The result is keyed on the lower-cased table name, so callers read a table by name without crossing to Excel again. A table the worksheet does not carry is left out of the collection.

Returns: Collection. The ListObjects found, keyed on the lower-cased name.

TableNamed #

table-named

Read one table out of a resolved collection

Signature:

Private Function TableNamed(ByVal tables As Collection, ByVal loName As String) As ListObject

A Collection cannot be asked whether a key exists, so the lookup itself is the test and a miss answers Nothing.

Parameters:

Returns: ListObject. The table, or Nothing when the collection has none.

EnsureAnalysisTables #

checktables

Report the expected analysis tables the worksheet does not carry

Signature:

Private Sub EnsureAnalysisTables(ByVal hostTables As Collection)

Walks the required-tables cache and logs a warning for every name absent from the resolved collection.

Parameters:

Used in (47 file(s))