AnalysisOutput

Internal Properties

self #

Current object instance.

Signature:

Public Property Get Self() As IAnalysisOutput

Properties used during factory construction and internal wiring. Convenience accessor that returns the current instance cast to the IAnalysisOutput interface, enabling the predeclared factory pattern.

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

output-ll-get #

Output linelist facade.

Signature:

Public Property Get OutputLL() As ILinelist

Returns the ILinelist reference representing the output workbook where analysis worksheets are created and populated.

Returns: ILinelist. The output linelist workbook facade.

output-ll-set #

Assign the output linelist facade.

Signature:

Public Property Set OutputLL(ByVal ll As ILinelist)

Parameters:

• ll: ILinelist. The output linelist workbook facade to store.

wksh-get #

Setup worksheet reference.

Signature:

Public Property Get Wksh() As Worksheet

Returns the setup worksheet containing analysis definition ListObjects.

Returns: Worksheet. The setup worksheet containing analysis ListObjects.

wksh-set #

Assign the setup worksheet reference.

Signature:

Public Property Set Wksh(ByVal specSh As Worksheet)

Parameters:

• specSh: Worksheet. The setup worksheet to store.

Dependency Properties

linelist-data #

Linelist metadata facade.

Signature:

Private Property Get LinelistData() As ILinelistSpecs

Convenience accessors for frequently used collaborators retrieved through the output linelist and its metadata facade. Convenience accessor that retrieves the ILinelistSpecs object from the stored output linelist. ILinelistSpecs is the central metadata facade providing access to the dictionary, translation objects, formula data, design format, passwords, and sheet configuration. This property avoids repeating OutputLL().LinelistData() throughout the class.

Returns: ILinelistSpecs. Metadata facade for the output linelist.

design-format #

Design format object.

Signature:

Private Property Get DesignFormat() As ILLFormat

Convenience accessor for the ILLFormat design format object used to apply visual formatting (fonts, colours, borders, cell styles) to analysis worksheets, cells, and chart elements. Delegates through LinelistData to retrieve the shared format singleton so all analysis output uses consistent styling from the setup workbook.

Returns: ILLFormat. Design format object for the output linelist.

trans-object #

Translation object for a given scope.

Signature:

Public Property Get TransObject(Optional ByVal scope As Byte = TranslationOfMessages) As ITranslationObject

Retrieves an ITranslationObject for the requested translation scope. Translation objects resolve human-readable labels, button captions, sheet names, and other locale-dependent strings from the setup workbook. The scope parameter controls which translation table is used: messages (default), shape names, or other categories defined by the TranslationScope enum.

Parameters:

• scope: Optional Byte. A constant from the TranslationScope enum. Defaults to TranslationOfMessages. Use TranslationOfShapes for button/shape labels.

Returns: ITranslationObject. Bound to the requested translation scope.

output-sh #

Output worksheet for a given analysis scope.

Signature:

Private Property Get OutputSh(Optional ByVal scope As Byte = AnalysisScopeNormal) As Worksheet

Resolves the output worksheet in the linelist workbook for the given analysis scope. Each scope maps to a translated sheet name stored in the messages translation table (e.g. "LLSHEET_Analysis" for normal, "LLSHEET_TemporalAnalysis" for time series). The translation ensures the output sheet names match the user's configured language.

Parameters:

• scope: Optional Byte. A constant from the AnalysisScope enum indicating which analysis worksheet to return. Defaults to AnalysisScopeNormal.

Returns: Worksheet. The output analysis sheet in the linelist workbook.

names-object #

Lazily-initialised analysis tracking object.

Signature:

Private Property Get NamesObject() As IAnaTabIds

Lazily-initialised accessor for the IAnaTabIds tracking object. AnaTabIds maintains a registry of all cross-table named ranges and graph metadata created during the analysis build. This registry is later used by the update/filter commands to locate tables and graphs at runtime. The object is created once on first access using the temporary analysis worksheet from the linelist, and all subsequent calls return the cached instance.

Returns: IAnaTabIds. Tracking object that records table and graph metadata.

Remarks:

• The backing worksheet comes from lData.TemporarySheetName(TemporarySheetAnalysis), which must exist in the output linelist before this property is first accessed.

Application Helpers

busy-app #

Suppress Excel visual feedback and recalculation for performance.

Signature:

Private Sub BusyApp()

Disables ScreenUpdating, DisplayAlerts, Calculation (switches to manual), and EnableAnimations on the Application object to maximise performance during batch write operations. These settings are restored externally after the full analysis build completes.

Remarks:

• This sub is called multiple times (e.g. inside SplitOutputSheet) because activating a worksheet can re-enable ScreenUpdating in some Excel versions.

split-output-sheet #

Apply freeze-pane split to an analysis output worksheet.

Signature:

Private Sub SplitOutputSheet(Optional ByVal scope As Byte = AnalysisScopeNormal)

Applies a freeze-pane split to the output analysis worksheet so that header rows and the left navigation columns remain visible while the user scrolls through tables. The row split is 2 for normal/spatial scopes and 3 for time-series and spatio-temporal scopes (which have an extra GoTo-header dropdown row). The column split is always 3. Zoom is set to 80% for a wider view of cross-tables.

Parameters:

• scope: Optional Byte. A constant from the AnalysisScope enum. Defaults to AnalysisScopeNormal. Time-series and spatio-temporal scopes get an extra frozen row.

Remarks:

• The sub activates the worksheet (required by the FreezePanes API), wraps everything in On Error Resume Next to tolerate protected-sheet or hidden-window conditions, and calls BusyApp twice because Activate can re-enable ScreenUpdating.

Sheet Preparation

add-commands #

Add a filter/update command button to the analysis output worksheet.

Signature:

Private Sub AddCommands(Optional ByVal scope As Byte = AnalysisScopeNormal)

Places a command button on the output worksheet that triggers either the standard filter-update macro (UPDATEFILTERCOMMAND) or the spatial-specific update macro (UPDATESPCOMMAND) depending on the scope. The button is placed at row 2 col 2, styled using the design format, and labelled with the translated SHP_Filter shape name. Exits early when the linelist is built from a template (mainobj.HasTemplate), because the template already contains the buttons.

Parameters:

• scope: Optional Byte. A constant from the AnalysisScope enum. Defaults to AnalysisScopeNormal. When AnalysisScopeSpatial, the spatial-specific update command is assigned.

prepare-sheet #

Perform five-step initialisation of an analysis output worksheet.

Signature:

Private Sub PrepareSheet(Optional ByVal scope As Byte = AnalysisScopeNormal)

Initialises an analysis output worksheet before any tables are written. The five steps are: (1) write a scope-specific flag named range at row 1 col 10 (e.g. RNG_HasTable_UNI), formatted as a hidden cell; (2) apply the AllAnalysisSheet base format to the entire worksheet; (3) write a sheet-info identifier at row 1 col 3 as a hidden cell; (4) place the ComputedOnFiltered() warning formula at row 2 col 5 with LinelistWarning formatting; (5) delegate to AddCommands and SplitOutputSheet for button and freeze-pane setup.

Parameters:

• scope: Optional Byte. A constant from the AnalysisScope enum. Defaults to AnalysisScopeNormal. Determines the flag range name, sheet-info string, and freeze-pane row count.

GoTo Dropdown Helpers

add-goto #

Create a data-validation dropdown for section/graph navigation.

Signature:

Private Sub AddGoTo(ByVal cellRng As Range, _
                    ByVal dropArray As BetterArray, _
                    ByVal goToLabel As String, _
                    Optional ByVal goToPrefix As String = "ua_", _
                    Optional ByVal goToSuffix As String = vbNullString)

Creates a data-validation dropdown in a single cell that lets the user navigate to a named section, header, or graph on the analysis sheet. The entries in dropArray become the validation list; selecting one triggers a GoTo macro that scrolls to the corresponding named range. The cell is formatted as a LinelistSelectionDropdown, its initial value is set to goToLabel, and it receives a workbook-level named range. If dropArray is empty the method exits silently so callers do not need to guard.

Parameters:

• cellRng: Range. The single-cell Range where the dropdown will be placed.
• dropArray: BetterArray. Display strings for the dropdown entries. Must have Length > 0 or the method exits early.
• goToLabel: String. The default display value written into the cell.
• goToPrefix: Optional String. A short prefix for the named range, scoped per analysis type. Defaults to "ua_".
• goToSuffix: Optional String. An optional suffix appended to the named range, used to distinguish section-level vs graph-level dropdowns. Defaults to vbNullString.

Temporal Section Helpers

min-max-formula #

Finalise temporal section date-period boundary formulas and GoTo header dropdown.

Signature:

Private Sub MinMaxFormula(ByVal outsh As Worksheet, ByVal secId As String, _
                          ByVal minFormula As String, ByVal maxFormula As String, _
                          ByVal goToPrefix As String, _
                          ByVal headersList As BetterArray, _
                          ByVal goToHeaderLabel As String)

Min/Max formula finalisation and GoTo header dropdown for temporal sections. Finalises a temporal (time-series or spatio-temporal) section by writing its date-period boundary formulas and adding a GoTo Header dropdown. Each temporal section has two named ranges: MIN_MIN_DATE_{secId} and MAX_MAX_DATE_{secId}, which hold the overall minimum and maximum dates across all tables in that section. The minFormula and maxFormula strings are comma-separated MIN/MAX fragments accumulated by WriteTable; this method wraps them in a final worksheet formula. After writing, a GoTo Header dropdown is placed two rows below the SECTION_{secId} named range.

Parameters:

• outsh: Worksheet. The output analysis worksheet where formulas and dropdown are written.
• secId: String. The section identifier string (e.g. "SEC1") used to locate the named ranges.
• minFormula: String. Comma-separated MIN expressions to wrap in the final = MIN(...) formula.
• maxFormula: String. Comma-separated MAX expressions to wrap in the final = MAX(...) formula.
• goToPrefix: String. Prefix for the GoTo named range ("ts_" or "spt_").
• headersList: BetterArray. Header display strings for the dropdown entries.
• goToHeaderLabel: String. Translated label for the GoTo Header dropdown default value.

Table Creation

write-table #

Iterate setup ListObject rows and build cross-tables on the output sheet.

Signature:

Private Sub WriteTable(ByVal loName As String, _
                       ByVal sectionsList As BetterArray, _
                       ByVal headersList As BetterArray, _
                       Optional ByVal scope As Byte = AnalysisScopeNormal)

Core table-creation loop and supporting write logic. Core table-creation loop. Iterates over every data row of the setup ListObject identified by loName, and for each valid row: creates a TableSpecs, builds a CrossTable on the output sheet, applies CrossTableFormula formulas, formats the table with the design, registers the table's named ranges in the AnaTabIds tracking object, and optionally creates a chart (GraphSpecs + Graphs). For time-series and spatio-temporal scopes, additional logic accumulates MIN/MAX date formulas per section and writes them via MinMaxFormula when a section boundary is detected. GoTo section and header entries are pushed into the caller-provided sectionsList and headersList arrays. Each table is wrapped in a per-table error handler (Bug #3 fix) so that a single malformed row does not abort the entire analysis; errors are logged to the Immediate window.

Parameters:

• loName: String. Name of the ListObject on the setup worksheet whose rows define the tables to build.
• sectionsList: BetterArray. Accumulates GoTo section entries (passed ByVal but mutated via Push). The caller uses this after the call to create the section-level dropdown.
• headersList: BetterArray. Accumulates GoTo header entries for time-series and spatio-temporal scopes.
• scope: Optional Byte. A constant from the AnalysisScope enum. Defaults to AnalysisScopeNormal. Controls which output sheet receives the tables.

Remarks:

• The loop uses Do...Loop While with Offset(1) advancement. If the ListObject has no DataBodyRange, the method exits early.

Graph Creation

write-time-series-graphs #

Build all time-series charts from the graph definition ListObjects.

Signature:

Private Sub WriteTimeSeriesGraphs(ByVal graphLoName As String, _
                                   ByVal graphTitleLoName As String, _
                                   ByVal tabLoName As String, _
                                   ByVal graphsList As BetterArray)

Methods that build time-series and spatio-temporal charts after tables have been written. Reads three setup ListObjects: the graph definition table (graphLoName), the graph title/label table (graphTitleLoName), and the original time-series table (tabLoName). These are bundled into a BetterArray and passed to GraphSpecs.CreateRangeSpecs, which operates in complex mode -- resolving multi-series graphs with mixed chart types and separate axis positions. The method then loops over each graph, creates it via Graphs.Create, iterates its series to add data ranges and labels, formats the chart, records metadata in AnaTabIds, and advances the anchor cell by 50 rows. Finally, a GoTo graph dropdown is placed at row 3 col 3.

Parameters:

• graphLoName: String. Name of the ListObject defining graph series.
• graphTitleLoName: String. Name of the ListObject defining graph titles and labels.
• tabLoName: String. Name of the time-series table ListObject, used by GraphSpecs to resolve named ranges for series data.
• graphsList: BetterArray. Accumulates GoTo graph display strings for the navigation dropdown.

Remarks:

• Exits early if the RNG_HasTable_TS flag is empty (no tables were written) or if either the graph or title ListObject has no data rows.

write-spatio-temporal-graph #

Build spatio-temporal charts from the table definition ListObject.

Signature:

Private Sub WriteSpatioTemporalGraph(ByVal loName As String, _
                                      ByVal graphsList As BetterArray)

Builds spatio-temporal charts by re-reading the same setup ListObject that was used to create the spatio-temporal tables (loName). Unlike WriteTimeSeriesGraphs which uses a separate graph-definition ListObject, this method iterates over the original table rows, checks HasGraph for each, and creates one chart per qualifying table using the simpler GraphSpecs.Create (non-complex) mode. Each graph's plot title is composed from the translated "MSG_Graph" label plus the STARTCOL named-range value. Charts are spaced 50 rows apart. After all charts are created, a GoTo graph dropdown is placed at row 3 col 3.

Parameters:

• loName: String. Name of the spatio-temporal ListObject whose rows define both the tables and the graphs.
• graphsList: BetterArray. Accumulates GoTo graph display strings for the navigation dropdown.

Remarks:

• Skips rows where ValidTable is False or HasGraph is False. Exits early if the ListObject has no DataBodyRange.

Error Handling

throw-error #

Raise a project error with standardised source.

Signature:

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

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

Parameters:

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

Throws:

• ProjectError. Always raises the specified error.

Interface Implementation

IAnalysisOutput_WriteAnalysis #

Signature:

Private Sub IAnalysisOutput_WriteAnalysis()

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

• AnalysisOutput.cls
• IAnalysisOutput.cls
• TestAnalysisOutput.bas