Prev: move item in multiselect listbox
Next: populating neighbouring columns based on drop-down choice
From: EagleOne on 2 May 2010 21:41
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
From: Ron Rosenfeld on 2 May 2010 22:28
On Sun, 02 May 2010 21:41:09 -0400, EagleOne(a)discussions.microsoft.com wrote:

>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
From: EagleOne on 3 May 2010 00:34
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


Ron Rosenfeld <ronrosenfeld(a)nospam.org> wrote:

>On Sun, 02 May 2010 21:41:09 -0400, EagleOne(a)discussions.microsoft.com wrote:
>
>>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
From: Ron Rosenfeld on 3 May 2010 06:31
On Mon, 03 May 2010 00:34:54 -0400, EagleOne(a)discussions.microsoft.com wrote:

>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
From: EagleOne on 3 May 2010 07:23
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 <ronrosenfeld(a)nospam.org> wrote:

>On Mon, 03 May 2010 00:34:54 -0400, EagleOne(a)discussions.microsoft.com wrote:
>
>>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
 |  Next  |  Last
Pages: 1 2
Prev: move item in multiselect listbox
Next: populating neighbouring columns based on drop-down choice