Working With Names In Visual Basic
You can use VBA code to work with defined names. To add a name, use
ThisWorkbook.Names.Add Name:="SomeName", _
RefersTo:=Worksheets("Sheet2").Range("A1:A10")
This will assign the Name SomeName to the range A1:A10 on Sheet2. If you add a Name that already exists, the Name is automatically deleted and recreated with the new reference. It is not necessary to delete the Name before recreating it.
By default, names created manually or with VBA code are visible -- they will appear in the Name Box drop down and in the Names dialog. You can, however, make the name hidden so that it isn't visible to the user. A hidden Name can be used in any manner just like a normal, visible Name. To make a name hidden, you set the Visible parameter to False. For example,
ThisWorkbook.Names.Add Name:="SomeName2", _
RefersTo:=Worksheets("Sheet2").Range("B1:B10"), _
Visible:=False
The name SomeName2 can be used exactly as a visible name, but will not appear to the user. The only way to hide a Defined Name is via VBA code. There is nothing in the Excel user interface that allows you to hide a Name.
To delete a name using VBA code, use code like the following:
ThisWorkbook.Names("TheName").Delete
Using Defined Names in VBA code is different than using them in worksheet cells. You must get the value of the Name using the RefersTo or RefersToRange properties. If a Name doesn't refer to a cell or range of cells (e.g., it refers to a constant), an attempt to use RefersToRange will fail. To get the value of a Name that contains a constant, use the RefersTo property.
V = ThisWorkbook.Names("TheName").RefersToRange
If the name refers to a range of 2 or more cells, you can assign it to a Range type variable. For example,
Dim R As Range
Set R = ThisWorkbook.Names("BigName").RefersToRange
Debug.Print R.Address
If the name refers to a constant, you'll need to strip out some characters to get the actual value. For example, if the name MyName refers to the text constant Chip Pearson, the RefersTo property will return the text ="Chip Pearson". You need to strip out the leading equals sign and the enclosing quotes. You can do this with code like the following:
Dim S As String
S = ThisWorkbook.Names("MyName").RefersTo
S = Mid(S, 3, Len(S) - 3)
Debug.Print S
If the name refers to a numeric constant, there will be a leading equal sign but no quotes. Thus, you'll need to strip off the leading equals sign. The code below illustrates this.
Dim S As String
S = ThisWorkbook.Names("MaxPages").RefersTo
S = Mid(S, 2)
Debug.Print S
We can put all this together into a function that will return what the given name refers to, be it a range, text constant, or numeric constant.
Function GetNameRefersTo(TheName As String) As String
Dim S As String
Dim HasRef As Boolean
Dim R As Range
Dim NM As Name
Set NM = ThisWorkbook.Names(TheName)
On Error Resume Next
Set R = NM.RefersToRange
If Err.Number = 0 Then
HasRef = True
Else
HasRef = False
End If
If HasRef = True Then
S = R.Text
Else
S = NM.RefersTo
If StrComp(Mid(S, 2, 1), Chr(34), vbBinaryCompare) = 0 Then
' text constant
S = Mid(S, 3, Len(S) - 3)
Else
' numeric contant
S = Mid(S, 2)
End If
End If
GetNameRefersTo = S
End Function
