CustomTable

Wraps one Excel ListObject. ColumnExists, CellRange, ValueRange and Value read the table and SetValue writes into it. InsertRowsAt, DeleteRowsAt, AddRows and RemoveRows change its shape, RenameColumn rewrites a header, Sort orders the rows, SetValidation puts a dropdown behind a column, and Clean empties the data. Import and Export move values between this table and another table or a DataSheet.

WHAT THE INSTANCE HOLDS

A column lookup reads the header row once as a block and matches the name in memory for the rest of that call. Nothing about the table shape is held between calls, so an instance that stays alive answers from the worksheet as it stands at each call.

THE IDENTIFIER COLUMN

AddIds fills an identifier column, and IdValue and PrefixValue say which column carries it and what its values start with. A row is then reached by its identifier.

MOVING VALUES IN AND OUT

Import records the table before it writes, and RestoreTableSnapshot puts that state back. Source headers with no match here leave through ImportColumnsNotFound, headers an export was handed and could not write leave through ExportColumnsNotFound, and HasColumnsNotImported answers over the last import. Both routines unhide every column before they write and hide the same columns again after.

Depends on: BetterArray, Checking, HiddenNames, DropdownLists

Version: 1.2 (2026-08-24)

StateManagement

RestoreTableSnapshot #

restore-table-snapshot

Restore the table from a previously saved snapshot

Signature:

Public Sub RestoreTableSnapshot()

Reapplies the cached headers and rows when an import needs to roll back. Resizes the ListObject, writes the backup data, resets state, and re-hides previously hidden columns.

Factory

Create #

create

Create a CustomTable wrapper around a ListObject

Signature:

Public Function Create(ByVal Lo As ListObject, _
                       Optional ByVal idCol As String = vbNullString, _
                       Optional ByVal idPrefix As String = vbNullString) As CustomTable

Entry point for creating CustomTable instances.

Wraps the supplied ListObject with convenience methods for column lookup, row manipulation, import/export, and diagnostic logging. An optional ID column enables dictionary-style keyed row access. When adding rows, the ID column is automatically numbered in sequence, optionally prefixed (e.g. "line 1", "line 2").

Parameters:

  • Lo: ListObject. The Excel table to wrap.
  • idCol: Optional String. Name of the ID column. Defaults to vbNullString.
  • idPrefix: Optional String. Prefix for auto-generated ID values. Defaults to vbNullString.

Returns: CustomTable. A fully initialised CustomTable instance.

Throws:

  • ProjectError.ObjectNotInitialized When Lo is Nothing.

Depends on:

  • BetterArray
  • Checking
Elements

IdValue #

id-value

ID column name

Signature:

Public Property Get IdValue() As String

Returns the name of the column used as a unique row identifier.

Returns: String. The ID column name.

HeaderRange #

header-range

Header row Range of the CustomTable

Signature:

Public Property Get HeaderRange() As Range

Returns the Range covering the header row of the underlying ListObject.

Returns: Range. The header row range.

ColumnExists #

column-exists

Check whether a column exists in the table

Signature:

Public Function ColumnExists(ByVal colName As String, _
                             Optional ByVal strictSearch As Boolean = False, _
                             Optional ByVal matchCase As Boolean = False) As Boolean

Delegates to ColumnIndex and returns True when the index is positive.

Parameters:

  • colName: String. Column name to search.
  • strictSearch: Optional Boolean. When True, uses exact matching. Defaults to False.
  • matchCase: Optional Boolean. When True, matching is case-sensitive. Defaults to False.

Returns: Boolean. True when the column is found.

DataRange #

data-range

Range of a column or the entire data body

Signature:

Public Property Get DataRange(Optional ByVal colName As String = "__all__", _
                              Optional ByVal includeHeaders As Boolean = False, _
                              Optional ByVal strictSearch As Boolean = False, _
                              Optional ByVal matchCase As Boolean = False) As Range

Returns a data Range for the specified column, or the entire data body when colName is "all". When strictSearch is False, the search uses partial matching; the first partial match is returned. Use strictSearch when column names share common text to avoid unexpected output. Returns Nothing when the column has no data rows or is not found. Answering Nothing is part of the contract: four callers read it as "this table does not have that column", so it can never become a raise or an empty range.

Parameters:

  • colName: Optional String. Column header to retrieve. Defaults to "all".
  • includeHeaders: Optional Boolean. When True, includes the header row. Defaults to False.
  • strictSearch: Optional Boolean. When True, uses exact whole-string matching. Defaults to False.
  • matchCase: Optional Boolean. When True, matching is case-sensitive. Defaults to False.

Returns: Range. The requested data range, or Nothing when unavailable.

Name #

name

Name of the attached ListObject

Signature:

Public Property Get Name() As String

Reads the name from the live ListObject. It is deliberately not kept on the instance: the user can rename a table from the Excel interface at any time, and a name stored at creation would then be wrong for the rest of the object's life.

Returns: String. The table name.

CellRange #

cell-range

A specific cell in the CustomTable

Signature:

Public Property Get CellRange(ByVal colName As String, _
                              ByVal lineNum As Long) As Range

Returns the cell at the intersection of the given column and worksheet row number. Returns Nothing when the column does not exist or the row is out of bounds.

Parameters:

  • colName: String. Column header identifying the column.
  • lineNum: Long. Worksheet row number (1-based).

Returns: Range. The target cell, or Nothing when not found.

ValueRange #

value-range

Locate the cell Range by column and key

Signature:

Public Function ValueRange(ByVal colName As String, ByVal keyName As String) As Range

Returns the Range at the intersection of the specified column and the row matching keyName in the ID column. Returns Nothing when the key or column is not found, or when no ID column is configured.

Parameters:

  • colName: String. Column header identifying the column.
  • keyName: String. Key value identifying the row.

Returns: Range. The target cell range, or Nothing when not found.

Value #

value

Retrieve a cell value by column and key

Signature:

Public Property Get Value(ByVal colName As String, _
                          ByVal keyName As String) As String

For tables with a key column, returns the cell value at the intersection of the specified column and the row matching keyName. Returns an empty string when the key or column is not found.

Parameters:

  • colName: String. Column header to read from.
  • keyName: String. Key value identifying the row.

Returns: String. The cell value converted to string, or vbNullString.

Modify

InsertRowsAt #

insert-rows-at

Insert rows at the selected position

Signature:

Public Sub InsertRowsAt(ByVal targetCell As Range, _
                        Optional ByVal insertShift As Boolean = False, _
                        Optional ByVal includeIds As Boolean = True)

Inserts rows matching the height of targetCell. Uses worksheet row insertion when insertShift is True to protect stacked tables, otherwise adds ListRows at the selection anchor. Renumbers the ID column when includeIds is True.

Parameters:

  • targetCell: Range. Selection anchoring the insertion point.
  • insertShift: Optional Boolean. When True, inserts worksheet rows. Defaults to False.
  • includeIds: Optional Boolean. When True, renumbers the ID column. Defaults to True.

DeleteRowsAt #

delete-rows-at

Delete rows intersecting the selection

Signature:

Public Sub DeleteRowsAt(ByVal targetCell As Range, _
                        Optional ByVal includeIds As Boolean = True, _
                        Optional ByVal forceShift As Boolean = False)

Removes data rows that intersect targetCell. Uses worksheet row deletion when forceShift or the stored shift tracker is active, otherwise removes ListRows directly. Adds a placeholder row when all rows are deleted. Renumbers the ID column when includeIds is True.

Parameters:

  • targetCell: Range. Selection identifying which rows to delete.
  • includeIds: Optional Boolean. When True, renumbers the ID column. Defaults to True.
  • forceShift: Optional Boolean. When True, forces worksheet row deletion. Defaults to False.

AddIds #

add-ids

Fill the ID column with sequential values

Signature:

Public Sub AddIds()

Writes sequential numbers (optionally prefixed) to every cell in the ID column. Silently exits when no ID column is configured or when the column does not exist. The ID column is typically locked and not modified by the user directly.

AddRows #

add-rows

Add rows to the CustomTable

Signature:

Public Sub AddRows(Optional ByVal nbRows As Long = 5, _
                   Optional ByVal insertShift As Boolean = False, _
                   Optional ByVal includeIds As Boolean = True)

Appends the requested number of rows, either by inserting worksheet rows (to protect stacked tables) or by extending the ListObject range directly. Insertion is useful when multiple ListObjects share a worksheet and overlaps must be avoided. Optionally renumbers the ID column afterwards.

Parameters:

  • nbRows: Optional Long. Number of rows to add. Defaults to 5.
  • insertShift: Optional Boolean. When True, inserts worksheet rows. Defaults to False.
  • includeIds: Optional Boolean. When True, renumbers the ID column. Defaults to True.

RemoveRows #

remove-rows

Remove empty rows from the CustomTable

Signature:

Public Sub RemoveRows(Optional ByVal totalCount As Long = 0, _
                      Optional ByVal includeIds As Boolean = True, _
                      Optional ByVal forceShift As Boolean = False)

Deletes rows whose non-empty cell count falls at or below totalCount. In the linelist, formula cells count as non-empty, so totalCount should match the number of formula columns. When totalCount is left at 0 the class counts the formula columns itself. Optionally renumbers the ID column afterwards.

Parameters:

  • totalCount: Optional Long. Maximum non-empty cells for a row to be considered empty. Defaults to 0.
  • includeIds: Optional Boolean. When True, renumbers the ID column. Defaults to True.
  • forceShift: Optional Boolean. When True, forces worksheet row deletion. Defaults to False.

SetValidation #

set-validation

Set dropdown validation on a column

Signature:

Public Sub SetValidation(ByVal colName As String, _
                         ByVal drop As DropdownLists, _
                         ByVal dropName As String, _
                         Optional ByVal alertType As String = "info", _
                         Optional ByVal message As String = vbNullString)

Applies data validation from the supplied DropdownLists to every cell in the specified column. Silently exits when the column does not exist or has no data range.

Parameters:

  • colName: String. Column header to validate.
  • drop: DropdownLists. Dropdown source providing the validation list.
  • dropName: String. Name of the dropdown list within the source.
  • alertType: Optional String. Alert severity ("info", "warning", "error"). Defaults to "info".
  • message: Optional String. Message shown when validation fails. Defaults to vbNullString.

SetValue #

set-value

Change the value of one specific cell

Signature:

Public Sub SetValue(ByVal colName As String, ByVal keyName As String, ByVal newValue As String)

Writes newValue to the cell identified by column name and key. Silently exits when the key or column is not found. Use with caution as it modifies data inside the ListObject.

Parameters:

  • colName: String. Column header identifying the cell.
  • keyName: String. Key value identifying the row.
  • newValue: String. The new value to assign.

RenameColumn #

rename-column

Rename a column header within the table

Signature:

Public Sub RenameColumn(ByVal colName As String, _
                        ByVal newName As String, _
                        Optional ByVal strictSearch As Boolean = True, _
                        Optional ByVal matchCase As Boolean = False)

Replaces the header text of the matched column with newName. Raises an error when newName is empty. The strict default here is the opposite of the loose default on ColumnIndex and DataRange, and that is on purpose: it is what stops a caller asking for "Label" from renaming "Formula Label".

Parameters:

  • colName: String. Current column header to find.
  • newName: String. New header text to assign.
  • strictSearch: Optional Boolean. When True, uses exact matching. Defaults to True.
  • matchCase: Optional Boolean. When True, matching is case-sensitive. Defaults to False.

Throws:

  • ProjectError.InvalidArgument When newName is empty.

Clean #

clean

Clear all non-formula, non-key columns

Signature:

Public Sub Clean()

Iterates every column in the table and clears the contents of those that do not contain formulas. The key column is preserved: it holds plain values, and clearing it wiped every ID whenever Clean was called outside Import. Each column is taken from its own position in the loop. Looking the header text back up cost about nine round trips and a worksheet search for a column whose place the loop already knew.

Sort #

sort

Sort the table on one or more columns

Signature:

Public Sub Sort(Optional ByVal colName As String = vbNullString, _
                Optional ByVal colList As Object = Nothing, _
                Optional ByVal directSort As Boolean = True, _
                Optional ByVal strictSearch As Boolean = True)

colList holds the sort keys in DECREASING significance: its first item ends up the primary key, its last item the weakest of the list, and colName is the final tiebreak of them all. That reads backwards until you see how it works. Each pass is a full single-key Range.Sort over the whole table, so the LAST pass applied is the one that decides the order. colName is therefore sorted first and colList is walked from its last item to its first. Because the keys are applied one at a time, the precedence between them rests on Excel leaving rows that tie on a key in the order it found them. Excel behaves that way, but it does not promise to. A caller that cannot live with that should issue one Range.Sort carrying key1/key2/key3 instead, the way LLChoices does.

Parameters:

  • colName: Optional String. Column header sorted first, so it ends up the weakest key. Defaults to vbNullString.
  • colList: Optional Object. BetterArray of column headers, most significant first. Defaults to Nothing.
  • directSort: Optional Boolean. When True, sorts directly; when False, groups rows by first occurrence of each distinct value. Defaults to True.
  • strictSearch: Optional Boolean. When True, uses exact column matching. Defaults to True.
DataExchange

Import #

import

Import data from a DataSheet or CustomTable

Signature:

Public Sub Import(ByVal impTab As Object, _
                  Optional ByVal pasteAtBottom As Boolean = False, _
                  Optional ByVal strictColumnSearch As Boolean = False, _
                  Optional ByVal insertShift As Boolean = True, _
                  Optional ByVal formatHeaders As Object = Nothing, _
                  Optional ByVal keepSourceHeaders As Boolean = False)

Import, export, and snapshot operations.

Copies column-matched data from the source object into this table. When keepSourceHeaders is True, replaces all headers and data entirely. Creates a snapshot before import and rolls back on error. Unhides hidden columns during the operation and restores their state afterwards. Note on size: with keepSourceHeaders False and the default insertShift True, this GROWS the table to fit the source but never shrinks it. A three-row table importing two rows stays three rows, with the third row merely blanked by Clean. Only the insertShift False branch trims.

Parameters:

  • impTab: Object. A DataSheet or CustomTable to import from.
  • pasteAtBottom: Optional Boolean. When True, appends below existing data. Defaults to False.
  • strictColumnSearch: Optional Boolean. When True, uses exact column matching. Defaults to False.
  • insertShift: Optional Boolean. When True, inserts worksheet rows during resize. Defaults to True.
  • formatHeaders: Optional Object. BetterArray of headers whose formatting to preserve. Defaults to Nothing.
  • keepSourceHeaders: Optional Boolean. When True, replaces destination headers. Defaults to False.

ImportColumnsNotFound #

import-columns-not-found

Columns not matched during the last import

Signature:

Public Property Get ImportColumnsNotFound() As BetterArray

Returns a clone of the BetterArray containing column headers from the source that had no match in this table. Only available after a successful import; logs a diagnostic and exits when no import has occurred.

Returns: BetterArray. Unmatched column headers, or Nothing.

ExportColumnsNotFound #

export-columns-not-found

Headers the last export was asked for and could not write

Signature:

Public Property Get ExportColumnsNotFound() As BetterArray

Export skips a header it cannot resolve against this table and leaves its output column where it was, so the block it writes is narrower than the header list it was handed. A caller that writes a second row from that same list -- the label row of a data export is the case -- has to take these entries out first, or every label after the first gap sits one column left of its data.

The array is emptied at the start of every export, so it always describes the last one. It is empty when every header matched.

Returns: BetterArray. The headers that were skipped.

HasColumnsNotImported #

has-columns-not-imported

Whether the last import had unmatched columns

Signature:

Public Property Get HasColumnsNotImported() As Boolean

Returns True when the most recent import left some source columns unmatched against this table.

Returns: Boolean. True when unmatched columns exist.

Export #

export

Export the table to a worksheet

Signature:

Public Sub Export(ByVal sh As Worksheet, _
                  Optional ByVal headersTable As Object = Nothing, _
                  Optional ByVal startLine As Long = 1, _
                  Optional ByVal startColumn As Long = 1, _
                  Optional ByVal addListObject As Boolean = False, _
                  Optional ByVal clearSheet As Boolean = True)

Writes the requested columns (or all columns when headersTable is not a BetterArray) to the target worksheet, optionally creating a ListObject on the output range. Unhides hidden columns during the operation and restores their state afterwards, on the failure path too.

Parameters:

  • sh: Worksheet. Target worksheet to write to.
  • headersTable: Optional Object. BetterArray of column headers to export. Defaults to Nothing (all columns).
  • startLine: Optional Long. Row to begin writing at. Defaults to 1.
  • startColumn: Optional Long. Column to begin writing at. Defaults to 1.
  • addListObject: Optional Boolean. When True, creates a ListObject on the output. Defaults to False.
  • clearSheet: Optional Boolean. When True, clears the target worksheet first. Defaults to True.
Checkings

HasCheckings #

has-checkings

Whether the table has logged diagnostic messages

Signature:

Public Property Get HasCheckings() As Boolean

Returns True when at least one diagnostic entry has been recorded.

Returns: Boolean. True when diagnostic entries exist.

CheckingValues #

checking-values

Retrieve the diagnostic log entries

Signature:

Public Property Get CheckingValues() As Object

Returns the internal Checking object containing all logged messages. Returns Nothing when no entries have been recorded.

Returns: Object. An Checking instance, or Nothing.

Internal members (not exported)

StateManagement

ResetCaches #

reset-caches

Drop the state a structural change can make wrong

Signature:

Private Sub ResetCaches()

Backup and guard helpers that keep internal state consistent.

Column lookups read the header row from the sheet on every call, so there is no lookup cache to drop here any more. The shift flag is the one piece of state a resize can leave saying the wrong thing.

ResetImportState #

reset-import-state

Reset import state so that imports start fresh

Signature:

Private Sub ResetImportState()

Clears the import flag, hidden-column list, not-imported-columns list, and any saved backup so the next import begins from a clean slate.

ClearBackup #

clear-backup

Release backup buffers

Signature:

Private Sub ClearBackup()

Sets the backup header and row arrays to Nothing and clears the hasBackup flag once the operation completes successfully.

SaveTableSnapshot #

save-table-snapshot

Save a snapshot of the current table state

Signature:

Private Sub SaveTableSnapshot()

Captures the current headers and data rows into BetterArray buffers so RestoreTableSnapshot can revert the table if an import fails. Unhides hidden columns before reading, and leaves them unhidden: the caller puts them back once the whole operation is over.

EnsureTableInitialized #

ensure-table-initialized

Guard against calls before Create has run

Signature:

Private Sub EnsureTableInitialized()

Raises an error when the ListObject reference is Nothing, preventing all table operations from executing on an uninitialised instance.

Throws:

Elements

Wksh #

wksh

Worksheet hosting the ListObject

Signature:

Private Property Get Wksh() As Worksheet

Properties that expose the table structure and cell access.

Returns the parent worksheet of the underlying ListObject.

Returns: Worksheet. The host worksheet.

SheetBlock #

sheet-block

Build a Range on the host worksheet from four numbers

Signature:

Private Function SheetBlock(ByVal firstRow As Long, ByVal firstColumn As Long, _
                            ByVal lastRow As Long, ByVal lastColumn As Long) As Range

The "With sh ... .Range(.Cells(a, b), .Cells(c, d))" shape was written out in five places. One small builder replaces all of them. It is deliberately bound to this table's own worksheet, so it must never be used to build a range on an export target sheet.

Parameters:

Returns: Range. The block.

RowValues #

row-values

Read a one-row Range as a 1-based list of values

Signature:

Private Function RowValues(ByVal blockRng As Range) As Variant

Range.Value hands back a plain value when the Range is one cell, and a two-dimensional array otherwise. This flattens both cases so callers see one shape and do not repeat the test.

Parameters:

Returns: Variant. A 1-based one-dimensional array of cell values.

ColumnValues #

column-values

Read a one-column Range as a 1-based list of values

Signature:

Private Function ColumnValues(ByVal blockRng As Range) As Variant

The column twin of RowValues, and for the same reason: a one-cell Range hands back a plain value where a taller one hands back a two-dimensional array.

Parameters:

Returns: Variant. A 1-based one-dimensional array of cell values.

CellText #

cell-text

Read one cell value as text without raising

Signature:

Private Function CellText(ByVal cellValue As Variant) As String

A cell holding an error value (#N/A and the like) makes CStr raise a type mismatch. Such a header can never match a column name, so it reads as empty text instead of stopping the caller.

Parameters:

Returns: String. The value as text, or empty text.

Table #

table

Underlying ListObject reference

Signature:

Public Property Get Table() As ListObject

Returns the ListObject wrapped by this CustomTable. Raises an error when the reference has not been set.

Returns: ListObject. The wrapped table.

Throws:

Table #

table-set

Assign the ListObject reference

Signature:

Public Property Set Table(ByVal Lo As ListObject)

Stores the ListObject and resets state and backup. Raises an error when Lo is Nothing.

Parameters:

Throws:

PrefixValue #

prefix-value

ID prefix string

Signature:

Public Property Get PrefixValue() As String

Returns the prefix prepended to sequential ID values.

Returns: String. The prefix text.

IdValue #

id-value-set

Assign the ID column name

Signature:

Public Property Let IdValue(ByVal idCol As String)

Parameters:

PrefixValue #

prefix-value-set

Assign the ID prefix string

Signature:

Public Property Let PrefixValue(ByVal idPrefix As String)

Parameters:

FindColumn #

find-column

Find one column in the header row, reading that row once

Signature:

Private Function FindColumn(ByVal headerRng As Range, _
                            ByVal colName As String, _
                            ByVal strictSearch As Boolean, _
                            ByVal matchCase As Boolean) As Long

This is the single lookup behind ColumnIndex, and so behind every column name this class is given. The header row is read once as a block and matched in memory: Range.Find used to cost a worksheet search plus five property reads, and a name that matched nothing repeated the whole search on every call because misses were never remembered. The two match rules are kept exactly as Range.Find applied them: strictSearch True is whole-string equality (xlWhole) and False is a substring test (xlPart); matchCase True compares binary and False compares text. The leftmost match wins, which several callers rely on. Two Range.Find behaviours are deliberately gone. It read "*", "?" and "~" in the search text as wildcards even under xlWhole, and it reused whatever LookIn and SearchOrder the last search anywhere in Excel had used, including the user's own Ctrl+F. Both were faults. The block is NOT kept on the instance: callers write to these sheets without going through this object, so it is read again on the next call.

Parameters:

Returns: Long. The 1-based position within the header row, or 0 when there is none.

ColumnIndex #

column-index

Resolve the 1-based column index for a header name

Signature:

Private Function ColumnIndex(ByVal colName As String, _
                             Optional ByVal strictSearch As Boolean = False, _
                             Optional ByVal matchCase As Boolean = False, _
                             Optional ByVal required As Boolean = False) As Long

Searches the header row for colName and returns its position within that row. Returns 0 when not found. When required is True, raises an error instead of returning 0. The 0-for-not-found answer is deliberate and stays: DataSheet answers -1, and lining the two up would make a "< 0" test written for a DataSheet silently pass for a CustomTable miss.

Parameters:

Returns: Long. The 1-based column index, or 0 when not found.

Throws:

ResolveListColumn #

resolve-list-column

Retrieve the ListColumn object for a header name

Signature:

Private Function ResolveListColumn(ByVal colName As String, _
                                   Optional ByVal strictSearch As Boolean = False, _
                                   Optional ByVal matchCase As Boolean = False, _
                                   Optional ByVal required As Boolean = False) As ListColumn

Centralises ListColumn retrieval so every caller benefits from the same guards. Returns Nothing when not found unless required is True.

Parameters:

Returns: ListColumn. The resolved column, or Nothing.

Throws:

Modify

AddShiftTracker #

add-shift-tracker

Record that worksheet row insertion was used

Signature:

Private Sub AddShiftTracker(Optional ByVal value As String = "Yes")

Methods that add, remove, or reorganise table rows and columns.

Persists a hidden name on the worksheet so future operations know to delete entire worksheet rows instead of just ListRows.

Parameters:

HasShiftTracker #

has-shift-tracker

Check whether worksheet row insertion was previously used

Signature:

Private Function HasShiftTracker() As Boolean

Reads the hidden name from the worksheet to determine whether prior operations used worksheet row insertion.

Returns: Boolean. True when the shift tracker is active.

Resize #

resize

Resize the table by adding or removing rows

Signature:

Private Sub Resize(Optional ByVal shouldAdd As Boolean = False, _
                   Optional ByVal insertShift As Boolean = True, _
                   Optional ByVal totalRowCount As Long = 0, _
                   Optional ByVal nbRows As Long = 5, _
                   Optional ByVal includeIds As Boolean = True, _
                   Optional ByVal forceShift As Boolean = False)

Dispatches to AddTableRows or RemoveEmptyDataRows depending on the shouldAdd flag, then optionally renumbers the ID column.

Parameters:

AddTableRows #

add-table-rows

Physically add rows to the ListObject

Signature:

Private Sub AddTableRows(ByVal lo As ListObject, ByVal nbRows As Long, ByVal insertShift As Boolean)

Inserts worksheet rows below the table when insertShift is True (protecting stacked tables), otherwise extends the ListObject range directly. Resets state after the operation.

Parameters:

EnsureSelectionOnTableSheet #

ensure-selection-on-table-sheet

Ensure the provided range belongs to the table worksheet

Signature:

Private Sub EnsureSelectionOnTableSheet(ByVal targetCell As Range)

Validates that targetCell is not Nothing and resides on the same worksheet as the wrapped ListObject.

Parameters:

Throws:

SelectionRowNumbers #

selection-row-numbers

Resolve worksheet row numbers intersecting the table

Signature:

Private Function SelectionRowNumbers(ByVal targetCell As Range, _
                                     ByVal restrictToDataRows As Boolean) As BetterArray

Returns a BetterArray of worksheet row numbers where targetCell intersects the table scope. When restrictToDataRows is True, only data rows are considered; otherwise the full table range is used.

Parameters:

Returns: BetterArray. Row numbers, or Nothing when no intersection.

GetTotalFormulaColumns #

get-total-formula-columns

Count the number of columns containing formulas

Signature:

Private Function GetTotalFormulaColumns() As Long

Scans the first data row and counts cells that have formulas. Used to determine the baseline non-empty cell count for row removal. This used to be a Sub with a ByRef argument, and its only caller wrapped that argument in brackets, which passes a copy: the answer never came back and the count was always zero. A Function cannot be called that way.

Returns: Long. The number of columns whose first data cell holds a formula.

RemoveEmptyDataRows #

remove-empty-data-rows

Delete data rows that are effectively empty

Signature:

Private Sub RemoveEmptyDataRows(ByVal lo As ListObject, _
                                Optional ByVal totalRowCount As Long, _
                                Optional ByVal forceShift As Boolean)

A row is empty when its non-empty cell count is at or below totalRowCount. The first data row is always kept: it carries the formulas and the formats every new row is built from.

The body is read once and every row is answered once, into a flag array both branches below read.

WHICH BRANCH RUNS

With the shift tracker on, or forceShift, the empty rows go as ENTIRE worksheet rows, so a table stacked under this one is pulled up with them. Without it the table closes over its own rows and nothing outside its columns moves. That difference is the promise each branch carries and the two stacked table tests pin it.

WHY THE TABLE BRANCH LOOKS AT THE BOTTOM FIRST

The rows a resize drops sit at the bottom of the table nearly every time: Add rows pads 199 at a time and a resize takes the pad back. Shrinking the table over that block and clearing it is one operation. Deleting the same block a ListRow at a time is one Excel call per row, measured at about 2.5 ms a row on macOS -- 1.2 s for 500 rows, and a pad of a few thousand was the whole of the wait a user felt on the linelist Resize button. Column count, formulas, dropdowns, conditional formatting and other sheets reading the table all left that figure where it was, so the per-row calls were the cost and nothing else was. Rows left empty between filled ones still go one at a time, and there are seldom many.

Parameters:

RowIsEmpty #

row-is-empty

Whether one row of the block counts as empty

Signature:

Private Function RowIsEmpty(ByRef bodyValues As Variant, ByVal rowIndex As Long, _
                            ByVal tabColumnCount As Long, _
                            ByVal rowCount As Long) As Boolean

Counts the filled cells of the row and compares the count with the threshold. The count stops as soon as it passes the threshold, so a filled row costs a few cells rather than the whole width.

What counts as filled is exactly what CountA counted: a formula that returns "" reads back from a block as "", not as Empty, so it counts on both sides.

Parameters:

THE BLOCK COMES IN BY REFERENCE ON PURPOSE

A Variant holding an array is COPIED when it is passed ByVal, and this is called once per row. Measured with ByVal, a trim of 597 rows over 60 columns took 0.160 s where the same work takes 0.016 s here, and the whole-row branch came out slower than the row-by-row delete it replaced. Every routine that reads the block takes it ByRef for that reason.

Returns: Boolean. True when the row counts as empty.

LastKeptRowOf #

last-kept-row

The last row of the block that stays

Signature:

Private Function LastKeptRowOf(ByRef bodyValues As Variant, ByVal tabRowCount As Long, _
                               ByVal tabColumnCount As Long, _
                               ByVal rowCount As Long) As Long

Answers the highest row that does not count as empty, and 1 when every row under the first is empty. Everything after it is the trailing block.

Parameters:

Returns: Long. A row index between 1 and tabRowCount.

DropTrailingRows #

drop-trailing-rows

Shrink the table over its trailing empty rows in one operation

Signature:

Private Function DropTrailingRows(ByVal lo As ListObject, ByVal firstBodyRow As Long, _
                                  ByVal lastKeptRow As Long, ByVal tabRowCount As Long, _
                                  ByVal tabColumnCount As Long) As Boolean

Resizes the ListObject to the rows that stay and clears the block it let go. The cells keep their values, their formats and their dropdowns until they are cleared, and the row-by-row delete this replaces took all three, so the clear is what makes the two the same. Nothing outside the table columns moves, which is the promise the table branch carries.

Answers False when Excel refuses the resize, and the caller then walks the rows one at a time exactly as before. A refused clear does not turn the answer back: the table has already let the rows go by then, and walking them again would delete rows that are no longer inside it.

Parameters:

Returns: Boolean. True when the table was shrunk.

DeleteWorksheetRows #

delete-worksheet-rows

Delete the empty rows as entire worksheet rows, in one call

Signature:

Private Sub DeleteWorksheetRows(ByVal lo As ListObject, ByVal sh As Worksheet, _
                                ByRef bodyValues As Variant, _
                                ByVal tabRowCount As Long, ByVal tabColumnCount As Long, _
                                ByVal rowCount As Long, ByVal firstBodyRow As Long)

The empty rows are gathered and deleted in ONE call. Deleting them one at a time made Excel shift the whole sheet up once per row, and a sheet holding a few thousand spare rows spent minutes on it. A multi-area range costs a single shift however many areas it carries.

What goes into the Union is a RUN of touching rows rather than a row. Union grows dearer as the range it already holds grows, and the rows a resize drops are one run nearly every time, so the old shape paid thousands of Union calls to build a single area.

The walk runs from the bottom so a deletion does not move the rows still to come. When Excel refuses the delete, the fallback walks the table itself, which is what a stacked layout sometimes needs. It is slow and it only runs when the one-shot delete was refused.

Parameters:

AppendRowRun #

append-row-run

Add one run of touching worksheet rows to the range being gathered

Signature:

Private Sub AppendRowRun(ByRef deadRows As Range, ByVal sh As Worksheet, _
                         ByVal firstRow As Long, ByVal lastRow As Long)

Parameters:

DeleteRowsThroughTable #

delete-rows-through-table

Delete the empty rows one at a time through the ListObject

Signature:

Private Sub DeleteRowsThroughTable(ByVal lo As ListObject, ByRef bodyValues As Variant, _
                                   ByVal tabRowCount As Long, ByVal tabColumnCount As Long, _
                                   ByVal rowCount As Long)

The fallback of RemoveEmptyDataRows. It walks from the bottom so an index stays good after the row below it goes, and it raises when the table refuses a delete, which is the signal a caller reads as stacked tables.

Parameters:

Throws:

DataRowCount #

data-row-count

Count the number of data rows in the table

Signature:

Private Function DataRowCount() As Long

Returns: Long. The data row count (excluding headers), or 0 when empty.

DataColumnCount #

data-column-count

Count the number of columns in the table

Signature:

Private Function DataColumnCount() As Long

Returns: Long. The column count.

EnsureRowCapacity #

ensure-row-capacity

Guarantee the table has at least the requested data rows

Signature:

Private Sub EnsureRowCapacity(ByVal requiredRows As Long, ByVal insertShift As Boolean)

Parameters:

EnsureColumnCapacity #

ensure-column-capacity

Guarantee the table has exactly the requested column count

Signature:

Private Sub EnsureColumnCapacity(ByVal requiredColumns As Long)

Adds or removes columns until the table matches requiredColumns. Trims excess columns using a descending loop so index shifts do not skip columns.

Parameters:

TrimTableRows #

trim-table-rows

Shrink the table to the requested number of data rows

Signature:

Private Sub TrimTableRows(ByVal targetRows As Long)

Resizes the ListObject so only targetRows data rows remain. Exits when the table already has fewer rows than the target.

Parameters:

SourceRowCount #

source-row-count

Derive the data row count from an import source

Signature:

Private Function SourceRowCount(ByVal impTab As Object) As Long

Reads the DataRange from the supplied CustomTable or DataSheet and returns the number of data rows. Returns 0 when the source is empty.

Parameters:

Returns: Long. The number of source data rows.

FormatIdValue #

format-id-value

Build an ID string from a prefix and numeric part

Signature:

Private Function FormatIdValue(ByVal prefixText As String, ByVal numericPart As Long) As String

Parameters:

Returns: String. The formatted ID value.

SortHelper #

sort-helper

Dispatch to the appropriate sort strategy

Signature:

Private Sub SortHelper(ByVal colName As String, _
                       Optional ByVal directSort As Boolean = True, _
                       Optional ByVal strictSearch As Boolean = True)

Parameters:

SortOnFirst #

sort-on-first

Sort by grouping rows by first occurrence of each value

Signature:

Private Sub SortOnFirst(ByVal colName As String, Optional ByVal strictSearch As Boolean = True)

Adds a temporary "__number" column, assigns ascending numbers based on first-seen order of each distinct value, sorts the table on that column, then removes it. This groups identical values together while preserving the original order of first appearance.

Parameters:

RemoveHelperColumn #

remove-helper-column

Drop the temporary sort column whatever happened

Signature:

Private Sub RemoveHelperColumn(ByVal lo As ListObject, ByVal helperColumn As Long)

The helper column must go on every path out of SortOnFirst. A failure to remove it must not replace the error that brought us here, so the attempt is protected.

Parameters:

SortSimple #

sort-simple

Sort the table on a single column in ascending order

Signature:

Private Sub SortSimple(ByVal colName As String, Optional ByVal strictSearch As Boolean = True)

Parameters:

DataExchange

ImportObject #

import-object

Import column-matched data from a source object

Signature:

Private Sub ImportObject(ByVal impTab As Object, _
                         Optional ByVal pasteAtBottom As Boolean = False, _
                         Optional ByVal strictColumnSearch As Boolean = False, _
                         Optional ByVal insertShift As Boolean = True, _
                         Optional ByVal formatHeaders As Object = Nothing)

Iterates the source headers, finds matching columns in this table, and copies data column by column. Columns not found are recorded and read back through ImportColumnsNotFound. Optionally preserves formatting for specified headers. Trims the table when insertShift is False, and only then: on the default True branch the table grows to fit the source but never shrinks.

Parameters:

ImportFormat #

import-format

Copy formatting from a source range to a destination range

Signature:

Private Sub ImportFormat(ByVal impRng As Range, ByVal currRng As Range)

Transfers comments, comment threads, interior colour, font colour, bold, and italic from each cell in impRng to the corresponding cell in currRng. Uses error suppression for Excel versions that do not support CommentThreaded. This is deliberately NOT a straight format copy: the interior colour is only carried over when the source is not white and the font colour only when the source is not black. xlPasteFormats would be faster and would not do the same thing, so the routine stays a per-cell copy. What it stopped doing is paying for each cell more than once.

Parameters:

ImportAll #

import-all

Replace the entire table with imported data

Signature:

Private Sub ImportAll(ByVal impTab As Object)

Replaces all headers and data with the content of the supplied object, discarding the current structure entirely. Trims the table to one row, adjusts column count to match the source, then overwrites headers and data body.

Parameters:

UnhideHiddenColumns #

unhide-hidden-columns

Unhide all hidden columns before import/export

Signature:

Private Sub UnhideHiddenColumns()

Import and export both write across the whole table, and a hidden column has to take its share, so every column is made visible first. The indexes of the columns that were hidden are recorded so ReturnBackHiddenColumns can put them back afterwards. The whole header band is tested first: EntireColumn.Hidden answers False when nothing is hidden, True when everything is and Null when it is mixed, so the common case costs one round trip instead of three per column.

ReturnBackHiddenColumns #

return-back-hidden-columns

Restore previously hidden columns after import/export

Signature:

Private Sub ReturnBackHiddenColumns()

Re-hides columns that were recorded by UnhideHiddenColumns. Uses error suppression as a safeguard against stale column indexes.

RestoreHiddenAndReset #

restore-hidden-and-reset

Put the hidden columns back and drop the transient state

Signature:

Private Sub RestoreHiddenAndReset()

The pair was written out three times, on both exits of Import and at the end of Export.

Checkings

LogInfo #

log-info

Log a diagnostic message

Signature:

Private Sub LogInfo(ByVal label As String, _
                    Optional ByVal scope As Byte = checkingNote)

Diagnostic logging and error handling.

Adds a message to the internal Checking object, creating it on first use. The checkCounter provides a unique key for each entry.

Parameters:

LogAndTrace #

log-and-trace

Log a message and print the debug trace

Signature:

Private Sub LogAndTrace(ByVal label As String, _
                        Optional ByVal scope As Byte = checkingNote)

The "log, show the trace, then leave" tail was written out on the failure paths of Import, ImportColumnsNotFound and Export.

Parameters:

ShowDebug #

show-debug

Print a debug trace to the Immediate window

Signature:

Private Sub ShowDebug()

ThrowError #

throw-error

Raise a ProjectError-based exception

Signature:

Private Sub ThrowError(ByVal errNumb As Long, ByVal errorMessage As String)

Wrapper around Err.Raise that standardises the source to "CustomTable" for consistent stack traces.

Parameters:

Throws:

Seal #

seal

Seal the instance so setup setters can no longer be written

Signature:

Public Sub Seal()

GuardNotSealed #

guard-not-sealed

Guard a setup setter against writes after sealing

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

Parameters:

Used in (33 file(s))