ExcelBanter - View Single Post - How in to parse constants in formula to cells

Ron Rosenfeld

On Sat, 25 Nov 2006 02:37:07 GMT, wrote:

2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells elsewhere on the w/s?

TIA EagleOne

Here's a somewhat cleaned up and better annotated version.

With regard to some of the issues:
it returns the negative signed values
it does NOT return "within string" constants
it returns 3% as 3
it returns all constants within a function
the output goes into the cells to the right of "selection"

============================================
Option Explicit
Sub ParseFormula()
'set up Regex
Dim objRegExp As RegExp
Dim objMatch As match
Dim colMatches As MatchCollection

' Create a regular expression object.
Set objRegExp = New RegExp
' Set Case Insensitivity.
objRegExp.IgnoreCase = True
'Set global applicability.
objRegExp.Global = True

Dim FormulaText As String
Const CellRef As String = "\$?[A-Z]{1,2}\$?\d{1,5}"
Const Operator As String = "[/*^&()=<,+]" 'No "-"
Const WithinString As String = """[^""]*"""
Const NumConstant As String = "-?(\d*\.)?\d+"
Dim Ignore As String
Dim i As Long
Dim c As Range

Ignore = Join(Array(WithinString, CellRef, Operator), "|")

'set the Regex pattern to replace unwanted stuff
objRegExp.pattern = Join(Array(WithinString, CellRef, Operator), "|")

For Each c In Selection
FormulaText = c.Formula

'Test whether the String can be compared.
If (objRegExp.Test(FormulaText) = True) Then

'change - to +- to retain negative signage
FormulaText = Replace(FormulaText, "-", "+-")

'replace unwanted stuff
objRegExp.pattern = Ignore
FormulaText = objRegExp.Replace(FormulaText, "~")

'Get the matches.
objRegExp.pattern = NumConstant
Set colMatches = objRegExp.Execute(FormulaText) ' Execute search.

End If

'Output for testing, but could go into any range
i = 1
For Each objMatch In colMatches
c.Offset(0, i).Value = objMatch
i = i + 1
Next objMatch

Next c

End Sub
===============================
--ron