ICustomTable

Defines the public contract for the CustomTable class, exposing read/write helpers around an Excel ListObject including column and cell access, row manipulation, sorting, validation, import/export, and diagnostic logging.

Elements

HeaderRange #

Header row Range of the CustomTable

Signature:

Public Property Get HeaderRange() As Range: End Property

Properties that expose the table structure and cell access. Returns the Range covering the header row of the underlying ListObject.

Returns: Range. The header row range.

IdValue #

ID column name

Signature:

Public Property Get IdValue() As String: End Property

Returns the name of the column designated as the unique row identifier.

Returns: String. The ID column name, or vbNullString when none is set.

DataRange #

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 Range for the specified column, or the entire data body when colName is "all". 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:

Public Property Get Name() As String: End Property

Returns the cached name of the ListObject assigned during creation.

Returns: String. The table name.

CellRange #

A specific cell in the CustomTable

Signature:

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

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 #

Retrieve a cell value by column and key

Signature:

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

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.

ValueRange #

Range of a cell identified by column and key

Signature:

Public Property Get 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.

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.

Modify

AddIds #

Add sequential IDs to the ID column

Signature:

Public Sub AddIds(): End Sub

Methods that add, remove, or reorganise table rows and columns. Fills the ID column with sequential values, optionally prefixed. Silently exits when no ID column is configured.

AddRows #

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. 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 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. 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 dropdown validation on a column

Signature:

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

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.

SetValue #

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.

Parameters:

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

RenameColumn #

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 and resets the lookup cache.

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.

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)

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.

Clean #

Clear all non-formula, non-key columns

Signature:

Public Sub Clean(): End Sub

Clears the contents of every data column except those containing formulas or serving as the key column.

InsertRowsAt #

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.

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

Parameters:

targetCell: Range. Selection identifying 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.

DataExchange

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 into this table. When keepSourceHeaders is True, replaces all headers and data entirely. Creates a snapshot before import and rolls back on error.

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.

ImportAll #

Replace the entire table with imported data

Signature:

Public Sub ImportAll(ByVal impTab As Object): End Sub

Replaces all headers and data with the content of the supplied object, discarding the current structure entirely.

Parameters:

impTab: Object. A DataSheet or CustomTable to import from.

RestoreTableSnapshot #

Restore the table from a saved snapshot

Signature:

Public Sub RestoreTableSnapshot(): End Sub

Reverts the table headers and data to the state captured by SaveTableSnapshot. Used for rollback after a failed import.

SaveTableSnapshot #

Save a snapshot of the current table state

Signature:

Public Sub SaveTableSnapshot(): End Sub

Captures the current headers and data rows so they can be restored later if an import fails.

ImportColumnsNotFound #

Columns not matched during the last import

Signature:

Public Property Get ImportColumnsNotFound() As BetterArray

Returns a BetterArray of column headers from the source that had no match in this table. Only available after a successful import.

Returns: BetterArray. Unmatched column headers, or Nothing.

HasColumnsNotImported #

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 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 to the target worksheet, optionally creating a ListObject on the output range.

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

HasCheckings #

Whether the table has logged diagnostic messages

Signature:

Public Property Get HasCheckings() As Boolean: End Property

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

Returns: Boolean. True when diagnostic entries exist.

CheckingValues #

Retrieve the diagnostic log entries

Signature:

Public Property Get CheckingValues() As Object: End Property

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

Returns: Object. An IChecking instance, or Nothing.