To to check whether a string is a valid reference
 

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

To to check whether a string is a valid reference
 

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

--
Regards
Frank Kabel
Frankfurt, Germany
"Nick Shinkins" <Nick schrieb im
Newsbeitrag ...
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

To to check whether a string is a valid reference
 

Looks good to me.

Thanks.

"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany
"Nick Shinkins" <Nick schrieb im
Newsbeitrag ...
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

To to check whether a string is a valid reference
 

Hi Nick,

I wish to take a string such as

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

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

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:

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.

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]