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