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.
|Form1||Variable1 * Variable2/Variable3|
Then use in case select:
Case is = Form2
How to I write the expression to get the correct Formula here?
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: firstname.lastname@example.org '*- 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.
Copyright © 1999-2008 by Shamil Salakhetdinov. All rights reserved.