PDA

View Full Version : Solved: Check if Named Range exists



Innany
06-06-2006, 11:24 AM
How would you check in VBA if the named range already defined on the sheet?

mdmackillop
06-06-2006, 12:06 PM
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

malik641
06-06-2006, 08:18 PM
You could also use a function (modifying MD's code a little bit)

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

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

Side note: it is not case-sensitive.

Innany
06-07-2006, 06:19 AM
Thank you, function is definetely what I need, I will try it.

geekgirlau
06-09-2006, 02:45 AM
Don't forget to mark this thread as "Solved" - go to the "Thread Tools" option at the top of the screen.

Innany
06-12-2006, 01:06 PM
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
06-12-2006, 01:15 PM
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:


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

End If

where server is an existing range, it will return 1.

if I use it with a variable:


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

End If

where c=server1, it returns 0.

Can't figure out what is going on.

Any ideas?

mdmackillop
06-12-2006, 02:38 PM
Because c is a variable, it doesn't go into quotes


If RangeExist(c) = 1 Then

malik641
06-12-2006, 07:07 PM
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:

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

End If
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.