How to evaluate string form of numeric expression in VBA?

[curiousgeorge408]

I want to write the following code:

dim s as variant ' or as string
dim x as double ' or as variant
s = "1+2+3"
x = someFunction(s)
debug.print "-----" & chr(10) & s & chr(10) & x

What is "someFunction"? That is, what VBA function takes a numeric
expression in string form, evaluates it and returns the numerical
result as if I entered the expression directly (e.g. x=1+2+3)?

Obviously, I could do the above by writing the expression in two
places, namely:

s = "1+2+3"
x = 1+2+3

But since I am experimenting with the expression, I would like be able
to modify it in just one place to ensure that "what you see is what
you get" (i.e. I do not make the mistake of modifying only one
instance of the expression).

 

[Dave Peterson]

application.evaluate(s)

should work ok.

I want to write the following code:

dim s as variant ' or as string
dim x as double ' or as variant
s = "1+2+3"
x = someFunction(s)
debug.print "-----" & chr(10) & s & chr(10) & x

What is "someFunction"? That is, what VBA function takes a numeric
expression in string form, evaluates it and returns the numerical
result as if I entered the expression directly (e.g. x=1+2+3)?

Obviously, I could do the above by writing the expression in two
places, namely:

s = "1+2+3"
x = 1+2+3

But since I am experimenting with the expression, I would like be able
to modify it in just one place to ensure that "what you see is what
you get" (i.e. I do not make the mistake of modifying only one
instance of the expression).

 

[curiousgeorge408]

application.evaluate(s)
should work ok.

But not "as if I entered the expression directly" in VBA. Not
surprisingly, application.Evaluate interprets the expression as Excel
does -- including its heuristics.

For example, application.Evaluate("12.22 - 0.02 - 12.2") returns
exactly zero, which is Excel's adjustment when the subtraction result
is "close" to zero. application.Evaluate("(12.22 - 0.02 - 12.2)")
returns about 1.7764E-15, but that is different from the VBA result of
about 1.3496E-15. As Jerry Lewis explained, this is because Excel
stores intermediate results into 64-bit doubles whereas VBA uses the
intermediate results in the 80-bit FPU registers. See the VBA example
below.

Having said as much, I confess that I don't know of a better answer.
Is there one?


Dim x As Double, y As Double, z As Double
Dim a As Double, b As Double, c As Double
x = Application.Evaluate("12.22 - 0.02 - 12.2")
y = Application.Evaluate("(12.22 - 0.02 - 12.2)")
z = 12.22 - 0.02 - 12.2
Debug.Print "-----"
Debug.Print IIf(x = 0, True, False)
Debug.Print IIf(y = z, True, False)
Debug.Print y
Debug.Print z
a = 12.22
b = a - 0.02
c = b - 12.2
Debug.Print IIf(y = c, True, False)
Debug.Print c

 

[Dave Peterson]

If the string you're evaluating is simple, maybe you could parse it to its
component parts and do the arithmetic yourself using VBA's functions.

But parsing a generic string/formula sounds pretty daunting to me.

 

[curiousgeorge408]

If the string you're evaluating is simple, maybe you could parse it to its
component parts and do the arithmetic yourself using VBA's functions.
But parsing a generic string/formula sounds pretty daunting to me.

Parse __and__ generate Intel-compatible assembly code!! I believe
that would be the only way of ensuring exactly the same results that
VBA produces, including side-effects of using the FPU registers for
intermediate results. (Unless, of course, you are also proposing that
I emulate the 80-bit arithmetic as well <wink>.)

And yes, I would need a generic expression parser/code-generator
because the expressions are complex and unpredictable.

Enough said! I take your response to mean: there is no such thing as
"someFunction" in VBA that will evaluate a numeric expression in
string form exactly as if I had entered it into VBA directly.

Not the answer I wanted. But thanks for clearing that up for me.

 

[Equiangular]

How about this?
It's quite powerful.

http://digilander.libero.it/foxes/mathparser/MathExpressionsParser.htm

 

[Dave Peterson]

I don't know anything that would work the way you want.