CustomTable

Wraps an Excel ListObject with convenience methods for column lookup, row insertion and deletion, sorting, validation, import/export, and diagnostic logging. The class maintains a header-index cache for fast repeated column lookups, supports snapshot-based rollback during import, and tracks hidden-column state so that Find-based operations see all columns. Consumers interact through the ICustomTable interface. An optional ID column provides dictionary-style keyed access to individual rows.

Depends on: BetterArray, Checking, HiddenNames, IDropdownLists, checking objects

Version: 1.0 (2026-02-09)

StateManagement

save-table-snapshot #

Save a snapshot of the current table state

Signature:

Private Sub SaveTableSnapshot(Optional ByVal returnHidden As Boolean = True)

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 to ensure full coverage.

Parameters:

returnHidden: Optional Boolean. When True, re-hides columns after capture. Defaults to True.

restore-table-snapshot #

Restore the table from a previously saved snapshot

Signature:

Private Sub RestoreTableSnapshot()

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

Factory

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 ICustomTable

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: ICustomTable. A fully initialised CustomTable instance.

Throws:

ProjectError.ObjectNotInitialized When Lo is Nothing.

Depends on:

BetterArray
Checking

Elements

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.

header-range #

Header row Range of the CustomTable

Signature:

Private Property Get HeaderRange() As Range

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

Returns: Range. The header row range.

data-range #

Range of a column or the entire data body

Signature:

Private 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.

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 of the attached ListObject

Signature:

Private Property Get Name() As String

Returns the cached name of the ListObject assigned during creation.

Returns: String. The table name.

cell-range #

A specific cell in the CustomTable

Signature:

Private 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.

value-range #

Locate the cell Range by column and key

Signature:

Private 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 #

Retrieve a cell value by column and key

Signature:

Private 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, or when the cell value cannot be converted to string.

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

insert-rows-at #

Insert rows at the selected position

Signature:

Private 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.

delete-rows-at #

Delete rows intersecting the selection

Signature:

Private 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.

add-ids #

Fill the ID column with sequential values

Signature:

Private 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.

add-rows #

Add rows to the CustomTable

Signature:

Private 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.

remove-rows #

Remove empty rows from the CustomTable

Signature:

Private 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. 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.

set-validation #

Set dropdown validation on a column

Signature:

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

Applies data validation from the supplied IDropdownLists 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: IDropdownLists. 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.

set-value #

Change the value of one specific cell

Signature:

Private 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.

rename-column #

Rename a column header within the table

Signature:

Private 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 and resets the lookup cache. Raises an error when newName is empty.

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 #

Clear all non-formula, non-key columns

Signature:

Private Sub Clean()

Iterates every column in the table and clears the contents of those that do not contain formulas. Key columns are preserved since they are identified by their header, not by content.

sort #

Sort the table on one or more columns

Signature:

Private 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)

Sorts in ascending order on the specified column, then on each column in colList. When directSort is True, uses a simple Range.Sort; when False, groups rows by first occurrence of each distinct value.

Parameters:

colName: Optional String. Column header to sort on. Defaults to vbNullString.
colList: Optional Object. BetterArray of additional column headers. Defaults to Nothing.
directSort: Optional Boolean. When True, sorts directly. Defaults to True.
strictSearch: Optional Boolean. When True, uses exact column matching. Defaults to True.

DataExchange

import #

Import data from a DataSheet or CustomTable

Signature:

Private 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.

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.

import-all #

Replace the entire table with imported data

Signature:

Private Sub ImportAll(ByVal impTab As Object, Optional ByVal insertShift As Boolean = False)

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:

impTab: Object. A DataSheet or CustomTable to import from.
insertShift: Optional Boolean. When True, inserts worksheet rows. Defaults to False.

import-columns-not-found #

Columns not matched during the last import

Signature:

Private 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.

has-columns-not-imported #

Whether the last import had unmatched columns

Signature:

Private 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 the table to a worksheet

Signature:

Private 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.

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.

Checkings

has-checkings #

Whether the table has logged diagnostic messages

Signature:

Private Property Get HasCheckings() As Boolean

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

Returns: Boolean. True when diagnostic entries exist.

checking-values #

Retrieve the diagnostic log entries

Signature:

Private Property Get CheckingValues() As Object

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

Returns: Object. An IChecking instance, or Nothing.

Internal members (not exported)

StateManagement

reset-caches #

Reset transient caches after structural changes

Signature:

Private Sub ResetCaches()

Cache, backup, and guard helpers that keep internal state consistent. Rebuilds the header index cache so column lookups remain accurate after columns are added, removed, or renamed.

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.

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.

cache-key-for-header #

Generate a unique cache key for header lookups

Signature:

Private Function CacheKeyForHeader(ByVal colName As String, _
                                   ByVal strictSearch As Boolean, _
                                   ByVal matchCase As Boolean) As String

Combines the column name with the search mode flags so that different search configurations stay isolated in the cache.

Parameters:

colName: String. The column name to look up.
strictSearch: Boolean. Whether to use exact matching.
matchCase: Boolean. Whether to match case.

Returns: String. The generated cache key.

try-get-cached-index #

Attempt to retrieve a cached column index

Signature:

Private Function TryGetCachedIndex(ByVal cacheKey As String, ByRef cachedValue As Long) As Boolean

Searches the lightweight BetterArray cache for a matching key. When found, populates cachedValue with the stored column index.

Parameters:

cacheKey: String. The cache key to look up.
cachedValue: ByRef Long. Populated with the column index on hit.

Returns: Boolean. True when found in cache, False otherwise.

store-header-index #

Store a successful column lookup in the cache

Signature:

Private Sub StoreHeaderIndex(ByVal cacheKey As String, ByVal columnIndexValue As Long)

Persists a successful lookup so subsequent queries avoid a Range.Find call. Ignores zero or negative column indexes.

Parameters:

cacheKey: String. The cache key to store.
columnIndexValue: Long. The column index to cache.

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:

ProjectError.ObjectNotInitialized When the ListObject is Nothing.

Factory

self #

Current object instance

Signature:

Public Property Get Self() As ICustomTable

Returns the current instance cast to ICustomTable. Used internally by the factory method to return an interface reference.

Returns: ICustomTable. Reference to the current object.

Elements

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.

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:

ProjectError.ObjectNotInitialized When the ListObject is Nothing.

table-set #

Assign the ListObject reference

Signature:

Public Property Set Table(ByVal Lo As ListObject)

Stores the ListObject, resets caches and backup, and caches the table name. Raises an error when Lo is Nothing.

Parameters:

Lo: ListObject. The table to assign.

Throws:

ProjectError.ObjectNotInitialized When Lo is Nothing.

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.

id-value-set #

Assign the ID column name

Signature:

Public Property Let IdValue(ByVal idCol As String)

Parameters:

idCol: String. Column name to use as the row key.

prefix-value-set #

Assign the ID prefix string

Signature:

Public Property Let PrefixValue(ByVal idPrefix As String)

Parameters:

idPrefix: String. Prefix to prepend to sequential ID values.

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 using Range.Find, consulting the cache first for speed. Returns 0 when not found. When required is True, raises an error instead of returning 0.

Parameters:

colName: String. Column name to look up.
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.
required: Optional Boolean. When True, raises an error if not found. Defaults to False.

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

Throws:

ProjectError.InvalidArgument When colName is empty and required is True.
ProjectError.ObjectNotInitialized When the header range is Nothing and required is True.
ProjectError.ElementNotFound When the column is not found and required is True.

column-exists #

Check whether a column exists in the table

Signature:

Private 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.

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:

colName: String. Column header to resolve.
strictSearch: Optional Boolean. When True, uses exact matching. Defaults to False.
matchCase: Optional Boolean. When True, matching is case-sensitive. Defaults to False.
required: Optional Boolean. When True, raises an error if not found. Defaults to False.

Returns: ListColumn. The resolved column, or Nothing.

Throws:

ProjectError.ElementNotFound When the column is not found and required is True.

Modify

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:

value: Optional String. Tracker value ("Yes" or "No"). Defaults to "Yes".

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 the table by adding or removing rows

Signature:

Private Sub Resize(Optional ByVal addRows 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 addRows flag, then optionally renumbers the ID column.

Parameters:

addRows: Optional Boolean. When True, adds rows; when False, removes empty rows. Defaults to False.
insertShift: Optional Boolean. When True, inserts worksheet rows. Defaults to True.
totalRowCount: Optional Long. Non-empty cell threshold for row removal. Defaults to 0.
nbRows: Optional Long. Number of rows to add. Defaults to 5.
includeIds: Optional Boolean. When True, renumbers the ID column. Defaults to True.
forceShift: Optional Boolean. When True, forces worksheet row deletion. Defaults to False.

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 caches after the operation.

Parameters:

lo: ListObject. The table to extend.
nbRows: Long. Number of rows to add.
insertShift: Boolean. When True, inserts worksheet rows.

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:

targetCell: Range. The range to validate.

Throws:

ProjectError.InvalidArgument When targetCell is Nothing, has no worksheet, or belongs to a different sheet.

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:

targetCell: Range. The selection to intersect.
restrictToDataRows: Boolean. When True, limits to data rows only.

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

get-total-formula-columns #

Count the number of columns containing formulas

Signature:

Private Sub GetTotalFormulaColumns(ByRef rowCount 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.

Parameters:

rowCount: ByRef Long. Populated with the formula column count.

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)

Scans from the bottom so deletions do not disturb subsequent indexes. A row is considered empty when its non-empty cell count is at or below totalRowCount. Deletes entire worksheet rows when the shift tracker is active or forceShift is True; otherwise removes ListRows directly. Always preserves the first data row to keep formulas and templates.

Parameters:

lo: ListObject. The table to clean.
totalRowCount: Optional Long. Non-empty cell threshold.
forceShift: Optional Boolean. When True, forces worksheet row deletion.

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.

data-column-count #

Count the number of columns in the table

Signature:

Private Function DataColumnCount() As Long

Returns: Long. The column count.

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:

requiredRows: Long. Minimum number of data rows needed.
insertShift: Boolean. When True, inserts worksheet rows.

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:

requiredColumns: Long. Target number of columns.

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:

targetRows: Long. Desired data row count.

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:

impTab: Object. A CustomTable or DataSheet to measure.

Returns: Long. The number of source data rows.

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:

prefixText: String. Prefix text, or empty for numeric-only IDs.
numericPart: Long. The sequential number.

Returns: String. The formatted ID value.

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:

colName: String. Column header to sort on.
directSort: Optional Boolean. When True, sorts directly. Defaults to True.
strictSearch: Optional Boolean. When True, uses exact column matching. Defaults to True.

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:

colName: String. Column header to sort on.
strictSearch: Optional Boolean. When True, uses exact column matching. Defaults to True.

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:

colName: String. Column header to sort on.
strictSearch: Optional Boolean. When True, uses exact column matching. Defaults to True.

DataExchange

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 cell by cell. Columns not found are tracked in NOTIMPORTEDCOLUMNS. Optionally preserves formatting for specified headers. Trims the table when insertShift is False.

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.

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.

Parameters:

impRng: Range. Source range with formatting to copy.
currRng: Range. Destination range in this table.

unhide-hidden-columns #

Unhide all hidden columns before import/export

Signature:

Private Sub UnhideHiddenColumns()

Range.Find does not include hidden columns, so all columns must be made visible before import or export operations. Records the indexes of previously hidden columns so ReturnBackHiddenColumns can restore them afterwards.

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.

Checkings

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 IChecking object, creating it on first use. The checkCounter provides a unique key for each entry.

Parameters:

label: String. The message to record.
scope: Optional Byte. Severity level from CheckingScope. Defaults to checkingNote.

show-debug #

Print a debug trace to the Immediate window

Signature:

Private Sub ShowDebug()

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:

errNumb: Long. ProjectError code.
errorMessage: String. Descriptive message.

Throws:

ProjectError. Always raises the specified error.

Interface

ICustomTable_HeaderRange #

Signature:

Private Property Get ICustomTable_HeaderRange() As Range

Delegated members satisfying the ICustomTable contract.

Used in (34 file(s))