UpdatedValues

Tracks the setup columns that are watched for a change, and answers whether one of them has been edited.

A setup column is watched when one of the three rows above its header carries a known tag: "watch for update", "translate as text" or "translate as formula". AddColumns reads those tags and writes one row per watched column into the registry table. CheckUpdate flips that row's flag when the user edits the column, and IsUpdated answers the flag. AddSheet registers a worksheet, RemoveLo drops one registry table, ClearUp and DeleteUp empty the registry, and SwitchTagsToNo and SwitchTagsToYes set every flag at once.

ON THE SHEET

The registry worksheet holds ONE registry ListObject, named UpLo_ where sheetkey is the normalised worksheet name. It carries four columns in this order, and both the names and the order are a published format that SetupTranslationsTable reads:

colname | rngname | updated | headername

-| RNG_... | no | watch for update

Each row also has a workbook-level defined name, RNG_

, pointing at the watched column. That name is how a registry row turns back into a Range for the translations run. It is built at build time, and the change handler leaves it alone.

When the registry empties, the table is unlisted and its cells are cleared. The sheet is then free of tables, which is what SetupTranslationsTable reads: it treats every table it finds on that sheet as a registry.

IN MEMORY

The registry is read once, on first use, in a single .Value2 call, into keyed Collections: the composite key "|" points at a row index and a flag, an alias map answers IsUpdated, and a table set lets the change handler leave an unwatched table without reading anything. Scripting.Dictionary is missing on Mac Excel, so these are keyed Collections.

An edit inside a watched table costs four to five COM crossings whatever the registry holds, and the flag cell is written on every one of them. The index answers reads; it never stands in for the cell on a write. The worksheet is the record every other reader of this registry goes to.

TWO READERS, ONE FLAG COLUMN

The translations run resets the flags itself through SwitchTagsToNo. The analysis dropdown refresh resets nothing, so it calls AcknowledgeUpdates: every flag reading "yes" at that moment goes into an in-memory set, and IsUpdated answers False for those keys until the user edits the column again. The set lives in memory alone, so opening the workbook starts it empty.

CREATE WRITES NOTHING

Building an instance binds a worksheet and stops there. Two helper factories build a fresh instance on every ribbon click.

Factory

Create #

create

Build a new watcher bound to the specified registry worksheet.

Signature:

Public Function Create(ByVal sh As Worksheet, Optional ByVal ignoredId As String = vbNullString) As UpdatedValues

Binds the worksheet and nothing else. No cell is read and no table is created until a member that needs the registry runs.

Parameters:

  • sh: Worksheet. Registry worksheet hosting the watcher table.
  • ignoredId: Optional String. Reserved for future use (ignored).

Returns: UpdatedValues. Configured watcher instance.

PublicState

Wksh #

worksheet

Worksheet hosting the watcher registry table.

Signature:

Public Property Get Wksh() As Worksheet

Returns: Worksheet. The registry worksheet.

StatusTracking

IsUpdated #

is-updated

Determine whether the tracked column has been marked as updated.

Signature:

Public Property Get IsUpdated(ByVal checkColName As String) As Boolean

The identifier is matched against the column name, the whole registry label and the defined name, all normalised the same way. So "variable name" and "variable_name" ask the same question, which is what the Dictionary headers need. The answer comes from memory and costs no worksheet call.

Parameters:

  • checkColName: String. Column name, registry label or defined name.

Returns: Boolean. True when the column carries a change nobody has acted on.

AcknowledgeUpdates #

acknowledge-updates

Record every flag that currently reads "yes" as already acted on.

Signature:

Public Sub AcknowledgeUpdates()

The reader that never resets the flag column calls this after it has rebuilt what it had to rebuild. The set is memory only, so a workbook open starts it empty and the first Analysis activation of a session still rebuilds.

CleanupHelpers

DeleteUp #

delete-up

Remove every registry table on the sheet and the defined names they used.

Signature:

Public Sub DeleteUp()

Every ListObject on the registry sheet goes, including the ones this class never created. A workbook built before the single-table layout carries one registry per source table and a name index table beside them; leaving any of them behind would make the translations run read the old rows and the new rows side by side and add every label twice. SetupPreparation calls this before it rebuilds, so this sweep is the whole upgrade.

SwitchTagsToNo #

switch-tags-to-no

Force every registry status flag to the default value.

Signature:

Public Sub SwitchTagsToNo()

SwitchTagsToYes #

switch-tags-to-yes

Force every registry status flag to the updated value.

Signature:

Public Sub SwitchTagsToYes()
Internal members (not exported)

Factory

Seal #

seal

Seal the instance so setup-only setters can no longer be used.

Signature:

Public Sub Seal()

GuardNotSealed #

guard-not-sealed

Raise when a setup-only setter is used after sealing.

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

PublicState

Wksh #

worksheet-set

Bind the watcher to a registry worksheet.

Signature:

Public Property Set Wksh(ByVal sh As Worksheet)

Parameters:

  • sh: Worksheet. The worksheet to assign.

EnsureWorksheet #

ensure-worksheet

Guard that the registry worksheet reference is initialised before use.

Signature:

Private Function EnsureWorksheet() As Worksheet

ColumnRegistration

AddColumns #

add-columns

Register or refresh watched columns for the supplied ListObject.

Signature:

Public Sub AddColumns(ByVal source As ListObject)

Reads the three rows above the header in one call and the header row in one call, keeps the columns whose tag is one of the three known tags, and rewrites the registry rows for this table. A table with no tagged column creates nothing. The flag of a column that was already registered is carried over, so a rebuild does not lose a pending change.

Parameters:

  • source: ListObject. Table to inspect for update tags.

AddSheet #

add-sheet

Register tagged columns for every ListObject on the provided worksheet.

Signature:

Public Sub AddSheet(ByVal targetSheet As Worksheet)

Every table on the sheet is read, and the registry is written once at the end rather than once per table.

RemoveLo #

remove-lo

Remove registry entries associated with the supplied ListObject.

Signature:

Public Sub RemoveLo(ByVal target As ListObject)

MergeTableEntries #

merge-table-entries

Replace the registry rows belonging to one table with the supplied rows.

Signature:

Private Sub MergeTableEntries(ByVal tableName As String, _
                              ByVal wanted As Collection, _
                              ByVal source As ListObject)

Parameters:

  • tableName: String. Source table whose rows are being replaced.
  • wanted: Collection. Rows to keep for that table, empty to drop them all.
  • source: ListObject. Table the defined names point at, Nothing when dropping.

ColumnLabelMatchesTable #

column-label-matches-table

Determine whether a registry column label belongs to the provided ListObject name.

Signature:

Private Function ColumnLabelMatchesTable(ByVal columnLabel As String, _
                                         ByVal tableName As String) As Boolean

ReadTagBlock #

read-tag-block

Read the rows above the header row in one call.

Signature:

Private Sub ReadTagBlock(ByVal headerRow As Range, ByRef block As Variant, ByRef tagRows As Long)

Parameters:

  • headerRow: Range. Header row of the source table.
  • block: Variant. Output, the values of the rows above the header.
  • tagRows: Long. Output, how many rows the block holds.

TagForColumn #

tag-for-column

Read the metadata tag of a column, walking up from the header row.

Signature:

Private Function TagForColumn(ByVal block As Variant, _
                              ByVal tagRows As Long, _
                              ByVal columnCount As Long, _
                              ByVal columnIndex As Long) As String

The walk answers with the first cell holding one of the three known tags. A section title or a note sitting between the tag row and the header is stepped over, and a value that is not a tag never wins. Analysis tables are stacked, so the row three above a header can belong to the table above it; looking for a known tag removes that risk with it.

ShouldWatchColumn #

should-watch-column

Determine whether a column should be monitored based on its tag value.

Signature:

Private Function ShouldWatchColumn(ByVal tagValue As String) As Boolean

Parameters:

  • tagValue: String. Tag retrieved from worksheet metadata.

RegistryWriting

BeginBatch #

begin-batch

Load the registry rows into memory so a build pass can change them.

Signature:

Private Sub BeginBatch()

EndBatch #

end-batch

Write the registry rows back in one call and drop the in-memory index.

Signature:

Private Sub EndBatch()

ReadRegistryEntries #

read-registry-entries

Read every registry row into a collection, in one call.

Signature:

Private Function ReadRegistryEntries() As Collection

WriteRegistryEntries #

write-registry-entries

Write the supplied rows over the registry table in one call.

Signature:

Private Sub WriteRegistryEntries(ByVal entries As Collection)

An empty registry leaves no table behind: the ListObject is unlisted and its cells are cleared. A table always keeps one data row, so an empty table would mean a blank row every reader has to step over.

ResolveRegistry #

resolve-registry

Locate the registry table, building it when asked.

Signature:

Private Function ResolveRegistry(ByVal createWhenMissing As Boolean) As ListObject

CreateRegistryTable #

create-registry-table

Build an empty registry ListObject with the expected headers.

Signature:

Private Function CreateRegistryTable(ByVal sh As Worksheet) As ListObject

EnsureRegistryHeaders #

ensure-registry-headers

Guarantee the registry ListObject exposes the expected header names.

Signature:

Private Sub EnsureRegistryHeaders(ByVal lo As ListObject)

RemoveRegistryTable #

remove-registry-table

Take the registry table off the sheet and clear the cells it held.

Signature:

Private Sub RemoveRegistryTable()

NamedRanges

EnsureNamedRange #

ensure-named-range

Create or refresh the workbook defined name pointing to the watched column.

Signature:

Private Sub EnsureNamedRange(ByVal source As ListObject, _
                             ByVal columnName As String, _
                             ByVal rangeName As String)

RemoveNamedRange #

remove-named-range

Delete a single defined name if it exists in the workbook.

Signature:

Private Sub RemoveNamedRange(ByVal rangeName As String)

RemoveSheetNamedRanges #

remove-sheet-named-ranges

Delete every watcher defined name belonging to the registry sheet, in one pass.

Signature:

Private Sub RemoveSheetNamedRanges()

StatusTracking

CheckUpdate #

check-update

Evaluate a range change against the registered watchers and flag updates.

Signature:

Public Sub CheckUpdate(ByVal tagReference As Variant, ByVal Target As Range)

Every watched column the change covers is flagged, so a paste across two watched columns flips two flags.

The handler only ever writes a flag. Cleanup belongs to AddColumns and AddSheet, which run at build time: a defined name can fail to resolve for a moment while a source table resizes or a sheet is renamed, and deleting a row on that would take the registration away for good.

The cost is four to five worksheet calls whatever the registry holds. A table nobody watches is left after two of them, and the flag cell is written every time a watched column is covered.

Parameters:

  • tagReference: Variant. Kept for the call shape; the table is read from Target.
  • Target: Range. The changed cells to evaluate.

MarkWideTarget #

mark-wide-target

Flag the watched tables a whole-row or whole-column change reaches.

Signature:

Private Sub MarkWideTarget(ByVal changed As Range)

Selecting a worksheet row or column gives a target with no ListObject. Only the watched tables on that sheet are asked, one Intersect each.

MarkColumns #

mark-columns

Flag every watched column of one table that the change covers.

Signature:

Private Sub MarkColumns(ByVal lo As ListObject, ByVal changed As Range)

The header row is read on every edit rather than cached. It is one worksheet call, and it stays right when the user inserts or deletes a column in the source table; a cached position would need an invalidation signal that costs a call to read anyway.

MarkKey #

mark-key

Write the updated flag for one watched column, on every change.

Signature:

Private Sub MarkKey(ByVal tableKey As String, ByVal headerText As String)

THE CELL IS WRITTEN EVERY TIME, and that is the whole point of this member. It used to skip the write whenever the in-memory flag already read "yes":

If KeyedText(this.statusByKey, key) = STATUS_UPDATED Then Exit Sub

The flag then lived in memory and never reached the worksheet, and the worksheet is what every reader of the registry actually reads. SetupTranslationsTable.ProcessRegistryTable takes the status straight off the cell, so a column whose flag stayed in memory was read as "not updated" and its translations were skipped. The saving was one write per edit against the four to five worksheet calls an edit already costs.

CleanupHelpers

ClearUp #

clear-up

Reset all tracked columns to the default (not updated) status.

Signature:

Public Sub ClearUp()

SwitchTags #

switch-tags

Set every registry status flag on the sheet to the supplied value.

Signature:

Private Sub SwitchTags(ByVal value As String)

Every table on the sheet carrying an "updated" column is reset, including the ones this class never built. The master setup registry is hand-built and a workbook made before the single-table layout carries its own registries; both have to reset when the translations button is pressed.

StatusColumnOf #

status-column-of

Return the flag column of a table that carries one.

Signature:

Private Function StatusColumnOf(ByVal lo As ListObject) As Range

InMemoryIndex

EnsureIndex #

ensure-index

Build the in-memory index the first time something needs it.

Signature:

Private Sub EnsureIndex()

ResetIndex #

reset-index

Drop the in-memory index so the next read rebuilds it from the sheet.

Signature:

Private Sub ResetIndex()

The acknowledged set is left alone. It answers for the session, and the index is dropped whenever the registry is written.

LoadIndex #

load-index

Read the whole registry in one call and build the lookups from it.

Signature:

Private Sub LoadIndex()

RefreshStatusIndex #

refresh-status-index

Put the same flag on every key the index holds.

Signature:

Private Sub RefreshStatusIndex(ByVal value As String)

SetStatus #

set-status

Record the flag of one key.

Signature:

Private Sub SetStatus(ByVal key As String, ByVal value As String)

ReadStatus #

read-status

Read a flag cell, answering the default for anything that is not "yes".

Signature:

Private Function ReadStatus(ByVal valueText As String) As String

RegistryColumnPosition #

registry-column-position

Find a registry column by header name, falling back to its published position.

Signature:

Private Function RegistryColumnPosition(ByVal lo As ListObject, _
                                        ByVal headerName As String, _
                                        ByVal fallbackColumn As Long) As Long

ColumnData #

column-data

Return the data body of one registry column by position.

Signature:

Private Function ColumnData(ByVal lo As ListObject, ByVal position As Long) As Range

Utilities

RegistryName #

registry-name

Compose the name of the registry table hosted on this sheet.

Signature:

Private Function RegistryName() As String

SheetKey #

sheet-key

Return the normalised worksheet name, read and normalised once.

Signature:

Private Function SheetKey() As String

BuildColumnLabel #

build-column-label

Compose the descriptive label stored in the registry for a watched column.

Signature:

Private Function BuildColumnLabel(ByVal tableName As String, ByVal columnName As String) As String

TableNameFromLabel #

table-name-from-label

Retrieve the table portion from a table-column label.

Signature:

Private Function TableNameFromLabel(ByVal columnLabel As String) As String

ExtractColumnNameFromLabel #

extract-column-name-from-label

Retrieve the column portion from a table-column label.

Signature:

Private Function ExtractColumnNameFromLabel(ByVal columnLabel As String) As String

BuildRangeName #

build-range-name

Generate the workbook defined name for a watched column, including table context.

Signature:

Private Function BuildRangeName(ByVal tableKey As String, ByVal columnKey As String) As String

NormalizeIdentifier #

normalize-identifier

Produce a safe workbook-level identifier from free-form text.

Signature:

Private Function NormalizeIdentifier(ByVal valueText As String) As String

Letters are lower-cased, digits are kept, and everything else becomes a single underscore, with leading and trailing underscores dropped. One pass writes the answer in place with Mid$, the way HiddenNames.SanitizeNameId does it.

NormalizeValue #

normalize-value

Normalise text by trimming spaces and applying lower case.

Signature:

Private Function NormalizeValue(ByVal valueText As String) As String

SafeText #

safe-text

Read a cell value as text, answering an empty string for an error value.

Signature:

Private Function SafeText(ByVal value As Variant) As String

CellText #

cell-text

Read one cell out of a bulk .Value2 result.

Signature:

Private Function CellText(ByVal block As Variant, _
                          ByVal rowIndex As Long, _
                          ByVal columnIndex As Long, _
                          ByVal rowTotal As Long, _
                          ByVal columnTotal As Long) As String

A single cell read through .Value2 answers a scalar rather than a 2D array, so both shapes are handled here.

KeyedAdd #

keyed-add

Add an item under a key, leaving the first entry in place.

Signature:

Private Sub KeyedAdd(ByVal target As Collection, ByVal item As Variant, ByVal key As String)

KeyedRemove #

keyed-remove

Remove the item held under a key, when there is one.

Signature:

Private Sub KeyedRemove(ByVal target As Collection, ByVal key As String)

KeyedHas #

keyed-has

Report whether a key holds an item.

Signature:

Private Function KeyedHas(ByVal source As Collection, ByVal key As String) As Boolean

KeyedText #

keyed-text

Read the text held under a key, answering an empty string when there is none.

Signature:

Private Function KeyedText(ByVal source As Collection, ByVal key As String) As String

KeyedNumber #

keyed-number

Read the number held under a key, answering zero when there is none.

Signature:

Private Function KeyedNumber(ByVal source As Collection, ByVal key As String) As Long

ThrowError #

throw-error

Raise a ProjectError aligned error from the current class.

Signature:

Private Sub ThrowError(ByVal errNumber As ProjectError, ByVal errorMessage As String)
Used in (13 file(s))