Wednesday, January 2, 2013

The Amazing Eval Function

The Eval function in VBA allows you to execute a string as if it were a line of code. There are some examples in Access VBA help that are marginally useful, but what I like to use Eval for is executing optional functions. Normally, if you call a function that doesn't exist, you'll get a compile error and your code won't run. But the Eval function is one place where you can call a function that may or may not exist, and trap the error condition that occurs when the function does not exist. Here's an example:

Dim strRtn As String
Dim strFunctionNameAndParms As String

strFunctionNameAndParms = "Date()"    '<== WE'RE GOING TO CALL THIS FUNCTION

On Error Resume Next
strRtn = Eval(strFunctionNameAndParms)
If Err <> 0 Then
    Select Case Err
    Case 2425, 2426
        '* The function is not found
        MsgBox "The function '" & strFunctionNameAndParms & "' was not found."
    Case Else
        '* There was an error calling the function
        MsgBox "Error when calling '" & strFunctionNameAndParms & "': " & Err.Number & " - " & Err.Description
    End Select
    MsgBox "Return value from '" & strFunctionNameAndParms & "' is: " & vbCrLf & strRtn
End If
On Error GoTo 0

Now, if the function above ("Date()", in this instance) doesn't exist, there's no compile error, the code keeps right on executing.

You could use this if you, for instance, wanted to optionally include an "Accounts Receivable" module in your   accounting application. The main menu item for AR could use Eval to call the "StartAR()" function that lives in a "basAccountsReceivable" module that you can optionally include in your database. If the module is there, then the function runs and Accounts Receivable opens. If the module is not there, then you could have a message appear... something like "The Accounts Receivable module has not been included in this application build. To purchase this additional module please contact ..."

1 comment:

Anonymous said...

Thanks for your insight. I was just curious about how others have creatively used this function.