Store Calculations in a table and run them in Code

[Cullihall]

Hi guys.

Does anyone know if it's possible (if so, how) to store a calculation in a table and run that calculation in VB?

I have about 20 different calculations that I would like to store in a table rather than hard code them so they could be edited by a general user who knows no VB.

For example I have a table called t_JobFunctions with a field called Calc1. In Calc1 I have entered this text: stockoutRatio * stkoutAdj * 100000 * hrsInFctn. The text contains variables which I will lookup from within the same procedure where I would like to use the calculation.

Is there a way I can now run this formula? I had to set the data type in the table to text so when the code looks up the calculation it is in string form so I cannot populate the variables and calculate the result.

Any help will be dearly appreciated. Thanks.

 

[SteveSchapel]

Cullihall,

Where you say "VB", are you referring here to VBA?

If so, check out the Eval() function. And also have a look at the Application.Run method.

 

[ChrisO]

There could be many places where errors may occur but there is a small demo attached.

Hope that helps.

Edit:
Good morning Steve.

 

[Cullihall]

There could be many places where errors may occur but there is a small demo attached.

Hope that helps.

Edit:
Good morning Steve.

Thank you very much ChrisO. That is exactly what I'm looking for. You are the best!

 

[Cullihall]

Hi there.

How would I go about adding the value to the stockoutRatio variable that's in the calculation, from a variable with the exact name in which was populated with a value earlier in the code?
For example, in the sample from ChrisO the variable is being filled with a lookup function. Instead of looking up the value from a table, the value is already in stockoutRatio, so the calculation says "stockoutRatio * stkoutAdj", I have to get the value from stockoutRatio to "StockoutRatio". I am having a tough time explaining what I want to accomplish here.

Function TotalInc(hrsInFctn As Double, actual As Double, stockoutRatio As Double) As Double
Dim fctn(35) As Integer, theMultiplier As Double, standard As Integer, adjustment As Double
Dim basicRate As Double, washOutPer As Double, washOutRatio As Double, stkoutAdj As Double
Dim AdjAboveStd As Double, AdjBelowStd As Double, WeekEnding As Date
Dim calculation1 As Variant, calculation2 As Variant, calculation3 As Variant
Dim strExec As String, intIndex As Integer
Dim rs As DAO.Recordset
' Move to next if there are no hours in the current function
If hrsInFctn = 0 Then Exit Function
' Get week ending from the form
WeekEnding = [Forms]![incentiveReviewForm]![IncWkEndCbo]
 
' Set the recordset the the appropriate table/query
Set rs = CurrentDb.OpenRecordset("t_JobFunctions", dbOpenDynaset)
' Select last record in the recordset
rs.MoveLast
' Get the record count in the table
theCnt = rs.RecordCount
' Select first record in the recordset
rs.MoveFirst
' Loop until it is not the end of the recordset
    For i = 1 To theCnt
    strExec = ""
        On Error Resume Next
        ' Put the function number into a variable
        fctn(i) = rs.Fields("[FunctionNbr]").Value
 
        ' Poplulate variables
        standard = DLookup("[Standard]", "[t_JobFunctions]", "[FunctionNbr]=" & fctn(i) & "")
        basicRate = DLookup("[BaseRate]", "[t_JobFunctions]", "[FunctionNbr]=" & fctn(i) & "")
        AdjAboveStd = DLookup("[EachPtOverStandard]", "[t_JobFunctions]", "[FunctionNbr]=" & fctn(i) & "")
        AdjBelowStd = DLookup("[EachPtUnderStandard]", "[t_JobFunctions]", "[FunctionNbr]=" & fctn(i) & "")
 
        ' Determine the multiplier to use
        If actual < standard Then
        multiplier = AdjBelowStd
        Else: multiplier = AdjAboveStd
        End If
 
        ' Look up values for the variables
        washOutPer = DLookup("[WashOut%]", "[t_JobFunctions]", "[FunctionNbr]=" & fctn(i) & "")
        washOutRatio = DLookup("[WashOutErrorRatio]", "[t_JobFunctions]", "[FunctionNbr]=" & fctn(i) & "")
        stkoutAdj = DLookup("[AdjForStockOutRatio]", "[t_JobFunctions]", "[FunctionNbr]=" & fctn(i) & "")
 
    ' Lookup the calculations that are stored in the table
    calculation1 = DLookup("[Calc1]", "[t_JobFunctions]", "[FunctionNbr]=" & fctn(i) & "")
 
    ' Split the text in the string variable
    calculation1 = Split(calculation1, " ")
 
    For intIndex = LBound(calculation1) To UBound(calculation1)
        If IsNumeric(calculation1(intIndex)) Then
            strExec = strExec & calculation1(intIndex)
        Else
            If Len(calculation1(intIndex)) = 1 Then
                strExec = strExec & calculation1(intIndex)
            Else
                strExec = strExec & Eval(calculation1(intIndex))
 
            End If
        End If
    Next intIndex
    adjustment = strExec
    TotalInc = Round(((((actual - standard) * multiplier) + basicRate) * hrsInFctn) - adjustment, 2)
 
    ' Move to next record
    rs.MoveNext
    Next i
 
' Release the objects
Set rs = Nothing
 
End Function
Function PlaySound(sWavFile As String)
' Purpose:  Plays a sound.
' Argument: the full path and file name.
If apisndPlaySound(sWavFile, 1) = 0 Then
Exit Function
End If
End Function

 

[ChrisO]

Sub Test(stockoutRatio As Double)
Dim strMyString As String

strMyString = CStr(stockoutRatio) & " * stkoutAdj"

End Sub

 

[Cullihall]

Sub Test(stockoutRatio As Double)
Dim strMyString As String

strMyString = CStr(stockoutRatio) & " * stkoutAdj"

End Sub

I apologize, I am not explaining clearly. Let me try again.

I have a formula (stockoutRatio * stkoutAdj * 100000 * hrsInFctn)stored as text in a table. At the beginning of my code I declare variables with the same names that's in the formula. I use the DLOOKUP function to popluate these variables with values from a different table than where the formula is stored. So I now have a value in stockoutRatio which is 0.000098 for example, stkoutAdj = 0.02 and hrsInFctn = 41.25.

I would like to lookup the formula, which is stored as text, and calculate a total based on the values that are in the variables.

Somehow I have to get the values that are in the variables into the words that are in the formula. And the formula could change so we won't know what variables to use until runtime.

This way, a user could change the formula to be stkoutAdj - stockoutRatio * 100000 + hrsInFctn for example.

 

[ChrisO]

I guess you could try storing the results of the Dlookup’s in controls on the form.

Little demo attached.

Chris.

 

[Cullihall]

I guess you could try storing the results of the Dlookup’s in controls on the form.

Little demo attached.

Chris.

Excellent. Thank you again ChrisO!!!