To to check whether a string is a valid reference

[Guest]

Hiya

I wish to take a string such as

ThisString = "'Sheet Name'!$A$1:$C$3"

And use it within a range object like so:

Set Location = Range(ThisString)

This example would work but I wish to avoid errors generated by strings
which aren't valid references.

Is there a method about which can check whether the string is a valid
reference?

I'm not too hot on error trapping and generally aim to avoid them in the
first place. So I would prefer not to use the On Error statement unless you
thoroughly explain it as if I'm very stupid.

TIA!

Nick

 

[Frank Kabel]

Hi
one way:
on error resume next
Set Location = Range(ThisString)
on error goto 0
if location is nothing then
msgbox "error"
exit sub
end if

 

[Guest]

Looks good to me.

Thanks.

Hi
one way:
on error resume next
Set Location = Range(ThisString)
on error goto 0
if location is nothing then
msgbox "error"
exit sub
end if

 

[Jake Marx]

Hi Nick,

on error resume next
Set Location = Range(ThisString)
on error goto 0
if location is nothing then
msgbox "error"
exit sub
end if

Frank's code should work fine.

Just to follow up on the second part of your post:

There's nothing wrong with using error handling in situations like this.
Since Frank used On Error Resume Next, the code will continue to the next
line even if a runtime error is encountered. So if the reference is
invalid, the Location object variable will not be set to a valid range, and
Location will be equal to Nothing.

Here's Frank's example wrapped in a function:

Public Function gbIsValidRange(rsAddress As String) As Boolean
On Error Resume Next
gbIsValidRange = Range(rsAddress).Row
On Error GoTo 0
End Function

Here's another function that shows how you could do it without error
handling:

Public Function gbIsValidRange2(rsAddress As String) As Boolean
gbIsValidRange2 = (StrComp(TypeName(Evaluate(rsAddress)), _
"range", vbTextCompare) = 0)
End Function

But I would recommend the first method, as it executes in about half the
time of the second.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]