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