Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero-Length string vs Zero (also a valid value) | Excel Worksheet Functions | |||
How to check valid Date value? | Excel Worksheet Functions | |||
Valid SQL string | Excel Discussion (Misc queries) | |||
How to check that a string is a valid formula ? | Excel Programming | |||
check for valid file | Excel Programming |