CustomPivotTable
Manages pivot table creation on a dedicated worksheet during linelist building. Each call to Add creates a new PivotTable block (title + pivot) stacked vertically. Position tracking and title references are stored as worksheet-level HiddenNames instead of hidden cell values.
Depends on: ICustomPivotTable, ILLFormat, IHiddenNames, HiddenNames, ProjectError
Factory
create #
Create a new CustomPivotTable instance
Signature:
Public Function Create(ByVal sh As Worksheet) As ICustomPivotTable
Validates the worksheet argument and initialises worksheet-level HiddenNames for position tracking (pivot_output_row, pivot_counter) if they do not already exist. This allows multiple Create calls on the same sheet to resume from the current position.
Parameters:
sh: Worksheet. The dedicated pivot table worksheet in the output workbook.
Returns: ICustomPivotTable. The initialised instance.
Throws:
- InvalidArgument. When sh is Nothing.
Internal members (not exported)
Factory
self #
Return the current instance as ICustomPivotTable
Signature:
Public Property Get Self() As ICustomPivotTable
Returns: ICustomPivotTable. Self-reference for factory pattern.
wksh-get #
The dedicated pivot table worksheet
Signature:
Public Property Get Wksh() As Worksheet
Returns: Worksheet. The host worksheet.
wksh-set #
Assign the host worksheet (factory use only)
Signature:
Public Property Set Wksh(ByVal sh As Worksheet)
Parameters:
sh: Worksheet. The worksheet to assign.
Public API
add #
Create a pivot table from a ListObject source and add a titled block
Signature:
Public Sub Add(ByVal title As String, _
ByVal tableName As String, _
Optional ByVal pivotName As String = "Pivot Table")
Reads the current position from worksheet-level HiddenNames, creates a PivotTable via PivotTableWizard, writes a styled title, stores the title cell address as a HiddenName, and advances the position for the next block. If the PivotTableWizard call fails (e.g. invalid source), the error is logged to Debug.Print and the method exits without creating a title.
Parameters:
title: String. Display label for the pivot block (typically the sheet name).tableName: String. Name of the source ListObject providing pivot data.pivotName: String. Localised prefix for the title. Defaults to "Pivot Table".
format #
Apply design formatting to the pivot table worksheet
Signature:
Public Sub Format(ByVal design As ILLFormat)
Delegates to ILLFormat.ApplyFormat with the HListCustomPivotTables scope.
Parameters:
design: ILLFormat. The format object providing style rules.
Internal Helpers
initialise-metadata #
Initialise worksheet-level HiddenNames for position tracking
Signature:
Public Sub InitialiseMetadata()
Creates pivot_output_row and pivot_counter HiddenNames if they do not already exist. Called by the factory to allow resuming from the current position when the same sheet is re-wrapped.
Error Handling
throw-error #
Raise a project error with class context
Signature:
Private Sub ThrowError(ByVal errNumber As Long, ByVal errMessage As String)
Wraps Err.Raise with CLASS_NAME as the source, providing consistent error attribution across all methods in this class.
Parameters:
errNumber: Long. The ProjectError constant to raise.errMessage: String. Descriptive error message.