UDF to Calculate an Arbitrary Formula

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