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

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.

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

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.

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

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

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

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

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