DataSheet

Provides low-level data manipulation on a worksheet with a table-like structure identified by a starting row and column. Unlike CustomTable, DataSheet does not require a ListObject — it works directly with cell ranges. Supports column lookup, filtering (single and multi-column), import/export to other DataSheets or CustomTables, and formatting transfer for designated columns. Consumers interact through the IDataSheet interface.

Depends on: Checking, BetterArray, HiddenNames, Formated columns list, checking objects

Version: 1.0 (2026-02-09)

Factory

create #

Create a DataSheet wrapper around a worksheet

Signature:

Public Function Create(ByVal sh As Worksheet, ByVal startLn As Long, _
                       ByVal startCl As Long, _
                       Optional ByVal forceEndRow As Boolean = False, _
                       Optional ByVal objName As String = vbNullString) As IDataSheet

Entry point for creating DataSheet instances. Wraps the supplied worksheet with convenience methods for column lookup, filtering, import/export, and formatting transfer. The data structure is identified by a starting row and column; end row and column are computed dynamically. Unlike CustomTable, no ListObject is required.

Parameters:

sh: Worksheet. The worksheet hosting the data.
startLn: Long. The 1-based header row index.
startCl: Long. The 1-based starting column index.
forceEndRow: Optional Boolean. When True, scans all columns to find the last row. Defaults to False.
objName: Optional String. Object name. Defaults to the worksheet name.

Returns: IDataSheet. A fully initialised DataSheet instance.

Throws:

ProjectError.ObjectNotInitialized When sh is Nothing.
ProjectError.InvalidArgument When startLn or startCl is less than 1.

Depends on:

Checking
BetterArray

Elements

wksh #

Worksheet backing the DataSheet

Signature:

Public Property Get Wksh() As Worksheet

Properties that expose the worksheet layout and column access. Returns the worksheet that hosts the table-like data.

Returns: Worksheet. The host worksheet.

data-start-row #

First row of data (header row)

Signature:

Public Property Get DataStartRow() As Long

Returns: Long. The 1-based header row index.

data-start-column #

First column of data

Signature:

Public Property Get DataStartColumn() As Long

Returns: Long. The 1-based starting column index.

name #

Name of the DataSheet

Signature:

Private Property Get Name() As String

Returns the assigned object name. When no name was provided during creation, defaults to the worksheet name.

Returns: String. The DataSheet name.

data-end-row #

Last row containing data

Signature:

Private Property Get DataEndRow() As Long

Dynamically computes the last occupied row. When StrictEnd is True, scans all non-formula columns for the furthest row. Guarantees at least one data row below the header.

Returns: Long. The 1-based last data row index.

data-end-column #

Last column containing data

Signature:

Private Property Get DataEndColumn() As Long

Returns: Long. The 1-based last column index.

header-range #

Header row Range

Signature:

Public Property Get HeaderRange() As Range

Returns the Range spanning from the start column to the end column on the header row.

Returns: Range. The header row range.

column-exists #

Check whether a column exists in the DataSheet

Signature:

Private Function ColumnExists(ByVal colName As String, _ 
                              Optional ByVal strictSearch As Boolean = True, _
                              Optional ByVal matchCase As Boolean = True) As Boolean

Searches the header row for the specified column name using Range.Find. Returns False for empty column names.

Parameters:

colName: String. Column header to search for.
strictSearch: Optional Boolean. When True, uses exact whole-string matching. Defaults to True.
matchCase: Optional Boolean. When True, matching is case-sensitive. Defaults to True.

Returns: Boolean. True when the column is found.

column-index #

Resolve the column index for a header name

Signature:

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

Returns the worksheet column index, or the data-range-relative index when inDataRange is True. Returns -1 when the column is not found and shouldExist is False; raises an error when shouldExist is True.

Parameters:

colName: String. Column header to look up.
inDataRange: Optional Boolean. When True, returns the index relative to the data range. Defaults to False.
shouldExist: Optional Boolean. When True, raises an error if not found. Defaults to False.
strictSearch: Optional Boolean. When True, uses exact whole-string matching. Defaults to True.
matchCase: Optional Boolean. When True, matching is case-sensitive. Defaults to True.

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

Throws:

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

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 = True, _ 
                              Optional ByVal matchCase As Boolean = True) As Range

Returns a data Range for the specified column, or the entire data body when colName is "all". When strictSearch is False, partial matching is used. Use strictSearch when column names share common text.

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 matching. Defaults to True.
matchCase: Optional Boolean. When True, matching is case-sensitive. Defaults to True.

Returns: Range. The requested data range.

rename-column #

Rename a header in the DataSheet

Signature:

Public Sub RenameColumn(ByVal currentName 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 or the column is not found.

Parameters:

currentName: 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.
ProjectError.ElementNotFound When the column is not found.

Operations

clean #

Clear all non-formula columns

Signature:

Private Sub Clean()

Filtering, cleaning, import, and export operations. Iterates every column in the header row and clears the contents of those that do not contain formulas in their first data cell.

filter-data #

Filter data on a single column and return the result

Signature:

Private Function FilterData(ByVal varName As String, _
                            ByVal criteriaName As String, _
                            ByVal returnedColumnName As String, _
                            Optional ByVal includeHeaders As Boolean = False, _
                            Optional ByVal matchCase As Boolean = True) _

Applies an AutoFilter on varName with criteriaName, extracts the visible rows from returnedColumnName into a temporary output area, reads them into a BetterArray, then cleans up. The filter is removed after the operation, leaving the DataSheet untouched.

Parameters:

varName: String. Column to filter on.
criteriaName: String. Filter criteria to apply.
returnedColumnName: String. Column whose values to return ("all" for all columns).
includeHeaders: Optional Boolean. When True, includes the header row. Defaults to False.
matchCase: Optional Boolean. When True, column lookup is case-sensitive. Defaults to True.

Returns: BetterArray. The filtered values.

filters-data #

Filter data on multiple columns and return the result

Signature:

Private Function FiltersData(ByVal varData As BetterArray, _
                             ByVal criteriaData As BetterArray, _
                             ByVal returnedColumnsData As BetterArray) As BetterArray

Applies multiple AutoFilters (one per entry in varData/criteriaData) and returns the visible values from each column in returnedColumnsData as a two-dimensional BetterArray. Exits when varData and criteriaData have different lengths.

Parameters:

varData: BetterArray. Column names to filter on.
criteriaData: BetterArray. Criteria corresponding to each column.
returnedColumnsData: BetterArray. Columns whose values to return.

Returns: BetterArray. A two-dimensional array of filtered values.

import #

Import data from another DataSheet or CustomTable

Signature:

Private Sub Import(ByVal importData As Object, Optional ByVal strictColumnSearch As Boolean = True)

Cleans this DataSheet, then iterates the source headers and copies matching column data. Formula columns are preserved. Applies format import for registered formatting columns after the data copy.

Parameters:

importData: Object. A DataSheet or CustomTable to import from.
strictColumnSearch: Optional Boolean. When True, uses case-sensitive column matching. Defaults to True.

import-format #

Import column formatting from a source object

Signature:

Private Sub ImportFormat(ByVal importData As Object, Optional ByVal matchColumnsCase As Boolean = True)

Copies visual formatting (colours, font weight, italic) from the source for all columns registered via AddFormatsColumns. Logs a warning when the source and destination row counts differ.

Parameters:

importData: Object. A DataSheet or CustomTable to copy formatting from.
matchColumnsCase: Optional Boolean. When True, uses case-sensitive column matching. Defaults to True.

export #

Export the DataSheet to a workbook

Signature:

Private Sub Export(ByVal Wkb As Workbook, Optional ByVal filteredVarName As String = "__all__", _
                   Optional ByVal filteredCondition As String = "<>", _ 
                   Optional ByVal Hide As Long = xlSheetHidden, _ 
                   Optional ByVal includeNames As Boolean = False)

Creates (or clears) a worksheet in the target workbook with the same name as this DataSheet, then writes all data. Optionally filters rows on a single column before export. The exported sheet is hidden by default. Applies formatting for registered columns and optionally exports hidden names.

Parameters:

Wkb: Workbook. Destination workbook.
filteredVarName: Optional String. Column used for filtering. Defaults to "all" (no filter).
filteredCondition: Optional String. Filter criteria. Defaults to "<>".
Hide: Optional Long. Worksheet visibility after export. Defaults to xlSheetHidden.
includeNames: Optional Boolean. When True, exports hidden names alongside data. Defaults to False.

Checkings

has-checkings #

Whether the DataSheet has logged diagnostic messages

Signature:

Private Property Get HasCheckings() As Boolean

Returns: Boolean. True when diagnostic entries exist.

checking-values #

Retrieve the diagnostic log entries

Signature:

Private Property Get CheckingValues() As Object

Returns: Object. An IChecking instance, or Nothing.

Internal members (not exported)

Factory

self #

Current object instance

Signature:

Public Property Get Self() As IDataSheet

Returns: IDataSheet. Reference to the current object.

wksh-set #

Assign the worksheet reference

Signature:

Public Property Set Wksh(ByVal sh As Worksheet)

Parameters:

sh: Worksheet. The worksheet to assign.

data-start-row-set #

Assign the header row index

Signature:

Public Property Let DataStartRow(ByVal startLn As Long)

Parameters:

startLn: Long. The 1-based header row index.

data-start-column-set #

Assign the starting column index

Signature:

Public Property Let DataStartColumn(ByVal startCl As Long)

Parameters:

startCl: Long. The 1-based starting column index.

strict-end-set #

Assign the strict end row flag

Signature:

Public Property Let StrictEnd(ByVal forceEnd As Boolean)

Parameters:

forceEnd: Boolean. When True, scans all columns for the last row.

name-set #

Assign the DataSheet name

Signature:

Public Property Let Name(ByVal objName As String)

Parameters:

objName: String. The name to assign.

Elements

strict-end #

Whether to scan all columns for the last row

Signature:

Public Property Get StrictEnd() As Boolean

Returns: Boolean. True when all columns are scanned.

matched-column-name #

Return the actual header text for a column name match

Signature:

Private Function MatchedColumnName(ByVal colName As String, _
                                Optional ByVal strictSearch As Boolean = True, _
                                Optional ByVal matchCase As Boolean = True) As String

Searches the header row and returns the exact text of the matched cell. Returns vbNullString when no match is found.

Parameters:

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

Returns: String. The matched header text, or vbNullString.

Operations

remove-autofilter #

Turn off AutoFilter on the host worksheet

Signature:

Private Sub RemoveAutoFilter()

Reliably removes any active AutoFilter. Called before and after filtering operations to guarantee a clean state.

apply-format #

Copy formatting from one Range to another

Signature:

Private Sub ApplyFormat(ByVal actRng As Range, ByVal impRng As Range)

Transfers interior colour, font colour, bold, and italic from each cell in impRng to the corresponding cell in actRng.

Parameters:

actRng: Range. Destination range to format.
impRng: Range. Source range with formatting to copy.

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)

Parameters:

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

Throws:

ProjectError. Always raises the specified error.

throw-error-empty-sheet #

Raise an error for a Nothing worksheet

Signature:

Private Sub ThrowErrorEmptySheetGiven()

Throws:

ProjectError.ObjectNotInitialized Always.

throw-error-start-number #

Raise an error for an invalid start row or column

Signature:

Private Sub ThrowErrorStartNumber(Optional ByVal startNumber As Long = -1, _
                                  Optional ByVal startLabel As String = "start row")

Parameters:

startNumber: Optional Long. The invalid value. Defaults to -1.
startLabel: Optional String. Label identifying the parameter. Defaults to "start row".

Throws:

ProjectError.InvalidArgument Always.

throw-error-unfound-column #

Raise an error for a missing column

Signature:

Private Sub ThrowErrorUnFoundColumn(ByVal colName As String)

Parameters:

colName: String. The column name that was not found.

Throws:

ProjectError.ElementNotFound Always.

Interface

IDataSheet_Wksh #

Signature:

Private Property Get IDataSheet_Wksh() As Worksheet

Delegated members satisfying the IDataSheet contract.

add-formats-columns #

Register columns for formatting import/export

Signature:

Private Sub IDataSheet_AddFormatsColumns(ByVal matchColumnsCase As Boolean, ByVal resetColumns As Boolean, ParamArray lsCols() As Variant)

Adds the specified column names to the internal formatting list so that Import and Export preserve their visual formatting. Validates each column exists before registering it.

Parameters:

matchColumnsCase: Boolean. When True, uses case-sensitive column matching.
resetColumns: Boolean. When True, clears the existing list before adding.
lsCols: ParamArray Variant. Column names to register for formatting.

Used in (21 file(s))