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 #
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
Wksh #
worksheet
Worksheet hosting the watcher registry table.
Signature:
Public Property Get Wksh() As Worksheet
Returns: Worksheet. The registry worksheet.
Interface implementation
Exists #
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 #
self
Current object instance cast to the interface.
Signature:
Public Property Get Self() As IUpdatedValues
Returns: IUpdatedValues. The current instance.
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.
PrivateAccessors
EnsureWorksheet #
ensure-worksheet
Guard that the registry worksheet reference is initialised before use.
Signature:
Private Function EnsureWorksheet() As Worksheet
RegistryListObjectForTable #
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
RegistryListObjectByName #
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
RegistryTable #
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
ResetRegistryCache #
reset-registry-cache
Clear cached references so subsequent calls rebuild registry helpers.
Signature:
Private Sub ResetRegistryCache()
TrackLastRegistryName #
track-last-registry-name
Remember the most recently accessed registry ListObject name.
Signature:
Private Sub TrackLastRegistryName(ByVal listName As String)
ResolveRegistryListName #
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
LastRegistryListName #
last-registry-list-name
Retrieve the most recently added registry ListObject name on the worksheet.
Signature:
Private Function LastRegistryListName() As String
RegistryListObjects #
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
RegistryListNameForTable #
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
BuildRegistryListName #
build-registry-list-name
Compose the registry ListObject name for the supplied table.
Signature:
Private Function BuildRegistryListName(ByVal tableName As String) As String
EnsureNameIndexTable #
ensure-name-index-table
Guarantee the registry name index ListObject exists on the worksheet.
Signature:
Private Function EnsureNameIndexTable() As ListObject
EnsureNameIndexHeaders #
ensure-name-index-headers
Apply the expected headers to the name index ListObject.
Signature:
Private Sub EnsureNameIndexHeaders(ByVal lo As ListObject)
EnsureNameIndexEntry #
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)
RemoveNameIndexEntry #
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)
ResetRegistryNameCache #
reset-registry-name-cache
Clear the cached registry name lookup.
Signature:
Private Sub ResetRegistryNameCache()
RegistryNameCache #
registry-name-cache
Load or retrieve the cached registry names for the current worksheet.
Signature:
Private Function RegistryNameCache() As Collection
LookupRegistryName #
lookup-registry-name
Retrieve the registry ListObject name associated with the supplied table.
Signature:
Private Function LookupRegistryName(ByVal tableName As String) As String
LookupTableNameFromRegistry #
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
ClearNameIndexEntries #
clear-name-index-entries
Remove every entry from the registry name index.
Signature:
Private Sub ClearNameIndexEntries()
ClampStartColumn #
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
WorksheetLastUsedColumn #
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
NextAvailableColumn #
next-available-column
Determine the next column available for placing the registry table.
Signature:
Private Function NextAvailableColumn() As Long
CreateRegistryListObject #
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
EnsureRegistryHeaders #
ensure-registry-headers
Guarantee the registry ListObject exposes the expected header names.
Signature:
Private Sub EnsureRegistryHeaders(ByVal lo As ListObject)
ColumnRegistration
AddColumns #
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.
AddSheet #
add-sheet
Register tagged columns for every ListObject on the provided worksheet.
Signature:
Private Sub AddSheet(ByVal targetSheet As Worksheet)
RemoveListObject #
remove-lo
Remove registry entries associated with the supplied ListObject.
Signature:
Private Sub RemoveListObject(ByVal target As ListObject)
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
ColumnTag #
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.
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.
BuildRegistryIndex #
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
EnsureRegistryRowFromIndex #
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.
FindRegistryRowInIndex #
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
PopulateRegistryRow #
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)
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)
AddExpectedKey #
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)
ExpectedContains #
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
PruneObsoleteEntries #
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
IsUpdated #
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.
CheckStatus #
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.
ResolveNamedRange #
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
UpdateStatusForCell #
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)
RemoveOutdatedEntry #
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
ResetUpdateStatus #
reset-update-status
Reset every registry row to the default (non-updated) status value.
Signature:
Private Sub ResetUpdateStatus()
CleanupHelpers
ClearUp #
clear-up
Reset all tracked columns to the default (not updated) status.
Signature:
Private Sub ClearUp()
DeleteUp #
delete-up
Remove the watcher registry and associated workbook named ranges.
Signature:
Private Sub DeleteUp()
RemoveRegistryTable #
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)
RemoveAllNamedRanges #
remove-all-named-ranges
Delete all workbook defined names created for watcher columns.
Signature:
Private Sub RemoveAllNamedRanges()
RemoveNamedRange #
remove-named-range
Delete a single defined name if it exists in the workbook.
Signature:
Private Sub RemoveNamedRange(ByVal rangeName As String)
SwitchTags #
switch-tags
Set every registry status flag to the supplied value.
Signature:
Private Sub SwitchTags(ByVal value As String)
Utilities
RegistryColumnData #
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
RegistryTagFromVariant #
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.
RowCount #
row-count
Return the number of rows contained in the supplied range.
Signature:
Private Function RowCount(ByVal target As Range) As Long
CurrentLoName #
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.
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
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
ColumnMatchesIdentifier #
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
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 normalizedColumnKey As String) As String
NormalizeColumnKey #
normalize-column-key
Produce a workbook-friendly identifier from a column header.
Signature:
Private Function NormalizeColumnKey(ByVal columnName 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
ReplaceRepeatedUnderscores #
replace-repeated-underscores
Collapse multiple underscores into a single instance.
Signature:
Private Function ReplaceRepeatedUnderscores(ByVal valueText As String) As String
TrimUnderscores #
trim-underscores
Remove leading and trailing underscore characters.
Signature:
Private Function TrimUnderscores(ByVal valueText As String) As String
NormalizeValue #
normalize-value
Normalise text by trimming spaces and applying lower case.
Signature:
Private Function NormalizeValue(ByVal valueText As String) As String
BulkValue #
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.
ThrowError #
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.
IUpdatedValues_SwitchTagsToNo #
switch-tags-to-no
Signature:
Private Sub IUpdatedValues_SwitchTagsToNo()
IUpdatedValues_SwitchTagsToYes #
switch-tags-to-yes
Signature:
Private Sub IUpdatedValues_SwitchTagsToYes()