VBA Function to Check if value in a String variable is actually a cell Address

[noname]

Hi all,


Does anyone know or has made a VBA function to check if the value
contained in a String variable is a cell address?

.....Something like its of Type CellAddress...

Regards,

 

[Rick Rothstein \(MVP - VB\)]

Does anyone know or has made a VBA function to check if

the value contained in a String variable is a cell address?

....Something like its of Type CellAddress...

I don't know for sure, but I **think** this function will return True only
for an String value that represents a valid range (either a single cell or a
range of cells)...

Function IsRange(Address As String) As Boolean
Dim R As Range
On Error Resume Next
Set R = Worksheets(1).Range(Address)
If Err.Number = 0 Then IsRange = True
End Function

It also seems to work with named ranges as well.

Rick

 

[Peter T]

I don't know for sure, but I **think** this function will return True only
for an String value that represents a valid range (either a single cell or a
range of cells)...

Function IsRange(Address As String) As Boolean
Dim R As Range
On Error Resume Next
Set R = Worksheets(1).Range(Address)
If Err.Number = 0 Then IsRange = True
End Function

It also seems to work with named ranges as well.

Rick

Hi Rick,

Concerning named ranges, it would only work if the name referred to a range
on Worksheets(1) in the activeworkbook. Similarly a 'full' address that
qualifies sheet and perhaps workbook name like -
"[theBook.xls]Sheet2!$A$1".

For most address's, providing the activesheet is a worksheet it should be OK
to do simply
Set R = Range(Address)
If Err.Number = 0 Then IsRange = True

If the address is in R1C1 style one way to validate it would be to assign it
to the Refersto property of a temporary name.

To cater for all types of address's, eg partially/fully qualified,
normal/named etc, would need a bit more than the one line test. However if
all the OP wants to do is to validate something like "A1:B2, D2:E3" (less
than 255 characters) your function should be just perfect!

Regards,
Peter T

 

[Rick Rothstein \(MVP - VB\)]

Good point about the referenced Worksheet... I didn't think that through all
the way and thought I had to provide a fool-proof sheet reference. Good
point also on the named range (try and guess which sheet I did my testing
on<g>).

By the way, the function I posted can (I'm pretty sure) be reduced in size
to this...

Function IsRange(TestAddress As String) As Boolean
On Error Resume Next
IsRange = Len(Range(TestAddress).Address)
End Function

Same comments you made about my original function would apply here.

Rick

I don't know for sure, but I **think** this function will return True
only
for an String value that represents a valid range (either a single cell
or a
range of cells)...

Function IsRange(Address As String) As Boolean
Dim R As Range
On Error Resume Next
Set R = Worksheets(1).Range(Address)
If Err.Number = 0 Then IsRange = True
End Function

It also seems to work with named ranges as well.

Rick

Hi Rick,

Concerning named ranges, it would only work if the name referred to a
range
on Worksheets(1) in the activeworkbook. Similarly a 'full' address that
qualifies sheet and perhaps workbook name like -
"[theBook.xls]Sheet2!$A$1".

For most address's, providing the activesheet is a worksheet it should be
OK
to do simply
Set R = Range(Address)
If Err.Number = 0 Then IsRange = True

If the address is in R1C1 style one way to validate it would be to assign
it
to the Refersto property of a temporary name.

To cater for all types of address's, eg partially/fully qualified,
normal/named etc, would need a bit more than the one line test. However if
all the OP wants to do is to validate something like "A1:B2, D2:E3" (less
than 255 characters) your function should be just perfect!

Regards,
Peter T

 

[Dana DeLouis]

Hi. Here's mine.

Function IsNamedRange(S As String) As Boolean
'// Does Name exists as a Range?
On Error Resume Next
IsNamedRange = Names(S).RefersToRange.Count > 0
End Function

One needs to add 1 additional line if you want to check if it refers to a
Range Named Constant.

--
Dana DeLouis

Good point about the referenced Worksheet... I didn't think that through
all the way and thought I had to provide a fool-proof sheet reference.
Good point also on the named range (try and guess which sheet I did my
testing on<g>).

By the way, the function I posted can (I'm pretty sure) be reduced in size
to this...

Function IsRange(TestAddress As String) As Boolean
On Error Resume Next
IsRange = Len(Range(TestAddress).Address)
End Function

Same comments you made about my original function would apply here.

Rick

Does anyone know or has made a VBA function to check if
the value contained in a String variable is a cell address?

....Something like its of Type CellAddress...

I don't know for sure, but I **think** this function will return True
only
for an String value that represents a valid range (either a single cell
or a
range of cells)...

Function IsRange(Address As String) As Boolean
Dim R As Range
On Error Resume Next
Set R = Worksheets(1).Range(Address)
If Err.Number = 0 Then IsRange = True
End Function

It also seems to work with named ranges as well.

Rick

Hi Rick,

Concerning named ranges, it would only work if the name referred to a
range
on Worksheets(1) in the activeworkbook. Similarly a 'full' address that
qualifies sheet and perhaps workbook name like -
"[theBook.xls]Sheet2!$A$1".

For most address's, providing the activesheet is a worksheet it should be
OK
to do simply
Set R = Range(Address)
If Err.Number = 0 Then IsRange = True

If the address is in R1C1 style one way to validate it would be to assign
it
to the Refersto property of a temporary name.

To cater for all types of address's, eg partially/fully qualified,
normal/named etc, would need a bit more than the one line test. However
if
all the OP wants to do is to validate something like "A1:B2, D2:E3" (less
than 255 characters) your function should be just perfect!

Regards,
Peter T

 

[Rick Rothstein \(MVP - VB\)]

Unless I am missing something, the (shortened) code I just posted will
return TRUE if the passed in String is either a valid address (or address
range) OR a valid Named Range... I don't think a separate test function is
required.

Rick

Hi. Here's mine.

Function IsNamedRange(S As String) As Boolean
'// Does Name exists as a Range?
On Error Resume Next
IsNamedRange = Names(S).RefersToRange.Count > 0
End Function

One needs to add 1 additional line if you want to check if it refers to a
Range Named Constant.

--
Dana DeLouis

Good point about the referenced Worksheet... I didn't think that through
all the way and thought I had to provide a fool-proof sheet reference.
Good point also on the named range (try and guess which sheet I did my
testing on<g>).

By the way, the function I posted can (I'm pretty sure) be reduced in
size to this...

Function IsRange(TestAddress As String) As Boolean
On Error Resume Next
IsRange = Len(Range(TestAddress).Address)
End Function

Same comments you made about my original function would apply here.

Rick

message Does anyone know or has made a VBA function to check if
the value contained in a String variable is a cell address?

....Something like its of Type CellAddress...

I don't know for sure, but I **think** this function will return True
only
for an String value that represents a valid range (either a single cell
or
a
range of cells)...

Function IsRange(Address As String) As Boolean
Dim R As Range
On Error Resume Next
Set R = Worksheets(1).Range(Address)
If Err.Number = 0 Then IsRange = True
End Function

It also seems to work with named ranges as well.

Rick

Hi Rick,

Concerning named ranges, it would only work if the name referred to a
range
on Worksheets(1) in the activeworkbook. Similarly a 'full' address that
qualifies sheet and perhaps workbook name like -
"[theBook.xls]Sheet2!$A$1".

For most address's, providing the activesheet is a worksheet it should
be OK
to do simply
Set R = Range(Address)
If Err.Number = 0 Then IsRange = True

If the address is in R1C1 style one way to validate it would be to
assign it
to the Refersto property of a temporary name.

To cater for all types of address's, eg partially/fully qualified,
normal/named etc, would need a bit more than the one line test. However
if
all the OP wants to do is to validate something like "A1:B2, D2:E3"
(less
than 255 characters) your function should be just perfect!

Regards,
Peter T

 

[Dana DeLouis]

..either a valid address ...

Oops! You are right. I missed that point. Sorry.

 

[Peter T]

Function IsRange(TestAddress As String) As Boolean

On Error Resume Next
IsRange = Len(Range(TestAddress).Address)
End Function

Same comments you made about my original function would apply here.

Looks good, I think most of the previous comments can now be discounted. The
only ones that might still to be considered perhaps -

- The activesheet is a chart-sheet and TestAddress is a simple A1 style
address that's not qualified, eg "A1" vs "Sheet1!A1"
- TestAddress is a name that refers to a range that's not in the active
workbook

Regards,
Peter T