Previous posts have presented the Eval() User Defined Function, using the Evaluate function in VBA to evaluate mathematical functions displayed as text (Evaluate Function, Evaluate Function – Update). It was recently pointed out by Jeff Weir that the Evaluate function has its origins as an Excel 4.0 macro command, which can be used in conjunction with defined names to evaluate text functions without any coding at all. A couple of examples are included in the new version of the Eval2.xls spreadsheet, and more details are given here: The EVALUATE Function in Excel.
Unlike the Eval function, where parameter values are read from ranges on the worksheet, when the Evaluate function is used in a name definition the parameters must either be converted to numerical values, or must be defined as named ranges. Playing with this it occurred to me that it would be useful for my Eval UDF to have the option to return a text string with the function parameters converted to values, rather than the evaluated function value, so I have added this capability. I have also added a new function, SubstituteA, that works the same as the built in Substitute function, but on a range of values, rather than just 1. This in effect does the same as the Eval UDF when set to return a text string. Finally I have added a simple UDF, EvalText(), to evaluate a text string where no substitution is required.
The updated spreadsheet can be downloaded from Eval2.xls for the Eval function with some simple integration examples, or Eval-Integration.xlsb for a version including some heavy duty integration functions (including the Tanh-Sinh Integration function).
The screenshots below show the documentation for the new functions, and two examples:
Eval, SubstituteA, and EvalText functions
Eval Function examples
Perhaps the most useful application of the Evaluate Function in conjunction with defined names is in plotting xy charts of functions entered as text, without the need to generate values on the spreadsheet. The next post will describe this in more detail.
Newton Excel Bach, not (just) an Excel Blog 
The future of XLM 4.0 macro functions is uncertain beyond Excel 2010
A better alternate is to use
Function Eval(nm as string)
Eval = Application.Evaluate(nm)
End Function
The Eval UDF can then be used to create the chart
LikeLike
If they take away support for the XLM Evaluate function I’m not sure that Application.Evaluate will work in a VBA macro, since at the moment that just calls the XLM function. I suppose they could remove support for the XLM function and replace it with a VBA only function, but I’m not sure why they would do that.
Either way, if people continue to use the XLM function perhaps they will continue to support it, or replace it with a function directly accessible from the spreadsheet, which would seem like the best option.
LikeLike
Pingback: Пользовательские функции в пространстве листа Excel « Мосты и немного программирования
Pingback: Daily Download 15: Evaluation of formulas in text | Newton Excel Bach, not (just) an Excel Blog