Is range a valid address?
 

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.

Is range a valid address?
 

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" wrote in message
...

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.

Is range a valid address?
 

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" wrote in message
...
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" wrote in message
...

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.

Is range a valid address?
 

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
...
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" wrote in message
...
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" wrote in message
...

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.

Is range a valid address?
 

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
...
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" wrote in message
...
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" wrote in message
...

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.

Is range a valid address?
 

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 wrote in message
...
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
...
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" wrote in message
...
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" wrote in message
...

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.

Is range a valid address?
 

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 wrote in message
...
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 wrote in message
...
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
...
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" wrote in message
...
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" wrote in message
...

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.

Is range a valid address?
 

Good improvments, Bob and Tom. :-)

Regards,

Vasant.

"Tom Ogilvy" wrote in message
...
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 wrote in message
...
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 wrote in message
...
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
...
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" wrote in message
...
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" wrote in message
...

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.