Excel VBA failure of repeated Evaluate method -

i have written little tool in vba charts function pass string (e.g. "1/(1+x)" or "exp(-x^2)"). use built-in evaluate method parse formula. nub of function, evaluates function of variable @ given value:

function eval(func string, variable string, value double) double   eval = evaluate(replace(func, variable, value)) end function 

this works fine, e.g. eval("x^2, "x", 2) = 4. apply element-wise down array of x values generate graph of function.

now want enable tool chart definite integral of function. have created integrate function takes input formula string , uses evaluate evaluate @ various points , approximate integral. actual integrate function uses trapezoidal rule, simplicity's sake let's suppose this:

function integrate(func string, variable string, value double) double   integrate = value * (eval(func, variable, 0) + eval(func, variable, value)) / 2 end function 

this works expected, e.g. integrate("t", "t", 2) = 2 area of triangle under identity function.

the problem arises when try run integrate through charting routine. when vba encounters line this

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

then stop no error warning when evaluate called inside eval function. (the internal quotes have doubled read formula properly.) expect value 2 since evaluate appears try , evaluate integrate("t", "t", 2)

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

thanks george

excel 2010 v14, vba 7.0

thanks chris, debug.print suggestion got me thinking , narrowed problem down bit more. seem evaluate gets called twice, example shows:

function g() variant   debug.print "g"   g = 1 end function 

run immediate window:

?evaluate("g()") g g  1  

i found http://www.decisionmodels.com/calcsecretsh.htm shows way round using worksheet.evaluate (evaluate default application.evaluate):

?activesheet.evaluate("g()+0") g  1  

however still doesn't solve problem evaluate calling itself. define

function f() variant   debug.print "f"   f = activesheet.evaluate("g()+0") end function 

then in immediate window:

?activesheet.evaluate("f()+0") f error 2015 

the solution found define different function second formula evaluation:

function eval2(formula string) variant   [a1] = "=" & formula   eval2 = [a1] end function 

this still uses excel's internal evaluation mechanism, via worksheet cell calculation. want:

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

it's slower due repeated worksheet hits, that's best can do. in original example, use eval calculate integral , eval2 chart it. still interested if has other suggestions.