### 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

Copyright © 1999-2008 by Shamil Salakhetdinov. All rights reserved.