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: LLFormat, HiddenNames, ProjectError
Factory
Create #
create
Create a new CustomPivotTable instance
Signature:
Public Function Create(ByVal sh As Worksheet) As CustomPivotTable
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: CustomPivotTable. The initialised instance.
Throws:
- InvalidArgument. When sh is Nothing.
Internal members (not exported)
Factory
Seal #
seal
Seal the instance against further setup writes.
Signature:
Public Sub Seal()
GuardNotSealed #
guard-not-sealed
Guard a setup setter against post-creation writes.
Signature:
Private Sub GuardNotSealed(ByVal propName As String)
Parameters:
propName: String. The property name for the error message.
Wksh #
wksh-get
The dedicated pivot table worksheet
Signature:
Public Property Get Wksh() As Worksheet
Returns: Worksheet. The host worksheet.
Wksh #
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 #
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 #
format
Apply design formatting to the pivot table worksheet
Signature:
Public Sub Format(ByVal design As LLFormat)
Delegates to LLFormat.ApplyFormat with the HListCustomPivotTables scope.
Parameters:
design: LLFormat. The format object providing style rules.
Internal Helpers
InitialiseMetadata #
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
ThrowError #
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.
Used in (3 file(s))
- Linelist.cls
- ListBuilder.cls
- TestCustomPivotTable.bas