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.

WHAT CREATE COSTS

Create walks the whole Names collection of the host and reads several COM properties off every name it tracks. A data entry sheet holds hundreds of names, so the walk is the largest cost this class has. An instance pays it once and answers every later read out of memory, keyed by name.

A caller that wants ONE value off a host it will not touch again calls QuickValue instead, which skips the walk entirely.

Depends on: Checking, BetterArray, What a Names collection raises for an identifier it does not hold. Excel, answers 1004 and the collection itself answers 9, so both are read as "the, container has no such name".

Version: 1.0 (2026-02-09)

Factory

Create #

create

Create a new HiddenNames instance

Signature:

Public Function Create(ByVal targetObj As Object) As HiddenNames

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: HiddenNames. The configured instance.

Throws:

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

Sheet #

sheet

Worksheet backing this manager

Signature:

Public Property Get Sheet() As Worksheet
Public API

QuickValue #

quick-value

Read one stored value off a host, with no instance behind it

Signature:

Public Function QuickValue(ByVal targetObj As Object, _
                           ByVal nameId As String, _
                           Optional ByVal defaultValue As String = vbNullString) As String

Three COM crossings and a Mid$: the name is resolved by identifier, its RefersTo is read, and the wrapper is parsed here. The Names walk that Create pays for is skipped, and so is every property read that walk does per name.

WHEN TO REACH FOR THIS

A caller that wants ONE value off a host it will not touch again. The linelist event handlers read sheet_type and table_name this way. Both are written once when the sheet is built and never change while the workbook is open, and the sheets that carry them hold hundreds of names.

A caller that reads several values off the same host, or writes any, calls Create instead. That instance pays the walk once and answers every later read out of memory.

THE LIMITS OF THIS

A visible name answers here. Create tracks hidden names alone.

The answer is the stored string whatever the type annotation says, so a caller wanting a Boolean or a Long coerces it. The typed readers are on the instance.

A workbook-scoped name whose RefersTo points at a worksheet is reached through the workbook alone. Create reaches it through either.

Parameters:

  • targetObj: Object. A Worksheet or Workbook holding the name.
  • nameId: String. The name identifier.
  • defaultValue: String. The answer when the host holds no such name.

Returns: String. The stored value.

HasName #

has-name

Check whether a hidden name exists

Signature:

Public Function HasName(ByVal nameId As String) As Boolean

EnsureName #

ensure-name

Ensure a hidden name exists with the provided default

Signature:

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

Value #

value

Retrieve a hidden value without coercion

Signature:

Public Function Value(ByVal nameId As String, _
                         Optional ByVal defaultValue As Variant) As Variant

SetValue #

set-value

Update the stored value of an existing hidden name

Signature:

Public Sub SetValue(ByVal nameId As String, ByVal value As Variant)

ValueAsString #

value-as-string

Retrieve the hidden value coerced to String

Signature:

Public 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:

Public 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:

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

RemoveName #

remove-name

Remove a hidden name definition

Signature:

Public Sub RemoveName(ByVal nameId As String)

ListNames #

list-names

Enumerate recorded hidden names filtered by optional prefix

Signature:

Public Function ListNames(Optional ByVal prefix As String = vbNullString) As BetterArray

ExportNames #

export-names

Export all tracked hidden names to another worksheet

Signature:

Public Sub ExportNames(ByVal targetSh As Worksheet, _
                      Optional ByVal prefix As String = vbNullString)

ExportNamesToWorkbook #

export-names-to-workbook

Export tracked names to another workbook scope

Signature:

Public Sub ExportNamesToWorkbook(ByVal targetWb As Workbook, _
                                Optional ByVal prefix As String = vbNullString)

ImportNames #

import-names

Import hidden names from another worksheet

Signature:

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

ImportNamesFromWorkbook #

import-names-from-workbook

Import hidden names from another workbook scope

Signature:

Public Sub ImportNamesFromWorkbook(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

EnsureStores #

ensure-stores

Build the record store and the name index

Signature:

Private Sub EnsureStores()

The two are built together and thrown away together, so this is the one place either of them comes into being. A host holding no hidden name still ends up with an empty store, which is the state every reader here expects.

MetadataStore #

metadata-store

Lazy-initialised BetterArray holding all metadata records

Signature:

Private Function MetadataStore() As BetterArray

Returns: BetterArray. The metadata cache.

NameIndex #

name-index

Lazy-initialised keyed Collection holding each record's position

Signature:

Private Function NameIndex() As Collection

The key is MetadataKey(nameId) and the item is the position of that record in the store. Lookups are kept in a keyed Collection because Scripting.Dictionary is missing on Mac Excel. LLFormat holds its three caches the same shape.

Returns: Collection. The name index.

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

The position is read straight out of the keyed index, so the cost of a lookup no longer grows with the number of names the host holds. This used to walk every record comparing strings, and a data entry sheet holds hundreds.

A Collection has no "does this key exist" test, so the miss is read off Err. Err is cleared first, because On Error Resume Next and On Error GoTo 0 both leave it alone and a stale number from earlier code would read as a miss.

Parameters:

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

PushMetadataRecord #

push-metadata-record

Append a record to the store and key its position in the index

Signature:

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

The first record to claim a key keeps it. Two tracked names can share an identifier: on a worksheet host, ShouldTrack accepts a sheet-scoped name and also a workbook-scoped name whose RefersTo points at that sheet. The walk this index replaced answered the first of the two, and this keeps that answer. Both records stay in the store, so ListNames and the two export routines still see them.

Parameters:

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)

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()

This walk is what Create costs. Every property it reads off a name is a COM crossing, and it runs once per tracked name, so each read removed from the body of the loop is worth hundreds on a data entry sheet.

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:

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

This is the key of the name index and of the two prefix comparisons in ListNames and the export routines.

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)

Every record after the removed one moves down a place, so the index is rebuilt in the same walk that builds the new store. Leaving the old index in place would point every one of those keys at its neighbour.

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).

WHY THE MISS RETURNS EARLY

The direct lookup raises for a name that does not exist yet, and a name that does not exist yet is the common case for every caller that creates names. So every first write used to fall through to the scan below, which walks the whole container. VarWriter writes three to five names per variable, and the collection grows with them, so the cost was quadratic in the variable count of a sheet. The scan is kept for the sheet-qualified case the comment below names, and it is skipped only when the container itself says it holds no such identifier. What that gives up: a hidden WORKBOOK-scoped name whose RefersTo points at a worksheet container is no longer reached through that worksheet's store. Nothing in the tree writes one.

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

ParseRefersTo #

parse-refers-to

Read the stored value back out of a RefersTo string

Signature:

Private Function ParseRefersTo(ByVal storedFormula As String) As String

The inverse of SerializeValue. A string was written as ="value" with every inner quote doubled, and every other type as =literal, so this strips the leading equals sign and then the quote wrapper when one is there.

Parameters:

Returns: String. The value the formula holds.

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 Value method, avoiding redundant SanitizeNameId calls and metadata index lookups when the record is already resolved.

Parameters:

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 (68 file(s))