CODEWITHSUNDEEP
excelvba
George Skelton
George Skelton

Reputation: 1125

Excel VBA failure of repeated Evaluate method

I have written a little tool in VBA that charts a function you pass it as a string (e.g. "1/(1+x)" or "exp(-x^2)"). I use the built-in Evaluate method to parse the formula. The nub of it is this function, which evaluates a function of some variable at a given value:

Function eval(func As String, variable As String, value As Double) As Double
  eval = Evaluate(Replace(func, variable, value))
End Function

This works fine, e.g. eval("x^2, "x", 2) = 4. I apply it element-wise down an array of x values to generate the graph of the function.

Now I want to enable my tool to chart the definite integral of a function. I have created an integrate function which takes an input formula string and uses Evaluate to evaluate it at various points and approximate the integral. My actual integrate function uses the trapezoidal rule, but for simplicity's sake let's suppose it is this:

Function integrate(func As String, variable As String, value As Double) As Double
  integrate = value * (eval(func, variable, 0) + eval(func, variable, value)) / 2
End Function

This also works as expected, e.g. integrate("t", "t", 2) = 2 for the area of the triangle under the identity function.

The problem arises when I try to run integrate through the charting routine. When VBA encounters a line like this

eval("integrate(""t"",""t"",x)", "x", 2)

then it will stop with no error warning when Evaluate is called inside the eval function. (The internal quotes have to be doubled up to read the formula properly.) I expect to get the value 2 since Evaluate appears to try and evaluate integrate("t", "t", 2)

I suspect the problem is with second call on Evaluate inside integrate, but I've been going round in circles trying to figure it out. I know Evaluate is finicky and poorly documented http://fastexcel.wordpress.com/2011/11/02/evaluate-functions-and-formulas-fun-how-to-make-excels-evaluate-method-twice-as-fast but can anyone think of a way round this?

Thanks George

Excel 2010 V14, VBA 7.0

Upvotes: 2

Views: 992

Answers (1)

George Skelton
George Skelton

Reputation: 1125

Thanks Chris, your Debug.Print suggestion got me thinking and I narrowed the problem down a bit more. It does seem like Evaluate gets called twice, as this example shows:

Function g() As Variant
  Debug.Print "g"
  g = 1
End Function

Run from the Immediate Window:

?Evaluate("g()")
g
g
 1

I found this http://www.decisionmodels.com/calcsecretsh.htm which shows a way round this by using Worksheet.Evaluate (Evaluate is actually the default for Application.Evaluate):

?ActiveSheet.Evaluate("g()+0")
g
 1

However this still doesn't solve the problem with Evaluate calling itself. Define

Function f() As Variant
  Debug.Print "f"
  f = ActiveSheet.Evaluate("g()+0")
End Function

Then in the Immediate Window:

?ActiveSheet.Evaluate("f()+0")
f
Error 2015

The solution I found was define a different function for the second formula evaluation:

Function eval2(formula As String) As Variant
  [A1] = "=" & formula
  eval2 = [A1]
End Function

This still uses Excel's internal evaluation mechanism, but via a worksheet cell calculation. Then I get what I want:

?eval2("f()")
f
g
 1

It's slower due to the repeated worksheet hits, but that's the best I can do. So in my original example, I use eval to calculate the integral and eval2 to chart it. Still interested if anyone has any other suggestions.

Upvotes: 0

Related Questions