Excel VBA - Verify A Named Range Exists

[Hack]

I found this piece of code on the web to verify whether or not a named range exists.

Function NameExists(TheName As String) As Boolean
On Error Resume Next
NameExists = Len(ThisWorkbook.Names(TheName).Name) <> 0
End Function

I use it like this

If NameExists("PYAcuteBase") = True Then
       Wkb1.Sheets("HFR Setting").Range(pyactuebase).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
Else
       Wkb1.Sheets("HFR Setting").Range("E16").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
End If

But, it always returns False even when the range does exist.

I just need to know if a range name exists...if it does, then I want to use it. If it doesn't, not big deal, I will just hard code the cell location I want.

[Ron Weller]

I use a Function almost identical to yours, and it works fine.

Public Function IsRangeName(RangeName As String) As Boolean
  On Error Resume Next
  IsRangeName = Len(Names(RangeName).Name) <> 0
End Function

[Hack]

Ok...maybe it is how I'm calling it.

What code do you use to call your IsRangeName function? Is it similiar to my If/Else statement?

[Hack]

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?

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

[KerryXEX]

Hi,

You both had the same error in the function.
It has to be "Len(.Range(RangeName).Name"

And also make sure, the function has a false value first. If then an object error occurs because the name does not exist, it stays false; if the name exist, it gets true:

Public Function IsRangeName(mySh As Worksheet, RangeName As String) As Boolean
On Error Resume Next
  
  IsRangeName = False
  IsRangeName = Len(mySh.Range(RangeName).Name) <> 0

End Function

[Hack]

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.)

[KerryXEX]

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.