Is range a valid address?

pk

unread,

Nov 9, 2003, 3:57:45 PM11/9/03

to

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.

Bob Phillips

unread,

Nov 9, 2003, 4:25:20 PM11/9/03

to

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)

"pk" <anon...@discussions.microsoft.com> wrote in message
news:00cd01c3a704$2013eb70$a601...@phx.gbl...

Vasant Nanavati

unread,

Nov 9, 2003, 4:33:47 PM11/9/03

to

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.

"Bob Phillips" <bob.ph...@tiscali.co.uk> wrote in message
news:uvD38fwp...@TK2MSFTNGP10.phx.gbl...

Bob Phillips

unread,

Nov 9, 2003, 4:44:58 PM11/9/03

to

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

"Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
news:OrfCqkw...@TK2MSFTNGP10.phx.gbl...

Tom Ogilvy

unread,

Nov 9, 2003, 5:11:48 PM11/9/03

to

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

--
Regards,
Tom Ogilvy

Vasant Nanavati <vasantn *AT* aol *DOT* com> wrote in message
news:OrfCqkw...@TK2MSFTNGP10.phx.gbl...

Tom Ogilvy

unread,

Nov 9, 2003, 5:21:24 PM11/9/03

to

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**.

--
Regards,
Tom Ogilvy

Bob Phillips <bob.ph...@tiscali.co.uk> wrote in message

news:eWhP#qwpDH...@tk2msftngp13.phx.gbl...

Tom Ogilvy

unread,

Nov 9, 2003, 5:43:11 PM11/9/03

to

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

--
Regards,
Tom Ogilvy

Tom Ogilvy <twog...@msn.com> wrote in message
news:vqtfh9c...@news.supernews.com...

Vasant Nanavati

unread,

Nov 9, 2003, 6:31:11 PM11/9/03

to

Good improvments, Bob and Tom. :-)

Regards,

Vasant.

"Tom Ogilvy" <twog...@msn.com> wrote in message

news:vqtgq2r...@news.supernews.com...