How to Parse a string; Delimiter is any Operator

[EagleOne]

2003/2007

Trying to consistently parse formula strings (Delimiter = any Opr sign) into i.e.

OprSigns = Array("+", "-", "*", "/", "^", ">", "<", "<>", ">=", "<=")
FormulaStr = "+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007 '!H22- _
A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"

All formulas will begin (stuffed) with "+" sign if not already a "-" [if this makes parsing easier]
Goal:
Operator
Parsed(1) = 123456789 +
Parsed(2) = 'Summary 2-22-2007'!H8 +
Parsed(3) = 'Summary 3-22-2007'!H22 -
Parsed(4) = A1 +
Parsed(5) = (144/6) *
Parsed(6) = Summary 4-22-2007'H23 -
Parsed(7) = B1 +
Parsed(8) = 9876 end of FormulaStr

It is important to isolate, for later retrieval, each operator AFTER each Parsed(x)

Attempted (looooose VBA) Split(FormulaStr, OprSigns,1,1)

Another challenge, avoiding sign-look-a-like characters between each " ' " followed by " ' ! " or
in the Path to other workbooks (still within " ' " followed by " ' ! " I believe) Eg., would be
the "-" in dates like above.

The approach I tried was to avoid parsing between " ' " followed by " ' ! " as Gap(1), Gap(2), etc.

I can get very close but I need someone with much better VBA skills to get the gold ring.

Thanks for any thoughts, approaches or cuss words.

EagleOne

 

[Guest]

This isn't a VBA problem, it is a linear algebra problem with no real answer.
To get a unique answer your grammar must be defined better. Dashes are in
dates and are being skipped because of the single quotes. Rules like these
need to be defined.

 

[EagleOne]

Thanks for the time.

The dashes in dates are not being skipped but intentionally avoided by skipping over anything
between " ' " and " '! ".

Frankly it is a VBA issue if one has to program this parsing challenge, in VBA.

This isn't a VBA problem, it is a linear algebra problem with no real answer.
To get a unique answer your grammar must be defined better. Dashes are in
dates and are being skipped because of the single quotes. Rules like these
need to be defined.

2003/2007

Trying to consistently parse formula strings (Delimiter = any Opr sign) into i.e.

OprSigns = Array("+", "-", "*", "/", "^", ">", "<", "<>", ">=", "<=")
FormulaStr = "+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007 '!H22- _
A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"

All formulas will begin (stuffed) with "+" sign if not already a "-" [if this makes parsing easier]
Goal:
Operator
Parsed(1) = 123456789 +
Parsed(2) = 'Summary 2-22-2007'!H8 +
Parsed(3) = 'Summary 3-22-2007'!H22 -
Parsed(4) = A1 +
Parsed(5) = (144/6) *
Parsed(6) = Summary 4-22-2007'H23 -
Parsed(7) = B1 +
Parsed(8) = 9876 end of FormulaStr

It is important to isolate, for later retrieval, each operator AFTER each Parsed(x)

Attempted (looooose VBA) Split(FormulaStr, OprSigns,1,1)

Another challenge, avoiding sign-look-a-like characters between each " ' " followed by " ' ! " or
in the Path to other workbooks (still within " ' " followed by " ' ! " I believe) Eg., would be
the "-" in dates like above.

The approach I tried was to avoid parsing between " ' " followed by " ' ! " as Gap(1), Gap(2), etc.

I can get very close but I need someone with much better VBA skills to get the gold ring.

Thanks for any thoughts, approaches or cuss words.

EagleOne

 

[Guest]

I like these type problems


Sub main()

Dim StringA As String
Dim StringB As String

StringA = "+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007
'!H22-A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"
StringB = ""
Call Parse("+", StringA, StringB)
StringA = StringB
Call Parse("+", StringA, StringB)
StringA = StringB
Call Parse("-", StringA, StringB)
StringA = StringB
Call Parse("+", StringA, StringB)
StringA = StringB
Call Parse("*", StringA, StringB)
StringA = StringB
Call Parse("-", StringA, StringB)
StringA = StringB
Call Parse("+", StringA, StringB)
End Sub
Sub Parse(ParseChar As String, ByRef String1, ByRef String2)

If StrComp(Left(String1, 1), ParseChar) = 0 Then
String1 = Mid(String1, 2)
End If

CharCount = 1
Do While StrComp(Mid(String1, CharCount, 1), ParseChar) <> 0

If StrComp(Mid(String1, CharCount, 1), "'") = 0 Then
CharCount = CharCount + 1
Do While Mid(String1, CharCount, 1) <> "'"
CharCount = CharCount + 1

Loop
End If
CharCount = CharCount + 1

Loop
String2 = Mid(String1, CharCount + 1)
String1 = Left(String1, CharCount - 1)
End Sub

Thanks for the time.

The dashes in dates are not being skipped but intentionally avoided by skipping over anything
between " ' " and " '! ".

Frankly it is a VBA issue if one has to program this parsing challenge, in VBA.

This isn't a VBA problem, it is a linear algebra problem with no real answer.
To get a unique answer your grammar must be defined better. Dashes are in
dates and are being skipped because of the single quotes. Rules like these
need to be defined.

2003/2007

Trying to consistently parse formula strings (Delimiter = any Opr sign) into i.e.

OprSigns = Array("+", "-", "*", "/", "^", ">", "<", "<>", ">=", "<=")
FormulaStr = "+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007 '!H22- _
A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"

All formulas will begin (stuffed) with "+" sign if not already a "-" [if this makes parsing easier]
Goal:
Operator
Parsed(1) = 123456789 +
Parsed(2) = 'Summary 2-22-2007'!H8 +
Parsed(3) = 'Summary 3-22-2007'!H22 -
Parsed(4) = A1 +
Parsed(5) = (144/6) *
Parsed(6) = Summary 4-22-2007'H23 -
Parsed(7) = B1 +
Parsed(8) = 9876 end of FormulaStr

It is important to isolate, for later retrieval, each operator AFTER each Parsed(x)

Attempted (looooose VBA) Split(FormulaStr, OprSigns,1,1)

Another challenge, avoiding sign-look-a-like characters between each " ' " followed by " ' ! " or
in the Path to other workbooks (still within " ' " followed by " ' ! " I believe) Eg., would be
the "-" in dates like above.

The approach I tried was to avoid parsing between " ' " followed by " ' ! " as Gap(1), Gap(2), etc.

I can get very close but I need someone with much better VBA skills to get the gold ring.

Thanks for any thoughts, approaches or cuss words.

EagleOne

 

[EagleOne]

Joel,

Thanks so much for your time and knowledge.

I am in the midst of assimilating your code.
How can I use the code when the order/number of the operators is variable.
i.e. assume that I have only two cell ref's separated by the "/" operator.

Please do not take this as critical. This code will absolutely work for the hardcoded
StringA. How can I adapt the code for use on any formula cell?

EagleOne

I like these type problems


Sub main()

Dim StringA As String
Dim StringB As String

StringA = "+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007
'!H22-A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"
StringB = ""
Call Parse("+", StringA, StringB)
StringA = StringB
Call Parse("+", StringA, StringB)
StringA = StringB
Call Parse("-", StringA, StringB)
StringA = StringB
Call Parse("+", StringA, StringB)
StringA = StringB
Call Parse("*", StringA, StringB)
StringA = StringB
Call Parse("-", StringA, StringB)
StringA = StringB
Call Parse("+", StringA, StringB)
End Sub
Sub Parse(ParseChar As String, ByRef String1, ByRef String2)

If StrComp(Left(String1, 1), ParseChar) = 0 Then
String1 = Mid(String1, 2)
End If

CharCount = 1
Do While StrComp(Mid(String1, CharCount, 1), ParseChar) <> 0

If StrComp(Mid(String1, CharCount, 1), "'") = 0 Then
CharCount = CharCount + 1
Do While Mid(String1, CharCount, 1) <> "'"
CharCount = CharCount + 1

Loop
End If
CharCount = CharCount + 1

Loop
String2 = Mid(String1, CharCount + 1)
String1 = Left(String1, CharCount - 1)
End Sub

Thanks for the time.

The dashes in dates are not being skipped but intentionally avoided by skipping over anything
between " ' " and " '! ".

Frankly it is a VBA issue if one has to program this parsing challenge, in VBA.

This isn't a VBA problem, it is a linear algebra problem with no real answer.
To get a unique answer your grammar must be defined better. Dashes are in
dates and are being skipped because of the single quotes. Rules like these
need to be defined.
2003/2007

Trying to consistently parse formula strings (Delimiter = any Opr sign) into i.e.

OprSigns = Array("+", "-", "*", "/", "^", ">", "<", "<>", ">=", "<=")
FormulaStr = "+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007 '!H22- _
A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"

All formulas will begin (stuffed) with "+" sign if not already a "-" [if this makes parsing easier]
Goal:
Operator
Parsed(1) = 123456789 +
Parsed(2) = 'Summary 2-22-2007'!H8 +
Parsed(3) = 'Summary 3-22-2007'!H22 -
Parsed(4) = A1 +
Parsed(5) = (144/6) *
Parsed(6) = Summary 4-22-2007'H23 -
Parsed(7) = B1 +
Parsed(8) = 9876 end of FormulaStr

It is important to isolate, for later retrieval, each operator AFTER each Parsed(x)

Attempted (looooose VBA) Split(FormulaStr, OprSigns,1,1)

Another challenge, avoiding sign-look-a-like characters between each " ' " followed by " ' ! " or
in the Path to other workbooks (still within " ' " followed by " ' ! " I believe) Eg., would be
the "-" in dates like above.

The approach I tried was to avoid parsing between " ' " followed by " ' ! " as Gap(1), Gap(2), etc.

I can get very close but I need someone with much better VBA skills to get the gold ring.

Thanks for any thoughts, approaches or cuss words.

EagleOne

 

[EagleOne]

Joel,

When....:

ParseChar : "-" : String
String1 : "'Summary 3-22-2007 " : Variant/String (note space 2007 ")
String2 : "'Summary 3-22-2007 " : Variant/String (note space 2007 ")

.... the following Loop does not terminate:

Do While Mid(String1, CharCount, 1) <> "'"
CharCount = CharCount + 1
Loop

EagleOne

I like these type problems


Sub main()

Dim StringA As String
Dim StringB As String

StringA = "+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007
'!H22-A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"
StringB = ""
Call Parse("+", StringA, StringB)
StringA = StringB
Call Parse("+", StringA, StringB)
StringA = StringB
Call Parse("-", StringA, StringB)
StringA = StringB
Call Parse("+", StringA, StringB)
StringA = StringB
Call Parse("*", StringA, StringB)
StringA = StringB
Call Parse("-", StringA, StringB)
StringA = StringB
Call Parse("+", StringA, StringB)
End Sub
Sub Parse(ParseChar As String, ByRef String1, ByRef String2)

If StrComp(Left(String1, 1), ParseChar) = 0 Then
String1 = Mid(String1, 2)
End If

CharCount = 1
Do While StrComp(Mid(String1, CharCount, 1), ParseChar) <> 0

If StrComp(Mid(String1, CharCount, 1), "'") = 0 Then
CharCount = CharCount + 1
Do While Mid(String1, CharCount, 1) <> "'"
CharCount = CharCount + 1

Loop
End If
CharCount = CharCount + 1

Loop
String2 = Mid(String1, CharCount + 1)
String1 = Left(String1, CharCount - 1)
End Sub

Thanks for the time.

The dashes in dates are not being skipped but intentionally avoided by skipping over anything
between " ' " and " '! ".

Frankly it is a VBA issue if one has to program this parsing challenge, in VBA.

This isn't a VBA problem, it is a linear algebra problem with no real answer.
To get a unique answer your grammar must be defined better. Dashes are in
dates and are being skipped because of the single quotes. Rules like these
need to be defined.
2003/2007

Trying to consistently parse formula strings (Delimiter = any Opr sign) into i.e.

OprSigns = Array("+", "-", "*", "/", "^", ">", "<", "<>", ">=", "<=")
FormulaStr = "+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007 '!H22- _
A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"

All formulas will begin (stuffed) with "+" sign if not already a "-" [if this makes parsing easier]
Goal:
Operator
Parsed(1) = 123456789 +
Parsed(2) = 'Summary 2-22-2007'!H8 +
Parsed(3) = 'Summary 3-22-2007'!H22 -
Parsed(4) = A1 +
Parsed(5) = (144/6) *
Parsed(6) = Summary 4-22-2007'H23 -
Parsed(7) = B1 +
Parsed(8) = 9876 end of FormulaStr

It is important to isolate, for later retrieval, each operator AFTER each Parsed(x)

Attempted (looooose VBA) Split(FormulaStr, OprSigns,1,1)

Another challenge, avoiding sign-look-a-like characters between each " ' " followed by " ' ! " or
in the Path to other workbooks (still within " ' " followed by " ' ! " I believe) Eg., would be
the "-" in dates like above.

The approach I tried was to avoid parsing between " ' " followed by " ' ! " as Gap(1), Gap(2), etc.

I can get very close but I need someone with much better VBA skills to get the gold ring.

Thanks for any thoughts, approaches or cuss words.

EagleOne

 

[EagleOne]

Never mind with previous post about the Loop. It was my BUE (basic user error)
in copy paste of:

StringA = "+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007
'!H22-A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"

I like these type problems


Sub main()

Dim StringA As String
Dim StringB As String

StringA = "+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007
'!H22-A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"
StringB = ""
Call Parse("+", StringA, StringB)
StringA = StringB
Call Parse("+", StringA, StringB)
StringA = StringB
Call Parse("-", StringA, StringB)
StringA = StringB
Call Parse("+", StringA, StringB)
StringA = StringB
Call Parse("*", StringA, StringB)
StringA = StringB
Call Parse("-", StringA, StringB)
StringA = StringB
Call Parse("+", StringA, StringB)
End Sub
Sub Parse(ParseChar As String, ByRef String1, ByRef String2)

If StrComp(Left(String1, 1), ParseChar) = 0 Then
String1 = Mid(String1, 2)
End If

CharCount = 1
Do While StrComp(Mid(String1, CharCount, 1), ParseChar) <> 0

If StrComp(Mid(String1, CharCount, 1), "'") = 0 Then
CharCount = CharCount + 1
Do While Mid(String1, CharCount, 1) <> "'"
CharCount = CharCount + 1

Loop
End If
CharCount = CharCount + 1

Loop
String2 = Mid(String1, CharCount + 1)
String1 = Left(String1, CharCount - 1)
End Sub

Thanks for the time.

The dashes in dates are not being skipped but intentionally avoided by skipping over anything
between " ' " and " '! ".

Frankly it is a VBA issue if one has to program this parsing challenge, in VBA.

This isn't a VBA problem, it is a linear algebra problem with no real answer.
To get a unique answer your grammar must be defined better. Dashes are in
dates and are being skipped because of the single quotes. Rules like these
need to be defined.
2003/2007

Trying to consistently parse formula strings (Delimiter = any Opr sign) into i.e.

OprSigns = Array("+", "-", "*", "/", "^", ">", "<", "<>", ">=", "<=")
FormulaStr = "+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007 '!H22- _
A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"

All formulas will begin (stuffed) with "+" sign if not already a "-" [if this makes parsing easier]
Goal:
Operator
Parsed(1) = 123456789 +
Parsed(2) = 'Summary 2-22-2007'!H8 +
Parsed(3) = 'Summary 3-22-2007'!H22 -
Parsed(4) = A1 +
Parsed(5) = (144/6) *
Parsed(6) = Summary 4-22-2007'H23 -
Parsed(7) = B1 +
Parsed(8) = 9876 end of FormulaStr

It is important to isolate, for later retrieval, each operator AFTER each Parsed(x)

Attempted (looooose VBA) Split(FormulaStr, OprSigns,1,1)

Another challenge, avoiding sign-look-a-like characters between each " ' " followed by " ' ! " or
in the Path to other workbooks (still within " ' " followed by " ' ! " I believe) Eg., would be
the "-" in dates like above.

The approach I tried was to avoid parsing between " ' " followed by " ' ! " as Gap(1), Gap(2), etc.

I can get very close but I need someone with much better VBA skills to get the gold ring.

Thanks for any thoughts, approaches or cuss words.

EagleOne

 

[Guest]

the subroutine main was just a test driver to demostrate the code worked.
when working with cells functtions can only return one value. Somehow you
need to get it to return two values. In this case I would add another input
parameter to indicate if you want the left side or right side of the parse.

=Parse("*","L",String) or
=Parse("*","R",String)


Function Parse(ParseChar As String, ReturnHalf as String, String1 as String)

If StrComp(Left(String1, 1), ParseChar) = 0 Then
String1 = Mid(String1, 2)
End If

CharCount = 1
Do While StrComp(Mid(String1, CharCount, 1), ParseChar) <> 0

If StrComp(Mid(String1, CharCount, 1), "'") = 0 Then
CharCount = CharCount + 1
Do While Mid(String1, CharCount, 1) <> "'"
CharCount = CharCount + 1

Loop
End If
CharCount = CharCount + 1

Loop

if ReturnHalf = "R" Then
Parse = Mid(String1, CharCount + 1)
else
Parse = Left(String1, CharCount - 1)
end if
End Function

Joel,

Thanks so much for your time and knowledge.

I am in the midst of assimilating your code.
How can I use the code when the order/number of the operators is variable.
i.e. assume that I have only two cell ref's separated by the "/" operator.

Please do not take this as critical. This code will absolutely work for the hardcoded
StringA. How can I adapt the code for use on any formula cell?

EagleOne

I like these type problems


Sub main()

Dim StringA As String
Dim StringB As String

StringA = "+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007
'!H22-A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"
StringB = ""
Call Parse("+", StringA, StringB)
StringA = StringB
Call Parse("+", StringA, StringB)
StringA = StringB
Call Parse("-", StringA, StringB)
StringA = StringB
Call Parse("+", StringA, StringB)
StringA = StringB
Call Parse("*", StringA, StringB)
StringA = StringB
Call Parse("-", StringA, StringB)
StringA = StringB
Call Parse("+", StringA, StringB)
End Sub
Sub Parse(ParseChar As String, ByRef String1, ByRef String2)

If StrComp(Left(String1, 1), ParseChar) = 0 Then
String1 = Mid(String1, 2)
End If

CharCount = 1
Do While StrComp(Mid(String1, CharCount, 1), ParseChar) <> 0

If StrComp(Mid(String1, CharCount, 1), "'") = 0 Then
CharCount = CharCount + 1
Do While Mid(String1, CharCount, 1) <> "'"
CharCount = CharCount + 1

Loop
End If
CharCount = CharCount + 1

Loop
String2 = Mid(String1, CharCount + 1)
String1 = Left(String1, CharCount - 1)
End Sub

Thanks for the time.

The dashes in dates are not being skipped but intentionally avoided by skipping over anything
between " ' " and " '! ".

Frankly it is a VBA issue if one has to program this parsing challenge, in VBA.


This isn't a VBA problem, it is a linear algebra problem with no real answer.
To get a unique answer your grammar must be defined better. Dashes are in
dates and are being skipped because of the single quotes. Rules like these
need to be defined.
2003/2007

Trying to consistently parse formula strings (Delimiter = any Opr sign) into i.e.

OprSigns = Array("+", "-", "*", "/", "^", ">", "<", "<>", ">=", "<=")
FormulaStr = "+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007 '!H22- _
A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"

All formulas will begin (stuffed) with "+" sign if not already a "-" [if this makes parsing easier]
Goal:
Operator
Parsed(1) = 123456789 +
Parsed(2) = 'Summary 2-22-2007'!H8 +
Parsed(3) = 'Summary 3-22-2007'!H22 -
Parsed(4) = A1 +
Parsed(5) = (144/6) *
Parsed(6) = Summary 4-22-2007'H23 -
Parsed(7) = B1 +
Parsed(8) = 9876 end of FormulaStr

It is important to isolate, for later retrieval, each operator AFTER each Parsed(x)

Attempted (looooose VBA) Split(FormulaStr, OprSigns,1,1)

Another challenge, avoiding sign-look-a-like characters between each " ' " followed by " ' ! " or
in the Path to other workbooks (still within " ' " followed by " ' ! " I believe) Eg., would be
the "-" in dates like above.

The approach I tried was to avoid parsing between " ' " followed by " ' ! " as Gap(1), Gap(2), etc.

I can get very close but I need someone with much better VBA skills to get the gold ring.

Thanks for any thoughts, approaches or cuss words.

EagleOne

 

[Ron Rosenfeld]

2003/2007

Trying to consistently parse formula strings (Delimiter = any Opr sign) into i.e.

OprSigns = Array("+", "-", "*", "/", "^", ">", "<", "<>", ">=", "<=")
FormulaStr = "+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007 '!H22- _
A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"

All formulas will begin (stuffed) with "+" sign if not already a "-" [if this makes parsing easier]
Goal:
Operator
Parsed(1) = 123456789 +
Parsed(2) = 'Summary 2-22-2007'!H8 +
Parsed(3) = 'Summary 3-22-2007'!H22 -
Parsed(4) = A1 +
Parsed(5) = (144/6) *
Parsed(6) = Summary 4-22-2007'H23 -
Parsed(7) = B1 +
Parsed(8) = 9876 end of FormulaStr

It is important to isolate, for later retrieval, each operator AFTER each Parsed(x)

Attempted (looooose VBA) Split(FormulaStr, OprSigns,1,1)

Another challenge, avoiding sign-look-a-like characters between each " ' " followed by " ' ! " or
in the Path to other workbooks (still within " ' " followed by " ' ! " I believe) Eg., would be
the "-" in dates like above.

The approach I tried was to avoid parsing between " ' " followed by " ' ! " as Gap(1), Gap(2), etc.

I can get very close but I need someone with much better VBA skills to get the gold ring.

Thanks for any thoughts, approaches or cuss words.

EagleOne

This solution, using Regular Expressions, seems to work on your example, as
well as on examples including two-character operators.

I parsed into a 2D array so that

Parsed(1,1) contains the string, and Parsed(1,2) contains the associated
operator.

I used a "1" based array to be consistent with what you had written above.

Not being certain what you wanted to do with the parsed string, I merely
printed it in the VBA Immediate Window. Obviously many different ways of using
the data.

See if this does what you want.

Given:
"+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007 '!H22-A1+(144/6)*'Summary
4-22-2007'!H23-B1+9876"

It returns:

Parsed(1) = 123456789 +
Parsed(2) = 'Summary 2-22-2007'!H8 +
Parsed(3) = 'Summary 3-22-2007 '!H22 -
Parsed(4) = A1 +
Parsed(5) = (144/6) *
Parsed(6) = 'Summary 4-22-2007'!H23 -
Parsed(7) = B1 +
Parsed(8) = 9876

===================================================
Option Explicit

Public Const FormulaStr As String = "+123456789+'Summary 2-22-2007'!H8+'Summary
3-22-2007 '!H22-A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"
Sub Parse()
Dim Parsed() As String

Dim objRegExp As Object
Dim objMatchCollection As Object
Dim sPattern As String
Dim i As Long

sPattern =
"(('[\s\S]*?'!\w+)|(\([\s\S]*?\))|([^-+*/^<>=]+))([-+*/^<>][<>=]?|$)"


Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.MultiLine = True

objRegExp.Pattern = sPattern
If objRegExp.Test(FormulaStr) = True Then
Set objMatchCollection = objRegExp.Execute(FormulaStr)
ReDim Parsed(1 To objMatchCollection.Count, 1 To 2)
For i = 1 To objMatchCollection.Count
Parsed(i, 1) = objMatchCollection(i - 1).submatches(0)
Parsed(i, 2) = objMatchCollection(i - 1).submatches(4)
Next i
End If

For i = 1 To UBound(Parsed)
Debug.Print "Parsed(" & i&; ") = " & Parsed(i, 1), Parsed(i, 2)
Next i


End Sub


==========================================

Note that the line starting "Public Const FormulaStr" is all on a single line
in the example above, but it could be on multiple lines if you were obtaining
it from an Excel cell or some other source.
--ron

 

[Ron Rosenfeld]

2003/2007

Trying to consistently parse formula strings (Delimiter = any Opr sign) into i.e.

OprSigns = Array("+", "-", "*", "/", "^", ">", "<", "<>", ">=", "<=")
FormulaStr = "+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007 '!H22- _
A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"

All formulas will begin (stuffed) with "+" sign if not already a "-" [if this makes parsing easier]
Goal:
Operator
Parsed(1) = 123456789 +
Parsed(2) = 'Summary 2-22-2007'!H8 +
Parsed(3) = 'Summary 3-22-2007'!H22 -
Parsed(4) = A1 +
Parsed(5) = (144/6) *
Parsed(6) = Summary 4-22-2007'H23 -
Parsed(7) = B1 +
Parsed(8) = 9876 end of FormulaStr

It is important to isolate, for later retrieval, each operator AFTER each Parsed(x)

Attempted (looooose VBA) Split(FormulaStr, OprSigns,1,1)

Another challenge, avoiding sign-look-a-like characters between each " ' " followed by " ' ! " or
in the Path to other workbooks (still within " ' " followed by " ' ! " I believe) Eg., would be
the "-" in dates like above.

The approach I tried was to avoid parsing between " ' " followed by " ' ! " as Gap(1), Gap(2), etc.

I can get very close but I need someone with much better VBA skills to get the gold ring.

Thanks for any thoughts, approaches or cuss words.

EagleOne

This solution, using Regular Expressions, seems to work on your example, as
well as on examples including two-character operators.

I parsed into a 2D array so that

Parsed(1,1) contains the string, and Parsed(1,2) contains the associated
operator.

I used a "1" based array to be consistent with what you had written above.

Not being certain what you wanted to do with the parsed string, I merely
printed it in the VBA Immediate Window. Obviously many different ways of using
the data.

See if this does what you want.

Given:
"+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007 '!H22-A1+(144/6)*'Summary
4-22-2007'!H23-B1+9876"

It returns:

Parsed(1) = 123456789 +
Parsed(2) = 'Summary 2-22-2007'!H8 +
Parsed(3) = 'Summary 3-22-2007 '!H22 -
Parsed(4) = A1 +
Parsed(5) = (144/6) *
Parsed(6) = 'Summary 4-22-2007'!H23 -
Parsed(7) = B1 +
Parsed(8) = 9876

===================================================
Option Explicit

Public Const FormulaStr As String = "+123456789+'Summary 2-22-2007'!H8+'Summary
3-22-2007 '!H22-A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"
Sub Parse()
Dim Parsed() As String

Dim objRegExp As Object
Dim objMatchCollection As Object
Dim sPattern As String
Dim i As Long

sPattern =
"(('[\s\S]*?'!\w+)|(\([\s\S]*?\))|([^-+*/^<>=]+))([-+*/^<>][<>=]?|$)"


Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.MultiLine = True

objRegExp.Pattern = sPattern
If objRegExp.Test(FormulaStr) = True Then
Set objMatchCollection = objRegExp.Execute(FormulaStr)
ReDim Parsed(1 To objMatchCollection.Count, 1 To 2)
For i = 1 To objMatchCollection.Count
Parsed(i, 1) = objMatchCollection(i - 1).submatches(0)
Parsed(i, 2) = objMatchCollection(i - 1).submatches(4)
Next i
End If

For i = 1 To UBound(Parsed)
Debug.Print "Parsed(" & i&; ") = " & Parsed(i, 1), Parsed(i, 2)
Next i


End Sub


==========================================

Note that the line starting "Public Const FormulaStr" is all on a single line
in the example above, but it could be on multiple lines if you were obtaining
it from an Excel cell or some other source.
--ron

I just noted that on my reader, the line starting with sPattern= is also
inappropriately word-wrapped.

By the way, if you decide to write this as a function, you can return the
result to the worksheet as an array, and then use the INDEX worksheet function
to return the appropriate strings, or enter it as an array function over an
appropriately sized range of cells. For example:

===================================
Function Parser(FormulaStr As String) As Variant
Dim Parsed() As String
Dim objRegExp As Object
Dim objMatchCollection As Object
Dim sPattern As String
Dim i As Long

sPattern =
"(('[\s\S]*?'!\w+)|(\([\s\S]*?\))|([^-+*/^<>=]+))([-+*/^<>][<>=]?|$)"


Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.MultiLine = True

objRegExp.Pattern = sPattern
If objRegExp.Test(FormulaStr) = True Then
Set objMatchCollection = objRegExp.Execute(FormulaStr)
ReDim Parsed(1 To objMatchCollection.Count, 1 To 2)
For i = 1 To objMatchCollection.Count
Parsed(i, 1) = objMatchCollection(i - 1).submatches(0)
Parsed(i, 2) = objMatchCollection(i - 1).submatches(4)
Next i
End If

Parser = Parsed

End Function
=========================================

=Parser(A4) will then return an 8x2 array of values. You could either enter
it as an array formula over an 8x2 range, or you could use a formula such as:

=INDEX(parser($A$4),ROWS($1:1),COLUMNS($A:A))

copy/drag one column to the right.

Then select both cells and copy/drag down seven rows


--ron

 

[Ron Rosenfeld]

when working with cells functtions can only return one value.

Not so. Both built-in and User Defined Functions can return arrays. See my
last posting for an example.
--ron

 

[EagleOne]

Joel,

I'll post back how I used your information when I get it all integrated.

Last night I did realize the purpose of your approach while watching the "locals" window
as I toggled through break points.

BTW, the purpose of all this is not an Excel or Algebraic issue but an audit-trail documentation
issue. Many users tend to gather multiple (cell; sheet; workbook) linked-cell references into one
cell and/or append unexplained constants. A reviewer of a printed excel sheet can not follow the
flow of information.

In lieu of:
i.e. 'Summary 2-22-2007'!H8+'Summary 3-22-2007'!H22-A1+*'Summary 4-22-2007'!H23-B1+9876"

The reviewer would see:

Info from 2-22-2007 $1,111
Info from 3-22-2007 $2,222
Info from cell A1 $3,333
Info from 4-22-2007 $4,444
Sales correction $9,876
______
Total $20,986
=======

Therefore, I was hoping that I could develop a VBA routine to parse into separate cells below the
previous usedrange to permit an explanation of the source/reason for that amount. (above)

Last night I realized , that I should only parse when the sign is "+" or "-" and not "/" nor "*"
It breaks up the algebraic logic and, in addition, grouping ( ) if used could present a nightmare.

I just wanted you to know that this was not an exercise in futility.

EagleOne

 

[EagleOne]

Ron,

I did not notice your work until I saw your comment relating to "my previous note ..."
Again, thanks for your time and knowledge.

Consider looking at my recent post (10 min ago) to Joel explaining the reason for all of this.
(Mainly because it is rationale for my insanity)


EagleOne

2003/2007

Trying to consistently parse formula strings (Delimiter = any Opr sign) into i.e.

OprSigns = Array("+", "-", "*", "/", "^", ">", "<", "<>", ">=", "<=")
FormulaStr = "+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007 '!H22- _
A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"

All formulas will begin (stuffed) with "+" sign if not already a "-" [if this makes parsing easier]
Goal:
Operator
Parsed(1) = 123456789 +
Parsed(2) = 'Summary 2-22-2007'!H8 +
Parsed(3) = 'Summary 3-22-2007'!H22 -
Parsed(4) = A1 +
Parsed(5) = (144/6) *
Parsed(6) = Summary 4-22-2007'H23 -
Parsed(7) = B1 +
Parsed(8) = 9876 end of FormulaStr

It is important to isolate, for later retrieval, each operator AFTER each Parsed(x)

Attempted (looooose VBA) Split(FormulaStr, OprSigns,1,1)

Another challenge, avoiding sign-look-a-like characters between each " ' " followed by " ' ! " or
in the Path to other workbooks (still within " ' " followed by " ' ! " I believe) Eg., would be
the "-" in dates like above.

The approach I tried was to avoid parsing between " ' " followed by " ' ! " as Gap(1), Gap(2), etc.

I can get very close but I need someone with much better VBA skills to get the gold ring.

Thanks for any thoughts, approaches or cuss words.

EagleOne

This solution, using Regular Expressions, seems to work on your example, as
well as on examples including two-character operators.

I parsed into a 2D array so that

Parsed(1,1) contains the string, and Parsed(1,2) contains the associated
operator.

I used a "1" based array to be consistent with what you had written above.

Not being certain what you wanted to do with the parsed string, I merely
printed it in the VBA Immediate Window. Obviously many different ways of using
the data.

See if this does what you want.

Given:
"+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007 '!H22-A1+(144/6)*'Summary
4-22-2007'!H23-B1+9876"

It returns:

Parsed(1) = 123456789 +
Parsed(2) = 'Summary 2-22-2007'!H8 +
Parsed(3) = 'Summary 3-22-2007 '!H22 -
Parsed(4) = A1 +
Parsed(5) = (144/6) *
Parsed(6) = 'Summary 4-22-2007'!H23 -
Parsed(7) = B1 +
Parsed(8) = 9876

===================================================
Option Explicit

Public Const FormulaStr As String = "+123456789+'Summary 2-22-2007'!H8+'Summary
3-22-2007 '!H22-A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"
Sub Parse()
Dim Parsed() As String

Dim objRegExp As Object
Dim objMatchCollection As Object
Dim sPattern As String
Dim i As Long

sPattern =
"(('[\s\S]*?'!\w+)|(\([\s\S]*?\))|([^-+*/^<>=]+))([-+*/^<>][<>=]?|$)"


Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.MultiLine = True

objRegExp.Pattern = sPattern
If objRegExp.Test(FormulaStr) = True Then
Set objMatchCollection = objRegExp.Execute(FormulaStr)
ReDim Parsed(1 To objMatchCollection.Count, 1 To 2)
For i = 1 To objMatchCollection.Count
Parsed(i, 1) = objMatchCollection(i - 1).submatches(0)
Parsed(i, 2) = objMatchCollection(i - 1).submatches(4)
Next i
End If

For i = 1 To UBound(Parsed)
Debug.Print "Parsed(" & i&; ") = " & Parsed(i, 1), Parsed(i, 2)
Next i


End Sub


==========================================

Note that the line starting "Public Const FormulaStr" is all on a single line
in the example above, but it could be on multiple lines if you were obtaining
it from an Excel cell or some other source.
--ron

I just noted that on my reader, the line starting with sPattern= is also
inappropriately word-wrapped.

By the way, if you decide to write this as a function, you can return the
result to the worksheet as an array, and then use the INDEX worksheet function
to return the appropriate strings, or enter it as an array function over an
appropriately sized range of cells. For example:

===================================
Function Parser(FormulaStr As String) As Variant
Dim Parsed() As String
Dim objRegExp As Object
Dim objMatchCollection As Object
Dim sPattern As String
Dim i As Long

sPattern =
"(('[\s\S]*?'!\w+)|(\([\s\S]*?\))|([^-+*/^<>=]+))([-+*/^<>][<>=]?|$)"


Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.MultiLine = True

objRegExp.Pattern = sPattern
If objRegExp.Test(FormulaStr) = True Then
Set objMatchCollection = objRegExp.Execute(FormulaStr)
ReDim Parsed(1 To objMatchCollection.Count, 1 To 2)
For i = 1 To objMatchCollection.Count
Parsed(i, 1) = objMatchCollection(i - 1).submatches(0)
Parsed(i, 2) = objMatchCollection(i - 1).submatches(4)
Next i
End If

Parser = Parsed

End Function
=========================================

=Parser(A4) will then return an 8x2 array of values. You could either enter
it as an array formula over an 8x2 range, or you could use a formula such as:

=INDEX(parser($A$4),ROWS($1:1),COLUMNS($A:A))

copy/drag one column to the right.

Then select both cells and copy/drag down seven rows


--ron

 

[EagleOne]

Ron,

I must say that, when I look at:
"(('[\s\S]*?'!\w+)|(\([\s\S]*?\))|([^-+*/^<>=]+))([-+*/^<>][<>=]?|$)"
my brain goes into instant f@rt.

Thank god that you are a expert with RE; in short, "what ever you say."

In addition to parsing the numbers into a separate schedule (above post to Joel) my VBA must build
the formula to sum to a total the parsed information. I am OK with the formula building but that is
why I needed the Operators separately, yet ID with, the parsed cell link/references.

2003/2007

Trying to consistently parse formula strings (Delimiter = any Opr sign) into i.e.

OprSigns = Array("+", "-", "*", "/", "^", ">", "<", "<>", ">=", "<=")
FormulaStr = "+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007 '!H22- _
A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"

All formulas will begin (stuffed) with "+" sign if not already a "-" [if this makes parsing easier]
Goal:
Operator
Parsed(1) = 123456789 +
Parsed(2) = 'Summary 2-22-2007'!H8 +
Parsed(3) = 'Summary 3-22-2007'!H22 -
Parsed(4) = A1 +
Parsed(5) = (144/6) *
Parsed(6) = Summary 4-22-2007'H23 -
Parsed(7) = B1 +
Parsed(8) = 9876 end of FormulaStr

It is important to isolate, for later retrieval, each operator AFTER each Parsed(x)

Attempted (looooose VBA) Split(FormulaStr, OprSigns,1,1)

Another challenge, avoiding sign-look-a-like characters between each " ' " followed by " ' ! " or
in the Path to other workbooks (still within " ' " followed by " ' ! " I believe) Eg., would be
the "-" in dates like above.

The approach I tried was to avoid parsing between " ' " followed by " ' ! " as Gap(1), Gap(2), etc.

I can get very close but I need someone with much better VBA skills to get the gold ring.

Thanks for any thoughts, approaches or cuss words.

EagleOne

This solution, using Regular Expressions, seems to work on your example, as
well as on examples including two-character operators.

I parsed into a 2D array so that

Parsed(1,1) contains the string, and Parsed(1,2) contains the associated
operator.

I used a "1" based array to be consistent with what you had written above.

Not being certain what you wanted to do with the parsed string, I merely
printed it in the VBA Immediate Window. Obviously many different ways of using
the data.

See if this does what you want.

Given:
"+123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007 '!H22-A1+(144/6)*'Summary
4-22-2007'!H23-B1+9876"

It returns:

Parsed(1) = 123456789 +
Parsed(2) = 'Summary 2-22-2007'!H8 +
Parsed(3) = 'Summary 3-22-2007 '!H22 -
Parsed(4) = A1 +
Parsed(5) = (144/6) *
Parsed(6) = 'Summary 4-22-2007'!H23 -
Parsed(7) = B1 +
Parsed(8) = 9876

===================================================
Option Explicit

Public Const FormulaStr As String = "+123456789+'Summary 2-22-2007'!H8+'Summary
3-22-2007 '!H22-A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"
Sub Parse()
Dim Parsed() As String

Dim objRegExp As Object
Dim objMatchCollection As Object
Dim sPattern As String
Dim i As Long

sPattern =
"(('[\s\S]*?'!\w+)|(\([\s\S]*?\))|([^-+*/^<>=]+))([-+*/^<>][<>=]?|$)"


Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.MultiLine = True

objRegExp.Pattern = sPattern
If objRegExp.Test(FormulaStr) = True Then
Set objMatchCollection = objRegExp.Execute(FormulaStr)
ReDim Parsed(1 To objMatchCollection.Count, 1 To 2)
For i = 1 To objMatchCollection.Count
Parsed(i, 1) = objMatchCollection(i - 1).submatches(0)
Parsed(i, 2) = objMatchCollection(i - 1).submatches(4)
Next i
End If

For i = 1 To UBound(Parsed)
Debug.Print "Parsed(" & i&; ") = " & Parsed(i, 1), Parsed(i, 2)
Next i


End Sub


==========================================

Note that the line starting "Public Const FormulaStr" is all on a single line
in the example above, but it could be on multiple lines if you were obtaining
it from an Excel cell or some other source.
--ron

I just noted that on my reader, the line starting with sPattern= is also
inappropriately word-wrapped.

By the way, if you decide to write this as a function, you can return the
result to the worksheet as an array, and then use the INDEX worksheet function
to return the appropriate strings, or enter it as an array function over an
appropriately sized range of cells. For example:

===================================
Function Parser(FormulaStr As String) As Variant
Dim Parsed() As String
Dim objRegExp As Object
Dim objMatchCollection As Object
Dim sPattern As String
Dim i As Long

sPattern =
"(('[\s\S]*?'!\w+)|(\([\s\S]*?\))|([^-+*/^<>=]+))([-+*/^<>][<>=]?|$)"


Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.MultiLine = True

objRegExp.Pattern = sPattern
If objRegExp.Test(FormulaStr) = True Then
Set objMatchCollection = objRegExp.Execute(FormulaStr)
ReDim Parsed(1 To objMatchCollection.Count, 1 To 2)
For i = 1 To objMatchCollection.Count
Parsed(i, 1) = objMatchCollection(i - 1).submatches(0)
Parsed(i, 2) = objMatchCollection(i - 1).submatches(4)
Next i
End If

Parser = Parsed

End Function
=========================================

=Parser(A4) will then return an 8x2 array of values. You could either enter
it as an array formula over an 8x2 range, or you could use a formula such as:

=INDEX(parser($A$4),ROWS($1:1),COLUMNS($A:A))

copy/drag one column to the right.

Then select both cells and copy/drag down seven rows


--ron

 

[Ron Rosenfeld]

Ron,

I did not notice your work until I saw your comment relating to "my previous note ..."
Again, thanks for your time and knowledge.

Consider looking at my recent post (10 min ago) to Joel explaining the reason for all of this.
(Mainly because it is rationale for my insanity)


EagleOne

I think if you use the latter, Function that I wrote, and enter it below using
the INDEX function to pull out the various array elements, you should get what
you want.

The routine I wrote will keep stuff between parentheses together. If you still
don't want to parse on certain operators, removing them from the sPattern
string should work.
--ron

 

[Ron Rosenfeld]

Ron,

I must say that, when I look at:
"(('[\s\S]*?'!\w+)|(\([\s\S]*?\))|([^-+*/^<>=]+))([-+*/^<>][<>=]?|$)"
my brain goes into instant f@rt.

Thank god that you are a expert with RE; in short, "what ever you say."

In addition to parsing the numbers into a separate schedule (above post to Joel) my VBA must build
the formula to sum to a total the parsed information. I am OK with the formula building but that is
why I needed the Operators separately, yet ID with, the parsed cell link/references.

Well, the operators are clearly identified with the references or constants by
virtue of the way they are parsed, in my routine.

But the references / constants are returned as strings, so you could probably
use the INDIRECT function in the worksheet if you want to obtain the original
info.

Another option would be to add another dimension to the Parser function so that

Dim 1 = cell reference or constant
Dim 2 = Value of cell reference or constant
Dim 3 = Operator


I'm no RE Expert. Others on the NG are more capable than I. But if you break
apart the regex, it gets easier to understand.

[\s\S] is needed in VB to take care of multiline issues.

The match could be described as

A series of characters bounded by "'" and "'!" followed by a sequence of
letters or numbers

or

A series of characters bounded by "(" and ")"

or

A series of characters which is NOT an operator

Followed by

An operator
which could be either a specified single character
optionally followed by a "<", ">", or "="

or

the end of the line.

The various parenthesis put the cell reference or constant into a different
submatch than the operator, so they can be split out.


--ron

 

[EagleOne]

Ron,

Your presentation of the background of RE and its use, is excellent.

I probably copied something in error but:

UBound(Parsed) gives me an (subscript out of range) error.

Also with:

Public Const FormulaStr As String = "+123456789+'Summary 2-22-2007'!H8+'Summary
3-22-2007'!H22-A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"

How do I initiate, address, and where (in the Sub Parse()?), if FormulaStr = ActiveCell.Formula

I am uninformed (self taught - Oh No! It is obvious!) as to why Public Const FormulaStr As String is
outside Sub Parse() and how to deal with it.

EagleOne

Ron,

I must say that, when I look at:
"(('[\s\S]*?'!\w+)|(\([\s\S]*?\))|([^-+*/^<>=]+))([-+*/^<>][<>=]?|$)"
my brain goes into instant f@rt.

Thank god that you are a expert with RE; in short, "what ever you say."

In addition to parsing the numbers into a separate schedule (above post to Joel) my VBA must build
the formula to sum to a total the parsed information. I am OK with the formula building but that is
why I needed the Operators separately, yet ID with, the parsed cell link/references.

Well, the operators are clearly identified with the references or constants by
virtue of the way they are parsed, in my routine.

But the references / constants are returned as strings, so you could probably
use the INDIRECT function in the worksheet if you want to obtain the original
info.

Another option would be to add another dimension to the Parser function so that

Dim 1 = cell reference or constant
Dim 2 = Value of cell reference or constant
Dim 3 = Operator


I'm no RE Expert. Others on the NG are more capable than I. But if you break
apart the regex, it gets easier to understand.

[\s\S] is needed in VB to take care of multiline issues.

The match could be described as

A series of characters bounded by "'" and "'!" followed by a sequence of
letters or numbers

or

A series of characters bounded by "(" and ")"

or

A series of characters which is NOT an operator

Followed by

An operator
which could be either a specified single character
optionally followed by a "<", ">", or "="

or

the end of the line.

The various parenthesis put the cell reference or constant into a different
submatch than the operator, so they can be split out.


--ron

 

[Ron Rosenfeld]

Ron,

Your presentation of the background of RE and its use, is excellent.

I probably copied something in error but:

UBound(Parsed) gives me an (subscript out of range) error.

I don't understand how that can be, unless word wrapping in the newsgroup
messed something up.

Also with:

Public Const FormulaStr As String = "+123456789+'Summary 2-22-2007'!H8+'Summary
3-22-2007'!H22-A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"

How do I initiate, address, and where (in the Sub Parse()?), if FormulaStr = ActiveCell.Formula

Well, you don't want to set the constant in the module, if you are going to use
this as a sub.

Rather something like:

=============================================================
Option Explicit
Sub Parse()
Dim Parsed() As String
Dim objRegExp As Object
Dim objMatchCollection As Object
Dim sPattern As String
Dim i As Long

Dim FormulaStr As String
FormulaStr = ActiveCell.Formula

sPattern = _
"(('[\s\S]*?'!\w+)|(\([\s\S]*?\))|([^-+*/^<>=]+))([-+*/^<>][<>=]?|$)"


Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.MultiLine = True

objRegExp.Pattern = sPattern
If objRegExp.Test(FormulaStr) = True Then
Set objMatchCollection = objRegExp.Execute(FormulaStr)
ReDim Parsed(1 To objMatchCollection.Count, 1 To 2)
For i = 1 To objMatchCollection.Count
Parsed(i, 1) = objMatchCollection(i - 1).submatches(0)
Parsed(i, 2) = objMatchCollection(i - 1).submatches(4)
Next i
End If

For i = 1 To UBound(Parsed)
Debug.Print "Parsed(" & i&; ") = " & Parsed(i, 1), Parsed(i, 2)
Next i

End Sub
=========================================================

Note the lines above:
Dim FormulaStr As String
FormulaStr = ActiveCell.Formula

You might also want to consider removing any extra line feeds before processing
the string. It'd make the output "cleaner".

FormulaStr = Replace(ActiveCell.Formula, Chr(10), "")

I am uninformed (self taught - Oh No! It is obvious!) as to why Public Const FormulaStr As String is
outside Sub Parse() and how to deal with it.

A Public Const is declared at the module level, and that constant is then
available to all procedures in all modules. I stuck it outside the procedure
so it would be very apparent, and I did not have any idea, at the time, where
you would be getting the string.

Since the constant has been declared, you can refer to it inside a module just
like any other constant or named variable.

--ron

 

[EagleOne]

Great! The error went away. All is fine unless the first Ref is negative.

i.e., -123456789+ .......

It seems that the first "sign" is not considered.

In short:
FormulaStr = "123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007'!H22-A1+(144/6)*'Summary
4-22-2007'!H23-B1+9876"

is processed the same as

FormulaStr = "-123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007'!H22-A1+(144/6)*'Summary
4-22-2007'!H23-B1+9876"

Note the "-" at the beginning of the entire string.

Ron, I have enough NewBe-knowledge to be dangerous. Sorry if this does not "pop" for me. That
said, if you are not an RE "expert," you are the best at transferring your RE knowledge integrated
with VBA. Others seem to assume everyone is or should, judgmentally, be at their level. Thank you
for your extra time and effort.

EagleOne

Ron,

Your presentation of the background of RE and its use, is excellent.

I probably copied something in error but:

UBound(Parsed) gives me an (subscript out of range) error.

I don't understand how that can be, unless word wrapping in the newsgroup
messed something up.

Also with:

Public Const FormulaStr As String = "+123456789+'Summary 2-22-2007'!H8+'Summary
3-22-2007'!H22-A1+(144/6)*'Summary 4-22-2007'!H23-B1+9876"

How do I initiate, address, and where (in the Sub Parse()?), if FormulaStr = ActiveCell.Formula

Well, you don't want to set the constant in the module, if you are going to use
this as a sub.

Rather something like:

=============================================================
Option Explicit
Sub Parse()
Dim Parsed() As String
Dim objRegExp As Object
Dim objMatchCollection As Object
Dim sPattern As String
Dim i As Long

Dim FormulaStr As String
FormulaStr = ActiveCell.Formula

sPattern = _
"(('[\s\S]*?'!\w+)|(\([\s\S]*?\))|([^-+*/^<>=]+))([-+*/^<>][<>=]?|$)"


Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.MultiLine = True

objRegExp.Pattern = sPattern
If objRegExp.Test(FormulaStr) = True Then
Set objMatchCollection = objRegExp.Execute(FormulaStr)
ReDim Parsed(1 To objMatchCollection.Count, 1 To 2)
For i = 1 To objMatchCollection.Count
Parsed(i, 1) = objMatchCollection(i - 1).submatches(0)
Parsed(i, 2) = objMatchCollection(i - 1).submatches(4)
Next i
End If

For i = 1 To UBound(Parsed)
Debug.Print "Parsed(" & i&; ") = " & Parsed(i, 1), Parsed(i, 2)
Next i

End Sub
=========================================================

Note the lines above:
Dim FormulaStr As String
FormulaStr = ActiveCell.Formula

You might also want to consider removing any extra line feeds before processing
the string. It'd make the output "cleaner".

FormulaStr = Replace(ActiveCell.Formula, Chr(10), "")

I am uninformed (self taught - Oh No! It is obvious!) as to why Public Const FormulaStr As String is
outside Sub Parse() and how to deal with it.

A Public Const is declared at the module level, and that constant is then
available to all procedures in all modules. I stuck it outside the procedure
so it would be very apparent, and I did not have any idea, at the time, where
you would be getting the string.

Since the constant has been declared, you can refer to it inside a module just
like any other constant or named variable.

--ron

 

[Ron Rosenfeld]

Great! The error went away. All is fine unless the first Ref is negative.

i.e., -123456789+ .......

It seems that the first "sign" is not considered.

In short:
FormulaStr = "123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007'!H22-A1+(144/6)*'Summary
4-22-2007'!H23-B1+9876"

is processed the same as

FormulaStr = "-123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007'!H22-A1+(144/6)*'Summary
4-22-2007'!H23-B1+9876"

Note the "-" at the beginning of the entire string.

In your specifications, you indicated that you wanted the operator "after" the
string, so I did not take into account an initial "-".

If you want an initial "-" to be treated differently, then perhaps the
following. I have added an optional "-" to the various string definitions (and
also had to change the operator submatch item from 4 to 5).

===============================================
Option Explicit
Sub Parse()
Dim Parsed() As String
Dim objRegExp As Object
Dim objMatchCollection As Object
Dim sPattern As String
Dim i As Long

Dim FormulaStr As String

FormulaStr = Replace(ActiveCell.Formula, Chr(10), "")

sPattern = _
"(-?(('[\s\S]*?'!\w+)|(\([\s\S]*?\))|([^-+*/^<>=]+)))([-+*/^<>][<>=]?|$)"


Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.MultiLine = True

objRegExp.Pattern = sPattern
If objRegExp.Test(FormulaStr) = True Then
Set objMatchCollection = objRegExp.Execute(FormulaStr)
ReDim Parsed(1 To objMatchCollection.Count, 1 To 2)
For i = 1 To objMatchCollection.Count
Parsed(i, 1) = objMatchCollection(i - 1).submatches(0)
Parsed(i, 2) = objMatchCollection(i - 1).submatches(5)
Next i
End If

For i = 1 To UBound(Parsed)
Debug.Print "Parsed(" & i&; ") = " & Parsed(i, 1), Parsed(i, 2)
Next i

End Sub
==================================================

The following formula:

=-123456789+'Summary 2-22-2007'!H8+'Summary 3-22-2007
'!H22-A1+(-144/6)*'Summary 4-22-2007'!H23-B1+9876

Gives this as a result:

Parsed(1) = -123456789 +
Parsed(2) = 'Summary 2-22-2007'!H8 +
Parsed(3) = 'Summary 3-22-2007 '!H22 -
Parsed(4) = A1 +
Parsed(5) = (-144/6) *
Parsed(6) = 'Summary 4-22-2007'!H23 -
Parsed(7) = B1 +
Parsed(8) = 9876

Note that I also stuck a leading "-" after the parenthesis term, and it seemed
to parse correctly.


--ron