Last night a colleague complained because there was no way to evaluate a formula in the worksheet. What he meant was, he wanted to enter “m X + b” (for example, with numerical entries in place of m and b) into a cell, then link to another cell that contained a value of X, and have Excel compute the formula.
I got an idea to try a User Defined Function (UDF), so your formula could be in cell B1, for example, and your X value in A2, and cell B2 would contain the formula
and the value displayed in B2 would be the formula’s value evaluated at the value of X in A2. I’d never heard of this being done, but I thought it must be possible. To avoid confusing X in the formula with the letter x in a function, like exp(), I decided x should be placed in square brackeds, like [x]. I then wrote the following UDF:
Function CalculateThisFormula(Formula As Variant, Xvalue As Variant) As Variant Dim sFormula As String Dim sXvalue As String Select Case TypeName(Formula) Case "Range" If Formula.Cells.Count = 1 Then sFormula = Formula.Value Else CalculateThisFormula = CVErr(xlErrValue) Exit Function End If Case "String" sFormula = Formula Case Else CalculateThisFormula = CVErr(xlErrValue) Exit Function End Select Select Case TypeName(Xvalue) Case "Range" If Xvalue.Cells.Count = 1 Then sXvalue = Xvalue.Value Else CalculateThisFormula = CVErr(xlErrValue) Exit Function End If Case "String" sXvalue = Xvalue Case "Double" sXvalue = CStr(Xvalue) Case Else CalculateThisFormula = CVErr(xlErrValue) Exit Function End Select sFormula = Replace(sFormula, "[x]", "(" & sXvalue & ")") sFormula = Replace(sFormula, "[X]", "(" & sXvalue & ")") CalculateThisFormula = Evaluate(sFormula) End Function
The syntax of the UDF is
Formula – string representation of formula, or reference to cell containing string representation of formula, where Formula is the f(x) part of y=f(x), and x must be represented as [x] and all operators must be explicitly included within Formula.
Xvalue – string or numeric representation of the value of x, or reference to single cell containing string or numeric representation of the value of x.
The result is the evaluated value of the formula at the value of x. If either argument contains a multiple cell range or other inappropriate input, the formula returns an error. The following shows a worksheet array in which the UDF is tested:
The formula in cell B2 is
and this cell is copied and pasted into B2:E6. It looks good to me.
Other examples of usage in a worksheet cell:
which duplicates cell D6 above
which duplicates cell C4