IDataSheet

Defines the public contract for the DataSheet class, exposing low-level worksheet data access, column lookup, filtering, import/export, and diagnostic logging for table-like structures that do not rely on ListObjects.

Elements

Wksh #

Worksheet backing the DataSheet

Signature:

Public Property Get Wksh() As Worksheet: End Property

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

Returns: Worksheet. The host worksheet.

DataStartRow #

First row of data (header row)

Signature:

Public Property Get DataStartRow() As Long: End Property

Returns the 1-based row index where the data starts.

Returns: Long. The header row index.

DataStartColumn #

First column of data

Signature:

Public Property Get DataStartColumn() As Long: End Property

Returns the 1-based column index where the data starts.

Returns: Long. The starting column index.

Name #

Name of the DataSheet

Signature:

Public Property Get Name() As String: End Property

Returns the assigned object name, or the worksheet name when none was provided during creation.

Returns: String. The DataSheet name.

DataEndRow #

Last row containing data

Signature:

Public Property Get DataEndRow() As Long: End Property

Dynamically computes the last occupied row. When StrictEnd is True, scans all non-formula columns for the furthest row.

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

DataEndColumn #

Last column containing data

Signature:

Public Property Get DataEndColumn() As Long: End Property

Dynamically computes the rightmost occupied column in the header row.

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

HeaderRange #

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 #

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.

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 #

Resolve the column index for a header name

Signature:

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

Returns the worksheet column index (or 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 the column is 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.

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 = 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 includeHeaders is True, the header row is included.

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

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

Clean #

Clear all non-formula columns

Signature:

Public Sub Clean() : End Sub

Filtering, cleaning, import, and export operations. Clears the contents of every data column that does not contain formulas.

AddFormatsColumns #

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.

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.

FilterData #

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) As BetterArray

Applies an AutoFilter on varName with criteriaName, extracts the visible rows from returnedColumnName, and returns them as a BetterArray. The filter is removed after the operation.

Parameters:

  • varName: String. Column to filter on.
  • criteriaName: String. Filter criteria to apply.
  • returnedColumnName: String. Column whose values to return.
  • 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 #

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.

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:

Public Sub Import(ByVal importedData As Object, Optional ByVal strictColumnSearch As Boolean = True): End Sub

Cleans this DataSheet, then copies matching columns from the source. Preserves formula columns. Applies format import for registered formatting columns.

Parameters:

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

ImportFormat #

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.

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:

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 before export. The exported sheet is hidden by default.

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 #

Whether the DataSheet 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.