DataSheet

Works with a block of data on a worksheet, marked out by a start row and a start column. The block is plain cells and needs no ListObject. ColumnExists and ColumnIndex look a column up by its header, HeaderRange and DataRange answer the ranges, RenameColumn rewrites a header, FilterData and FiltersData filter on one column or on several, and Import, Export and ImportFormat move values and formats between this block and another DataSheet or a CustomTable.

WHAT THE INSTANCE HOLDS

The start row and the start column are sealed at creation. The rest of the geometry is worked out on each call: DataEndRow, DataEndColumn and the header row are read again every time they are asked for, so a caller can insert and delete rows and columns while this object stays alive. Within one call the header row is read as a single block.

Depends on: Checking, BetterArray, HiddenNames

Version: 1.0 (2026-02-09)

Factory

Create #

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 DataSheet

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: DataSheet. 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 #

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.

DataStartRow #

data-start-row

First row of data (header row)

Signature:

Public Property Get DataStartRow() As Long

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

DataStartColumn #

data-start-column

First column of data

Signature:

Public Property Get DataStartColumn() As Long

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

Name #

name

Name of the DataSheet

Signature:

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

DataEndRow #

data-end-row

Last row containing data

Signature:

Public 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. This is read fresh every single time. It is the value that moves most: the class itself collapses it in Clean and grows it back in Import, and callers insert and delete body rows without telling this object.

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

DataEndColumn #

data-end-column

Last column containing data

Signature:

Public Property Get DataEndColumn() As Long

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

HeaderRange #

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.

ColumnExists #

column-exists

Check whether a column exists in the DataSheet

Signature:

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

ColumnIndex #

column-index

Resolve the column index for a header name

Signature:

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

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

RenameColumn #

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

FilterData #

filter-data

Filter data on a single column and return the result

Signature:

Public 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 and reads the visible rows of returnedColumnName straight into a BetterArray. The filter is removed on every path, including the failure paths, so the DataSheet is left as it was found. Nothing is written to the worksheet. The routine used to copy the visible rows into a scratch block two columns past the last data column, read it back and clear it; see VisibleBlock for why that block was sized wrong and what it left behind.

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.

Throws:

  • ProjectError.ElementNotFound When a column name is not found.

FiltersData #

filters-data

Filter data on multiple columns and return the result

Signature:

Public 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 with an empty array when varData and criteriaData have different lengths. The filters are removed on every path, including the failure paths. Nothing is written to the worksheet. Each returned column used to be pasted into its own scratch column past the last data column and the block measured back off the sheet, which had three ways to answer wrong: it gated BOTH the read and the clear on ONE cell being non-empty, so a blank first value (normal for a short-label column) dropped the whole block; it took the bottom edge from the first scratch column only; and it took the right edge from the header row, so it stopped short when the last column started blank and ran into the user's own content when anything already sat further right.

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.

Throws:

  • ProjectError.ElementNotFound When a column name is not found.

Import #

import

Import data from another DataSheet or CustomTable

Signature:

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

ImportFormat #

import-format

Import column formatting from a source object

Signature:

Public 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

Export the DataSheet to a workbook

Signature:

Public Sub Export(ByVal toWkb 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:

  • toWkb: 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

HasCheckings #

has-checkings

Whether the DataSheet has logged diagnostic messages

Signature:

Public Property Get HasCheckings() As Boolean

Returns: Boolean. True when diagnostic entries exist.

CheckingValues #

checking-values

Retrieve the diagnostic log entries

Signature:

Public Property Get CheckingValues() As Object

Returns: Object. An Checking instance, or Nothing.

AddFormatsColumns #

add-formats-columns

Register columns for formatting import/export

Signature:

Public Sub 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.
Internal members (not exported)

Factory

Wksh #

wksh-set

Assign the worksheet reference

Signature:

Public Property Set Wksh(ByVal sh As Worksheet)

Parameters:

DataStartRow #

data-start-row-set

Assign the header row index

Signature:

Public Property Let DataStartRow(ByVal startLn As Long)

Parameters:

DataStartColumn #

data-start-column-set

Assign the starting column index

Signature:

Public Property Let DataStartColumn(ByVal startCl As Long)

Parameters:

StrictEnd #

strict-end-set

Assign the strict end row flag

Signature:

Public Property Let StrictEnd(ByVal forceEnd As Boolean)

Parameters:

Name #

name-set

Assign the DataSheet name

Signature:

Public Property Let Name(ByVal objName As String)

Parameters:

Elements

StrictEnd #

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.

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.

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.

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.

FindColumn #

find-column

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

Signature:

Private Function FindColumn(ByVal colName As String, _
                            ByVal strictSearch As Boolean, _
                            ByVal matchCase As Boolean, _
                            Optional ByRef matchedHeader As String) As Long

This is the single lookup behind ColumnExists, ColumnIndex, RenameColumn and AddFormatsColumns. Each of them used to build the header Range and run Range.Find on its own, so one ColumnIndex call built the range twice and searched twice. Here the row is read once and matched in memory. The out argument carries the third answer: the header text as the sheet spells it. The four 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 header block is deliberately 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 worksheet column of the match, or -1 when there is none.

Operations

Clean #

clean

Clear all non-formula columns

Signature:

Private Sub Clean()

Filtering, cleaning, import, and export operations.

Clears the body of every column that does not hold a formula in its first data cell. Formula columns are generated and must survive an import. The column range is built from the loop position. Looking each header text back up paid for a full column lookup on a column whose place was already known, and it had two faults: a blank header inside the range made the lookup raise and killed the whole import, and two columns sharing a header cleared the first twice and the second never. The last row is read once, before the first clear. Reading it per column shrinks it as soon as the first column is emptied, so the columns after it were left with old values below the first row.

RemoveAutoFilter #

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. Known, and left as it is on purpose: this clears EVERY filter on the host sheet, including one the user set by hand on a ListObject of their own. The two filter routines below need the sheet unfiltered to read the right rows, and a stray filter left behind hides data from the user, which is worse than losing their view. Do not narrow this sweep without a way to prove the sheet ends up unfiltered on every path.

AreaValues #

area-values

Read one solid Range as a 1-based two-dimensional array

Signature:

Private Function AreaValues(ByVal blockRng As Range) As Variant

Range.Value hands back a plain value when the Range is a single cell and a two-dimensional array otherwise. This gives callers one shape so they do not repeat the test.

Parameters:

Returns: Variant. A two-dimensional array, 1 To rows, 1 To columns.

VisibleBlock #

visible-block

Read a filtered Range as one solid 1-based two-dimensional array

Signature:

Private Function VisibleBlock(ByVal visibleRng As Range, _
                              ByVal firstColumn As Long, _
                              ByVal lastColumn As Long) As Variant

SpecialCells(xlCellTypeVisible) hands back one Area per unbroken run of visible rows, and Range.Rows.Count on such a Range answers for Areas(1) ONLY. Anything sized from that number is short as soon as the filter leaves two blocks, which is what used to break the two routines below: they sized a scratch block on the sheet from it, pasted the WHOLE selection into it (Excel anchors the paste at the top-left and writes everything, a smaller destination neither truncates nor raises), read the short block back and cleared the short block. So the caller got a truncated list and the rows past the block stayed on the worksheet. Every area is read with one .Value instead and the areas are stacked in the order Excel hands them out, which is top to bottom. Nothing is written to the worksheet, so there is no block to clear, no clipboard to reset and no fixed spot past the last column to collide with. BetterArray.FromExcelRange cannot do this job: it reads .Row, .Column, .Rows.Count and .Columns.Count off the Range - all Areas(1) answers - and then rebuilds one contiguous block from those bounds, which would pull in the hidden rows sitting inside the first area. Only the ROW runs are taken from the areas. A column hidden inside the block splits the areas sideways as well, and each row run then shows up more than once, so a run already taken is never taken twice and every run is read across the WHOLE requested column span. That also keeps a hidden column from stopping the routine: Copy raises 1004 on areas that do not line up.

Parameters:

Returns: Variant. A two-dimensional array, 1 To total visible rows, 1 To columns.

StoreBlock #

store-block

Put a block into a BetterArray with the shape callers expect

Signature:

Private Sub StoreBlock(ByVal target As BetterArray, ByRef block As Variant)

This reproduces exactly what BetterArray.FromExcelRange did for the block the two filter routines used to build, and callers depend on all four cases: one cell gives a one-element array, a single column gives a FLAT one-dimensional array, a single row gives a flat one-dimensional array, and anything wider gives a two-dimensional array (BA_MULTIDIMENSION, pinned by TestDataSheet). The array is handed over in ONE Items assignment. Pushing row by row would reallocate and copy the whole array on every element.

Parameters:

ApplyFormat #

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. This is not a straight format copy and xlPasteFormats would not do the same thing: only a colour that is not the default travels, so the destination keeps its own white background and black text, and borders, number formats and font names are left alone.

IsImportable #

is-importable

Test whether an object can be imported from

Signature:

Private Function IsImportable(ByVal importData As Object, ByVal whatIsImported As String) As Boolean

Import and ImportFormat both accept a DataSheet or a CustomTable and nothing else. The argument is declared As Object on purpose, so the test is on TypeName. Passing anything else logs and returns False; it does not raise, and a test drives that path with a Range.

Parameters:

Returns: Boolean. True when the object can be read from.

HasFormatColumns #

has-format-columns

Test whether any column was registered for formatting

Signature:

Private Function HasFormatColumns() As Boolean

TypeName is used and not Is Nothing because it answers both "never created" and "not a BetterArray" in one test.

Returns: Boolean. True when at least one format column is registered.

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 check counter 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 six times on the failure paths of FiltersData, Import, ImportFormat 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)

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:

ThrowErrorEmptySheetGiven #

throw-error-empty-sheet

Raise an error for a Nothing worksheet

Signature:

Private Sub ThrowErrorEmptySheetGiven()

Throws:

ThrowErrorStartNumber #

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:

Throws:

ThrowErrorUnFoundColumn #

throw-error-unfound-column

Raise an error for a missing column

Signature:

Private Sub ThrowErrorUnFoundColumn(ByVal colName As String)

Parameters:

Throws:

Used in (19 file(s))