FilteredData

Copies every HList table of the source workbook into its filtered companion sheet, so a filtered export and the analysis formulas read what the user can see on the screen. The companion keeps the rows that are visible and filled: a row stays out of the copy when its source row is hidden or when the row holds no value at all.

Sync walks every worksheet, rewrites each companion, and answers the number of sheets it brought over. A sheet that fails is skipped and recorded, and the walk continues, so one broken sheet leaves the others synced. FailedSheets names each skipped sheet with the reason. A run that skipped a sheet also writes one failure line to the user log of the workbook. The caller reads FailedSheets and decides what a failure costs: ExportButton aborts the export, and the linelist filter refresh shows one box.

THE WHOLE ROW TEST RUNS IN MEMORY

Each companion is written once. The source block is read in one crossing, visibility comes off one SpecialCells call over the source rows, the blank test runs on the array, and the kept rows go down in one write. A shape that copies the whole block over and then deletes the rejects pays two crossings per row, on tables that ClickAddRows pads 199 blank rows at a time.

THE SHEET TAG IS READ THE CHEAP WAY

The walk reads sheet_type through HiddenNames.QuickValue, which reads one name, and the full store is built for the HList sheets alone. A full store costs a walk of a sheet's whole Names collection, and the linelist filter refresh runs far more often than an export.

Depends on: HiddenNames, LLLog, BetterArray

Version: 1.1 (2026-08-12)

Factory

Create #

create

Create a filtered data sync over a workbook

Signature:

Public Function Create(ByVal sourceWkb As Workbook) As FilteredData

Factory method that validates the workbook and hands back a ready instance. Called on the predeclared instance.

Parameters:

sourceWkb: Workbook. The linelist workbook holding the HList sheets.

Returns: FilteredData. A ready instance.

Throws:

ProjectError.ObjectNotInitialized When sourceWkb is Nothing.

Public Accessors

FailedSheets #

failed-sheets

The sheets the last sync skipped, each with the reason

Signature:

Public Property Get FailedSheets() As BetterArray

One entry per skipped sheet, shaped "sheet name: reason". The list is rebuilt at the top of every Sync, so it always describes the last run. Before the first run it answers an empty list.

Returns: BetterArray. The skipped sheets of the last sync.

Sync

Sync #

sync

Rewrite every filtered companion from its live table

Signature:

Public Function Sync() As Long

Walks the worksheets of the source workbook. Each sheet whose sheet_type is HList has its visible filled rows written into the filtered companion. A sheet whose table holds no data rows is left as it is. A sheet that raises is recorded in FailedSheets with the reason and the walk continues.

Returns: Long. The number of sheets whose companion was rewritten.

Internal members (not exported)

Public Accessors

SourceWorkbook #

source-workbook-set

Assign the source linelist workbook

Signature:

Public Property Set SourceWorkbook(ByVal wkb As Workbook)

Parameters:

wkb: Workbook. The workbook to store.

Seal #

seal

Prevent further changes to setup-only properties

Signature:

Public Sub Seal()

Sync

SyncSheet #

sync-sheet

Rewrite one filtered companion from its HList table

Signature:

Private Function SyncSheet(ByVal sh As Worksheet) As Boolean

Reads the companion name off the sheet store and hands the table and the companion sheet to the rewrite. Raises when the filtered_sheet name is missing, and lets any worksheet failure travel up to the per-sheet handler in Sync.

Parameters:

sh: Worksheet. The HList sheet to copy.

Returns: Boolean. True when the companion was rewritten.

Throws:

ProjectError.ElementNotFound When the filtered_sheet name is missing.

RewriteCompanion #

rewrite-companion

Write the visible filled rows of one table into its companion

Signature:

Private Sub RewriteCompanion(ByVal sourceTable As ListObject, _
                             ByVal filtsh As Worksheet)

A row is kept when it is visible and holds at least one value. Visibility is read off the source rows in one SpecialCells call. The read spans the whole row on purpose: a column hidden by show/hide keeps its values in the companion, and a row leaves when a filter or the user hides it. The values travel through memory -- one read of the whole block, the blank test on the array, and one write of the kept rows. The two tables sit at the same address on their two sheets, which is the layout the builder writes.

Parameters:

sourceTable: ListObject. The data entry table the rows come from.
filtsh: Worksheet. The sheet holding the filtered companion table.

RowHoldsAValue #

row-holds-a-value

Say whether one row of a value block holds anything

Signature:

Private Function RowHoldsAValue(ByRef blockValues As Variant, _
                                ByVal rowIdx As Long, _
                                ByVal columnCount As Long) As Boolean

An empty cell and an empty string count as nothing, and every other value -- a zero, a date, an error -- counts as data. This is what CountA answers on a worksheet row, read off the array instead.

Parameters:

blockValues: Variant. The value block, 1-based on both sides.
rowIdx: Long. The row to read.
columnCount: Long. The width of the block.

Returns: Boolean. True when at least one cell of the row holds a value.

Private Helpers

RecordFailure #

record-failure

Record one skipped sheet with the reason

Signature:

Private Sub RecordFailure(ByVal sheetName As String, ByVal reason As String)

Parameters:

sheetName: String. The sheet the sync skipped.
reason: String. What the failure said.

LogFailures #

log-failures

Write the skipped sheets of a run to the user log

Signature:

Private Sub LogFailures()

One failure line per run, with the skipped sheets joined in the detail. A run that skipped nothing writes nothing. A log write must never take down the sync it records, so a write that raises is swallowed here.

UserLog #

user-log

The user log of the source workbook, built once and held

Signature:

Private Function UserLog() As LLLog

A workbook whose log cannot be built answers Nothing and the sync keeps its result, so the log stays a bystander. The tried flag keeps a failed build from being paid again on every run.

Returns: LLLog. The held log, or Nothing when the build failed.

GuardNotSealed #

guard-not-sealed

Raise when a setup-only property is written after sealing

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

Parameters:

propName: String. The name of the property being guarded.

ThrowError #

throw-error

Raise a ProjectError-based exception

Signature:

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

Parameters:

errNumber: Long. A ProjectError enum value (e.g. ObjectNotInitialized).
message: String. Human-readable description of the failure.

Throws:

ProjectError. Always raises the specified error.

Used in (5 file(s))