LLSheets

Answers questions about ONE sheet of a linelist: the dictionary row that describes it, its type, its table name, how many variables it holds, and where a variable sits on it. Contains says whether the dictionary knows the sheet, RowIndex gives its dictionary row, SheetInfo answers its type or its table name, NumberOfVars counts its variables, DataBounds answers the first and the last row and column of its data, ContainsControl says whether a control appears on it, and VariableAddress gives the address of one variable. Every read checks the state it needs and raises a ProjectError whose message names the sheet.

WHAT THE INSTANCE HOLDS

The dictionary row is held per sheet name. Before a held row is handed out, the cell it points at is read once and compared with the name it should carry, so a dictionary whose rows moved gives a fresh row.

The variable count is held per sheet name until InvalidateCaches. LLdictionary.Prepare inserts geo rows, so an object built before preparation answers over the dictionary as it stood then.

The sheet-type and table-name column indexes are held too.

The preparation flag is asked of the dictionary on every call that needs it. LLdictionary.Prepared holds a True answer itself and drops it in every routine that can turn True back into False. This class has no way to see a Clean or an AddColumn run on the dictionary, so it keeps no copy of that flag.

Depends on: BetterArray, DataSheet, LLdictionary, LLVariables

Factory

Create #

Instantiate a sheet helper bound to the supplied dictionary

Signature:

Public Function Create(ByVal dict As LLdictionary) As LLSheets

Parameters:

  • dict: LLdictionary providing sheet metadata.

Returns: LLSheets fully initialised helper.

Throws:

  • ProjectError.ObjectNotInitialized When dict is Nothing or has no DataSheet.
  • ProjectError.ElementNotFound When a required dictionary column is missing.
Sheet Queries

Contains #

Determine whether the dictionary includes the supplied sheet

Signature:

Public Function Contains(ByVal sheetName As String) As Boolean

The match is case-insensitive, the same rule Excel applies to worksheet names. A sheet stored as "Data" and asked for as "data" is the same sheet, and LLDataEntry refuses to build a sheet this call reports as missing.

Parameters:

  • sheetName: String. Sheet identifier to locate.

Returns: True when the sheet exists.

RowIndex #

Lookup the worksheet row hosting the sheet metadata

Signature:

Public Function RowIndex(ByVal sheetName As String) As Long

Answers 0 for a sheet the dictionary does not hold, and for the header row. The sheet-name range is captured with its header, so this used to hand back the header row for a call asking about the header text, and LLDataEntry places rows from the answer.

Parameters:

  • sheetName: String. Sheet identifier to locate.

Returns: Worksheet row index for the sheet entry, or 0 when not found.

SheetInfo #

Fetch additional metadata about a sheet

Signature:

Public Function SheetInfo(ByVal sheetName As String, Optional ByVal info As Byte = SheetInfoType.SheetInfoSheetType) As String

Parameters:

  • sheetName: String. Sheet identifier.
  • info: Byte. Requested metadata selector, from SheetInfoType.

Returns: Requested metadata value; raises when the selector is unknown.

Throws:

  • ProjectError.ElementNotFound When the sheet or the metadata column is missing.
  • ProjectError.InvalidArgument When the selector is unknown.

NumberOfVars #

Count the variables associated with a sheet

Signature:

Public Function NumberOfVars(ByVal sheetName As String) As Long

The count is read once per sheet name and held. One AutoFilter pass answers it, and DataBounds asks for it twice per sheet on the LLDataEntry path, which asks again on its own.

Parameters:

  • sheetName: String. Sheet identifier.

Returns: Long number of variables present on the sheet.

Throws:

  • ProjectError.ElementNotFound When the sheet is missing from the dictionary.

DataBounds #

Compute the data boundaries for a sheet layout

Signature:

Public Function DataBounds(ByVal sheetName As String, _
                            Optional ByVal Bound As Byte = SheetBound.RowSart) As Long

The four bounds of a horizontal sheet: top row 8, bottom row 8 + 201, left column 1, right column 1 + variables - 1. The four bounds of a vertical sheet: top row 4, bottom row 4 + variables - 1, left column 5, right column 5.

Parameters:

  • sheetName: String. Sheet identifier.
  • Bound: Byte. Selector from the SheetBound enumeration.

Returns: Long representing the requested bound.

Throws:

  • ProjectError.InvalidArgument When the sheet type or the selector is unknown.

ContainsControl #

Test whether a sheet exposes a control of the requested type

Signature:

Public Function ContainsControl(ByVal sheetName As String, _
                                 Optional ByVal ribbonControl As String = "list_auto", _
                                 Optional ByVal colName As String = COL_CONTROL) As Boolean

The default control value is "list_auto". LLDataEntry asks for "geo1", which the dictionary holds only after LLdictionary.AppendGeoLines has run.

Parameters:

  • sheetName: String. Sheet identifier.
  • Control: String. Control type to search for.
  • colName: String. Column hosting the control types. Defaults to "control".

Returns: True when the control type exists on the sheet.

Throws:

  • ProjectError.ElementNotFound When the sheet or the control column is missing.

VariableAddress #

Build an Excel address pointing to the variable slot on its sheet

Signature:

Public Function VariableAddress(ByVal varName As String, Optional ByVal onSheet As String = vbNullString) As String

The address is built from the DICTIONARY worksheet on purpose. .Address returns an A1 string that carries no sheet name, so only the row and the column number reach the result, and the sheet prefix is added here.

A horizontal sheet gets a relative address and a vertical sheet gets an absolute one. Formulas and VarWriter drop both straight into formulas, so the two forms behave differently when Excel fills them down; that is the shape those two callers expect.

Parameters:

  • varName: String. Variable identifier.
  • onSheet: Optional String. Sheet where the reference will be consumed; suppresses sheet prefix when matching.

Returns: String Excel address for the variable.

Throws:

  • ProjectError.ObjectNotInitialized When the dictionary is missing or unprepared.
  • ProjectError.InvalidArgument When varName is empty or the sheet type is unknown.
  • ProjectError.ElementNotFound When the variable is absent from the dictionary.
Sheet Row Resolution

InvalidateCaches #

Drop everything this object holds about the worksheet

Signature:

Public Sub InvalidateCaches()

The way out for a caller that changed the dictionary behind this object. The variable counts and the sheet-name range are held for the life of the instance, so a caller that adds or removes dictionary rows calls this to be answered from the worksheet again.

Internal members (not exported)

Public Accessors

Dictionary #

Retrieve the backing dictionary

Signature:

Public Property Get Dictionary() As LLdictionary

Returns: LLdictionary reference captured during initialisation.

Dictionary #

Store the backing dictionary and prime cached lookups

Signature:

Public Property Set Dictionary(ByVal dict As LLdictionary)

Parameters:

Seal #

Prevent further changes to creation-only setters

Signature:

Public Sub Seal()

GuardNotSealed #

Guard creation-only setters after sealing

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

Parameters:

Throws:

Sheet Queries

CountVariables #

count-variables

Count the dictionary rows that belong to one sheet

Signature:

Private Function CountVariables(ByVal sheetName As String) As Long

Parameters:

Returns: Long number of variables on that sheet.

BoundValue #

bound-value

Compute one bound from a sheet type that is already resolved

Signature:

Private Function BoundValue(ByVal sheetName As String, _
                            ByVal sheetType As String, _
                            ByVal Bound As Byte) As Long

The sheet type is validated once, here. VariableAddress resolves the type for its own use and hands it down, so the address path reads the type cell once where it used to read it twice.

Parameters:

Returns: Long representing the requested bound.

Throws:

State Validation

EnsureDictionaryReady #

Confirm the backing dictionary and cached range are initialised

Signature:

Private Sub EnsureDictionaryReady(Optional ByVal requirePrepared As Boolean = False)

LLdictionary.Prepared is asked on every call that requires preparation. It holds a True answer on the dictionary itself, so the read costs one Boolean once the dictionary is prepared, and it stays honest when another object undoes the preparation.

Parameters:

Throws:

EnsureSheetExists #

Raise a descriptive error when the sheet does not exist

Signature:

Private Sub EnsureSheetExists(ByVal sheetName As String)

EnsureSheetRow raises for a sheet the dictionary does not hold, so this is the same check under the name the two callers read better with.

Parameters:

Throws:

EnsureSheetRow #

Retrieve the worksheet row for the sheet or raise when absent

Signature:

Private Function EnsureSheetRow(ByVal sheetName As String) As Long

Parameters:

Returns: Long worksheet row index.

Throws:

EnsureControlColumnExists #

Guarantee the control column exists before filtering

Signature:

Private Sub EnsureControlColumnExists(ByVal colName As String)

Parameters:

Throws:

EnsureValidSheetName #

Validate that the supplied sheet name is usable

Signature:

Private Sub EnsureValidSheetName(ByVal sheetName As String)

Parameters:

Throws:

Lookup Helpers

SheetInfoValue #

Resolve sheet metadata based on the selector

Signature:

Private Function SheetInfoValue(ByVal sheetName As String, ByVal infoSelector As Byte) As String

Parameters:

Returns: String metadata value.

Throws:

HostSheet #

host-sheet

Worksheet holding the dictionary

Signature:

Private Property Get HostSheet() As Worksheet

Held on the instance. Reaching it through dict.Data.Wksh costs two crossings before any work, on every cell this class resolves.

Returns: Worksheet

CellText #

cell-text

Read one cell value as text

Signature:

Private Function CellText(ByVal cellValue As Variant) As String

A dictionary cell can hold an error value (#N/A, #REF!) or a Null, and CStr raises on both.

Parameters:

Returns: String. The text, or an empty string when there is none.

SheetTypeColumnIndex #

Cache the column index for sheet type lookups

Signature:

Private Function SheetTypeColumnIndex() As Long

Returns: Long worksheet column index.

TableNameColumnIndex #

Cache the column index for table name lookups and raise if missing

Signature:

Private Function TableNameColumnIndex() As Long

Returns: Long worksheet column index.

Throws:

VariablesProvider #

Lazily initialise the LLVariables helper for address resolution

Signature:

Private Function VariablesProvider() As LLVariables

Returns: LLVariables cached instance.

ColumnIndex #

Resolve a dictionary column, and raise when it is absent

Signature:

Private Function ColumnIndex(ByVal colName As String) As Long

DataSheet.ColumnIndex raises with shouldExist:=True, so the answer that comes back is always a real column.

Parameters:

Returns: Long worksheet column index.

Throws:

Sheet Row Resolution

ResolveSheetRow #

sheet-row

Worksheet row that describes one sheet

Signature:

Private Function ResolveSheetRow(ByVal sheetName As String) As Long

The row is held per sheet name. A held row is checked against the cell it points at before it is handed out: LLdictionary.Prepare inserts rows, and handing out a moved row would answer for the WRONG sheet.

Parameters:

Returns: Long. The worksheet row, or 0 when the dictionary has no such sheet.

RowStillHolds #

row-still-holds

Check one cell against the sheet name it should hold

Signature:

Private Function RowStillHolds(ByVal rowNum As Long, ByVal sheetName As String) As Boolean

Parameters:

Returns: Boolean. True when the cell still reads as sheetName.

FarthestOf #

farthest-of

Take the farther of a counted bound and the last written index

Signature:

Private Function FarthestOf(ByVal countedBound As Long, _
                            ByVal sheetName As String) As Long

VarWriter places a variable at the "column index" its own dictionary row carries: for an HList sheet that number is the WORKSHEET COLUMN, for a VList sheet the WORKSHEET ROW (VarWriter.VarRange). Counting the variables and adding the count to the start only agrees with that when the indexes run 1, 2, 3 with no gap. A dictionary whose indexes skip a number - the shape a setup takes as soon as variables are removed or reordered - writes variables past the counted edge, and the data table was then built short of them: the columns were on the sheet, outside the ListObject, invisible to the filter, the export and every reader that walks the table.

So the counted bound becomes a floor. A dictionary with no index column, or one holding nothing numeric, answers with the count as before.

Parameters:

Returns: Long. The farther of the two.

LastVariableIndex #

last-variable-index

The largest column index the sheet's variables carry

Signature:

Private Function LastVariableIndex(ByVal sheetName As String) As Long

Held per sheet name beside the count, and dropped by the same invalidations. A dictionary missing the index column answers 0, which leaves every bound where the count put it.

Parameters:

Returns: Long. The largest index, or 0 when there is none to read.

MaxColumnIndex #

max-column-index

Read the index column of one sheet and take its largest number

Signature:

Private Function MaxColumnIndex(ByVal sheetName As String) As Long

Parameters:

Returns: Long. The largest numeric index, or 0.

CacheSlot #

cache-slot

Find or create the cache slot of one sheet name

Signature:

Private Function CacheSlot(ByVal sheetName As String) As Long

Names are matched without case, which is the rule the lookups use. A dictionary holds a handful of sheets, so a walk over the slots is cheaper than anything that would index them.

Parameters:

Returns: Long. The slot number, from 1.

FindSheetCell #

Locate the range corresponding to a sheet name

Signature:

Private Function FindSheetCell(ByVal sheetName As String) As Range

LookIn, SearchOrder and SearchDirection are all passed. Excel reuses whatever the last Find anywhere in the application used when they are left out, and that includes the user's own Ctrl+F dialog. The header row is refused here, so every caller gets the same rule: the sheet-name range is captured with its header, and the header text is a column title rather than a sheet.

Parameters:

Returns: Range when found; Nothing otherwise.

SanitizeSearchTerm #

Escape Excel wildcard characters before using Range.Find

Signature:

Private Function SanitizeSearchTerm(ByVal value As String) As String

Excel reads *, ?, [, ] and # in the search text as patterns, so a sheet named with one of them would match the wrong row. LLdictionary keeps its own copy beside the search it serves; a class cannot reach a standard module, so the two live where they are used.

Parameters:

Returns: String sanitized search term.

BuildSheetPrefix #

Produce a quoted sheet prefix for addresses

Signature:

Private Function BuildSheetPrefix(ByVal sheetName As String) As String

Parameters:

Returns: String sheet prefix including trailing exclamation mark.

ResetCaches #

Reset cached lookups following dictionary changes

Signature:

Private Sub ResetCaches()

ValidateDictionary #

Ensure the supplied dictionary exposes the required columns

Signature:

Private Sub ValidateDictionary(ByVal dict As LLdictionary)

Parameters:

Throws:

EnsureColumnExists #

Validate that a dictionary column is present

Signature:

Private Sub EnsureColumnExists(ByVal dict As LLdictionary, ByVal colName As String)

Parameters:

Throws:

Error Handling

ThrowError #

Raise a ProjectError-coded runtime error

Signature:

Private Sub ThrowError(ByVal errNumb As Long, ByVal errorMessage As String)

Parameters:

Used in (18 file(s))