regex validate rangename

[mp]

any regex experts here?
trying to validate a string passed in to see if it refers to a valid excel
range name
"a1" or "A1" to "iv65536" or "IV65536"

this clearly isn't it, i'm a regex dunce!
Regex regex = new Regex("[a-zA-Z][a-iA-I][a-vA-V][1-65536]");
this is no good either
Regex regex = new Regex("[a-zA-Zaa-ivAA-IV][1-65536]");

bool b;
b = regex.IsMatch("x1");
Debug.Print(b.ToString());//true

b = regex.IsMatch("1a");
Debug.Print(b.ToString());//false

b = regex.IsMatch("AC65536");
Debug.Print(b.ToString());//true

b = regex.IsMatch("IV65537");
Debug.Print(b.ToString());//false

b = regex.IsMatch("IV65536");
Debug.Print(b.ToString());//true

any tips appreciated
i suppose i could parse the incoming string and separate the alpha and
numeric parts
but with regex that shouldn't be necessary, right?
thanks
mark

 

[GS]

Firstly, the example you posted aren't range names. They're range
address references. -Don't have a regex solution but I do have some VBA
solutions...

For checking if a cell address exists:



For checking if a 'named' range exists:

Function bNameExists(DefinedName As String) As Boolean
' Checks for a name in the active workbook
' Arguments: DefinedName The defined name
' Returns: True if name exists

Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Names(DefinedName)
bNameExists = (Err = 0)
End Function

Function bValidSheet(RangeName As String) As Boolean
' Checks for a local named range on the active sheet
' Arguments: RangeName The defined name of a range
' Returns: True if sRangeName exists

Dim x As Object
On Error Resume Next
Set x = ActiveSheet.Range(RangeName)
bValidSheet = (Err = 0)
End Function

Usage:
If bNameExists("DefinedName") Then
'Do stuff
End If

If bValidSheet("RangeName") Then
'Do stuff
End If

OR use an operator
If Not...

 

[GS]

GS used his keyboard to write :

For checking if a cell address exists:

?? Message sent before I clicked 'Send'!!!

To check if a cell address exists:
Dim x As Range, bRangeExists As Boolean
On Error Resume Next
Set x = ActiveSheet.Range("A1")
bRangeExists = (Err = 0)

 

[Rick Rothstein]

How about a non-RegEx solution...

Function IsRangeAddress(PossibleAddress As String) As Boolean
On Error Resume Next
IsRangeAddress = Range(PossibleAddress).Row
On Error GoTo 0
End Function

Just pass your range address text into the function and it will return True
if that text is a valid range address for the workbook it is being run from.
If you do not want he function housing, you can just embed the three lines
of code directly into your own code at the appropriate location.

Rick Rothstein (MVP - Excel)



"mp" wrote in message
any regex experts here?
trying to validate a string passed in to see if it refers to a valid excel
range name
"a1" or "A1" to "iv65536" or "IV65536"

this clearly isn't it, i'm a regex dunce!
Regex regex = new Regex("[a-zA-Z][a-iA-I][a-vA-V][1-65536]");
this is no good either
Regex regex = new Regex("[a-zA-Zaa-ivAA-IV][1-65536]");

bool b;
b = regex.IsMatch("x1");
Debug.Print(b.ToString());//true

b = regex.IsMatch("1a");
Debug.Print(b.ToString());//false

b = regex.IsMatch("AC65536");
Debug.Print(b.ToString());//true

b = regex.IsMatch("IV65537");
Debug.Print(b.ToString());//false

b = regex.IsMatch("IV65536");
Debug.Print(b.ToString());//true

any tips appreciated
i suppose i could parse the incoming string and separate the alpha and
numeric parts
but with regex that shouldn't be necessary, right?
thanks
mark

 

[mp]

How about a non-RegEx solution...

Function IsRangeAddress(PossibleAddress As String) As Boolean
On Error Resume Next
IsRangeAddress = Range(PossibleAddress).Row
On Error GoTo 0
End Function

Just pass your range address text into the function and it will return
True if that text is a valid range address for the workbook it is being
run from. If you do not want he function housing, you can just embed the
three lines of code directly into your own code at the appropriate
location.

Rick Rothstein (MVP - Excel)

yes, easier to let Excel do the validation
thanks Rick and GS
mark

 

[Ron Rosenfeld]

any regex experts here?
trying to validate a string passed in to see if it refers to a valid excel
range name
"a1" or "A1" to "iv65536" or "IV65536"

There are easier ways to validate a range reference than using regular expressions, but for what it's worth, the following should work with Excel 2003 and earlier, and also demonstrates a method to validate defined ranges of letters or numbers:

\$?\b([A-Z]|[A-H][A-Z]|I[A-V])\$?([1-9]\d{0,3}|[1-5]\d{4}|6[0-4]\d{3}|65[0-4]\d{2}|655[0-2]\d|6553[0-6])\b([:\s]\$?\b([A-Z]|[A-H][A-Z]|I[A-V])\$?([1-9]\d{0,3}|[1-5]\d{4}|6[0-4]\d{3}|65[0-4]\d{2}|655[0-2]\d|6553[0-6])\b)?

And try this for Excel 2007 and later:

\$?(?:\bXF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b([:\s]\$?(?:\bXF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b)?

 

[mp]

any regex experts here?
trying to validate a string passed in to see if it refers to a valid excel
range name
"a1" or "A1" to "iv65536" or "IV65536"

There are easier ways to validate a range reference than using regular
expressions, but for what it's worth, the following should work with Excel
2003 and earlier, and also demonstrates a method to validate defined
ranges of letters or numbers:

\$?\b([A-Z]|[A-H][A-Z]|I[A-V])\$?([1-9]\d{0,3}|[1-5]\d{4}|6[0-4]\d{3}|65[0-4]\d{2}|655[0-2]\d|6553[0-6])\b([:\s]\$?\b([A-Z]|[A-H][A-Z]|I[A-V])\$?([1-9]\d{0,3}|[1-5]\d{4}|6[0-4]\d{3}|65[0-4]\d{2}|655[0-2]\d|6553[0-6])\b)?

And try this for Excel 2007 and later:

\$?(?:\bXF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b([:\s]\$?(?:\bXF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b)?

holy cow batman, that's one for the record books!
it will take me a while to digest that!
Thanks
like you say, and others have pointed out elsewhere, probably easier to let
excel throw it's own error if given bad input.
but if i get to where i can decipher your answer it will take me a long
way toward learning more about regex

I would have to Ucase the input I see.
Since i can pass "a1" to excel and it will understand I mean "A1"
where the regex wouldn't...however that would be an easy
and sensible fix for a function that would be doing this validating
Thanks
mark

 

[Ron Rosenfeld]

I would have to Ucase the input I see.
Since i can pass "a1" to excel and it will understand I mean "A1"
where the regex wouldn't...however that would be an easy
and sensible fix for a function that would be doing this validating

Actually not. If case insensitivity is a requirement, you can just set to ignore case in your regex object.

In VBA, ignorecase is a property of the regex object, so you just need to set it to True.

e.g. regex.Ignorecase = true

(where regex is the name of your regular expression object)

 

[mp]

Actually not. If case insensitivity is a requirement, you can just set to
ignore case in your regex object.

In VBA, ignorecase is a property of the regex object, so you just need to
set it to True.

e.g. regex.Ignorecase = true

(where regex is the name of your regular expression object)

oh right, i forgot about that!
Thanks again
mark
ps, i'm on excel 2002
did the max row/col increase with later versions?
I seem to have IV65536 as the lowest rightmost cell.

 

[Gord Dibben]

2003 remains same as 2002 regarding rows and columns.

2007 and later moved to 16,384 columns and 1,048,576 rows.


Gord Dibben MS Excel MVP

 

[mp]

Thanks
mark

2003 remains same as 2002 regarding rows and columns.

2007 and later moved to 16,384 columns and 1,048,576 rows.


Gord Dibben MS Excel MVP