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



"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"Peter T" <peter_t@discussions wrote in message
...
"Rick Rothstein (MVP - VB)" wrote in
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