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 the worksheet-level HiddenNames, creates a
PivotTable, writes a styled title, stores the title text under
pivot_title_, and advances the position for the next block.
The pivot is built through PivotCaches.Create, NOT through
Worksheet.PivotTableWizard. The wizard does not stack: handed a worksheet that
already carries a PivotTable, it retargets that one at the destination it was
given rather than creating another. Every block after the first therefore
moved the first block's pivot down to its own row and left the sheet holding
one pivot table, with nothing raised and nothing recorded. The title, the
cursor and pivot_title_ all advanced normally, which is what made
it look like a build that had worked.
The source table is resolved against every worksheet of the workbook before
the pivot is built. A name that resolves to nothing is recorded as a checking
and nothing is built: the wizard answered such a name with a modal dialog,
which On Error cannot see, and that dialog froze Excel in front of the user in
the middle of a build and hung a headless run. See SourceTableExists for why
the guard outlived the wizard. An error the creation call does raise is
recorded the same way, so one bad table leaves the rest of the build alone.
The position advances in both cases, so the next block cannot land on top of
whatever the failed attempt left behind.
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".
Throws:
- ObjectNotInitialized. When the instance holds no worksheet.
format
Apply design formatting to the pivot table worksheet
Signature:
Public Sub Format(ByVal design As LLFormat)
Owns the sheet's visibility: a pivot sheet that never took a pivot stays
very hidden, and one that did becomes visible. The cosmetic pass rewrites
every row of the worksheet, so pivot_formatted holds it to one run however
many times this is called.
Parameters:
design: LLFormat. The format object providing style rules.
Throws:
- ObjectNotInitialized. When the instance holds no worksheet.
- InvalidArgument. When design is Nothing.
Title #
title
The stored title of one table's pivot block
Signature:
Public Function Title(ByVal tableName As String) As String
Answers what Add wrote for that table, which is what the linelist shows when
it lists variables under their pivot. Empty when that table has no block.
Parameters:
tableName: String. Name of the source ListObject.
Returns: String. The title text, or an empty string.
Throws:
- ObjectNotInitialized. When the instance holds no worksheet.
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-set
Assign the host worksheet (factory use only)
Signature:
Public Property Set Wksh(ByVal sh As Worksheet)
Parameters:
sh: Worksheet. The worksheet to assign.
Internal Helpers
initialise-metadata
Seed the worksheet-level HiddenNames this class reads
Signature:
Public Sub InitialiseMetadata()
Creates the four private names if they do not already exist. Called by the
factory, so every later write can be a bare SetValue and re-wrapping a
populated sheet resumes from the position it left.
SheetNames #
sheet-names
The one HiddenNames manager for the host worksheet
Signature:
Private Function SheetNames() As HiddenNames
Creating a HiddenNames scans and caches every name in the container, so the
instance is built once and held. Add, Format and InitialiseMetadata all read
through this.
Returns: HiddenNames. The cached manager.
SourceTableExists #
source-table-exists
Whether the workbook defines the source ListObject
Signature:
Private Function SourceTableExists(ByVal tableName As String) As Boolean
PivotTableWizard showed a modal dialog for a SourceData naming no ListObject.
In production that was a frozen Excel in the middle of a generation, and in a
headless run it hung the harness and left a zero-byte results file under a
healthy import log. On Error reached none of it, so the name is resolved here
before the pivot is built.
The wizard is gone -- Add builds through PivotCaches.Create now -- but this
guard stays. What that call does with a name no ListObject holds has never
been measured, and the cost of finding out the hard way is a hung run.
The source table of a pivot sits on the data entry sheet it was built from, so
the walk covers every worksheet of the pivot sheet's workbook. It runs once
per block added.
Parameters:
tableName: String. Name of the source ListObject.
Returns: Boolean. True when one worksheet of the workbook defines that table.
UniquePivotName #
unique-pivot-name
A PivotTable name no pivot in the workbook already holds
Signature:
Private Function UniquePivotName(ByVal baseName As String) As String
CreatePivotTable raises on a name that is taken, and asking for the same
source table twice is a real case: a rebuild over a populated pivot sheet does
it. The wizard this replaced renamed such a clash away on its own. This does
the same explicitly, so the first block for a table keeps the plain
PivotTable_ that TestAddCreatesPivotTable and any reader expect,
and a later block for the same table takes a numbered spelling.
Parameters:
baseName: String. The name the block would like.
Returns: String. baseName, or baseName with a numeric suffix.
pivot-name-taken
Whether some worksheet of the workbook holds a pivot of that name
Signature:
Private Function PivotNameTaken(ByVal pivotName As String) As Boolean
PivotTable names are unique across the workbook, not the worksheet, so the
walk covers every sheet the way SourceTableExists does.
Parameters:
pivotName: String. The candidate name.
Returns: Boolean. True when a pivot table of that name already exists.
GuardReady #
guard-ready
Guard a member against use on an instance with no worksheet
Signature:
Private Sub GuardReady(ByVal memberName As String)
Parameters:
memberName: String. The member name for the error message.
Checkings
LogCheck #
log-check
Append a diagnostic entry to the internal checking log
Signature:
Private Sub LogCheck(ByVal tableName As String, _
ByVal label As String, _
Optional ByVal scope As Byte = checkingNote)
The key carries the source table, so a reader of the generation report can see
which pivot block the entry is about. The counter keeps the key free when the
same table files twice, which Checking.Add requires.
Parameters:
tableName: String. Name of the source ListObject the entry is about.label: String. Message describing the issue.scope: Byte. Severity scope for the log entry.
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. The ProjectError value is
raised as it stands, the way every other class in the tree raises it.
Parameters:
errNumber: Long. The ProjectError constant to raise.errMessage: String. Descriptive error message.