UpdatedValues

Tracks watched setup columns and exposes update status to events. Maintains a registry of ListObjects on a dedicated worksheet, backed by workbook-level named ranges, to detect column-level changes and report update flags.

Factory

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 IUpdatedValues

Parameters:

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

Returns: IUpdatedValues. Configured watcher instance.

PublicState

worksheet #

Worksheet hosting the watcher registry table.

Signature:

Public Property Get Wksh() As Worksheet

Returns: Worksheet. The registry worksheet.

Interface implementation

exists #

Determine whether a registry ListObject already exists for the supplied identifier.

Signature:

Public Function Exists(ByVal listName As String) As Boolean

Parameters:

listName: String. Table or registry identifier to look up.

Returns: Boolean. True when a matching registry ListObject exists.

Internal members (not exported)

Factory

self #

Current object instance cast to the interface.

Signature:

Public Property Get Self() As IUpdatedValues

Returns: IUpdatedValues. The current instance.

PublicState

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.

PrivateAccessors

ensure-worksheet #

Guard that the registry worksheet reference is initialised before use.

Signature:

Private Function EnsureWorksheet() As Worksheet

registry-list-object-for-table #

Retrieve or create the registry ListObject that stores watchers for the supplied source table.

Signature:

Private Function RegistryListObjectForTable(ByVal tableName As String, _
                                            ByVal createWhenMissing As Boolean) As ListObject

registry-list-object-by-name #

Locate a registry ListObject on the worksheet by its name.

Signature:

Private Function RegistryListObjectByName(ByVal listName As String) As ListObject

registry-table #

Return a CustomTable helper for the specified registry ListObject name or the last accessed one.

Signature:

Private Property Get RegistryTable(Optional ByVal tableName As String = vbNullString) As ICustomTable

reset-registry-cache #

Clear cached references so subsequent calls rebuild registry helpers.

Signature:

Private Sub ResetRegistryCache()

track-last-registry-name #

Remember the most recently accessed registry ListObject name.

Signature:

Private Sub TrackLastRegistryName(ByVal listName As String)

resolve-registry-list-name #

Determine the registry ListObject name to use for the provided identifier or the last accessed registry.

Signature:

Private Function ResolveRegistryListName(ByVal candidate As String) As String

last-registry-list-name #

Retrieve the most recently added registry ListObject name on the worksheet.

Signature:

Private Function LastRegistryListName() As String

registry-list-objects #

Enumerate every registry ListObject hosted on the watcher worksheet.

Signature:

Private Function RegistryListObjects(Optional ByVal preferredName As String = vbNullString) As Collection

NameIndex

registry-list-name-for-table #

Resolve the registry ListObject name for the supplied table, creating a new identifier when requested.

Signature:

Private Function RegistryListNameForTable(ByVal tableName As String, _
                                          ByVal allowCreateEntry As Boolean) As String

build-registry-list-name #

Compose the registry ListObject name for the supplied table.

Signature:

Private Function BuildRegistryListName(ByVal tableName As String) As String

ensure-name-index-table #

Guarantee the registry name index ListObject exists on the worksheet.

Signature:

Private Function EnsureNameIndexTable() As ListObject

ensure-name-index-headers #

Apply the expected headers to the name index ListObject.

Signature:

Private Sub EnsureNameIndexHeaders(ByVal lo As ListObject)

ensure-name-index-entry #

Insert or refresh the name index entry for the supplied table.

Signature:

Private Sub EnsureNameIndexEntry(ByVal tableName As String, ByVal registryName As String)

remove-name-index-entry #

Remove the name index entry associated with the supplied registry.

Signature:

Private Sub RemoveNameIndexEntry(ByVal tableName As String, ByVal registryName As String)

reset-registry-name-cache #

Clear the cached registry name lookup.

Signature:

Private Sub ResetRegistryNameCache()

registry-name-cache #

Load or retrieve the cached registry names for the current worksheet.

Signature:

Private Function RegistryNameCache() As Collection

lookup-registry-name #

Retrieve the registry ListObject name associated with the supplied table.

Signature:

Private Function LookupRegistryName(ByVal tableName As String) As String

lookup-table-name-from-registry #

Attempt to determine the original table name from a registry ListObject.

Signature:

Private Function LookupTableNameFromRegistry(ByVal registryName As String) As String

clear-name-index-entries #

Remove every entry from the registry name index.

Signature:

Private Sub ClearNameIndexEntries()

clamp-start-column #

Ensure a proposed registry column start fits within the worksheet boundaries.

Signature:

Private Function ClampStartColumn(ByVal startColumn As Long, _
                                  Optional ByVal targetSh As Worksheet) As Long

worksheet-last-used-column #

Determine the last used column on the registry worksheet using End(xlToLeft).

Signature:

Private Function WorksheetLastUsedColumn(ByVal sh As Worksheet) As Long

next-available-column #

Determine the next column available for placing the registry table.

Signature:

Private Function NextAvailableColumn() As Long

create-registry-list-object #

Build an empty registry ListObject with the expected headers.

Signature:

Private Function CreateRegistryListObject(ByVal sh As Worksheet, ByVal loName As String) As ListObject

ensure-registry-headers #

Guarantee the registry ListObject exposes the expected header names.

Signature:

Private Sub EnsureRegistryHeaders(ByVal lo As ListObject)

ColumnRegistration

add-columns #

Register or refresh watched columns for the supplied ListObject.

Signature:

Private Sub AddColumns(ByVal source As ListObject)

Scans the source ListObject header row for metadata tags and synchronises the registry table so named ranges remain aligned with the current layout.

Parameters:

source: ListObject. Table to inspect for update tags.

add-sheet #

Register tagged columns for every ListObject on the provided worksheet.

Signature:

Private Sub AddSheet(ByVal targetSheet As Worksheet)

remove-lo #

Remove registry entries associated with the supplied ListObject.

Signature:

Private Sub RemoveListObject(ByVal target As ListObject)

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

column-tag #

Read the metadata tag associated with a column header, searching above the header row.

Signature:

Private Function ColumnTag(ByVal headerCell As Range) As String

Parameters:

headerCell: Range. The target header cell.

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.

build-registry-index #

Create a lightweight lookup of existing registry rows keyed by normalised range name.

Signature:

Private Function BuildRegistryIndex(ByVal registry As ListObject) As BetterArray

ensure-registry-row-from-index #

Locate or append a registry row for the provided range name.

Signature:

Private Function EnsureRegistryRowFromIndex(ByVal registry As ListObject, _
                                            ByVal registryIndex As BetterArray, _
                                            ByVal rangeName As String, _
                                            ByRef isNew As Boolean) As Long

Parameters:

registryIndex: BetterArray. Index built from existing entries.
rangeName: String. Normalised range identifier.
isNew: Boolean. Output flag indicating whether a new row was added.

find-registry-row-in-index #

Locate a registry row index in the cached index by its normalised key.

Signature:

Private Function FindRegistryRowInIndex(ByVal registryIndex As BetterArray, _
                                        ByVal key As String) As Long

populate-registry-row #

Write watcher metadata to the specified registry row and initialise status flags.

Signature:

Private Sub PopulateRegistryRow(ByVal registry As ListObject, _
                                ByVal rowIndex As Long, _
                                ByVal columnLabel As String, _
                                ByVal rangeName As String, _
                                ByVal tagValue As String, _
                                ByVal isNew As Boolean)

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)

add-expected-key #

Track range names detected during scanning to support registry pruning.

Signature:

Private Sub AddExpectedKey(ByVal expected As BetterArray, ByVal rangeName As String)

expected-contains #

Test whether the expected list already includes the provided range name.

Signature:

Private Function ExpectedContains(ByVal expected As BetterArray, ByVal rangeName As String) As Boolean

prune-obsolete-entries #

Remove registry rows and named ranges for columns no longer tagged for watching.

Signature:

Private Sub PruneObsoleteEntries(ByVal registry As ListObject, _
                                 ByVal expected As BetterArray, _
                                 ByVal tableName As String)

StatusTracking

is-updated #

Determine whether the tracked column has been marked as updated.

Signature:

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

Searches across all registry ListObjects for a matching entry and returns True when the column is flagged as updated since the last reset.

check-update #

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

Signature:

Private Sub CheckStatus(ByVal tagReference As Variant, ByVal Target As Range)

Compares the changed range with named ranges tracked in the registry and marks the first matching entry as updated.

Parameters:

tagReference: Variant. Identifier selecting the registry scope.
Target: Range. The changed cells to evaluate.

resolve-named-range #

Attempt to resolve the workbook defined name into a concrete Range reference.

Signature:

Private Function ResolveNamedRange(ByVal rangeName As String) As Range

update-status-for-cell #

Write the supplied status flag into the registry row matching the range cell.

Signature:

Private Sub UpdateStatusForCell(ByVal rangeCell As Range, ByVal statusValue As String)

remove-outdated-entry #

Clean registry rows that point to missing or invalid named ranges.

Signature:

Private Function RemoveOutdatedEntry(ByVal registry As ListObject, _
                                     ByVal rangeCell As Range) As Boolean

reset-update-status #

Reset every registry row to the default (non-updated) status value.

Signature:

Private Sub ResetUpdateStatus()

CleanupHelpers

clear-up #

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

Signature:

Private Sub ClearUp()

delete-up #

Remove the watcher registry and associated workbook named ranges.

Signature:

Private Sub DeleteUp()

remove-registry-table #

Remove an entire registry ListObject and associated named ranges from the worksheet.

Signature:

Private Sub RemoveRegistryTable(ByVal registry As ListObject, _
                                Optional ByVal tableName As String = vbNullString)

remove-all-named-ranges #

Delete all workbook defined names created for watcher columns.

Signature:

Private Sub RemoveAllNamedRanges()

remove-named-range #

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

Signature:

Private Sub RemoveNamedRange(ByVal rangeName As String)

switch-tags #

Set every registry status flag to the supplied value.

Signature:

Private Sub SwitchTags(ByVal value As String)

Utilities

registry-column-data #

Return the DataBodyRange for the specified registry column if available.

Signature:

Private Function RegistryColumnData(ByVal registry As ListObject, _
                                    ByVal headerName As String) As Range

registry-tag-from-variant #

Normalise a tag reference into a registry identifier string.

Signature:

Private Function RegistryTagFromVariant(ByVal tagReference As Variant) As String

Converts a Variant tag reference into a registry identifier:

ListObject: returns ListObject.Name (scopes search to that table's registry).
String: returns the string value (used as table name or registry identifier).
Worksheet: returns vbNullString (caller should fall back to Target.ListObject).

Parameters:

tagReference: Variant. The tag reference to normalise.

Returns: String. The registry identifier, or vbNullString when not resolvable.

row-count #

Return the number of rows contained in the supplied range.

Signature:

Private Function RowCount(ByVal target As Range) As Long

current-lo-name #

Compose the ListObject name used when storing the registry table.

Signature:

Private Function CurrentLoName(ByVal tableName As String) As String

Parameters:

tableName: String. Source ListObject name associated with the registry.

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

extract-column-name-from-label #

Retrieve the column portion from a table-column label.

Signature:

Private Function ExtractColumnNameFromLabel(ByVal columnLabel As String) As String

column-matches-identifier #

Determine whether the supplied registry label or range name matches the target identifier.

Signature:

Private Function ColumnMatchesIdentifier(ByVal columnLabel As String, _
                                         ByVal rangeName As String, _
                                         ByVal targetKey As String) As Boolean

build-range-name #

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

Signature:

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

normalize-column-key #

Produce a workbook-friendly identifier from a column header.

Signature:

Private Function NormalizeColumnKey(ByVal columnName As String) As String

normalize-identifier #

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

Signature:

Private Function NormalizeIdentifier(ByVal valueText As String) As String

replace-repeated-underscores #

Collapse multiple underscores into a single instance.

Signature:

Private Function ReplaceRepeatedUnderscores(ByVal valueText As String) As String

trim-underscores #

Remove leading and trailing underscore characters.

Signature:

Private Function TrimUnderscores(ByVal valueText As String) As String

normalize-value #

Normalise text by trimming spaces and applying lower case.

Signature:

Private Function NormalizeValue(ByVal valueText As String) As String

bulk-value #

Read a value from a bulk-read .Value2 result.

Signature:

Private Function BulkValue(ByVal data As Variant, ByVal idx As Long, ByVal rowTotal As Long) As Variant

When a single-row Range is read via .Value2, VBA returns a scalar Variant rather than a 2D array. This helper transparently handles both cases.

Parameters:

data: Variant. The .Value2 result (scalar for 1 row, 2D array otherwise).
idx: Long. The 1-based row index to read.
rowTotal: Long. Total number of rows in the source range.

Returns: Variant. The cell value at the requested row.

throw-error #

Raise a ProjectError aligned error from the current class.

Signature:

Private Sub ThrowError(ByVal errNumber As ProjectError, ByVal errorMessage As String)

Interface implementation

IUpdatedValues_AddColumns #

Signature:

Private Sub IUpdatedValues_AddColumns(ByVal Lo As ListObject)

Thin delegation stubs forwarding each IUpdatedValues member to the corresponding internal implementation above.

switch-tags-to-no #

Signature:

Private Sub IUpdatedValues_SwitchTagsToNo()

switch-tags-to-yes #

Signature:

Private Sub IUpdatedValues_SwitchTagsToYes()

Used in (14 file(s))