Analysis

Manages an analysis worksheet within an outbreak linelist workbook. Provides row manipulation (add, insert, delete, remove), data exchange (import, export, sort, clean), translation of analysis labels, and validation of required ListObjects. Each instance is bound to a single worksheet and delegates table operations to CustomTable. Trace diagnostics are recorded via IChecking and can be retrieved through HasCheckings and CheckingValues.

Depends on: CustomTable, Checking, ITranslationObject, BetterArray, Checking support, ListObject names used in analysis worksheets

Version: 2025-01-15

Instantiation

create #

Create an Analysis instance bound to a worksheet

Signature:

Public Function Create(ByVal hostsheet As Worksheet) As IAnalysis

Factory method on the predeclared instance. Validates that the worksheet is not Nothing, binds a new Analysis to it, and populates the required- tables cache on the predeclared instance. Callers interact with the returned IAnalysis interface.

Parameters:

hostsheet: Worksheet. The worksheet hosting the analysis tables.

Returns: IAnalysis. A fully initialised analysis controller.

Throws:

ProjectError.ObjectNotInitialized When hostsheet is Nothing.

Depends on:

CustomTable
Checking

RowManagement

addrows #

Add rows based on worksheet header selection

Signature:

Private 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 in cell A1 of the worksheet.

insertrows #

Insert rows based on the supplied selection

Signature:

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

Delete rows intersecting the selection

Signature:

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

Locates the analysis table hosting the selection via ResolveSelectionTable, retrieves its settings, and delegates to CustomTable.DeleteRowsAt. The includeIds flag controls whether identifier columns are cleared, and forceShift controls whether worksheet rows are shifted up after deletion.

Parameters:

targetCell: Range. Anchor describing the rows to delete.
includeIds: Optional Boolean. When True, identifiers are cleared along with data. Defaults to True.
forceShift: Optional Boolean. When True, worksheet rows are shifted up after deletion. Defaults to True.

removerows #

Remove rows while preserving minimum entries

Signature:

Private Sub RemoveRows()

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

DataExchange

clean #

Clean all analysis tables on the worksheet

Signature:

Private Sub Clean()

Import, export, translate, sort, and clean operations. Iterates through all ListObjects on the analysis worksheet and delegates to CustomTable.Clean for each one. Ensures the required-tables cache is populated before processing.

import #

Import analysis tables from an external worksheet

Signature:

Private Sub Import(ByVal sourcesheet As Worksheet)

Copies each ListObject and its associated headers from the source worksheet into the managed worksheet. The method first cleans existing tables, then imports each valid ListObject via CustomTable.Import with strict column matching. Missing source tables are logged as warnings. After import the method validates all required tables and sorts them.

Parameters:

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

sort #

Sort analysis ListObjects and enforce minimum rows

Signature:

Private Sub Sort()

Iterates through the required-tables cache, 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 to enforce structural rules.

export #

Export the analysis worksheet to a workbook

Signature:

Private 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. Each ListObject is copied by value and its header rows (up to 3 rows above) are transferred with font formatting.

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 analysis labels using the provided engine

Signature:

Private Sub Translate(ByVal TransObject As ITranslationObject)

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. Logs a note on completion and a warning when the translator is Nothing.

Parameters:

TransObject: ITranslationObject. The translation engine to use.

Internal members (not exported)

Instantiation

self #

Current object instance

Signature:

Public Property Get Self() As IAnalysis

Convenience accessor so consuming code can fluently retrieve the interface reference from the predeclared Create method.

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

wksh #

Backing analysis worksheet

Signature:

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

hostsheet: Worksheet. The worksheet to bind.

RowManagement

managerows #

Central row-management dispatcher

Signature:

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

Reads the header instruction from cell A1 of the analysis worksheet and determines which ListObject(s) to target. When del is False, rows are appended via CustomTable.AddRows and identifiers are regenerated. When del is True, rows are removed via CustomTable.RemoveRows down to the required minimum. Falls back to processing all known tables when the header text does not match a specific table name.

Parameters:

del: Optional Boolean. When True, rows are removed; otherwise rows are added. Defaults to False.

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:

targetCell: Range. The user selection to resolve.
actionLabel: String. Human-readable action name for log messages (e.g. "insert", "delete").

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

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.

Parameters:

loName: String. Name of the target ListObject.
prefix: ByRef String. Populated with the identifier prefix.
idColumn: ByRef String. Populated with the identifier column name.
minimumCount: ByRef Long. Populated with the minimum row count.

Checking

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 IChecking instance, initialising it on first use with a name derived from the class and worksheet. Each call increments the check counter and sets the hasChecks flag.

Parameters:

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

hascheckings #

Whether trace entries were recorded

Signature:

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

Retrieve collected trace entries

Signature:

Private Property Get CheckingValues() As Object

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

Returns: Object. The IChecking instance, or Nothing.

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:

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

Throws:

ProjectError. Always raises the specified error.

Interface Implementation

IAnalysis_Wksh #

Signature:

Private Property Get IAnalysis_Wksh() As Worksheet

Delegated members satisfying the IAnalysis contract. See the corresponding Private/Public members above for full documentation.

Helpers

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.

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:

Lo: ListObject. The ListObject to validate.

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

checktables #

Ensure all expected analysis tables are present

Signature:

Private Sub EnsureAnalysisTables(Optional ByVal raiseError As Boolean = False)

Iterates through the required-tables cache and checks that each ListObject exists on the analysis worksheet. When raiseError is True, missing tables raise a ProjectError.ElementNotFound; otherwise a warning is logged via LogInfo.

Parameters:

raiseError: Optional Boolean. When True, raises an error for missing tables instead of logging. Defaults to False.

Throws:

ProjectError.ElementNotFound When raiseError is True and a required ListObject is missing.

Used in (18 file(s))