How to evaluate string form of numeric expression in VBA?

  • Thread starter Thread starter curiousgeorge408
  • Start date Start date
C

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).
 
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).
Click to expand...
 
application.evaluate(s)
should work ok.
Click to expand...

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
 
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.
 
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.
Click to expand...

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.
 
Back
Top