Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The address of this site is not valid. Check the address and try | Excel Discussion (Misc queries) | |||
The address of the site is not valid... | Excel Discussion (Misc queries) | |||
The address of this site is not valid. | Excel Discussion (Misc queries) | |||
How to create a range address with ADDRESS function? | Excel Worksheet Functions | |||
Database or list range is not valid | Excel Worksheet Functions |