LLVariables

Answers questions about ONE variable of a dictionary: the row it sits on, the value of any column on that row, and the named fields the rest of the tree reads most. Contains says whether a variable name is in the dictionary, Index gives its row, Value reads any column of that row and SetValue writes one, CellRange answers the cell, and SheetName, ControlType and TableName answer the three named fields. VariableNames lists every name the dictionary holds.

WHAT THE INSTANCE HOLDS

The variable-name column and the header row are each read once as a block, and every lookup after that is a string compare in memory.

The names are held until InvalidateCaches. LLdictionary.Prepare inserts geo rows, so an object built before preparation answers over the dictionary as it stood then. EventSetup is the one production caller that drops the held names.

The header row is held too. A column the held row cannot answer makes the header row be read again before the answer is "missing", so a column added after this object was built resolves. A column the held row CAN answer is checked with one cell read before its index is handed out, so a header renamed on the worksheet is seen.

Depends on: BetterArray, Checking, DataSheet, LLdictionary

Creation

Create #

create

Build a variables reader over one dictionary

Signature:

Public Function Create(ByVal dict As LLdictionary) As LLVariables

Validates the dictionary before anything else: a missing object, a dictionary with no DataSheet, and a dictionary with no "variable name" column each raise their own error. TestLLVariables pins all three numbers.

Parameters:

  • dict: LLdictionary. The dictionary to read.

Returns: LLVariables. A sealed instance bound to that dictionary.

Throws:

  • ProjectError.InvalidArgument When dict is Nothing.
  • ProjectError.ObjectNotInitialized When the dictionary has no DataSheet.
  • ProjectError.ElementNotFound When the variable-name column is missing.

Dictionary #

dictionary

Dictionary this object reads

Signature:

Public Property Get Dictionary() As LLdictionary

Returns: LLdictionary

Cache management

InvalidateCaches #

Drop everything this object holds about the worksheet

Signature:

Public Sub InvalidateCaches()

The two snapshots and the host worksheet live here. Read the class header for what each one promises.

The way out for a caller that changed the dictionary behind this object: EventSetup calls it after the setup sheets are rebuilt. The search range goes too, so the next read rebuilds it from the dictionary.

Reading

Contains #

contains

Test whether a variable is in the dictionary

Signature:

Public Function Contains(ByVal varName As String, Optional ByVal matchCase As Boolean = True) As Boolean

Case-sensitive by default. LLdictionary.UniqueVarNames normalises variable names during preparation, so the stored case is whatever the user typed, and LLSheets.VariableAddress and LLExport.ResolveVariableChunk both rely on the case-sensitive answer. The match is a string compare in memory, so a name holding *, ?, [, ] or # is matched as written. The Range.Find this replaced had to escape those characters first.

Parameters:

  • varName: String. Variable to look for.
  • matchCase: Optional Boolean. When True, the match is case-sensitive. Defaults to True.

Returns: Boolean. True when the variable is present.

Value #

value

Value of one column for one variable

Signature:

Public Function Value(ByVal colName As String, ByVal varName As String) As String

The argument order is column first. Twenty production sites call it with named arguments, so both names are part of the contract.

Parameters:

  • colName: String. Column header.
  • varName: String. Variable name.

Returns: String. The cell text, or an empty string when either is missing.

SheetName #

Sheet a variable belongs to

Signature:

Public Function SheetName(ByVal varName As String, Optional ByVal matchCase As Boolean = True) As String

Parameters:

  • varName: String. Variable name.
  • matchCase: Optional Boolean. When True, the variable match is case-sensitive. Defaults to True.

Returns: String. The sheet name, or an empty string.

ControlType #

Control type of a variable

Signature:

Public Function ControlType(ByVal varName As String, Optional ByVal matchCase As Boolean = True) As String

Parameters:

  • varName: String. Variable name.
  • matchCase: Optional Boolean. When True, the variable match is case-sensitive. Defaults to True.

Returns: String. The control value, or an empty string.

TableName #

Table a variable belongs to

Signature:

Public Function TableName(ByVal varName As String) As String

The table name column is written by LLdictionary.Prepare, so this answers an empty string on a dictionary that has not been prepared.

Parameters:

  • varName: String. Variable name.

Returns: String. The table name, or an empty string.

CellRange #

cell-range

Cell holding one column of one variable

Signature:

Public Property Get CellRange(ByVal colName As String, ByVal varName As String) As Range

Parameters:

  • colName: String. Column header.
  • varName: String. Variable name.

Returns: Range. The cell, or Nothing when the row or the column is missing.

VariableNames #

List of every variable name in the dictionary

Signature:

Public Function VariableNames() As BetterArray

Built from the snapshot, so it costs no worksheet read once the column has been loaded. Empty cells are left out.

Returns: BetterArray. The names, lower bound 1.

Writing

SetValue #

Write one column of one variable

Signature:

Public Function SetValue(ByVal varName As String, _
                         ByVal colName As String, _
                         ByVal newValue As String, _
                         Optional ByVal onEmpty As Boolean = False) As Boolean

onEmpty tests IsEmpty on the target cell. That is False for a cell holding an empty string and for a formula that returns "", which is the behaviour LinelistSpecs relies on for the "list auto" column. The return value says whether the write happened. A skipped write used to be recorded in a checking object that nothing ever read.

Parameters:

  • varName: String. Variable to write to.
  • colName: String. Column header to write to.
  • newValue: String. The value to write.
  • onEmpty: Optional Boolean. When True, write only into an empty cell. Defaults to False.

Returns: Boolean. True when the value was written.

Throws:

  • ProjectError.ElementNotFound When the variable or the column is missing.

Index #

index

Column index of a variable on its own sheet

Signature:

Public Function Index(ByVal varName As String) As Long

Reads the "column index" column, which LLdictionary.Prepare creates. Before preparation the cell is empty and this raises ElementNotFound; that is the contract LLSheets.VariableAddress turns into its own message.

Parameters:

  • varName: String. Variable name.

Returns: Long. The index stored for that variable.

Throws:

  • ProjectError.ElementNotFound When the column index is missing.
  • ProjectError.InvalidArgument When the stored index is not a number.
Internal members (not exported)

Creation

Dictionary #

dictionary-set

Bind the dictionary, at creation only

Signature:

Public Property Set Dictionary(ByVal dict As LLdictionary)

Parameters:

Seal #

seal

Prevent further changes to creation-only setters

Signature:

Public Sub Seal()

GuardNotSealed #

guard-not-sealed

Refuse a write to a creation-only setter

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

Parameters:

Throws:

Cache management

DropSnapshots #

drop-snapshots

Forget the two blocks and the host worksheet

Signature:

Private Sub DropSnapshots()

EnsureDictionaryReady #

Refuse to read without a dictionary

Signature:

Private Sub EnsureDictionaryReady()

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 cost two crossings before any work, on every cell this class resolved.

Returns: Worksheet

RefreshVariableRange #

Rebuild the range holding the variable names

Signature:

Private Sub RefreshVariableRange()

One path. DataSheet.DataRange resolves the column with shouldExist:=True, so a dictionary without a "variable name" column raises here, and it always hands back at least one data row. The second hand-built path this routine used to carry could never run.

Throws:

EnsureVariableRange #

Build the search range when it is missing

Signature:

Private Sub EnsureVariableRange()

EnsureNamesLoaded #

ensure-names-loaded

Read the variable-name column once

Signature:

Private Sub EnsureNamesLoaded()

One crossing for the whole column. Every Contains, RowIndex, VariableNames and cell resolve is answered from this array afterwards.

ReloadHeaders #

reload-headers

Read the header row once

Signature:

Private Sub ReloadHeaders()

EnsureHeadersLoaded #

ensure-headers-loaded

Read the header row when it has not been read yet

Signature:

Private Function EnsureHeadersLoaded() As Boolean

Says whether it read the sheet. A snapshot built in this very call is already fresh, so the caller can skip both the cell check and the second read.

Returns: Boolean. True when the header row was read in this call.

HeaderColumn #

header-column

Find a column in the header snapshot

Signature:

Private Function HeaderColumn(ByVal colName As String) As Long

Whole string, case-insensitive, leftmost wins. That is the same rule DataSheet.FindColumn applies for the arguments this class used to pass it (strictSearch:=True, matchCase:=False).

Parameters:

Returns: Long. The worksheet column, or 0 when the snapshot does not hold it.

HeaderStillReads #

header-still-reads

Check one header cell against the name it is expected to hold

Signature:

Private Function HeaderStillReads(ByVal columnNumber As Long, ByVal colName As String) As Boolean

One crossing, and it is what stops a stale index. A header renamed on the worksheet leaves the snapshot pointing at a column that now holds something else, and handing that index out would read the wrong column's value.

Parameters:

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

ResolveColumnIndex #

Resolve a dictionary column to a worksheet column

Signature:

Private Function ResolveColumnIndex(ByVal colName As String, Optional ByVal required As Boolean = False) As Long

Answers from the header snapshot, checks the answer with one cell read, and reads the header row again when either step comes up short. The second read is what lets a column added after this object was built resolve.

Parameters:

Returns: Long. The worksheet column, or 0 when it is missing and required is False.

Throws:

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. Every read in this class goes through here. The parameter is called cellValue because this module also holds a public Value function. VBA renormalises every use of an identifier to whichever spelling it saw last, across the whole project.

Parameters:

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

Reading

RowIndex #

row-index

Find the worksheet row of one variable

Signature:

Private Function RowIndex(ByVal varName As String, Optional ByVal matchCase As Boolean = True) As Long

Answers 0 when the variable is absent, and every caller tests for 0. A duplicated variable name answers with the FIRST row that holds it. Range.Find started after the first cell and wrapped, so it used to answer with the second. LLdictionary.UniqueVarNames makes the names unique during preparation, so two equal names are a fault in the sheet either way.

Parameters:

Returns: Long. The worksheet row, or 0 when the variable is absent.

CellRangeInternal #

cell-range-internal

Resolve the cell holding one column of one variable

Signature:

Private Function CellRangeInternal(ByVal colName As String, _
                                   ByVal varName As String, _
                                   ByVal matchCase As Boolean) As Range

Parameters:

Returns: Range. The cell, or Nothing when the row or the column is missing.

ReadColumnValue #

read-column-value

Read one column of one variable as text

Signature:

Private Function ReadColumnValue(ByVal varName As String, _
                                 ByVal colName As String, _
                                 ByVal matchCase As Boolean) As String

The single reader. Value, SheetName, ControlType and TableName are all one line over this; the two argument orders those members expose are the only thing that used to differ between them.

Parameters:

Returns: String. The cell text, or an empty string.

Errors

ThrowError #

throw-error

Raise an error from this class

Signature:

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

Parameters:

Used in (30 file(s))