How to evaluate string form of numeric expression in VBA?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

curiousgeorge408@hotmail.com - 31 Dec 2007 00:41 GMT

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).

Reply to this Message

Dave Peterson - 31 Dec 2007 00:51 GMT

application.evaluate(s)

should work ok.

> I want to write the following code:
>
[quoted text clipped - 18 lines]
> you get" (i.e. I do not make the mistake of modifying only one
> instance of the expression).

Signature

Dave Peterson

Reply to this Message

curiousgeorge408@hotmail.com - 31 Dec 2007 07:46 GMT

> curiousgeorge...@hotmail.com wrote:
> > what VBA function takes a numeric expression in string form,
[quoted text clipped - 3 lines]
> 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

Reply to this Message

Dave Peterson - 31 Dec 2007 14:38 GMT

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.

> > curiousgeorge...@hotmail.com wrote:
> > > what VBA function takes a numeric expression in string form,
[quoted text clipped - 35 lines]
> Debug.Print IIf(y = c, True, False)
> Debug.Print c

Signature

Dave Peterson

Reply to this Message

curiousgeorge408@hotmail.com - 31 Dec 2007 15:23 GMT

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

Reply to this Message

Equiangular - 31 Dec 2007 16:22 GMT

How about this?
It's quite powerful.

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

>> 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.
[quoted text clipped - 14 lines]
>
> Not the answer I wanted. But thanks for clearing that up for me.

Reply to this Message

Dave Peterson - 31 Dec 2007 17:23 GMT

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

> > 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.
[quoted text clipped - 14 lines]
>
> Not the answer I wanted. But thanks for clearing that up for me.

Signature

Dave Peterson

Reply to this Message