If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

After much playing around I came up with this which seems to work just fine. Any of you Excel gurus see a potential problem that I don't see with using this?

Code:

Function NamedRangeExists(strName As String, Optional wkbName As String) As Boolean
Dim rngRangeNameToFind As Range
Dim i As Long
On Error Resume Next 'because we will be going through all ranges, all non matches
'will generate an error that we can avoid
If wkbName = vbNullString Then wkbName = ActiveWorkbook.Name
With Workbooks(wkbName)
For i = 1 To .Sheets.Count Step 1
Set rngRangeNameToFind = .Sheets(i).Range(strName)
Select Case Err.Number
Case 0
NamedRangeExists = True
Exit Function
Case 1004 '"Application defined or Object Defined error" - this is what
'we will get if the range name does not exist so just
'clear it out and move on
Err.Clear
Case Else
MsgBox Err.Number & " " & Err.Description
End Select
Next
End With
On Error GoTo 0
End Function

Why the "On Error Resume Next" (basically I would rather have a double root canal than ever use that - if there is an error I want to correct it, or handle it, not ignore it.)

As there is no method to check for if the name exists, the ERROR raised when accessing a non-existing name is what we need to detect if it exists or not.

So there is no complicated error-handling necessary because the method only does exactly this and we know that the error can only come from this event because that's what we're actually testing. Therefore the error itself can be ignored.

Function RangeNameExists(RangeName As String, Optional Wkbk As Workbook) As Boolean
' Returns TRUE if the range name exists.
Dim i As Long, RangeToFind As Range
Dim S As String
RangeNameExists = False
If Wkbk Is Nothing Then
Set Wkbk = ActiveWorkbook
End If
Err.Clear
On Error Resume Next
With Wkbk
For i = 1 To .Sheets.Count
' S = .Sheets(i).Name 'For test purposes.
Set RangeToFind = .Sheets(i).Range(RangeName)
If Err.Number = 0 Then
RangeNameExists = True
Exit For
Else
Err.Clear
End If
Next i
End With
On Error GoTo 0
Err.Clear
End Function

Then default RANGE object in EXCEL is the Workbook.Range, therefore only RANGE(RangeName).Name is required to return the name of the specified range.

FYI: a Boolean data type internally is considered a numeric data type with the values of 0 or -1
where 0 is false and -1 is true. Since all numeric variables initialize to zero, a boolean variable's
initialization to 0 is automatically FALSE...

The only error you will get with this is when the RangeName does not exsist
So simply ignoring the error and allowing the function to return its's default value of zero
which as a boolean data type means False, is the correct responce.