2003, 2007

What is the smartest way to be able to keep a "." (period) or a number from an cell address or
worksheet name/link or a workbook name/link being considered in the Case line below:

Dim CheckStr as string

CheckStr = Activecell.formula
TestChar = Mid(CheckStr, Counter, 1)
TestAsc = Asc(TestChar)
.....
' Is current character a Number or a "." (period)?
Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46

...... <Do things

I need to consider the complete formula string so that I can obtain the starting position of every
constant (defined as a number preceeded by a mathmatical operator) in the formula string. So I
cannot delete anything from CheckStr.

In short, I do not want the numbers from a cell address, a sheetname, workbook name or from a
directory link to be considered.

For example, in the formula below:
=-'Min. Int.'!F26-'Min. Int.'!F31+2803835+[C:\123]'Closing'!E31

I do not want the "." or 26 or 31 or 31 or 123 to be considered "acceptable" by:

Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46

My thoughts have included but not limited to:

1) ActiveWorkbook.LinkSources(xlExcelLinks)
2) Toggling Booleen True/False for alternate " ' " in the formula string (elim w/s references)
3) Toggling Booleen True/False for alternate "[" then "]" for links

Bottom line the VBA code to effectively:

Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46
(except "." and numbers which are not constants)

Hopefully there is an easy way to do this that I have not considered. ??

TIA EagleOne

Not exactly clear, but try this...
(and "\" is an arithmetic operator)
---
Sub FigureItOut()
Dim N As Long
Dim M As Long
Dim x As Long
Dim strWhat As String
Dim strGiven As String
Dim vThings As Variant
Dim strArr() As String
ReDim strArr(1 To 100)

'some extras in the string
strGiven = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038^35+[C:\123]'Clos-6ing'!E3^1"
vThings = Array("-", "+", "^", "\", "/", "*")

M = 0
For N = 0 To UBound(vThings)
Do
M = InStr(M + 1, strGiven, vThings(N), vbBinaryCompare)
If M 0 Then
If Mid$(strGiven, M + 1, 1) Like "#" Then
strWhat = Mid$(strGiven, M, 2)
x = x + 1
strArr(x) = strWhat
End If
Else
Exit Do
End If
Loop
Next

ReDim Preserve strArr(1 To x)
Range("A1", Cells(1, x)).Value = strArr()
End Sub

--
Jim Cone
Portland, Oregon USA






wrote in message
2003, 2007
What is the smartest way to be able to keep a "." (period) or a number from an cell address or
worksheet name/link or a workbook name/link being considered in the Case line below:
Dim CheckStr as string
CheckStr = Activecell.formula
TestChar = Mid(CheckStr, Counter, 1)
TestAsc = Asc(TestChar)
.....
' Is current character a Number or a "." (period)?
Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46

....... <Do things
I need to consider the complete formula string so that I can obtain the starting position of every
constant (defined as a number preceeded by a mathmatical operator) in the formula string. So I
cannot delete anything from CheckStr.
In short, I do not want the numbers from a cell address, a sheetname, workbook name or from a
directory link to be considered.
For example, in the formula below:
=-'Min. Int.'!F26-'Min. Int.'!F31+2803835+[C:\123]'Closing'!E31

I do not want the "." or 26 or 31 or 31 or 123 to be considered "acceptable" by:

Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46

My thoughts have included but not limited to:
1) ActiveWorkbook.LinkSources(xlExcelLinks)
2) Toggling Booleen True/False for alternate " ' " in the formula string (elim w/s references)
3) Toggling Booleen True/False for alternate "[" then "]" for links

Bottom line the VBA code to effectively:
Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46
(except "." and numbers which are not constants)
Hopefully there is an easy way to do this that I have not considered. ??
TIA EagleOne

Some progress below:

Assuming that CheckStr = "='Min. Int.'!D18-362.33"

The following code deletes Worksheet references and links from CheckStr2 and yields "=D18-362.33"
which represents the remaining Constants and Cell references.

CheckStr2 = CheckStr2
Set Wks = Nothing
For Each Wks In Worksheets
CheckStr2 = Replace(CheckStr2, "'" + Wks.Name + "'!", "")
CheckStr2 = Replace(CheckStr2, Wks.Name + "!", "")
Next Wks
On Error Resume Next
'Delete links from CheckStr2
Do
LinkStart = WorksheetFunction.Find("'", CheckStr2, 1)
' Intention below find " ' " plus link plus " '!" thus + 2
LinkEnd = WorksheetFunction.Find("'", CheckStr2, LinkStart + 1) + 2
LinkLength = LinkEnd - LinkStart
LinkStringToDelete = Trim(Mid(CheckStr2, LinkStart, LinkLength))
CheckStr2 = Trim(Replace(CheckStr2, LinkStringToDelete, ""))
Loop While WorksheetFunction.Find("'", CheckStr2)

Need help to programmatically remove all Cell references in CheckStr2 (in this case "D18") ?

Then I was going to do some kind of analysis of CheckStr and CheckStr2 like checking
InStr(1,CheckStr2,TestChar,xlTextCompare) 0 vs ?? InStr(1,CheckStr2,TestChar,xlTextCompare) 0
This is where my progress stops

Any thoughts appreciated

TIA EagleOne

wrote:

2003, 2007

What is the smartest way to be able to keep a "." (period) or a number from an cell address or
worksheet name/link or a workbook name/link being considered in the Case line below:

Dim CheckStr as string

CheckStr = Activecell.formula
TestChar = Mid(CheckStr, Counter, 1)
TestAsc = Asc(TestChar)
.....
' Is current character a Number or a "." (period)?
Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46

...... <Do things

I need to consider the complete formula string so that I can obtain the starting position of every
constant (defined as a number preceeded by a mathmatical operator) in the formula string. So I
cannot delete anything from CheckStr.

In short, I do not want the numbers from a cell address, a sheetname, workbook name or from a
directory link to be considered.

For example, in the formula below:
=-'Min. Int.'!F26-'Min. Int.'!F31+2803835+[C:\123]'Closing'!E31

I do not want the "." or 26 or 31 or 31 or 123 to be considered "acceptable" by:

Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46

My thoughts have included but not limited to:

1) ActiveWorkbook.LinkSources(xlExcelLinks)
2) Toggling Booleen True/False for alternate " ' " in the formula string (elim w/s references)
3) Toggling Booleen True/False for alternate "[" then "]" for links

Bottom line the VBA code to effectively:

Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46
(except "." and numbers which are not constants)

Hopefully there is an easy way to do this that I have not considered. ??

TIA EagleOne

Jim, I saw your response just after my additional information.

Does my additional info change your code?

Thank you so much for your time and knowledge!

"Jim Cone" wrote:

Not exactly clear, but try this...
(and "\" is an arithmetic operator)
---
Sub FigureItOut()
Dim N As Long
Dim M As Long
Dim x As Long
Dim strWhat As String
Dim strGiven As String
Dim vThings As Variant
Dim strArr() As String
ReDim strArr(1 To 100)

'some extras in the string
strGiven = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038^35+[C:\123]'Clos-6ing'!E3^1"
vThings = Array("-", "+", "^", "\", "/", "*")

M = 0
For N = 0 To UBound(vThings)
Do
M = InStr(M + 1, strGiven, vThings(N), vbBinaryCompare)
If M 0 Then
If Mid$(strGiven, M + 1, 1) Like "#" Then
strWhat = Mid$(strGiven, M, 2)
x = x + 1
strArr(x) = strWhat
End If
Else
Exit Do
End If
Loop
Next

ReDim Preserve strArr(1 To x)
Range("A1", Cells(1, x)).Value = strArr()
End Sub

I don' know; you were talking operator constants and now cell references?
My head hurts. The code is self contained - just run it.
--
Jim Cone
Portland, Oregon USA



wrote in message
Jim, I saw your response just after my additional information.
Does my additional info change your code?
Thank you so much for your time and knowledge!

Jim, I have to integrate the code in the morning. I'll let you know tomorrow morning.

I do not wish any numeric's other than numeric constants.

=-'Min. Int.'!F26-'Min. Int.'!F31+28038.35+[C:\123]'Closing'!E31+ A1

From Cell Refs, worksheet Tab names or filepath link info to be in the subset of allowable
characters. DO NOT want the numerals in sheetnames or the inclusive"." Periods or 26 or 123 or 31
or A1to be considered "acceptable".

I DO want the 28038.35 and the "+" sign infront of it . Hope that I have been clear!

Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46
..


"Jim Cone" wrote:


I don' know; you were talking operator constants and now cell references?
My head hurts. The code is self contained - just run it.

On Thu, 07 May 2009 19:08:44 -0400, wrote:

2003, 2007

What is the smartest way to be able to keep a "." (period) or a number from an cell address or
worksheet name/link or a workbook name/link being considered in the Case line below:

Dim CheckStr as string

CheckStr = Activecell.formula
TestChar = Mid(CheckStr, Counter, 1)
TestAsc = Asc(TestChar)
.....
' Is current character a Number or a "." (period)?
Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46

...... <Do things

I need to consider the complete formula string so that I can obtain the starting position of every
constant (defined as a number preceeded by a mathmatical operator) in the formula string. So I
cannot delete anything from CheckStr.

In short, I do not want the numbers from a cell address, a sheetname, workbook name or from a
directory link to be considered.

For example, in the formula below:
=-'Min. Int.'!F26-'Min. Int.'!F31+2803835+[C:\123]'Closing'!E31

I do not want the "." or 26 or 31 or 31 or 123 to be considered "acceptable" by:

Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46

My thoughts have included but not limited to:

1) ActiveWorkbook.LinkSources(xlExcelLinks)
2) Toggling Booleen True/False for alternate " ' " in the formula string (elim w/s references)
3) Toggling Booleen True/False for alternate "[" then "]" for links

Bottom line the VBA code to effectively:

Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46
(except "." and numbers which are not constants)

Hopefully there is an easy way to do this that I have not considered. ??

TIA EagleOne

I'm not quite sure exactly what you want to do.

But if you have defined the value that you wish to extract from the string as
being any numeric value that is preceded by an arithmetic operator, then the
following code is one example of how to do that, using Regular Expressions:

===========================
Option Explicit
Private Sub ExtrConstants(str As String)
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[-+/*^](\b\d*\.?\d+\b)"
If re.test(str) = True Then
Set mc = re.Execute(str)
For Each m In mc
Debug.Print m.SubMatches(0)
Next m
End If
End Sub
========================================
--ron

Jim, your code is very helpful. I have modified it slightly to add a second element to the array to
capture the character position in the strArr.

I am not sure how to correctly ReDim Preserve the new 2 element strArr. Currently, I get a
"Subscript out of range" the way it is. I tried ReDim Preserve(1 To x).elements(1 to 2) but it
failed.

Sub FigureItOut()
Dim N As Long
Dim M As Long
Dim x As Long
Dim strWhat As String
Dim strGiven As String
Dim vThings As Variant
Dim strArr() As String

ReDim strArr(1 To 100, 1 To 2) 'Note the 2nd element

'some extras in the string
strGiven = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038^35+[C:\123]'Clos-6ing'!E3^1"
vThings = Array("-", "+", "^", "\", "/", "*")

M = 0
For N = 0 To UBound(vThings)
Do
M = InStr(M + 1, strGiven, vThings(N), vbBinaryCompare)
If M 0 Then
If Mid$(strGiven, M + 1, 1) Like "#" Then
strWhat = Mid$(strGiven, M, 2)
x = x + 1
strArr(x, 1) = strWhat
strArr(x, 2) = M + 1 'Note the 2nd element
End If
Else
Exit Do
End If
Loop
Next

ReDim Preserve strArr(1 To x) 'This line fails because I added a 2nd element
For x = 0 To UBound(strArr)
Debug.Print strArr(x)
Next
End Sub

Except for the "\1", which I do not want, your code gives me the "Operator" and the first digit and
now its position, in the strGiven. As each TestChar approaches the Case below I could test if
TestChar IsNumeric(strArr(x)) AND if it is within the position-range of the entire Numeric.

[Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46]

The challenge is the code as written only captures the first digit after Operator.

At the end of the day, I would like only the numbers and "." for each true numeric constant in
strGiven to pass to the Case above. In strGiven the only true Numeric constants preceded by an
Operator a

Begining Full Numeric
1st Digit
-9 9
-7 7
-6 6
+2 28038
^3 35
^1 1
\1 <I'll remove this from consideration 123

Is there any easy way to capture the full numeric constant value?

Thank you again for your help!

Recapping:
1) How to correctly write the ReDim Preserve (with two elements)?
2) How to capture the full Numeric Constant in lieu of only 1st digit?
3) Any ideas on how to limit the strGiven characters to sucessfully pass to
the Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46

EagleOne

"Jim Cone" wrote:

Not exactly clear, but try this...
(and "\" is an arithmetic operator)
---
Sub FigureItOut()
Dim N As Long
Dim M As Long
Dim x As Long
Dim strWhat As String
Dim strGiven As String
Dim vThings As Variant
Dim strArr() As String
ReDim strArr(1 To 100)

'some extras in the string
strGiven = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038^35+[C:\123]'Clos-6ing'!E3^1"
vThings = Array("-", "+", "^", "\", "/", "*")

M = 0
For N = 0 To UBound(vThings)
Do
M = InStr(M + 1, strGiven, vThings(N), vbBinaryCompare)
If M 0 Then
If Mid$(strGiven, M + 1, 1) Like "#" Then
strWhat = Mid$(strGiven, M, 2)
x = x + 1
strArr(x) = strWhat
End If
Else
Exit Do
End If
Loop
Next

ReDim Preserve strArr(1 To x)
Range("A1", Cells(1, x)).Value = strArr()
End Sub

Ron, thank you.

Ron Rosenfeld wrote:

On Thu, 07 May 2009 19:08:44 -0400, wrote:

2003, 2007

What is the smartest way to be able to keep a "." (period) or a number from an cell address or
worksheet name/link or a workbook name/link being considered in the Case line below:

Dim CheckStr as string

CheckStr = Activecell.formula
TestChar = Mid(CheckStr, Counter, 1)
TestAsc = Asc(TestChar)
.....
' Is current character a Number or a "." (period)?
Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46

...... <Do things

I need to consider the complete formula string so that I can obtain the starting position of every
constant (defined as a number preceeded by a mathmatical operator) in the formula string. So I
cannot delete anything from CheckStr.

In short, I do not want the numbers from a cell address, a sheetname, workbook name or from a
directory link to be considered.

For example, in the formula below:
=-'Min. Int.'!F26-'Min. Int.'!F31+2803835+[C:\123]'Closing'!E31

I do not want the "." or 26 or 31 or 31 or 123 to be considered "acceptable" by:

Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46

My thoughts have included but not limited to:

1) ActiveWorkbook.LinkSources(xlExcelLinks)
2) Toggling Booleen True/False for alternate " ' " in the formula string (elim w/s references)
3) Toggling Booleen True/False for alternate "[" then "]" for links

Bottom line the VBA code to effectively:

Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46
(except "." and numbers which are not constants)

Hopefully there is an easy way to do this that I have not considered. ??

TIA EagleOne

I'm not quite sure exactly what you want to do.

But if you have defined the value that you wish to extract from the string as
being any numeric value that is preceded by an arithmetic operator, then the
following code is one example of how to do that, using Regular Expressions:

===========================
Option Explicit
Private Sub ExtrConstants(str As String)
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[-+/*^](\b\d*\.?\d+\b)"
If re.test(str) = True Then
Set mc = re.Execute(str)
For Each m In mc
Debug.Print m.SubMatches(0)
Next m
End If
End Sub
========================================
--ron

You can only "preserve" the last dimension of an array.
I recommend you see if Ron Rosenfeld's regular expressions code
does what you want before going further down the looping path.
--
Jim Cone
Portland, Oregon USA




wrote in message
Jim, your code is very helpful. I have modified it slightly to add a second element to the array to
capture the character position in the strArr.

I am not sure how to correctly ReDim Preserve the new 2 element strArr. Currently, I get a
"Subscript out of range" the way it is. I tried ReDim Preserve(1 To x).elements(1 to 2) but it
failed.

Sub FigureItOut()
Dim N As Long
Dim M As Long
Dim x As Long
Dim strWhat As String
Dim strGiven As String
Dim vThings As Variant
Dim strArr() As String

ReDim strArr(1 To 100, 1 To 2) 'Note the 2nd element

'some extras in the string
strGiven = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038^35+[C:\123]'Clos-6ing'!E3^1"
vThings = Array("-", "+", "^", "\", "/", "*")

M = 0
For N = 0 To UBound(vThings)
Do
M = InStr(M + 1, strGiven, vThings(N), vbBinaryCompare)
If M 0 Then
If Mid$(strGiven, M + 1, 1) Like "#" Then
strWhat = Mid$(strGiven, M, 2)
x = x + 1
strArr(x, 1) = strWhat
strArr(x, 2) = M + 1 'Note the 2nd element
End If
Else
Exit Do
End If
Loop
Next

ReDim Preserve strArr(1 To x) 'This line fails because I added a 2nd element
For x = 0 To UBound(strArr)
Debug.Print strArr(x)
Next
End Sub

Except for the "\1", which I do not want, your code gives me the "Operator" and the first digit and
now its position, in the strGiven. As each TestChar approaches the Case below I could test if
TestChar IsNumeric(strArr(x)) AND if it is within the position-range of the entire Numeric.

[Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46]

The challenge is the code as written only captures the first digit after Operator.

At the end of the day, I would like only the numbers and "." for each true numeric constant in
strGiven to pass to the Case above. In strGiven the only true Numeric constants preceded by an
Operator a

Begining Full Numeric
1st Digit
-9 9
-7 7
-6 6
+2 28038
^3 35
^1 1
\1 <I'll remove this from consideration 123

Is there any easy way to capture the full numeric constant value?

Thank you again for your help!

Recapping:
1) How to correctly write the ReDim Preserve (with two elements)?
2) How to capture the full Numeric Constant in lieu of only 1st digit?
3) Any ideas on how to limit the strGiven characters to sucessfully pass to
the Case TestAsc = 48 And TestAsc <= 57 Or TestAsc = 46

EagleOne

"Jim Cone" wrote:

Not exactly clear, but try this...
(and "\" is an arithmetic operator)
---
Sub FigureItOut()
Dim N As Long
Dim M As Long
Dim x As Long
Dim strWhat As String
Dim strGiven As String
Dim vThings As Variant
Dim strArr() As String
ReDim strArr(1 To 100)

'some extras in the string
strGiven = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038^35+[C:\123]'Clos-6ing'!E3^1"
vThings = Array("-", "+", "^", "\", "/", "*")

M = 0
For N = 0 To UBound(vThings)
Do
M = InStr(M + 1, strGiven, vThings(N), vbBinaryCompare)
If M 0 Then
If Mid$(strGiven, M + 1, 1) Like "#" Then
strWhat = Mid$(strGiven, M, 2)
x = x + 1
strArr(x) = strWhat
End If
Else
Exit Do
End If
Loop
Next

ReDim Preserve strArr(1 To x)
Range("A1", Cells(1, x)).Value = strArr()
End Sub

I real stupid question, I have been able to use array elements (I think) within the same procedure
w/o "Preserving".

The latest "myRevision" of your code gives me the info I need as I believe than Ron's code will give
me the numeric constants but not the position in the strGiven.

As you can tell I am a newbie to Arrays, actually VBA.

Thank you very much for your help. For me at my age I learn new areas best when I must deal with it
in a real challenge. Reading about VBA flies thru my brain unless I can embellish code which works.

BTW, I posted an additional quested re "Preserving" in between my work and your response which I
appreciate..

EagleOne


"Jim Cone" wrote:

You can only "preserve" the last dimension of an array.
I recommend you see if Ron Rosenfeld's regular expressions code
does what you want before going further down the looping path.

Love the code; but very weak in RegEx.

That said, your code works fine.

CharPlusSign: -9 StartPosInStr: 1 StrLength: 2
CharPlusSign: -7 StartPosInStr: 24 StrLength: 2
CharPlusSign: -6 StartPosInStr: 62 StrLength: 2
CharPlusSign: +28038.66 StartPosInStr: 36 StrLength: 9
CharPlusSign: ^35 StartPosInStr: 45 StrLength: 3
CharPlusSign: ^1 StartPosInStr: 71 StrLength: 2


I used your code to get:
9
7
28038.66
35
1

Your code as I used it:

Private Sub ExtrConstants(str As String)
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[-+/*^](\b\d*\.?\d+\b)"
If re.Test(str) = True Then
Set mc = re.Execute(str)
For Each m In mc
Debug.Print m.SubMatches(0)
Next m
End If
End Sub
Sub Test()
Dim str As String
str = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038.66^35+[C:\123]'Clos-6ing'!E3^1"
ExtrConstants (str)
End Sub

Is there a way to include the sign, numeric constant and additionally
the position in the string and its Length?


Option Explicit
Private Sub ExtrConstants(str As String)
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[-+/*^](\b\d*\.?\d+\b)"
If re.test(str) = True Then
Set mc = re.Execute(str)
For Each m In mc
Debug.Print m.SubMatches(0)
Next m
End If
End Sub

It is a pleasure to see how well a professional can make VBA concise and efficient!

Thanks Jim

"Jim Cone" wrote:

With a 2 dimensional array, you can only resize & preserve the second dimension.
So with. arr(1 to 100, 1 to 3) - you cannot change the 1 to 100 part.
'---
Sub FigureItOut_R1()
Dim N As Long
Dim M As Long
Dim x As Long
Dim i As Long
Dim lngLength As Long
Dim strWhat As String
Dim strGiven As String
Dim vThings As Variant
Dim strArr() As String
ReDim strArr(1 To 100)

strGiven = _
"-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038.66^35+[C:\123]'Clos-6ing'!E3^1"
vThings = Array("-", "+", "^", "\", "/", "*")
strGiven = strGiven & " "
lngLength = Len(strGiven)
M = 0

For N = 0 To UBound(vThings)
Do
M = InStr(M + 1, strGiven, vThings(N), vbBinaryCompare)
If M 0 Then
If Mid$(strGiven, M + 1, 1) Like "#" Then
For i = M + 2 To lngLength
If Not Mid(strGiven, i, 1) Like "[0-9.]" Then
' strWhat = Mid$(strGiven, M + 1, i - (M + 1))
strWhat = Mid$(strGiven, M, i - M) & Chr$(10) & _
"Len: " & i - M & Chr$(10) & "Pos: " & M
Exit For
End If
Next
x = x + 1
strArr(x) = strWhat
End If
Else
Exit Do
End If
Loop
Next

ReDim Preserve strArr(1 To x)
Range("A1", Cells(1, x)).Value = strArr()
End Sub

On Fri, 08 May 2009 12:15:59 -0400, wrote:

CharPlusSign: -9 StartPosInStr: 1 StrLength: 2
CharPlusSign: -7 StartPosInStr: 24 StrLength: 2
CharPlusSign: -6 StartPosInStr: 62 StrLength: 2
CharPlusSign: +28038.66 StartPosInStr: 36 StrLength: 9
CharPlusSign: ^35 StartPosInStr: 45 StrLength: 3
CharPlusSign: ^1 StartPosInStr: 71 StrLength: 2


I used your code to get:
9
7
28038.66
35
1

Your code as I used it:

Private Sub ExtrConstants(str As String)
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[-+/*^](\b\d*\.?\d+\b)"
If re.Test(str) = True Then
Set mc = re.Execute(str)
For Each m In mc
Debug.Print m.SubMatches(0)
Next m
End If
End Sub
Sub Test()
Dim str As String
str = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038.66^35+[C:\123]'Clos-6ing'!E3^1"
ExtrConstants (str)
End Sub

Is there a way to include the sign, numeric constant and additionally
the position in the string and its Length?

If what you mean is to replicate what you have above, then yes.

However, please confirm that you do NOT want the "-6" to be returned. That
does not look like a constant to me, but rather a worksheet name.

Also, in your example above, there is no "=" prior to the -9. If that will be
an option, one further modification is needed.

====================
Option Explicit
Private Sub ExtrConstants(str As String)
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "(^|[-+/*^=])\b\d*\.?\d+\b"
If re.Test(str) = True Then
Set mc = re.Execute(str)
For Each m In mc
Debug.Print "CharPlusSign: " & m.Value, _
"StartPosInStr: " & m.FirstIndex + 1, _
"StrLength: " & m.Length
Next m
End If
End Sub
===================================

Returns (using your Sub Test()):
CharPlusSign: -9 StartPosInStr: 1 StrLength: 2
CharPlusSign: -7 StartPosInStr: 24 StrLength: 2
CharPlusSign: +28038.66 StartPosInStr: 36 StrLength: 9
CharPlusSign: ^35 StartPosInStr: 45 StrLength: 3
CharPlusSign: ^1 StartPosInStr: 71 StrLength: 2


--ron

Excellent catch! I do not know how I missed that.

That string was originally conceived as:
"=-'Min. Int.'!F26-'Min. Int.'!F31+2803835+'Min. Int.'!E31"

Then stuff was added to test for odd-ball situations. That said,
I should have caught it

As always, you are outstanding with RegEx.

Thanks Ron

p.s. Consider checking back if I have any questions.




Ron Rosenfeld wrote:

On Fri, 08 May 2009 12:15:59 -0400, wrote:

CharPlusSign: -9 StartPosInStr: 1 StrLength: 2
CharPlusSign: -7 StartPosInStr: 24 StrLength: 2
CharPlusSign: -6 StartPosInStr: 62 StrLength: 2
CharPlusSign: +28038.66 StartPosInStr: 36 StrLength: 9
CharPlusSign: ^35 StartPosInStr: 45 StrLength: 3
CharPlusSign: ^1 StartPosInStr: 71 StrLength: 2


I used your code to get:
9
7
28038.66
35
1

Your code as I used it:

Private Sub ExtrConstants(str As String)
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[-+/*^](\b\d*\.?\d+\b)"
If re.Test(str) = True Then
Set mc = re.Execute(str)
For Each m In mc
Debug.Print m.SubMatches(0)
Next m
End If
End Sub
Sub Test()
Dim str As String
str = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038.66^35+[C:\123]'Clos-6ing'!E3^1"
ExtrConstants (str)
End Sub

Is there a way to include the sign, numeric constant and additionally
the position in the string and its Length?

If what you mean is to replicate what you have above, then yes.

However, please confirm that you do NOT want the "-6" to be returned. That
does not look like a constant to me, but rather a worksheet name.

Also, in your example above, there is no "=" prior to the -9. If that will be
an option, one further modification is needed.

====================
Option Explicit
Private Sub ExtrConstants(str As String)
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "(^|[-+/*^=])\b\d*\.?\d+\b"
If re.Test(str) = True Then
Set mc = re.Execute(str)
For Each m In mc
Debug.Print "CharPlusSign: " & m.Value, _
"StartPosInStr: " & m.FirstIndex + 1, _
"StrLength: " & m.Length
Next m
End If
End Sub
===================================

Returns (using your Sub Test()):
CharPlusSign: -9 StartPosInStr: 1 StrLength: 2
CharPlusSign: -7 StartPosInStr: 24 StrLength: 2
CharPlusSign: +28038.66 StartPosInStr: 36 StrLength: 9
CharPlusSign: ^35 StartPosInStr: 45 StrLength: 3
CharPlusSign: ^1 StartPosInStr: 71 StrLength: 2


--ron

On Fri, 08 May 2009 17:29:12 -0400, wrote:

Excellent catch! I do not know how I missed that.

That string was originally conceived as:
"=-'Min. Int.'!F26-'Min. Int.'!F31+2803835+'Min. Int.'!E31"

Then stuff was added to test for odd-ball situations. That said,
I should have caught it

As always, you are outstanding with RegEx.

Thanks Ron

p.s. Consider checking back if I have any questions.


You're welcome. Thanks for the feedback.

My newsreader should pick up any additions to this thread, so if you have more
questions, try to keep it in the thread.

Best wishes,
--ron

Works great!

Ron Rosenfeld wrote:

On Fri, 08 May 2009 17:29:12 -0400, wrote:

Excellent catch! I do not know how I missed that.

That string was originally conceived as:
"=-'Min. Int.'!F26-'Min. Int.'!F31+2803835+'Min. Int.'!E31"

Then stuff was added to test for odd-ball situations. That said,
I should have caught it

As always, you are outstanding with RegEx.

Thanks Ron

p.s. Consider checking back if I have any questions.


You're welcome. Thanks for the feedback.

My newsreader should pick up any additions to this thread, so if you have more
questions, try to keep it in the thread.

Best wishes,
--ron

Ron,

When you have time, would you mind discussing the components of the pattern line? i.e.
The only recognizable aspect to me are the "signs"/operators [-+/*^=]

're.Pattern = "[-+/*^](\b\d*\.?\d+\b)" 'Previous version
re.Pattern = "(^|[-+/*^=])\b\d*\.?\d+\b" ' Adds (^| ) and "="

Changes noted (^| = ) )

Also, what pattern adds (does not add) the "signs?

I love this "Regex"

TIA EagleOne


Ron Rosenfeld wrote:

On Fri, 08 May 2009 17:29:12 -0400, wrote:

Excellent catch! I do not know how I missed that.

That string was originally conceived as:
"=-'Min. Int.'!F26-'Min. Int.'!F31+2803835+'Min. Int.'!E31"

Then stuff was added to test for odd-ball situations. That said,
I should have caught it

As always, you are outstanding with RegEx.

Thanks Ron

p.s. Consider checking back if I have any questions.


You're welcome. Thanks for the feedback.

My newsreader should pick up any additions to this thread, so if you have more
questions, try to keep it in the thread.

Best wishes,
--ron

On Sat, 09 May 2009 10:51:20 -0400, wrote:

re.Pattern = "(^|[-+/*^=])\b\d*\.?\d+\b"

Here is a formal explanation:

======================================
Extract Constants

(^|[-+/*^=])\b\d*\.?\d+\b

Match the regular expression below and capture its match into backreference
number 1 «(^|[-+/*^=])»
Match either the regular expression below (attempting the next alternative
only if this one fails) «^»
Assert position at the beginning of the string «^»
Or match regular expression number 2 below (the entire group fails if this
one fails to match) «[-+/*^=]»
Match a single character present in the list “-+/*^=” «[-+/*^=]»
Assert position at a word boundary «\b»
Match a single digit 0..9 «\d*»
Between zero and unlimited times, as many times as possible, giving back as
needed (greedy) «*»
Match the character “.” literally «\.?»
Between zero and one times, as many times as possible, giving back as needed
(greedy) «?»
Match a single digit 0..9 «\d+»
Between one and unlimited times, as many times as possible, giving back as
needed (greedy) «+»
Assert position at a word boundary «\b»


Created with RegexBuddy
======================================

We are not using the backreferences. The parentheses are used for grouping
purposes.

A "word boundary" is a position that is between a "word character" and a
"non-word character". Word charactes include [A-Za-z0-9_]

Some of these references may be helpful (some may not still be valid, though):

Regular Expressions
http://www.regular-expressions.info/reference.html
http://support.microsoft.com/default...02&Product=vbb
http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx
http://msdn2.microsoft.com/en-us/library/ms974619.aspx
http://www.regex-guru.info/
--ron

On Sat, 09 May 2009 13:51:20 -0400, wrote:

Ron,

I recalled this information from a few years ago. I present it only for your ease.

Is there anything you would like to add/amplify re the current subject?

TIA EagleOne

Not really.

I think the current routine works better for extracting constants.

Every so often I work on a routine to parse formulas correctly, but it is still
a work in progress.
--ron

Your comment "A work in progress" is extremely interesting to me.

Silly me, I thought I was close that goal - with 100% your help even!

What are some examples/situations your are struggling with?

In short, I do not know what I do not know. I thought I was near heaven.

EagleOne

Ron Rosenfeld wrote:

On Sat, 09 May 2009 13:51:20 -0400, wrote:

Ron,

I recalled this information from a few years ago. I present it only for your ease.

Is there anything you would like to add/amplify re the current subject?

TIA EagleOne

Not really.

I think the current routine works better for extracting constants.

Every so often I work on a routine to parse formulas correctly, but it is still
a work in progress.
--ron

Excellent! Thanks

Ron Rosenfeld wrote:

On Sat, 09 May 2009 10:51:20 -0400, wrote:

re.Pattern = "(^|[-+/*^=])\b\d*\.?\d+\b"

Here is a formal explanation:

======================================
Extract Constants

(^|[-+/*^=])\b\d*\.?\d+\b

Match the regular expression below and capture its match into backreference
number 1 «(^|[-+/*^=])»
Match either the regular expression below (attempting the next alternative
only if this one fails) «^»
Assert position at the beginning of the string «^»
Or match regular expression number 2 below (the entire group fails if this
one fails to match) «[-+/*^=]»
Match a single character present in the list “-+/*^=” «[-+/*^=]»
Assert position at a word boundary «\b»
Match a single digit 0..9 «\d*»
Between zero and unlimited times, as many times as possible, giving back as
needed (greedy) «*»
Match the character “.” literally «\.?»
Between zero and one times, as many times as possible, giving back as needed
(greedy) «?»
Match a single digit 0..9 «\d+»
Between one and unlimited times, as many times as possible, giving back as
needed (greedy) «+»
Assert position at a word boundary «\b»


Created with RegexBuddy
======================================

We are not using the backreferences. The parentheses are used for grouping
purposes.

A "word boundary" is a position that is between a "word character" and a
"non-word character". Word charactes include [A-Za-z0-9_]

Some of these references may be helpful (some may not still be valid, though):

Regular Expressions
http://www.regular-expressions.info/reference.html
http://support.microsoft.com/default...02&Product=vbb
http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx
http://msdn2.microsoft.com/en-us/library/ms974619.aspx
http://www.regex-guru.info/
--ron

On Sun, 10 May 2009 13:11:42 -0400, wrote:

Your comment "A work in progress" is extremely interesting to me.

Silly me, I thought I was close that goal - with 100% your help even!

What are some examples/situations your are struggling with?

In short, I do not know what I do not know. I thought I was near heaven.

EagleOne

Well, in addition to parsing out constants and operators, you would also need
to parse out Functions; Names; Function arguments; cell addresses and ranges in
a variety of formats; handle arrays properly; and so forth.
--ron

My needs "might" be a bit more limited. I do realize that Excel, VBA, UDF can manipulate values.

That said I need to be able to find undocumented (almost always, unlabeled additions, subtractions
and/or * or / of same) in formula strings. The user then needs to document those constants.

Obviously my game plan can be made difficult by those sophisticated accountants who may manipulate
values in hidden cells, hidden formulas, Interior.ColorIndex = Font.ColorIndex, ranges, sheets or
functions.

I have proceedures to catch all the above except for functions.

Below are some notes from your prior work that I collected; with current comments:

'From: Ron Rosenfeld
'Subject: How in to parse constants in formula to cells
'Date: Mon, 27 Nov 2006 09:05:29 -0500
'Newsgroups: microsoft.public.Excel.programming
'With regard to some of the issues:
' it returns the negative signed values
' it does NOT return "within string" constants
Here I think you mean the formula string Yes/No?
or do you mean "123456" within a Function?
i.e. VLookup
' it returns 3% as 3
' it returns all constants within a function
This is OK as surprises can occur as a result

Realizing that I am now to be a dead man resulting from my limited perspective, what have I missed?
Gulp!

EagleOne

Ron Rosenfeld wrote:

On Sun, 10 May 2009 13:11:42 -0400, wrote:

Your comment "A work in progress" is extremely interesting to me.

Silly me, I thought I was close that goal - with 100% your help even!

What are some examples/situations your are struggling with?

In short, I do not know what I do not know. I thought I was near heaven.

EagleOne

Well, in addition to parsing out constants and operators, you would also need
to parse out Functions; Names; Function arguments; cell addresses and ranges in
a variety of formats; handle arrays properly; and so forth.
--ron

On Sun, 10 May 2009 15:02:15 -0400, wrote:

'From: Ron Rosenfeld
'Subject: How in to parse constants in formula to cells
'Date: Mon, 27 Nov 2006 09:05:29 -0500
'Newsgroups: microsoft.public.Excel.programming
'With regard to some of the issues:
' it returns the negative signed values
' it does NOT return "within string" constants
Here I think you mean the formula string Yes/No?
or do you mean "123456" within a Function?
i.e. VLookup

It's been a long time since I've worked on this routine. But, off the top of
my head, this routine will not return anything from a formula of the type:

="1/2/09"-"4/6/08"

It also needs to be able to handle NAME'd constants.
--ron

That is what I thought and that is fine for my purposes.

Again, thank you so much for sharing your wealth re Regex + other thoughts.

EagleOne


Ron Rosenfeld wrote:

On Sun, 10 May 2009 15:02:15 -0400, wrote:

'From: Ron Rosenfeld
'Subject: How in to parse constants in formula to cells
'Date: Mon, 27 Nov 2006 09:05:29 -0500
'Newsgroups: microsoft.public.Excel.programming
'With regard to some of the issues:
' it returns the negative signed values
' it does NOT return "within string" constants
Here I think you mean the formula string Yes/No?
or do you mean "123456" within a Function?
i.e. VLookup

It's been a long time since I've worked on this routine. But, off the top of
my head, this routine will not return anything from a formula of the type:

="1/2/09"-"4/6/08"

It also needs to be able to handle NAME'd constants.
--ron

Ron,

For the heck of it:

Starting with: Pattern = (^|[-+/*^=])\b\d*\.?\d+\b

Is there a modification to the pattern which would eliminate all Functions from consideration?

EagleOne

Ron Rosenfeld wrote:

On Sun, 10 May 2009 15:02:15 -0400, wrote:

'From: Ron Rosenfeld
'Subject: How in to parse constants in formula to cells
'Date: Mon, 27 Nov 2006 09:05:29 -0500
'Newsgroups: microsoft.public.Excel.programming
'With regard to some of the issues:
' it returns the negative signed values
' it does NOT return "within string" constants
Here I think you mean the formula string Yes/No?
or do you mean "123456" within a Function?
i.e. VLookup

It's been a long time since I've worked on this routine. But, off the top of
my head, this routine will not return anything from a formula of the type:

="1/2/09"-"4/6/08"

It also needs to be able to handle NAME'd constants.
--ron

On Sun, 10 May 2009 17:49:43 -0400, wrote:

Ron,

For the heck of it:

Starting with: Pattern = (^|[-+/*^=])\b\d*\.?\d+\b

Is there a modification to the pattern which would eliminate all Functions from consideration?

EagleOne

I can't think of one off-hand. Probably you'd need a list of functions to
start with, and then look for paired parentheses. Then remove those substrings
from the original. You'll have to deal with nesting also.
--ron

Well it was thought. Heck it could have been ~{/ !

Ron Rosenfeld wrote:

On Sun, 10 May 2009 17:49:43 -0400, wrote:

Ron,

For the heck of it:

Starting with: Pattern = (^|[-+/*^=])\b\d*\.?\d+\b

Is there a modification to the pattern which would eliminate all Functions from consideration?

EagleOne

I can't think of one off-hand. Probably you'd need a list of functions to
start with, and then look for paired parentheses. Then remove those substrings
from the original. You'll have to deal with nesting also.
--ron