Evaluate formula

[Dan]

Hi,
Is it possible via vba to dissect a spreadsheet formula and evualte every
part of it?
I do not need an addin that do it (I thik it exists on the net) - I need to
understand how to do it - so I need to see the code behind the solution.
Many Thanks,
RG

 

[Gary''s Student]

You need to input the formula as Text and parse it. Then EVALUATE the parsed
items.

Say in Cell A1 we have:

=(1+2) + (3+4)

then:

Sub pieces()
Dim s As String
s = Range("A1").Formula
s2 = Replace(s, ")", "(")
s3 = Split(s2, "(")
MsgBox (Evaluate(s3(1)))
MsgBox (Evaluate(s3(3)))
End Sub


This is NOT a solution, only posted to give you the general idea.

 

[Joel]

It is possible but very complicated. It is a language parsing problem. The
worksheet formulas have a syntax that must be defined. I would search the
web for a general purpose PARSER.

The solution usualy uses a recursive algorithm to get the individual pieces.
The formulas have parenthesis and you need performa the operations following
the parenthesis.

There are better programming languages to do these tasks than BASIC. SNOBOL
programming language is one of the programming languages is designed to
handle these type problems.