Store expressions in a table - Run from Module or Query

Question

At present I have a database that is used for mixing various food products. There are a myraid of formulas that are used in these recipes which is making my database more and more difficult to follow and maintain. What I would like to do is simplfy the the use of the formulas by string them in a table, and then calling from a module. ie.

*FormulaName*	*Formula*
Form1	Variable1 * Variable2/Variable3
Form2	(Variable2/Variable3)*Variable2
Form3	Variable3*((Variable1+Varialble2)/100)

Then use in case select:

Case is = Form2
...
How to I write the expression to get the correct Formula here?

Answer

David,

There should be several ways to solve subject and the one presented here isn't probably the best because of the fact that it assumes that:

- Your formulas' calculations aren't heavily cycled - usually only one formula is calculated as a result of end-user's action (selected formula/row, button clicked etc.) ;
- You know in advance the maximium quantity of variables in the most lengthy formula.

If you can accept assumptions made above, then you can do the following:

- create a form ;
- create on form a textbox control named Result and several textboxes named Variable1,...,VariableN respectively;
- set format property of textboxes to General Number ;
- save form and name it frmCalcFormula ;
- create class module:

'* ++++ cut here ++++
'*+
' Class module: CCalcFormula
' Purpose:      Calculate formulas specified as char strings, e.g.
'               Variable3*((Variable1+Variable2)/100)
' Written by:    Shamil Salakhetdinov, e-mail: shamil@marta.darts.spb.ru
'*-
Private Const mcstrModuleName As String = "CCalcFormula"

Private mfrm As Form

Private Sub Class_Initialize()
  Set mfrm = New Form_frmCalcFormula
  mfrm.Visible = False
End Sub

Private Sub Class_Terminate()
  If Not mfrm Is Nothing Then
    Set mfrm = Nothing
  End If
End Sub

Public Property Let VariableN(ByVal vintIdx As Integer, _
                              ByVal vvarValue As Variant)

  mfrm("Variable" & vintIdx) = vvarValue
End Property

Public Property Get Result(ByVal vstrFormula As String) As Variant
  mfrm!Result.ControlSource = "=" & vstrFormula
  Result = mfrm!Result
End Property

Public Property Get CalcFormula(ByVal vstrFormula As String, _
                                ParamArray avarArgs() As Variant)
  Dim i As Integer
  For i = 0 To UBound(avarArgs)
    VariableN(i + 1) = avarArgs(i)
  Next i
  CalcFormula = Result(vstrFormula)
End Property
'*- ------ cut here -------

- copy, paste and run the following test function (this function assumes
that your table with formulas is named tblFormulas and that this table has
the following columns: FormulaName, Formula and Result:

'*+ +++++ cut here +++++++
public sub a_test()
  Dim dbs As Database
  Dim rst As Recordset
  Dim objCalc As New CCalcFormula

  Set dbs = CodeDb()
  Set rst = dbs.OpenRecordset("tblFormulas", dbOpenDynaset)
  While Not rst.EOF
    rst.Edit
      rst![Result] = objCalc.CalcFormula(rst![Formula], 10.12, 50.76, 20.03)
    rst.Update
    rst.MoveNext
  Wend
end sub
'*- ---------- cut here ------------

A little bit tricky solution but it works and it doesn't need to use Eval(...) and/or replace functions etc.

HTH,
Shamil