It would often be convenient to evaluate a function entered as text; for instance if we have the function for the deflection of a cantilever under point loading at the end:
F*L^3/(3*E*I)
then it would be convenient to be able to allocate different values to F, L, E and I, and calculate the value of the function, without having to re-enter it.
Unfortunately Excel provides no such function. Fortunately it is quite easy to write one in VBA.
Function Eval(form As String, RepA As Variant, ParamA As Variant) Dim Eform As String, i As Long GetArray RepA GetArray ParamA Eform = form For i = 1 To UBound(RepA, 1) - LBound(RepA, 1) + 1 Eform = Replace(Eform, RepA(i, 1), ParamA(i, 1)) Next i Eval = Evaluate(Eform) End Function
Usage of this function is illustrated in this screen shot:
The UDF parameters are:
The cell containing the function to be evaluated
A range containing the function parameters
A range containing the values to be substituted into each corresponding parameter
Note that the GetArray function can be found here: GetArray Function
A working version of this function can also be found in the section properties spreadsheet here: Section Properties Spreadsheet
The idea for this function was borrowed from: Lamda Function
Newton Excel Bach, not (just) an Excel Blog 
I recently cobbled together a similar routine here, though I had only accounted for a single input parameter:
http://peltiertech.com/WordPress/2008/03/07/udf-to-calculate-an-arbitrary-formula/
LikeLike
Hi Jon
I just had time to have a good look at your evaluate function. I thought at first glance that your code was much longer than mine, but I see most of it was to get the input in a suitable form, which I did with my GetArray function, which converts any input parameter (e.g. single cell, multi-cell range, single value, array of values) into a 2D single column array).
As far as naming the parameters is concerned, that’s certainly a significant issue. I ended up deciding that the parameters should all start with upper case and functions should start with lower case. Fortunately Replace() is case sensitive. If I want to be extra sure I add an _ to the end of all the parameters, but this makes the functions less readable, so usually I just visually check that there is no duplication.
Finally, you might like to have a look at:
http://www.freevbcode.com/ShowCode.asp?ID=7426
Which includes a similar “eval” function in VB code, but since VB does not include its own Evaluate function, they had to write one, which was not a trivial task by the look of it.
LikeLike
Pingback: Evaluate Function - update « Newton Excel Bach, not (just) an Excel Blog
Unfortunately Excel provides no such function.
Actually, it does…it’s just not documented. Check out http://www.vertex42.com/ExcelArticles/evaluate-function.html where you’ll see that , The problem with the EVALUATE function (besides the fact that it is undocumented and may not work in all versions of Excel) is that it is actually a Microsoft Excel 4.0 Macro and when you open a file that uses this function, you will likely get a message asking if you want to enable macros (depending on your security settings).
Also, if you try to use this function within a cell, you’ll get the error message “The function is not valid”. The EVALUATE function can only be used in the definition of a Name
Also see http://www.jkp-ads.com/Articles/ChartAnEquation00.asp
LikeLike
Jeff – thanks for the comments. I’ll update the article to reflect what you say (when I have time :))
LikeLike
No problem, I’m happy to share my dumb luck at seeing this somewhere else then stumbling across your post before I forgot about it 🙂 It’s funny/fortuitous to stumble across stuff like this that isn’t documented. Pre internet/pre RSS, you’d have no chance of such luck: especially for those of us in the Southern Hemispheres where we probably don’t get to interact with too many excel boffins in person (I’m in Wellington).
And it’s fun when you see other people who stumble across new methods courtesy of interactions in the comments of blogs. Another example: I was reading http://www.excelhero.com/blog/2010/07/excel-partial-match-database-lookup.html when I noted Daniel Ferry commenting about a creative use of Advanced Filter “Well there you go. Two decades of work and never used the AdvancedFilter method like this. Excel is amazing and no matter who you are, there is more to learn!”
Amen.
LikeLike
Pingback: More on the Excel Evaluate function | Newton Excel Bach, not (just) an Excel Blog
Pingback: Seeing through Mingus eyes | Newton Excel Bach, not (just) an Excel Blog
Pingback: Daily Download 15: Evaluation of formulas in text | Newton Excel Bach, not (just) an Excel Blog