Calculating a Value based on a formula typed into a record.

Hi all, trust you are keeping well.

I have a table that has say the following fields.
Var1
Var2
Var3
cFormula

CFormula is a text field with for example: "=Var1*Var2*Var3"
or
CFormula could have: "=3.141593*Var2"

The idea being that I can create a library of formulas that would use "generic" fields.
The user then chooses the formula they want and it works out the result.

I would like some assistance (if possible) on how I can get VBA to actually "do" the formula stored in the CFormula field?

Thanks
Sean
LVL 1
SeanNijAsked:
Who is Participating?
 
RyanConnect With a Mentor Project Engineer, ElectricalCommented:
you can do a simple string replace and the Evaluate()

populate rs as recordset

str=rs!cFormula
str=replace(str,"Var1",rs!var1)
str=replace(str,"Var2",rs!var2)
str=replace(str,"Var3",rs!var3)
value=Evaluate(str)

Certainly this code can be cleaned up, and could be a single line if you want.
value=Evaluate(replace(replace(replace(rs!cFormula,"Var1",rs!var1),"Var2",rs!var2),"Var3",rs!var3))
0
 
SeanNijAuthor Commented:
Fantastic!!!
Called Eval() in Access 2010 - not Evaluate()

Thank you!
0
 
als315Commented:
You also can create a table with list of possible formula:
ID Function
1   A*B
2   B-C
and function like this:
Public Function calcf(F As Long, Optional ByVal A, Optional ByVal B, Optional ByVal C) As Variant
Select Case F
 Case 1
    calcf = A * B
 Case 2
    calcf = B - C
 Case Else
    calcf = "Error"
End Select
End Function

Open in new window

Create combobox where user can select formula and send it to function with values from other fields
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.