Solved: Check if Named Range exists

[Innany]

How would you check in VBA if the named range already defined on the sheet?

[mdmackillop]

[VBA]Sub TestRange()
Dim t As Range, Txt As String
Txt = "Test1"
On Error Resume Next
Set t = Range(Txt)
If Err <> 0 Then
MsgBox "There is no range called " & Txt
Exit Sub
Else
Err.Clear
Range(Txt).Select
End If
End Sub
[/VBA]

[malik641]

You could also use a function (modifying MD's code a little bit)

[VBA]Function TestRange(Txt As String) As String
Dim t As Range
On Error Resume Next
Set t = Range(Txt)
If Err <> 0 Then
TestRange = "No Range"
Set t = Nothing
Exit Function
Else
Err.Clear
TestRange = "Range Exists"
End If

Set t = Nothing
End Function[/VBA]

Say my range was "Joe" then in any cell I would write: =TestRange("Joe")

Side note: it is not case-sensitive.

[Innany]

Thank you, function is definetely what I need, I will try it.

[geekgirlau]

Don't forget to mark this thread as "Solved" - go to the "Thread Tools" option at the top of the screen.

[Innany]

This is really bazaar. I am using your function above, just changed it to 1 for range exist and 0 to range does not. Works great from a cell, also works fine , when I call it via vba using a static range:

If RangeExist("del_mcc_auth1") = 1 Then
MsgBox "Range Exists: "
Else
MsgBox "Range Does not exist: "

End If

[Innany]

This is bazaar. I have tested your function above from a cell, works perfect. I only changed the result to integer, 0 for no range, 1 for range exists.

If I use from VBA this way it works fine:

[VBA]
If RangeExist("server") = 1 Then
MsgBox "Range Exists: "
Else
MsgBox "Range Does not exist: "

End If
[/VBA]
where server is an existing range, it will return 1.

if I use it with a variable:

[VBA]
If RangeExist("c") = 1 Then
MsgBox "Range Exists: "
Else
MsgBox "Range Does not exist: "

End If
[/VBA]
where c=server1, it returns 0.

Can't figure out what is going on.

Any ideas?

[mdmackillop]

Because c is a variable, it doesn't go into quotes

[VBA]
If RangeExist(c) = 1 Then

[/VBA]

[malik641]

Hey Innany,

I would be careful with using "= 1" to be true. False only equals zero, and all other values are considered true. What I would do is:

[VBA]If RangeExist("whatever") <> 0 Then
MsgBox "Range Exists: "
Else
MsgBox "Range Does not exist: "

End If[/VBA]
Just incase the function returns something other than 0 and 1....that is of course unless you set the function to either 1 or 0 in your code....in that case, nevermind.