HiddenNames

Manages hidden worksheet/workbook-level name-value storage with typed serialization and metadata caching. Each name carries a type annotation (String/Boolean/Long/Variant) and a last-updated timestamp stored in the Name.Comment field. Supports cross-scope import/export between worksheets and workbooks.

Depends on: ProjectError, BetterArray

Version: 1.0 (2026-02-09)

Factory

Create #

create

Create a new HiddenNames instance

Signature:

Public Function Create(ByVal targetObj As Object) As IHiddenNames

Validates the input and creates a fully initialised manager bound to the supplied worksheet or workbook.

Parameters:

targetObj: Object. A Worksheet or Workbook hosting the hidden names.

Returns: IHiddenNames. The configured instance.

Throws:

ProjectError.ObjectNotInitialized When targetObj is Nothing.
ProjectError.InvalidArgument When targetObj is not a Worksheet or Workbook.

Interface Helpers

SheetInternal #

sheet

Worksheet backing this manager

Signature:

Private Property Get SheetInternal() As Worksheet

Public API

HasName #

has-name

Check whether a hidden name exists

Signature:

Private Function HasName(ByVal nameId As String) As Boolean

Ensure #

ensure-name

Ensure a hidden name exists with the provided default

Signature:

Private Function Ensure(ByVal nameId As String, _
                        ByVal initialValue As Variant, _
                        Optional ByVal valueType As Byte = HiddenNameTypeVariant) As Name

ValueOf #

value

Retrieve a hidden value without coercion

Signature:

Private Function ValueOf(ByVal nameId As String, _
                         Optional ByVal defaultValue As Variant) As Variant

Update #

set-value

Update the stored value of an existing hidden name

Signature:

Private Sub Update(ByVal nameId As String, ByVal value As Variant)

ValueAsString #

value-as-string

Retrieve the hidden value coerced to String

Signature:

Private Function ValueAsString(ByVal nameId As String, _
                               Optional ByVal defaultValue As String = vbNullString) As String

ValueAsBoolean #

value-as-boolean

Retrieve the hidden value coerced to Boolean

Signature:

Private Function ValueAsBoolean(ByVal nameId As String, _
                                Optional ByVal defaultValue As Boolean = False) As Boolean

ValueAsLong #

value-as-long

Retrieve the hidden value coerced to Long

Signature:

Private Function ValueAsLong(ByVal nameId As String, _
                             Optional ByVal defaultValue As Long = 0) As Long

Remove #

remove-name

Remove a hidden name definition

Signature:

Private Sub Remove(ByVal nameId As String)

Enumerate #

list-names

Enumerate recorded hidden names filtered by optional prefix

Signature:

Private Function Enumerate(Optional ByVal prefix As String = vbNullString) As BetterArray

ExportAll #

export-names

Export all tracked hidden names to another worksheet

Signature:

Private Sub ExportAll(ByVal targetSh As Worksheet, _
                      Optional ByVal prefix As String = vbNullString)

ExportAllToWorkbook #

export-names-to-workbook

Export tracked names to another workbook scope

Signature:

Private Sub ExportAllToWorkbook(ByVal targetWb As Workbook, _
                                Optional ByVal prefix As String = vbNullString)

ImportAll #

import-names

Import hidden names from another worksheet

Signature:

Private Sub ImportAll(ByVal sourceSh As Worksheet, _
                      Optional ByVal prefix As String = vbNullString, _
                      Optional ByVal overwriteExisting As Boolean = True)

ImportAllFromWorkbook #

import-names-from-workbook

Import hidden names from another workbook scope

Signature:

Private Sub ImportAllFromWorkbook(ByVal sourceWb As Workbook, _
                                  Optional ByVal prefix As String = vbNullString, _
                                  Optional ByVal overwriteExisting As Boolean = True)

SetListObjectHeader #

set-list-object-header

Create a workbook-level hidden name referencing a ListObject header

Signature:

Public Sub SetListObjectHeader(ByVal nameId As String, _
                               ByVal Lo As ListObject, _
                               ByVal headerName As String)

Internal members (not exported)

Metadata Helpers

MetadataStore #

metadata-store

Lazy-initialised BetterArray holding all metadata records

Signature:

Private Function MetadataStore() As BetterArray

Returns: BetterArray. The metadata cache.

BuildMetadataRecord #

build-metadata-record

Construct a metadata record array

Signature:

Private Function BuildMetadataRecord(ByVal nameId As String, _
                                     ByVal valueType As Byte, _
                                     ByVal lastUpdated As Date, _
                                     ByVal definition As Name) As Variant

MetadataName #

metadata-name

Read the name field from a metadata record

Signature:

Private Function MetadataName(ByRef record As Variant) As String

MetadataType #

metadata-type

Read the type field from a metadata record

Signature:

Private Function MetadataType(ByRef record As Variant) As Byte

MetadataUpdated #

metadata-updated

Read the last-updated field from a metadata record

Signature:

Private Function MetadataUpdated(ByRef record As Variant) As Date

MetadataDefinition #

metadata-definition

Read the Name definition from a metadata record

Signature:

Private Function MetadataDefinition(ByRef record As Variant) As Name

MetadataSetDefinition #

metadata-set-definition

Write the Name definition into a metadata record

Signature:

Private Sub MetadataSetDefinition(ByRef record As Variant, ByVal definition As Name)

MetadataSetUpdated #

metadata-set-updated

Write the last-updated timestamp into a metadata record

Signature:

Private Sub MetadataSetUpdated(ByRef record As Variant, ByVal lastUpdated As Date)

MetadataSetType #

metadata-set-type

Write the value type into a metadata record

Signature:

Private Sub MetadataSetType(ByRef record As Variant, ByVal valueType As Byte)

MetadataIndex #

metadata-index

Find the index of a metadata record by name

Signature:

Private Function MetadataIndex(ByVal nameId As String) As Long

Returns: Long. The index, or -1 when not found.

StoreMetadataRecord #

store-metadata-record

Insert or replace a metadata record in the cache

Signature:

Private Sub StoreMetadataRecord(ByVal nameId As String, ByRef record As Variant)

Factory

Self #

self

Self-reference returning the interface pointer

Signature:

Public Property Get Self() As IHiddenNames

Returns: IHiddenNames. This instance.

Initialisation

Initialise #

initialise

Bind the manager to a worksheet or workbook and load cache

Signature:

Public Sub Initialise(ByVal targetObj As Object)

LoadMetadataCache #

load-metadata-cache

Scan existing hidden names and populate the metadata cache

Signature:

Private Sub LoadMetadataCache()

Interface Helpers

ValidateInput #

validate-input

Validate the factory input argument

Signature:

Private Sub ValidateInput(ByVal targetObj As Object)

NameOwner #

name-owner

Resolve the host object owning the names collection

Signature:

Private Function NameOwner() As Object

ScopeLabel #

scope-label

Human-readable label for the current scope

Signature:

Private Function ScopeLabel() As String

SanitizeNameId #

sanitize-name-id

Convert a raw identifier to a valid Excel Name

Signature:

Private Function SanitizeNameId(ByVal nameId As String) As String

Excel defined names only allow letters, digits, underscores, and periods. The first character must be a letter or underscore. This function replaces any disallowed character (spaces, hyphens, etc.) with an underscore and prepends one when the name starts with a digit or period. Called once at each public API entry point so that all internal methods receive pre-sanitized identifiers.

Parameters:

nameId: String. The raw name identifier.

Returns: String. An Excel-safe name identifier.

MetadataKey #

metadata-key

Normalise a name identifier for case-insensitive lookup

Signature:

Private Function MetadataKey(ByVal nameId As String) As String

Expects a pre-sanitized nameId (callers must call SanitizeNameId first).

EnsureMetadataRecord #

ensure-metadata-record

Ensure a metadata record exists for the given name

Signature:

Private Function EnsureMetadataRecord(ByVal nameId As String) As Long

Expects a pre-sanitized nameId (callers must call SanitizeNameId first).

Returns: Long. The record index, or -1 when the name is not defined.

MetadataRecord #

metadata-record

Retrieve a metadata record by name

Signature:

Private Function MetadataRecord(ByVal nameId As String) As Variant

RemoveMetadataRecord #

remove-metadata-record

Remove a metadata record from the cache

Signature:

Private Sub RemoveMetadataRecord(ByVal nameId As String)

Lookup Helpers

ShouldTrack #

should-track

Determine whether a Name definition belongs to this scope

Signature:

Private Function ShouldTrack(ByVal definition As Name) As Boolean

FindDefinition #

find-definition

Locate a Name definition by identifier

Signature:

Private Function FindDefinition(ByVal nameId As String) As Name

Expects a pre-sanitized nameId (callers must call SanitizeNameId first).

CompareName #

compare-name

Case-insensitive comparison of a Name definition to an expected identifier

Signature:

Private Function CompareName(ByVal definition As Name, ByVal expected As String) As Boolean

ExtractSimpleName #

extract-simple-name

Strip sheet qualification from a Name string

Signature:

Private Function ExtractSimpleName(ByVal qualifiedName As String) As String

Serialization

SerializeValue #

serialize-value

Convert a typed value to its RefersTo formula string

Signature:

Private Function SerializeValue(ByVal value As Variant, _
                                ByVal valueType As Byte) As String

SerializeVariant #

serialize-variant

Serialize a Variant value by inspecting its runtime type

Signature:

Private Function SerializeVariant(ByVal value As Variant) As String

EscapeString #

escape-string

Escape double-quotes for formula embedding

Signature:

Private Function EscapeString(ByVal value As String) As String

ApplyComment #

apply-comment

Write the type and timestamp comment onto a Name definition

Signature:

Private Sub ApplyComment(ByVal definition As Name, _
                         ByVal valueType As Byte, _
                         ByVal updatedOn As Date)

BuildComment #

build-comment

Assemble the structured comment string

Signature:

Private Function BuildComment(ByVal valueType As Byte, ByVal updatedOn As Date) As String

NameTypeFromComment #

name-type-from-comment

Parse the value type from a Name comment

Signature:

Private Function NameTypeFromComment(ByVal comment As String) As Byte

UpdatedFromComment #

updated-from-comment

Parse the last-updated timestamp from a Name comment

Signature:

Private Function UpdatedFromComment(ByVal comment As String) As Date

ExtractCommentValue #

extract-comment-value

Extract a key-value token from a structured comment

Signature:

Private Function ExtractCommentValue(ByVal comment As String, ByVal prefix As String) As String

ValueTypeName #

value-type-name

Convert a HiddenNameValueType to its string representation

Signature:

Private Function ValueTypeName(ByVal valueType As Byte) As String

StringToValueType #

string-to-value-type

Convert a string representation back to HiddenNameValueType

Signature:

Private Function StringToValueType(ByVal value As String) As Byte

Value Coercion

CoerceValue #

coerce-value

Cast a raw value to the specified HiddenNameValueType

Signature:

Private Function CoerceValue(ByVal value As Variant, _
                             ByVal valueType As Byte) As Variant

RecordValue #

record-value

Extract the stored value directly from a metadata record

Signature:

Private Function RecordValue(ByVal record As Variant) As Variant

Reads the value from the record definition without going through the public ValueOf method, avoiding redundant SanitizeNameId calls and metadata index lookups when the record is already resolved.

Parameters:

record: Variant. A metadata record array.

Returns: Variant. The coerced value, or Empty when the definition is Nothing.

Core Operations

EnsureDefinition #

ensure-definition

Create a hidden Name definition if it does not exist

Signature:

Private Function EnsureDefinition(ByVal nameId As String, _
                                  ByVal initialValue As Variant, _
                                  ByVal valueType As Byte) As Name

Expects a pre-sanitized nameId (callers must call SanitizeNameId first).

UpdateDefinitionValue #

update-definition-value

Write a new value into an existing Name definition

Signature:

Private Sub UpdateDefinitionValue(ByVal definition As Name, _
                                  ByVal value As Variant, _
                                  ByVal valueType As Byte, _
                                  Optional ByVal updatedOn As Date = 0)

DefinitionStoredValue #

definition-stored-value

Extract the raw stored value from a Name definition

Signature:

Private Function DefinitionStoredValue(ByVal definition As Name, _
                                       ByVal valueType As Byte) As Variant

RemoveDefinition #

remove-definition

Delete a Name definition from the host scope

Signature:

Private Sub RemoveDefinition(ByVal nameId As String)

EnsureReady #

ensure-ready

Guard that the manager is properly initialised

Signature:

Private Sub EnsureReady()

Internal Export Helpers

PersistOnWorksheet #

persist-on-worksheet

Write a hidden name onto a target worksheet

Signature:

Private Sub PersistOnWorksheet(ByVal targetSh As Worksheet, _
                               ByVal nameId As String, _
                               ByVal value As Variant, _
                               ByVal valueType As Byte)

Expects a pre-sanitized nameId (callers must call SanitizeNameId first).

RemoveWorksheetName #

remove-worksheet-name

Delete a Name definition from a specific worksheet

Signature:

Private Sub RemoveWorksheetName(ByVal targetSh As Worksheet, ByVal nameId As String)

RemoveWorkbookName #

remove-workbook-name

Delete a Name definition from a workbook scope

Signature:

Private Sub RemoveWorkbookName(ByVal targetWb As Workbook, ByVal nameId As String)

Expects a pre-sanitized nameId (callers must call SanitizeNameId first).

Error Handling

ThrowError #

throw-error

Raise a ProjectError-based exception

Signature:

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

Used in (60 file(s))