Runtime Math Formulas Evaluation in Excel

by Leonardo Volpi, April 2003

This article explains how to evaluate in Excel functions f(x), f(x,y) that are passed as string like

"x^2+3*x+1", "sin(x^2+y^2)", "1/5+x/y", etc.

Expression string can be both symbolic or numerical.
The code is in VBA and works all in and only in EXCEL 97/98/2000. It can be used for user functions and macros as well.

Built-in Evaluation Method

Excel has the Evaluation method of the Application object that can be used for this scope. Its main limitation is that it can evaluate only numeric expression. However, it can evaluate practically all common elementary functions. Take care that the functions recognized are not exactly the same of built-in VBA code nor the built-in worksheet functions.

List of functions recognized by Excel Evaluation Method:
*, /, +, -, ^, abs, atan, cos, exp, round, int, ln, log, rand, sign, sin, sqrt, tan, acos, asin, cosh, sinh, tanh, acosh, asinh, atanh, fact, min, max, mod

For a general purpose evaluator we have to add an extra code that accept also symbolic variables.
A simple code for this scope is shown below

'****************************************************************

' Math Expression Evaluation Function. rev. 20.9.04

' using the standard Application.Evaluate EXCEL object

' It work with both numeric/symbolic expression

' variable symbols are: x, y, z

'function recognized are:

'abs,atan,cos,exp,round,int,ln,log,rand,sign,sin,sqrt,tan,acos,

'asin,cosh,sinh,tanh,acosh,asinh,atanh,fact,min,max,mod

'****************************************************************

Function EvalFormula(Formula, Optional x, Optional y, Optional z)

' Math Expression Evaluation Function. r1.1 , Sept 2004

' using the standard Application.Evaluate EXCEL object

' It work with both numeric/symbolic expression

' variable symbols are: x, y, z

' formula= any expr string like "x^2+3*x+2", "sin(x^2+y^2)"

' returns a numeric evaluation or an error message

'Error   2029    function not found

'Error   2036    evaluate impossible

'Error   2007    division for 0

'Error   2036    evaluate impossible

'Error   2015    syntax error

'----------------------------------------------------------------

Dim f As String, VarValue As String

    f = Formula

    If Not IsMissing(x) Then

        VarValue = "(" + Trim(Str(x)) + ")"

        strSubstitute f, "x", VarValue

    End If

    If Not IsMissing(y) Then

        VarValue = "(" + Trim(Str(y)) + ")"

        strSubstitute f, "y", VarValue

    End If

    If Not IsMissing(z) Then

        VarValue = "(" + Trim(Str(z)) + ")"

        strSubstitute f, "z", VarValue

    End If

    EvalFormula = Application.Evaluate(f)

End Function

Private Sub strSubstitute(ByRef str1 As String, str2 As String, str3 As String)

'subsitutes string str2 with str3 into str1

'rev. 20-9-04 VL

Dim i As Long, s1 As String, s2 As String, L1 As Long, L2 As Long

str1 = " " & str1 & " "

L2 = Len(str2)

i = 0

Do

    i = InStr(i + 1, str1, str2, vbTextCompare)

    If i = 0 Then Exit Do

    s1 = Mid(str1, i - 1, 1)

    If Not IsLetter(s1) Then

        s2 = Mid(str1, i + 1, 1)

        If Not IsLetter(s2) Then

            'OK substitute

            s1 = Left(str1, i - 1)

            s2 = Right(str1, Len(str1) - i - L2 + 1)

            str1 = s1 & str3 & s2

        End If

    End If

Loop

str1 = Trim(str1)

End Sub

'check if it is a letter

'rev. 20-9-04 VL

Private Function IsLetter(ByVal char As String) As Boolean

  Dim code As Long

  code = Asc(char)

  IsLetter = (65 <= code And code <= 90) Or (97 <= code And code <= 122) Or char = "_"

End Function 

The function EvalFormula() can be recall from any sort of VBA code (Functions and Sub macros) for evaluate general function f(x, y, z). in a way very easy and compact. Look at this example:

Formula = "1/sqrt(x^2+y^2)"

............

ret = EvalFormula(Formula, x, y)

If IsError(ret) Then

   out = "Error:" + Str(CDbl(ret))

Else

  For i = 1 To 30

    x= 0.1*i

    For j = 1 To 30

      y= 0.1*j

      ret = EvalFormula(Formula, x, y)

    next j

  Next i

  out = ret

End If

.........

The above evaluation takes about 0.5 sec with a 450 Mhz CPU. This means about 2000 evaluations /sec

You can find this code in the "Excel Fast parser test.xls" workbook (see ExcelFastFormula in the download section of this site)

Parser Subroutines

To write a parser in VBA is not quite simple. There is lots and lots of theory that you have acquire (not always clear, indeed!), and many time results are not so good as you wish. Many sophisticated descendent recursive parser that we are tested has been slower than the previous simple routine.
For better details see "Example to Evaluate Basic Numeric Expressions (Q86688)" at MSDN site.
You can also download the MathParser20.bas code from the download section of our site. It is a general, well built, routine written in simple Visual Basic. It was originally developed for visual basic application, but it works fine also in Excel. In the example above, MathParser20 is 3-4 times faster (6000 - 8000 Evaluations/sec) than EvalFormula() routine
(a copy MathParser is in the download section of this site)

 Dynamic Subroutines

Remaining into the EXCEL environment is it possible to run faster than the previous example? The answer is yes. We can evaluate a symbolic expression in EXCEL at a very fast speed with a little trick. All us have noted the fast evaluation time of the VBA functions or Sub. But they cannot be built at the run-time, because the Excel Debugger get angry!. But, surprisingly, it allow to change the body of any Sub/Function at the run-time. So, during the elaboration, we can arrange the internal part of a specific Sub - without changing the header definition line -, to perform the computation that we want.

This is the idea of "Dynamic Subroutine" or "Dynamic Function".
Yes, I agree with you, that it is at the border of Excel tolerance :-). In fact the main limitation is during the debug. In fact the Debugger cannot stop a program if some lines is changed in the same module. All goes right, on the contrary, if you separate the Dynamic Routine in a separate xla add-in.

The "ExcelFastFormula.xla" that is based on this idea. It contains subroutines to performs at high speed evaluations of symbolic or numerical math expressions. At this release variables are the following symbols:
x, y, z, t, n

The macro Formula_Define is used to create the body of a predefined subroutine, called DynSub1..
At this ver. 1.0 there are up to 5 different subroutines: DynSub1, DynSub2, DynSub3, DynSub4, DynSub5.
By the parameter Id=1 to 5 you can modify the body of the above Dynamic Subroutines (default Id=1).
This mean that you can works at the same time with 5 different formulas at the maximum speed .

The macro Formula_Define is called at the first time to store the expression string; it returns a variable containing the error found (syntax error, function unknown, etc.). If always goes right the RetError is empty

The macros DynSub performs the evaluation

Using is very simple. Look at this example:

Formula = "1/sqrt(x^2+y^2)"

............

Formula_Define Formula, RetError

If RetError <> "" Then

  out = RetError

Else

  For i = 1 To 30

    x= 0.1*i

    For j = 1 To 30

      y= 0.1*j

      dynsub1 ret, x, y

    next j

  Next i

  out = ret

End If

.........

The above evaluation takes less than 0.005 sec with a 450 Mhz CPU.
This means about 200.000 evaluations /sec !

We have compared this result with one of the fastest DLL parser, the UCalc Fast Math Parser 2.0 by by Daniel Corbier. In the same condition this excellent parser (but not free!) reaches the performs of about 500.000 evaluations /sec. No bad, isnt'it?

You can find this code and in the ExcelFastFormula.xla add-in "Excel Fast parser test.xls" workbook (see ExcelFastFormula.zip in the download section of this site). In the same file is also a copy of this document
All this files are free. Have fun!

Leonardo Volpi

April 2003

Foxes Team