Is range a valid address?

  • Thread starter Thread starter pk
  • Start date Start date
P

pk

Hello, hope someone can help?

I need a bullet proof function to which I can send a
variable to see whether it contains a valid cell/range
address.

As you all know range addresses may vary quite a bit:

A:A
$1:8
C$5
R27:$AC$4759

etc.

Perhaps in another function, to test if a variable
contains a valid named range?

Your example code would be most appreciated. Thanks in
advance.
 
Function IsRange(inRange) As Boolean
IsRange = TypeName(inRange) = "Range"
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob:

Or perhaps:

Function IsRange(addrRange As String) As Boolean
IsRange = TypeName(Range(addrRange)) = "Range"
End Function

just to avoid confusion between range objects and range addresses.

Regards,

Vasant.
 
Hi Vasant,

Ah yes .. but is a range address a range?

Seriously though, a good suggestion. Trouble is, a valid range, or not a
range address gives an error. The best I could come up with is this messy
alternative

Function IsRange(inRange) As Boolean
IsRange = TypeName(inRange) = "Range"
If Not IsRange Then
IsRange = False
On Error Resume Next
IsRange = TypeName(Range(inRange)) = "Range"
End If
End Function


Regards

Bob
 
You would still need to trap for an error when it isn't

Function IsRange(addrRange As String) As Boolean
On Error Resume Next
IsRange = TypeName(Range(addrRange)) = "Range"
End Function
 
Original question was about a string:
I need a bullet proof function to which I can send a
variable to see whether it contains a valid cell/range
**address**.
Click to expand...
 
to do both perhaps:

Function IsRange(addrRange As Variant) As Boolean
Select Case TypeName(addrRange)
Case "Range"
IsRange = True
Case "String"
On Error Resume Next
IsRange = TypeName(Range(addrRange)) = "Range"
Case Else
End Select
End Function
 
Back
Top