Microsoft Access Tutorial: Using The Access VBA Eval Function

Here’s a widely underused and understood Access VBA function known as EVAL which I’ve included here in my Microsoft Access tutorial series.

The Microsoft website information page on the Eval Function covers the syntax and general information with some examples that explains how to use this function.

Eval is short for evaluation and that is exactly what this function returns; the evaluation of a string statement which could be another function call

Microsoft Access Tutorial: Using The Access VBA Eval Function

This is an amazing function and one that’s only available in an application development package like Access. The Eval function (short for Evaluation) allows you to execute a code statement. All you have to do is create a string that holds the line of code that you want to execute and pass it to the Eval function. This code, for instance, puts the number 4 into the variable ing:

Dim strTwo As String

Dim strStatement As String

Dim ing As Integer

strTwo = “2”

strStatement = strTwo & ” + ” & strTwo

ing = Eval(strStatement)

For a function like Eval to work its magic, your application execution environment must have access to the language’s interpreter. Visual Basic 6 programmers can’t have an equivalent to the Eval function because VB6 applications are distributed without the Visual Basic compiler. But, because Access is distributed with the VBA interpreter, we get to use the Eval function. The downside of having access to this feature is that distributing the VBA interpreter with Access applications makes the Access distribution package quite large.

But the key issue is what you can do with the Eval function. The Eval function lets you deliver applications that can create themselves dynamically at runtime. You can build your application code at runtime, pass it to the Eval statement, and have your dynamically generated line of code processed.

For instance, imagine that your application accepts a wide variety of input from your users and has to call special processing depending on the kind of data being processed. You could write a massive Select Case statement that checks the type of data being input and calls the right function:

Select Case (Me.txtProcessingType)

Case “Update”

Call UpdateRoutine

Case “Delete”

Call DeleteRoutine

Case “Add”

Call “AddRoutine”

End Select

A simpler solution is to use the Eval function. Since the subroutines that are being called have names that that are based on the value in txtProcessingType, you can just do this:

Eval Me.txtProcessingType & “Routine”

You can even pass parameters to a subroutine that you call with the Eval function. This code passes the value of the strName parameter to a routine called ProcessName:

Eval “ProcessName ‘” & strName & “‘”

If the Eval function is passed the name of some other function (including one of your own), the Eval function will call the other function and return the result. This code sample passes the numbers 2 and 3 to a function called AddNumbers and puts the result in a variable called intResult:

intResult = Eval(“AddNumbers(2, 3)”)

How might you use this? One way that I can think of is to create a table of function names with the key in the table tied to some processing code. Your program could then retrieve the function associated with the processing code by reading the function from the table. Here’s that code:

Dim rst As New ADODB.Recordset

rst.Open “Select FunctionName ” & _

” From FunctionTable ” & _

” Where ProcessingCode = ‘” & _

Me.txtProcessingType & “‘;”, _

CurrentProject.Connection

Eval rst(“FunctionName”)

This code would let you add new functions to your application (or change the function to be used for some processing type) just by adding a new processing type/function name combination to the table. There’s another benefit: You would validate processing types by checking to see if the processing code is in the FunctionTable.