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

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.

Joel wrote:

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

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


" wrote:

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.

Joel wrote:

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

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

Joel wrote:

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


" wrote:

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.

Joel wrote:

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

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

Joel wrote:

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


" wrote:

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.

Joel wrote:

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

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"


Joel wrote:

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


" wrote:

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.

Joel wrote:

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

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


" wrote:

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

Joel wrote:

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


" wrote:

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.

Joel wrote:

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

On Tue, 01 May 2007 14:00:49 -0400, wrote:

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

On Wed, 02 May 2007 08:59:36 -0400, Ron Rosenfeld
wrote:

On Tue, 01 May 2007 14:00:49 -0400, wrote:

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

On Wed, 2 May 2007 03:26:00 -0700, Joel wrote:

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

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


Joel wrote:

=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

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


Ron Rosenfeld wrote:

On Wed, 02 May 2007 08:59:36 -0400, Ron Rosenfeld
wrote:

On Tue, 01 May 2007 14:00:49 -0400, wrote:

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,

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.

Ron Rosenfeld wrote:

On Wed, 02 May 2007 08:59:36 -0400, Ron Rosenfeld
wrote:

On Tue, 01 May 2007 14:00:49 -0400, wrote:

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

On Wed, 02 May 2007 13:49:07 GMT, wrote:

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

On Wed, 02 May 2007 14:03:23 GMT, wrote:

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,

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 Rosenfeld wrote:

On Wed, 02 May 2007 14:03:23 GMT, wrote:

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

On Wed, 02 May 2007 15:01:14 GMT, wrote:

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

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 Rosenfeld wrote:

On Wed, 02 May 2007 15:01:14 GMT, wrote:

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

On Wed, 02 May 2007 17:08:38 GMT, wrote:

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

Yes you are correct. I did specify "following" Operators. As we all know, asking the correct
question leads to a best answer. I was not clear and/or not complete.

That said, "Houston - we have a solution!"

Thanks again Ron for your skill, knowledge and empathy for we NewBees.

EagleOne

Ron Rosenfeld wrote:

On Wed, 02 May 2007 17:08:38 GMT, wrote:

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

I appreciate the time and effort that you expended. Sincerely, I hope that we all have gained from
same.

EagleOne

Joel wrote:

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


" wrote:

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

Joel wrote:

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


" wrote:

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.

Joel wrote:

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

On Wed, 02 May 2007 18:26:38 GMT, wrote:

Yes you are correct. I did specify "following" Operators. As we all know, asking the correct
question leads to a best answer. I was not clear and/or not complete.

I should have asked about that. My oversight.


That said, "Houston - we have a solution!"

Glad it's working for you. Thanks for the feedback. Let me know if you run
into any more problems.


Thanks again Ron for your skill, knowledge and empathy for we NewBees.

I, too, benefit and learn by trying to do what you request. I, too, am self
taught so far as computers are concerned. And you're problem was interesting
to me -- took me a bit to figure out how to parse things they way you
requested. I'm still very much a newbie with regard to regular expressions.

--ron

Insite:

As you know, Excel is the prime S/W used in Corporate finance in "civilized" most countries.

With USA Sarbanes-Oxley or SOX (mandatory Financial Internal Controls with Big Teeth), documentation
of Excel worksheets is now a prime consulting driver. With your RE knowledge coupled with VBA and
your obvious ability to ascertain the shortest distance between two points, your future should be or
will be surrounded with success. BTY, I am very serious and not just patronizing you.

EagleOne

If I have any interface issues I'll post back.

Thanks

Ron Rosenfeld wrote:

On Wed, 02 May 2007 18:26:38 GMT, wrote:

Yes you are correct. I did specify "following" Operators. As we all know, asking the correct
question leads to a best answer. I was not clear and/or not complete.

I should have asked about that. My oversight.


That said, "Houston - we have a solution!"

Glad it's working for you. Thanks for the feedback. Let me know if you run
into any more problems.


Thanks again Ron for your skill, knowledge and empathy for we NewBees.

I, too, benefit and learn by trying to do what you request. I, too, am self
taught so far as computers are concerned. And you're problem was interesting
to me -- took me a bit to figure out how to parse things they way you
requested. I'm still very much a newbie with regard to regular expressions.

--ron

In the code previously posted:

Change Error.Number to Err.Number

EagleOne


wrote:

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,

If you are "listening" hello!


Using RegEx, what is the best (least risk of an error) to stuff a formula string with " ' " before
and after a SheetName that has no spaces.

As you know, Sheet Names without spaces now are now encapsulated like
'SheetName!CellRef.

TIA EagleOne



Ron Rosenfeld wrote:

On Tue, 01 May 2007 14:00:49 -0400, wrote:

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