I need Help Using Eval( ) in VBA 3

[AccessNewbe]

Greetings all,

Although I have many years programming and database experience I am a babe in the woods when it comes to working with Microsoft Access and VBA. I am currently developing a prototype for a new system and Access 2003 is the only tool that I have available (long story – don’t ask).

So far, I have made fairly good progress and found the answers to many of my questions right here in these wonderful forums. Unfortunately I have one problem that, after two days of research and trying many different sources, I still have not been able to resolve;

I would like to store some simple equations in a text field in my database and then retrieve and evaluate those equations in my Visual Basic program. Whenever I try to reference a variable in my equation, I get a runtime error 2482 “Microsoft Office Access can’t find the name ‘VariableName’ you entered in the expression”.

For example:

strFormulaText = “numActualWidth * numActualHeight / 144”
numActualArea = Eval(strFormulaText)

Will result in the runtime error while:

strFormulaText = “12 * 12 / 144”
numActualArea = Eval(strFormulaText)

Works fine.

I have found enough here (

http://www.tek-tips.com/viewthread.cfm?qid=833752)

to suspect that the variables I am using need to be declared in a class and fully qualified. However, I have not been able to get the example in the noted thread to work.

If anyone has any insights as to how I might accomplish this I would certainly appreciate hearing from you!

Best wishes and thanks in advance,
Todd Bailey

 

[Savil]

Have you tried

strFormulaText = "" & numActualWidth & " * " & numActualHeight & " / 144"

 

[Eupher]

Hi, Todd,

Remember, a variable name enclosed in quotes isn't a variable, it's a literal string. Savil's example shows how to concatenate a string expression using variables.

Regarding declaring your variables, when you say "class" I presume you mean a class module. Your variables don't necessarily have to be declared in a class module, but they do need to be declared as appropriate to the purpose. As a programmer I'm sure you are familiar with the concept of the scope of a variable - a search in the VBA help files on Public and Dim should help you with the syntax.

HTH,

Ken S.

 

[AccessNewbe]

Hello again,

Many thanks to both you and Eupher for the quick and helpful replies. Initially I had suspected that I had a scoping issue, as Eupher had suggested, but the other thread, which I noted in my first post, led me to mistakenly believe that the variables needed to be declared in a class. So much for that garden path. ;-)

Your response had gotten me very close to what I need. If I have the code:

-------------------------------------------
Dim numActualWidth As Double
Dim numActualheight As Double
Dim numActualArea As Double
Dim strFormulaText As String

numActualWidth = 12
numActualheight = 12
strFormulaText = "" & numActualWidth & " * " & numActualHeight & " / 144"

numActualArea = Eval(strFormulaText)
-------------------------------------------

numActualArea does, indeed, come out to be 1 (one) which is the correct answer. So far, so good.

I then attempted to expand on this technique, so that I could read strFormulaText from a text field in my database rather than a hard coded line, and am having a problem with that. Reading the formula text from the database is no problem however running that formula through the Eval() function is not working as I had hoped.

If I save the following formula text in my database field …

"" & numActualWidth & " * " & numActualHeight & " / 144"

… and retrieve that into strFormulaText with …

strFormulaText = adoRS.Fields(“Formula”).Value

… then I get the “can not find variable” error again when trying to run strFormulaText through Eval().

I tried saving the formula text as…

" & numActualWidth & " * " & numActualHeight & " / 144

… but got a “data type mismatch” error when trying to run strFormulaText through Eval().

I examined the contents of strFormulaText right after setting it by hard coded line and found that the variable now contained…

12 * 12 / 144

After reading strFormulaText from the database the variable contains exactly what was in the database field rather than the “interpreted” expression as seen with the hard coded line. So the hard coded line produces some “pre-processing”, which replaces &Variablename& with the text value of that variable in the string. That “pre-processing” is not happening when the value is read from the database.

This is not surprising, once you think about it, but I am not sure how to work around this issue. Would you have any thoughts on how I might get this to work?

Thanks again for your help,
Todd Bailey

P.S. Would you have any recommendations on good books covering Access 2003 and VBA programming? The Access 2002 VBA programming book I have does not even mention this usage of the ampersand. :-(

 

[Ascentient]

Are the variables populated at the time you pull the formula from the table? I am guessing they are since you are calling the formula, but double check just to be sure.

Could you strip that formula apart after its brought from your table and then process your formula?

 

[Eupher]

Yes, you're bumping into that whole variable<->string conversion thing again. Once you save the variable names to your table, they're not variables anymore, they're part of a literal string. So you have to parse the variable names back out of the string. I'm sure it's possible, but seems like way too much trouble. Why not save your formulas as functions in a module? Something like this is easy:

Public Function MyFormula1(i As integer, j As integer) As Integer
MyFormula1 = (i * j) / 144
End Function

Then call the function from a sub:

Private Sub Command0_Click()
Dim numActualWidth As Integer
Dim numActualHeight As Integer
Dim MyResult As Integer
numActualWidth = 24
numActualHeight = 12
MyResult = MyFormula1(numActualWidth, numActualHeight)
MsgBox "MyResult is " & MyResult
End Sub

I have found "Mastering Access 2000" by Alan Simpson and Celeste Robinson, published by Sybex, to be helpful for learning Access; also "Access 2000 VBA Handbook" by Susann Novalis for the basics of programming in Access. A wieghtier set (both literally and figuratively) is "Access 2000 Developer's Handbook" vols. 1 & 2 by Paul Litwin, et al. Volume 1 is the Desktop Edition, Volume 2 is the Enterprise Edition.

HTH,

Ken S.

 

[AccessNewbe]

Hi Ken,

I’ll try to provide some background as to my reasons for wanting to implement this, seemingly crazy, scheme:

The system that I am working on is going to cost material and labor in a manufacturing environment. There are certain basic characteristics that all manufactured models share – things like they all have a width, height and length, for a simple example. There are many different labor and material requirements that vary from model to model. For a given model, these labor and material requirements can be calculated by a formula using these common basic characteristics as input variables. While I could write a specific function for each formula, and keep a key to which function to call in the database rather than the actual formula itself, there would be a lot of functions. Further, these formulas often change as models are revised and new formulas will be required as new models are developed. The main thing that I hoped to avoid was recoding the application whenever the formulas changed.

I had used Centura Team Developer in the last system I was responsible for. CTD had a nifty CompileAndEvaluate function which would take a string containing program code, compile it and run it. I had implemented “code in the database” for some often changed information in this CTD system and that had worked out very well. The CTD CompileAndEvaluate did not have “issues” with variables in the code string though.

I had originally planned to implement each formula, in this new system, with its own function, exactly as you have suggested. When I learned of the VBA Eval( ) function I thought I might be able to do the same thing as I had done in that old CTD system.

I did find that the code…

strFormulaText = Str(numActualWidth) & " * " & Str(numActualHeight) & " / 144"

…will produce the same 12 * 12 / 144 in strFormulaText as the…

strFormulaText = "" & numActualWidth & " * " & numActualHeight & " / 144"

…code did and works fine with Eval( ). So, perhaps I can build on Ascentient’s suggestion and parse the text from the database to build up the string required by Eval( ).

It would seem that I need to take this idea back to the drawing board!

In any case, my sincere thanks to you and the other responders for the quick and useful help. Also, thank you for the feedback on the books. Since I am now coding in Access and VBA, I probably should read up on how to use it so I’ll take a look at those books ASAP!

Best wishes,
Todd bailey

 

[Eupher]

Todd,

Some functions to look at in parsing your variables: Left, Right, Mid, InStr, InStrRev, Replace.

Good luck, post back if you run into any more glitches.

Ken S.

 

[earthandfire]

How and where are the variables assigned values?

How many variables do you have?

They may be easier workarounds, depending on the above.

 

[Ascentient]

To bad VBA couldn't do the same thing FoxPro for DOS did. They used the & to reference the contents of a variable as actual code.

Wants and wishes...

 

[AccessNewbe]

Good morning everyone,

Yesterday I decided to try my hand at a quick and dirty parser as Ascentient had suggested. To put the following function in perspective, I had already created a class called clsModelInfo which I am currently using to hold and transport all that common data about a model. I created a ParseFormula( ) function and pass it the formula string read from the database and the clsModelInfo which would contain any of the variables that the formulas might need to work with. In the database, I surrounded the variable name with curly brackets like this {VariableName}.

Function ParseFormula(Formula As String, ModelData As clsModelInfo) As String

    Dim nPointer      As Integer
    Dim cCharacter    As String
    Dim sVariable     As String
    Dim sReturnString As String
    Dim bInVariable   As Boolean
    
    sReturnString = ""
    sVariable = ""
    bInVariable = False

    For nPointer = 1 To Len(Formula)
        cCharacter = Mid(Formula, nPointer, 1)
        If StrComp(cCharacter, "{") = 0 Then
            bInVariable = True
        ElseIf bInVariable Then
            If StrComp(cCharacter, "}") = 0 Then
                If StrComp(sVariable, "ActualWidth") = 0 Then
                    sReturnString = sReturnString & Str(ModelData.ActualWidth)
                ElseIf StrComp(sVariable, "ActualDepth") = 0 Then
                    sReturnString = sReturnString & Str(ModelData.ActualDepth)
                ElseIf StrComp(sVariable, "ActualHeight") = 0 Then
                    sReturnString = sReturnString & Str(ModelData.ActualHeight)
                ElseIf StrComp(sVariable, "NumBlades") = 0 Then
                    sReturnString = sReturnString & Str(ModelData.NumBlades)
                End If
                bInVariable = False
                sVariable = ""
            Else
                sVariable = sVariable & cCharacter
            End If
        Else
            sReturnString = sReturnString & cCharacter
        End If
    Next nPointer
    
    ParseFormula = sReturnString

End Function

This seems to get the job done although I am not completely satisfied with the if-then-else block which interpreters the parsed variable name into an actual value (there will have be quite a few more variables in that block to handle them all). It seems like there should be a more elegant way to handle this than the brute force approach I employed here. It is too bad the Eval( ) function is so curiously limited in it’s ability to work with variables. Oh Well, I suppose that code is sometimes like sausages; if you saw what goes into them you wouldn’t want to eat them! ;-)

Thanks again to everyone for the help,
Todd Bailey

 

[earthandfire]

I assume from your code example, the variables are fields on a form, as is the equation.

If you add a reference to the scripting library, you could use regular expressions.

A match expression would consist of your complete list of variable names

(ActualWidth)|(ActualHeight) etc. etc.

Use the regular expression replace function to replace each match with the appropriate value (using a case statement in the replace function to fetch the value from the form field). You would then end up with a string that could be passed directly to Eval, additionally you would not have to handle brackets (especially nested brackets) since Eval should do that for you.

Hope this helps

 

[PHV]

If the variables are fields on the form, you may try this:
strFormulaText = "[numActualWidth] * [numActualHeight] / 144"
[numActualArea] = Eval(strFormulaText)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Ascentient]

earthandfire,

Could you do a quick example? I follow part of your message but not all of it.

 

[earthandfire]

I won't get a chance before tonight (its 3pm UK time now), so if its urgent, hopefuly someone else will put something together for you. Failing that I'll post an example for you ready for the morning (UK time obvioulsy).

 

[AccessNewbe]

Hello earthandfire and PHV,

Currently my variables are in a class which contains a group of variables common to all model types. I am passing that class between my various functions as an easy way to share those values. The equation or formula is being read from a text field in a database. So, no, the variables and equations are not on a form at this time (if I understand you correctly).

However, your suggestions do bring up an interesting possibility;

My program does have a form which is a simple UI allowing selection of a model type and available options. There is a button who’s _Click( ) function launches the costing function and then displays the resulting material and labor costs.

Is there a possibility that I could create some (hidden) fields on my form and use those to pass data and work with the formulas via Eval( )? Keep in mind that I would be reading the formula from a text field in my database rather than a hard coded line in the module.

I am thinking that I would probably run into that same it’s-a-string-not-a-variable issue working with form fields as I had with variables though. Still, the question may be worth asking.

At this point, the little parsing function does not seem to be too a bad solution to my problem. The formula text, which I store in the database, is exactly as it would appear in code with the exception of the curly brackets. That makes it easy to test the code, then paste it into the database and add the brackets around the variables. The only thing that I really dislike is that inelegant if-then block but then you can’t have everything! ;-)

Best wishes,
Todd Bailey

 

[Ascentient]

I don't see why you can't you the text boxes visible property on a form and then store values to them. Then if you needed to reference the contents from other forms you could do so. Though I think passing that data would be "safer." I don't think you will have any problems reading the conents from the text boxes as you would from tables. I ran into a similar issue awhile back and I Think I was able to reference the contents with no problem.

Yea, I got a big IF ENDIF and IF ElseIF block I just completed. It is the ugliest thing I have seen in a long time. I am going to post it into another thread and see if anyone has any ideas on how to shorten it.