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

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

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

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

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

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

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

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

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