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.