"String" manipulation for a Case clause

E

EagleOne

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
 
J

Jim Cone

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





<[email protected]>
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
 
E

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

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
Click to expand...
 
E

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!
 
J

Jim Cone

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


<[email protected]>
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!
 
E

EagleOne

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
..
 
R

Ron Rosenfeld

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
Click to expand...

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
 
E

EagleOne

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 are:

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 said:
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
Click to expand...
 
E

EagleOne

Ron, thank you.

Ron Rosenfeld said:
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
Click to expand...

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
Click to expand...
 
J

Jim Cone

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



<[email protected]>
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 are:

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 said:
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
Click to expand...
 
E

EagleOne

Got a bit further by capturing the full Numeric Constant string with:
*****************************************************************************
Sub FigureItOut()
Dim N As Long
Dim M As Long
Dim X As Long
Dim A 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 3)

'some extras in the string
strGiven = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038.66^35+[C:\123]'Clos-6ing'!E3^1"
'vThings = Array("-", "+", "^", "\", "/", "*")
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
A = M + 1
strWhat = Mid$(strGiven, M, 2)
Do
strWhat = strWhat & IIf(Mid$(strGiven, A + 1, 1) Like "#" Or _
Mid$(strGiven, A + 1, 1) = ".", Mid$(strGiven, A + 1, 1), "")
A = A + 1
Loop While Mid$(strGiven, A + 1, 1) Like "#" Or Mid$(strGiven, A + 1, 1) = "."
X = X + 1
strArr(X, 1) = strWhat
strArr(X, 2) = M
strArr(X, 3) = Len(strArr(X, 1))
Debug.Print "CharPlusSign: "; strArr(X, 1) & Space(5) & "StartPosInStr: " & _
strArr(X, 2) & Space(5) & "StrLength: " & strArr(X, 3)
End If
Else
Exit Do
End If
Loop
Next
Stop
ReDim Preserve strArr(1 To X)
'Range("A1", Cells(1, x)).Value = strArr()
For X = 0 To UBound(strArr)
Debug.Print strArr(X)
Next
End Sub
*****************************************************************************

Still need how to correctly write ReDim Preserve strArr(1 To X).elements( 1 to 3)???
 
E

EagleOne

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
 
E

EagleOne

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?
 
J

Jim Cone

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
--
Jim Cone
Portland, Oregon USA


<[email protected]>
wrote in message
Got a bit further by capturing the full Numeric Constant string with:
*****************************************************************************
Sub FigureItOut()
Dim N As Long
Dim M As Long
Dim X As Long
Dim A 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 3)

'some extras in the string
strGiven = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038.66^35+[C:\123]'Clos-6ing'!E3^1"
'vThings = Array("-", "+", "^", "\", "/", "*")
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
A = M + 1
strWhat = Mid$(strGiven, M, 2)
Do
strWhat = strWhat & IIf(Mid$(strGiven, A + 1, 1) Like "#" Or _
Mid$(strGiven, A + 1, 1) = ".", Mid$(strGiven, A + 1, 1), "")
A = A + 1
Loop While Mid$(strGiven, A + 1, 1) Like "#" Or Mid$(strGiven, A + 1, 1) = "."
X = X + 1
strArr(X, 1) = strWhat
strArr(X, 2) = M
strArr(X, 3) = Len(strArr(X, 1))
Debug.Print "CharPlusSign: "; strArr(X, 1) & Space(5) & "StartPosInStr: " & _
strArr(X, 2) & Space(5) & "StrLength: " & strArr(X, 3)
End If
Else
Exit Do
End If
Loop
Next
Stop
ReDim Preserve strArr(1 To X)
'Range("A1", Cells(1, x)).Value = strArr()
For X = 0 To UBound(strArr)
Debug.Print strArr(X)
Next
End Sub
*****************************************************************************

Still need how to correctly write ReDim Preserve strArr(1 To X).elements( 1 to 3)???
 
E

EagleOne

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

Thanks Jim
 
R

Ron Rosenfeld

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?
Click to expand...

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
 
E

EagleOne

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 said:
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?
Click to expand...

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
Click to expand...
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
E

EagleOne

Works great!

Ron Rosenfeld said:
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
Click to expand...
 
E

EagleOne

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Matching/Finding/Search Array Element 8
OR clause doesn't execute the last condition 2
Importing Long String - String Manipulation (EDI EANCOM 96a) 6
Displaying time gaps 6
Simultaneous calls 4
Creating combinations based on a criteria 6
Totally stuck trying to format,loop etc...please help a novice!tha 20
Extracting data from a text string: 6

Top