VBA and VB.NET give different results for same RegEx code

[EagleOne]

Excel 2007 & 2003


If you are thinking "Not this group" I can understand. That said, I am not sure where else to start?

Assume myCell.formula = "=2620137"
also FormulaText = "=2620137"

Assume a "For Each objMatch In objcolMatches"
which produces an "objMatch" in VBA of 2620137

But in VB.NET, the FormulaTest is parsed like:
2
6
2
0
1
3
7

GOAL: How to get VB.NET to match the VBA result of 2620137?

Any thoughts appreciated

EagleOne

********************** Code ***************************************

Const CellRef As String = "\$?[A-Z]{1,2}\$?\d{1,5}"
Const SignOperator As String = "[/*^&()=<>,+]"
Const WithinString As String = """[^""]*"""
Const NumConstant As String = "-?(\d*\.)?\d+"
Ignore = (WithinString & ", " & CellRef & ", " & SignOperator _
& ", " & "|")

FormulaText = myCell.Formula
If objRegExp.Test(FormulaText) = True Then
' replace unwanted stuff
objRegExp.Pattern = Ignore
' Get the matches.
objRegExp.Pattern = NumConstant
objcolMatches = objRegExp.Execute(FormulaText)
End If

 

[Ron Rosenfeld]

Excel 2007 & 2003


If you are thinking "Not this group" I can understand. That said, I am not sure where else to start?

Assume myCell.formula = "=2620137"
also FormulaText = "=2620137"

Assume a "For Each objMatch In objcolMatches"
which produces an "objMatch" in VBA of 2620137

But in VB.NET, the FormulaTest is parsed like:
2
6
2
0
1
3
7

GOAL: How to get VB.NET to match the VBA result of 2620137?

Any thoughts appreciated

EagleOne

********************** Code ***************************************

Const CellRef As String = "\$?[A-Z]{1,2}\$?\d{1,5}"
Const SignOperator As String = "[/*^&()=<>,+]"
Const WithinString As String = """[^""]*"""
Const NumConstant As String = "-?(\d*\.)?\d+"
Ignore = (WithinString & ", " & CellRef & ", " & SignOperator _
& ", " & "|")

FormulaText = myCell.Formula
If objRegExp.Test(FormulaText) = True Then
' replace unwanted stuff
objRegExp.Pattern = Ignore
' Get the matches.
objRegExp.Pattern = NumConstant
objcolMatches = objRegExp.Execute(FormulaText)
End If

I think the problem lies in the VB.NET syntax.

Your NumConstant regex looks OK. It should capture a floating point number with
an optional minus sign, and an optional integer portion. If there is an
integer portion, it will go into capturing group 1; but the entire integer
portion should go into that group.

So perhaps a newsgroup devoted to vb.net would be more appropriate.
--ron

 

[EagleOne]

Ron,

You are exactly the person that might find. You ARE the reason that I got involved with Regex.

In fact the code about which I used to layout my challenge is your code! Glad to hear that you are
fine. thanks for your thoughts,

As I remember, you ARE Regex in the US.

Thanks Eagleone

Excel 2007 & 2003


If you are thinking "Not this group" I can understand. That said, I am not sure where else to start?

Assume myCell.formula = "=2620137"
also FormulaText = "=2620137"

Assume a "For Each objMatch In objcolMatches"
which produces an "objMatch" in VBA of 2620137

But in VB.NET, the FormulaTest is parsed like:
2
6
2
0
1
3
7

GOAL: How to get VB.NET to match the VBA result of 2620137?

Any thoughts appreciated

EagleOne

********************** Code ***************************************

Const CellRef As String = "\$?[A-Z]{1,2}\$?\d{1,5}"
Const SignOperator As String = "[/*^&()=<>,+]"
Const WithinString As String = """[^""]*"""
Const NumConstant As String = "-?(\d*\.)?\d+"
Ignore = (WithinString & ", " & CellRef & ", " & SignOperator _
& ", " & "|")

FormulaText = myCell.Formula
If objRegExp.Test(FormulaText) = True Then
' replace unwanted stuff
objRegExp.Pattern = Ignore
' Get the matches.
objRegExp.Pattern = NumConstant
objcolMatches = objRegExp.Execute(FormulaText)
End If

I think the problem lies in the VB.NET syntax.

Your NumConstant regex looks OK. It should capture a floating point number with
an optional minus sign, and an optional integer portion. If there is an
integer portion, it will go into capturing group 1; but the entire integer
portion should go into that group.

So perhaps a newsgroup devoted to vb.net would be more appropriate.
--ron

 

[Ron Rosenfeld]

Ron,

You are exactly the person that might find. You ARE the reason that I got involved with Regex.

In fact the code about which I used to layout my challenge is your code! Glad to hear that you are
fine. thanks for your thoughts,

As I remember, you ARE Regex in the US.

Thanks Eagleone

Sorry I couldn't be of more help but I am not familiar with VB.NET.

And my inspiration to get into regular expressions was from Harlan Grove, who
is *much* more knowledgeable than I, but only posts here occasionally these
days.
--ron

 

[EagleOne]

Harlan Grove, there is a serious contributor. That said, when it comes to RegEx in the past 5-7
years you are the name that junps to the top!

 

[Ron Rosenfeld]

Harlan Grove, there is a serious contributor. That said, when it comes to RegEx in the past 5-7
years you are the name that junps to the top!

Thank you. I find them useful because, once I became familiar with them, it
saved me a lot of programming time.
--ron