DataSheet

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

---

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:

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.

---

DataEndRow #

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.

---

DataEndColumn #

data-end-column

Last column containing data

Signature:

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

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.

---

ColumnIndex #

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.

---

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.

---

Clean #

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.

---

FilterData #

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.

---

FiltersData #

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

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.

---

ImportFormat #

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

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.

---

HasCheckings #

has-checkings

Whether the DataSheet has logged diagnostic messages

Signature:

Private Property Get HasCheckings() As Boolean

Returns: Boolean. True when diagnostic entries exist.

---

CheckingValues #

checking-values

Retrieve the diagnostic log entries

Signature:

Private Property Get CheckingValues() As Object

Returns: Object. An IChecking instance, or Nothing.

---