Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function to Check if value in a String variable is actually a cell Address
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function to Check if value in a String variable is actually a cell Address
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function to Check if value in a String variable is actually a cell Address
"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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function to Check if value in a String variable is actually a cell Address
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function to Check if value in a String variable is actually a cell Address
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function to Check if value in a String variable is actually a cell Address
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 "Dana DeLouis" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function to Check if value in a String variable is actually a cell Address
..either a valid address ...
Oops! You are right. I missed that point. Sorry. -- Dana DeLouis "Rick Rothstein (MVP - VB)" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Function to Check if value in a String variable is actually a cell Address
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check if variable contains a string | Excel Worksheet Functions | |||
How can I use a variable as cell address? | Excel Programming | |||
MIN Function w/ variable address reference | Excel Worksheet Functions | |||
Passing Cell Address (String or Range) | Excel Programming | |||
Setting a variable cell address | Excel Discussion (Misc queries) |