Evaluate Function
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
Filed under: Excel, Maths, UDFs | Tagged: Evaluate, UDF, VBA
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/
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.
[...] Evaluate Function - update Posted on July 5, 2008 by dougaj4 Previous post [...]