This page was written by Laurent Longre, and is included on my site at his request and with his kind permission.  I have made some minor changes in formatting and spelling.  Descriptive text past the ---------- END OF LAURENT'S ARTICLE ------- marker line are my own comments and code which supplement Laurent's article.

About Excel's Hidden Name Space

The hidden name space is a memory area belonging to the current Excel instance, where add-in DLLs ("XLLs") can store temporary names.  With this area, XLLs can define names even though there is no macro sheet available.

The manipulation of hidden names are based on the XLM functions SET.NAME, GET.NAME and EVALUATE. Whereas SET.NAME defines a normal sheet-level name when used in an XLM macro sheet, it creates an application-level name and stores it in the hidden area when it is called in an XLL.

The names defined in this hidden area have some special features, which make them very different from the standard workbook names. These features will be described later.

The available C API commands related to hidden names are :

Excel4(xlfSetName,&xResult,2,&xName,&xValue);
... defines the name xName containing xValue

Excel4(xlfGetName,&xResult,1,&xName);
... retrieves the definition (for instance, "=1") of xName and stores it in xResult

Excel4(xlfEvaluate,&xResult,1,&xName);
... retrieves the contents of xName (for instance : 1) and stores it in xResult

Excel4(xlfSetName,&xResult,1,&xName);
... deletes xName (second argument missing)


Accessing the hidden name space in VBA

Although it should not be possible (following the SDK), the hidden name space is accessible  not only to DLLs, but also to VBA modules (and even worksheets).   The undocumented trick consists simply in calling the XLM function SET.NAME in the VBA module with the ExecuteExcel4Macro method :

Creating A Hidden Name

To create a hidden name named Test, containing the string "OK", use

Application.ExecuteExcel4Macro "SET.NAME(""Test"",""OK"")"

Retrieving The Contents Of A Hidden Name

To retrieve the contents of the name Test, use

TestVal = Application.ExecuteExcel4Macro("Test")

(just use the name itself as the argument of ExecuteExcel4Macro)

Deleting A Hidden Name

To delete the name Test, use

Application.ExecuteExcel4Macro "SET.NAME(""Test"")"

(second argument of SET.NAME missing)

Accessing The Hidden Name Space In Worksheets

The hidden names can be also accessed directly in worksheet formulae. You must use SET.NAME and EVALUATE in combination with CALL() and the API function Excel4 (for more information about the CALL function and the Excel4 library, see The CALL Function ).   The numbers of the functions SET.NAME and EVALUATE are 88 and 257, respectively .

Creating A Hidden Name

To create a hidden name named Test, containing the string "OK", use

=CALL("Xlcall32","Excel4","2JRJRR#",88,,2,"Test","OK")

Retrieving The Contents Of A Hidden Name

To retrieve the contents of the name Test, use

=CALL("Xlcall32","Excel4","2JRJR#",257,,1,"Test")

Deleting A Hidden Name

To delete the name Test, use

=CALL("Xlcall32","Excel4","2JRJRR#",88,,1,"Test")

Defining and deleting hidden names directly in worksheet formulas is possible, because SET.NAME is not a command (like DEFINE.NAME), but a macro control function.

The hidden names can be used in macro sheets in the same way, except that you don't need to append the number sign to the type string.


Features Of The Hidden Name Space

The main feature of names defined in the hidden name space is that they don't belong to any workbook, but to the application itself.

This feature means in particular that :

These name can be accessed directly anywhere in Excel

Any VBA module, worksheet or macro sheet located in any workbook (and of course any DLL add-in) can read and modify them directly, no matter which workbook created the name.

Their "life time" corresponds to the current Excel session.

If you create such a name in a VBA module of the workbook Wbk1.xls, and then close this workbook, the name is still stored in the hidden name space.  If you then open another workbook, Wbk2.xls, its VBA procedures can still retrieve and modify this hidden name, even though it was created by Wbk1.xls.  Names defined in the hidden name space can be used as permanent "public variables" accessible without limitation from any VBA add-in.

As these hidden names belong to the application, closing all workbooks and add-ins won't destroy them.  They can only be destroyed either by an explicit call to SET.NAME (without the second argument), or by quitting and restarting Excel.  In this matter, these names can be used as a sort of Excel environmental variables.

They are "very hidden"

When a protected add-in uses the hidden name space, the new names can't be read by any other VBA module, nor by the user, unless they know their identification.  There is no way to "list" the names that are defined in the hidden name space.

These names must not be confused with standard hidden names (workbook or sheet names whose .Visible property has been set to False). Standard workbook-level names are never really hidden, because they can be read and modified by any VBA procedure through the Application.Names collection, as shown below :

Dim CName As Name
For Each CName In Workbooks("Wbks1.xls").Names
    If CName.Hidden Then
        MsgBox CName.Name & " deleted"
        CName.Delete
    End If
Next CName

This code, which loops through all hidden names in Wbks1.xls and delete them, will fail to detect the existence of the names stored in the hidden name space, because they simply don't belong to the Application.Names collection.  Consequently, they are protected against any undesirable access or modification.


Examples

The following code illustrates a possible use of the hidden name space in a VBA protected add-in demo.

It prevents the user from executing the main Sub of the add-in more than three times in the same Excel session. The counter of the allowed remaining executions is not stored in a module-level variable nor in a name attached to the add-in, but in the hidden name space.

Using the name space prevents the user from breaking the protection by eliminating the following weakness of  more conventional methods:

• Like all variables, a counter stored in a VBA variable can be cleared manually in the VB Editor (Execution -> Initialize).
  
• In the same way, all workbook names of the add-in (hidden or not) can be read, modified and possibly deleted by any external Sub which would loop through the Names collection of the add-in.

The hidden name space avoids both risks;  it is also simpler than other methods based for instance on environment strings, temporary files or registry entries. As the hidden name space is permanent, the user can close and re-open the workbook, without resetting the counter.

In this code, the functions SetHName, GetHName and DelHName allow you to create, read, and delete hidden names without having to use directly the verbose Application.ExecuteExcel4Macro method.

Sub Main()
Application.EnableCancelKey = xlDisabled
Dim Count
Count = GetHName("TswbkCount")
If IsError(Count) Then
    SetHName "TswbkCount", 3
ElseIf Count = 1 Then
    MsgBox "Macro disabled. You must restart Excel.", vbInformation
Else
    SetHName "TswbkCount", Count - 1
End If
End Sub

Sub SetHName(Name As String, Value)
Application.ExecuteExcel4Macro _
    "SET.NAME(""" & Name & """," & Value & ")"
End Sub


Function GetHName(Name As String)
    GetHName = Application.ExecuteExcel4Macro(Name)
End Function


Sub DelHName(Name As String)
    Application.ExecuteExcel4Macro "SET.NAME(""" & Name & """)"
End Sub

� Copyright, 1998, Laurent Longre.

-------------------------------------------------- END OF LAURENT'S ARTICLE -------------------------------------------------------------------------

I have expanded upon the information presented above to create a downloadable bas module file that contains the following functions.

IsValidName

	Public Function IsValidName(HiddenName As String) As Boolean

This function returns True or False indicating whether HiddenName is a valid name. The following characters, and the space characters, are invalid in names:  / - : ; ! @ # $ % ^ & *( ) + = , < >

HiddenNameExists

	Public Function HiddenNameExists(HiddenName As String) As Boolean

This function returns True or False indicating whether the hidden name exists.

AddHiddenName

	Public Function AddHiddenName(HiddenName As String, NameValue As Variant, _
    		Optional OverWriteExisting As Boolean = False) As Boolean

This function adds a new name to the hidden name space. If the name does not exist, it is added and function returns True. If the name exists, and OverWriteExisting is omitted or False, the function returns False. If OverWriteExisting is True, the original name is deleted and recreated with the new value.

DeleteHiddenName

	Public Sub DeleteHiddenName(HiddenName As String)

This procedure deletes HiddenName. If the name does not exist, no action is taken. This procedure does not return a value.

GetHiddenNameValue

	Public Function GetHiddenNameValue(HiddenName As String) As Variant

This function returns the value of HiddenName if it exists and is a valid name. It returns NULL if an error occcurred. Note that the return value is always a String type variable.

The entire contents of the downloadable bas file is shown below:

Option Explicit
Option Compare Text
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' modHiddenNames
' By Chip Pearson, www.cpearson.com , chip@cpearson.com
'
' This module expands on "HiddenNameSpace" as documented by Laurent Longre at
' "www.cpearson.com/excel/hidden.htm". This code is my own, but concept comes from
' Laurent Longre.
' Note that these names persist as long as the application is running, even if the
' workbook that create then names (or any other workbook) is closed.
'
' This module contains the following procedures for working with names in the hidden name
' space in Excel.
'           HiddenNameExists
'               Returns True or False indicating whether then specified name exists.
'           IsValidName
'               Returns True or False indicating whether the specified name is valid.
'           AddHiddenName
'               Adds a hidden name and value. Optionally overwrites the name if it
'               already exists.
'           DeleteHiddenName
'               Deletes a name in the hidden name space. Ignores the condition if the
'               name does not exist. This function does not return a value.
'           GetHiddenNameValue
'               Returns the value of a hidden if that name exists. Returns the value of the
'               name if it exists, or NULL if it does not exist.
'
' To change the value of an existing name, first call DeleteHiddenName to remove the name,
' then call AddHiddenName to add the name with the next value.
'
' There is no way to enumerate the existing names. You must know the name in order to
' access or delete it.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Const C_ILLEGAL_CHARS = " /-:;!@#$%^&*()+=,<>"

Public Function IsValidName(HiddenName As String) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' IsValidName
' This function returns True if HiddenName is a valid name, i.e., it
' is not an empty string and does not contain any character in the
' C_ILLEGAL_CHARS constant.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim C As String
Dim NameNdx As Long
Dim CharNdx As Long
If Trim(HiddenName) = vbNullString Then
    IsValidName = False
    Exit Function
End If
''''''''''''''''''''''''''''''''''''
' Test each character in HiddenName
' against each character in
' C_ILLEGALCHARS. If a match is
' found, get out and return False.
'''''''''''''''''''''''''''''''''''
For NameNdx = 1 To Len(HiddenName)
    For CharNdx = 1 To Len(C_ILLEGAL_CHARS)
        If StrComp(Mid(HiddenName, NameNdx, 1), Mid(C_ILLEGAL_CHARS, CharNdx, 1), vbBinaryCompare) = 0 Then
            ''''''''''''''''''''''''''''' 
            ' Once one invalid character 
            ' is found, there is no
            ' need to continue. Get out
            ' with a result of False.
            '''''''''''''''''''''''''''''
            IsValidName = False
            Exit Function
        End If
    Next CharNdx
Next NameNdx

''''''''''''''''''''''''''''''
' If we made out of the loop,
' the name is valid.
''''''''''''''''''''''''''''''
IsValidName = True

End Function


Public Function HiddenNameExists(HiddenName As String) As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' HiddenNameExists
' This function returns True if the hidden name HiddenName
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim V As Variant
On Error Resume Next

''''''''''''''''''''''''''''''''''''''''
' Ensure the name is valid
''''''''''''''''''''''''''''''''''''''''
If IsValidName(HiddenName) = False Then
    HiddenNameExists = False
    Exit Function
End If


V = Application.ExecuteExcel4Macro(HiddenName)
On Error GoTo 0
If IsError(V) = False Then
    ''''''''''''''''''''''''''''''
    ' No error. Name exists.
    ''''''''''''''''''''''''''''''
    HiddenNameExists = True
Else
    ''''''''''''''''''''''''''''''
    ' Error. Name does not exists.
    ''''''''''''''''''''''''''''''
    HiddenNameExists = False
End If

End Function

Public Function AddHiddenName(HiddenName As String, NameValue As Variant, _
    Optional OverWriteExisting As Boolean = False) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' AddHiddenName
' This adds the hidden name HiddenName with a value NameValue to Excel's
' hidden name space. If OverWriteExisting is omitted or False, the function
' will not overwrite the existing name and will return False if the name
' already exists. If OverWriteExisting is True, the original name is
' deleted and replace with the values passed to this function, and the
' function will return True.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim V As Variant
Dim Res As Variant

''''''''''''''''''''''''''''''''''''''''
' Ensure the name is valid
''''''''''''''''''''''''''''''''''''''''
If IsValidName(HiddenName) = False Then
    AddHiddenName = False
    Exit Function
End If


'''''''''''''''''''''''''''''''''
' If V is an object, an array,
' or a user-defined type, then
' return False.
''''''''''''''''''''''''''''''''
If VarType(V) >= vbArray Then
    AddHiddenName = False
    Exit Function
End If
If (VarType(V) = vbUserDefinedType) Or (VarType(V) = vbObject) Then
    AddHiddenName = False
    Exit Function
End If


'''''''''''''''''''''''''''''''''
' Test to see if the name exists.
'''''''''''''''''''''''''''''''''
On Error Resume Next
V = Application.ExecuteExcel4Macro(HiddenName)
On Error GoTo 0
If IsError(V) = False Then
    '''''''''''''''''''''''''''''
    ' Error. Name Exists. If
    ' OverWriteExisting is False,
    ' exit with False. Otherwise
    ' delete the name.
    '''''''''''''''''''''''''''''
    If OverWriteExisting = False Then
        AddHiddenName = False
        Exit Function
    Else
        DeleteHiddenName HiddenName:=HiddenName
    End If
End If
V = Application.ExecuteExcel4Macro("SET.NAME(" & Chr(34) & HiddenName & Chr(34) & "," & Chr(34) & NameValue & Chr(34) & ")")
If IsError(V) = True Then
    AddHiddenName = False
Else
    AddHiddenName = True
End If

End Function

Public Sub DeleteHiddenName(HiddenName As String)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DeleteHiddenName
' This deletes an name from Excel's hidden name space. It ignores the
' condition that the name does not exist. The procedure does not return
' an result.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
On Error Resume Next
Application.ExecuteExcel4Macro ("SET.NAME(" & Chr(34) & HiddenName & Chr(34) & ")")

End Sub

Public Function GetHiddenNameValue(HiddenName As String) As Variant
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' GetHiddenNameValue
' This function returns the value of HiddenName. If the name does
' not exist, the function returns NULL. Otherwise, it returns the
' value of HiddenName. Note that the value returned by this function
' is always a string value. You'll have to convert it to another
' data type is desired.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim V As Variant

''''''''''''''''''''''''''''''''''''''''
' Ensure the name is valid
''''''''''''''''''''''''''''''''''''''''
If IsValidName(HiddenName) = False Then
    GetHiddenNameValue = Null
    Exit Function
End If

If HiddenNameExists(HiddenName:=HiddenName) = False Then
    GetHiddenNameValue = Null
    Exit Function
End If
On Error Resume Next
V = Application.ExecuteExcel4Macro(HiddenName)
On Error GoTo 0
If IsError(V) = True Then
    GetHiddenNameValue = Null
    Exit Function
End If

GetHiddenNameValue = V
    

End Function

You can download bas module file here containing the code above.