'if no input cells, exit
9.13 WORKING WITH RANGE NAMES
9.13.1 Working With Range Names
If you have a range name in a worksheet and want to use it in your code, Excel will only recognize the range name if you are on the sheet containing the range name, or if you qualify the Range reference with the worksheet.
For example, if range name "MyRange" is on worksheet "Sheet12", and you are on "Sheet3"
when you run the following statement, the code will crash:
Range("MyRange").Copy
The solution is very simple, but not obvious. The following approaches will work:
Approach 1
Range("MyRange").Worksheet.Activate Range("MyRange").Copy
Approach 2 - which does not require you to go to the worksheet Range("myRange").Worksheet.Range("myrange").Copy
If all you want to do is to determine the worksheet containing the range, then you can do the following
Dim oSheet As Worksheet
Set oSheet = Range("myRange").Worksheet MsgBox oSheet.Name
Please note that the above approaches require that the active workbook be the workbook containing the range name, and that the range name is not a local sheet specific range name
9.13.2 Creating Range Names
You can create a range name very simply:
Range("C4:R9").Name = "SomeName"
or Set tempRange = Range("C4:R9") tempRange.Name = "SomeName"
9.13.3 Creating Hidden Range Names
The following illustrates how to create a hidden range name:
ActiveWorkbook.Names.Add Name:="hiddenName", _ RefersTo:= "=1000", Visible:=False
Hidden range names are useful to store settings that are not visible to the user.
9.13.4 Referring To A Range Name In Your Code
Use of range names can make your work in Visual Basic easier. However, to get the full benefit of range names, you should use them to set a range variable to the range name's range. This is easy to do if the active sheet is the sheet containing the range name. For example, if the range name is "MyRange" and it refers to cells on the active sheet, then the following will work:
Dim MyVariable As Range
Set MyVariable = Range("MyRange")
However, the above will crash if the range name "MyRange" is not on the active sheet. The following is a workaround you can use:
If in the active workbook:
Dim myVariable As Range
Set myVariable = Names("MyRange").RefersToRange If in a different workbook:
Dim myVariable As Range Set myVariable = _
Workbooks("book3.xls").Names("MyRange").RefersToRange If in a different workbook
Sub GoToARange() Dim myRange As Range
'set a variable to a range name's range by calling a user defined function
Set myRange = RangeNameToRange(Workbooks("book3"), "myRange") 'got to the range
Application.Goto myRange End Sub
Function RangeNameToRange(wBook As Workbook, _ rName As String) As Range
Dim I As Integer, J As Integer,
Dim sName As String, tempStr As String 'get full address excluding = sign
tempStr = Mid(wBook.Names(rName).RefersTo, 2) I = InStr(tempStr , "'!")
If I = 0 Then
I = InStr(tempStr , "!") Else
J = 1 End If
'get only the sheet name in the address
sName = Mid(tempStr , 1 + J, I - J - 1) 'use the pieces to return a range reference
Set RangeNameToRange = wBook.Sheets(sName).Range(rName) End Function
9.13.5 How To Refer To Range Names In Your Code
If you need to refer to ranges in your workbook, then you can use statements like the following:
If the range name is on the active sheet, then the following refers to the range:
Range("range name")
If the range name is on a different sheet in the active workbook, then use statements like the following:
Sheets("sheet name").Range("range name") or Range("'sheet name'!range name")
If the range name is not in the active workbook, then use a statement like the following:
Workbooks("name").Sheets("sheet name").Range("range name")
If you have object variables set that refer to the workbook or sheet, you can use the object variables instead of the Workbooks("name") and Sheets("sheet name") statements.
9.13.6 Check For Existence Of A Range Name
The following function returns True if the range name exists and is not a local (sheet specify) range name, False if it has note.
Function NameExists(theName As String) As Boolean Dim S As String
On Error GoTo EndFunction
S = ThisWorkbook.Names(theName).RefersTo NameExists = True
Exit Function EndFunction:
NameExists = False End Function
9.13.7 Determining If A Range Has Been Assigned A Range
Name
The following function will return True if the range passed to it has been assigned a range name.
It will return False if is not a named range.
Function bNamed(anyRange As Range) As Boolean Dim nm
Dim nameRange
'set error trap in case name is not for a range On Error GoTo errorTrap
For Each nm In anyRange.Parent.Parent.Names 'set a variable equal to the range of the name
Set nameRange = Range(Mid(nm.RefersTo, 2)) 'compare complete addresses, including sheet name
If nameRange.Address(external:=True) = _ anyRange.Address(external:=True) Then 'if a match, set function to true and exit
bNamed = True Exit Function End If
nextName:
Next nm
'if no match found function exits and returns its default value of false Exit Function
errorTrap:
'resets error trap and returns to process the next name Resume nextName
End Function
For example, the following is one way of using this function:
Sub TestFunction()
MsgBox bNamed(Selection) End Sub
9.13.8 Determining The Name Assigned To A Cell
The following statement will return the name assigned to the cell begin referenced, in this case the active cell
MsgBox ActiveCell.Name.Name
ActiveCell.Name returns a name object, and then Name returns its name property.
9.13.9 Expanding A Range Name's Range
The following statement illustrates how to expand a named range to include new data:
Range("myName").CurrentRegion.Name = "myName
9.13.10 Accessing A Named Range's Value In Another Workbook
To determine the value of a cell that is a named range in another open workbook, use a statement like the following:
cellValue = Workbooks("myworkbook.xls").Worksheets("mysheet") _ .Range("mycell").Value
To set the value of such a cell, use a statement like this one:
Workbooks("myworkbook.xls").Worksheets("mysheet") _ .Range("mycell").Value = "XXX"
9.13.11 Deleting Range Names
Every now and then, a workbook will end up with range names that are bad. I.e., they refer to
#REF, as the sheet or range they refer to have been deleted. The following code will delete such names:
Sub DeleteBadNames() Dim nm As Variant Dim vTest As Variant
'rotate through all the names in the workbook For Each nm In ActiveWorkbook.Names 'reset vTest each time through
vTest = Empty
On Error Resume Next 'evaluate the name reference
vTest = Application.Evaluate(nm.RefersTo) On Error GoTo 0
'if an error, delete the name
If TypeName(vTest) = "Error" Then nm.Delete Next nm
End Sub
If you just want to delete all the range names in a workbook, try the following:
Sub DeleteAllNames() Dim I As Integer
For I = ActiveWorkbook.Names.Count To 1 Step -1 Range(ActiveWorkbook.Names(I).Name).Name.Delete Next
End Sub
9.13.12 Deleting Range Names - Another Example
You can delete all the names in a workbook like this:
Sub DeleteAllNames() Dim Nm As Name
'loop through the names in the workbook, deleting each For Each Nm In Names
Nm.Delete Next
End Sub
or just the ones that refer to items on the active sheet like this:
Sub DeleteActivesheetNames()
Dim Nm As Name, SheetLen As Integer
'get the length of the sheet name plus one for the = sign SheetLen = Len(ActiveSheet.Name) + 1
For Each Nm In Names
compare the sheet part of the name to the active SheetName
If Left(nm.RefersTo, SheetLen) = "=" & ActiveSheet.Name Then Nm.Delete
End If Next End Sub
9.13.13 Deleting All The Range Names In A Workbook
The following should delete all the range names in the active workbook.
SubDeleteAllNames()
For i = ActiveWorkbook.Names.Count To 1 Step -1 Range(ActiveWorkbook.Names(i).Name).Name.Delete Next
End Sub
9.13.14 Deleting Bad Range Names With A Macro
The following code will delete all range names in a workbook that no longer refer to a valid range. An example would be a range name that refers to #REF!$A$!:$P$266. Such range name problems sometimes occur when sheets or ranges are deleted.
Sub DeleteBadNames() Dim nm As Excel.Name Dim vTest As Variant
'loop through all the names in the active workbook For Each nm In ActiveWorkbook.Names 'clear vTest of any value or reference
vTest = Empty
'use On Error to prevent the evaluation to an error value from 'halting the macro
On Error Resume Next
vTest = Application.Evaluate(nm.RefersTo) On Error GoTo 0
'test the value of the range and if an error, delete the name If TypeName(vTest) = "Error" Then nm.Delete Next nm
End Sub