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
=CalculateThisFormula(B$1,$A2)
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
CalculateThisFormula(Formula, Xvalue)
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
=CalculateThisFormula(B$1,$A2)
and this cell is copied and pasted into B2:E6. It looks good to me.
Other examples of usage in a worksheet cell:
=CalculateThisFormula("4*[x]^2-3*[x]+5",2)
which duplicates cell D6 above
=CalculateThisFormula("1/[x]",4)
which duplicates cell C4
