On Wed, 9 May 2007 13:14:01 -0700, ExcelMonkey
wrote:

Norman, quick question for you. I want to incorporate what you provided with
another function I pulled from the newsgroup which splits an excel fomula by
operator. The first function is in example one below. Problem is, that it
does not preserve the absolute references of the cells ("$"). How do I
change the pattern do incorporate the "$"?

What do you mean when you write "does not preserve the absolute references of
the cells ("$")"?

I don't see it stripping out the "$"'s from cell references, at least in simple
formulas.



Example 1:
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)
Debug.Print Parsed(i, 1)
Parsed(i, 2) = objMatchCollection(i - 1).submatches(4)
Debug.Print Parsed(i, 2)
Next i
End If

Parser = Parsed
End Function

--ron