Calling Access VBA function from Excel

Question

I am building a compensation worksheet. The tables and key information is in an access database, Compensation.accdb, which has a function to figure out the compensation based on the agent's contract. If the agent is a "REP" the Access VBA function does one computation and if the agent is a SRP it does another. Several separate users want to call this function from Excel (btw, MS Office 2013). The idea is a user changes or inputs the quantity in their spreadsheet and the spreadsheet calls the Compensation.accdb VBA function. Compensation.accdb does the computation, and passes the value back to the originating spreadsheet.

Here is the Compensation.accdb VBA code:

Option Compare Database

Function AutoComp(WritingRepContract As String) As Integer 'Auto insurance
    'Debug.Print WritingRepContract
    If WritingRepContract = "REP" Then
        AutoComp = 1 'Compensation formula for Rep will go here
    End If

    If WritingRepContract = "SRP" Then
        AutoComp = 2 'Compensation formula for Senior Rep will go here, etc.
    End If

End Function

Here is the Excel VBA code:

Public Sub Worksheet_Change(ByVal Target As Range) 
    'Is there a better way than this???
    Dim WritingLevel As String
    If Target.Address = "$B$8" Then 
    'This is the cell where one would enter Auto value.
        If Target.Value = 1 Then 'just a test to see if working
            WritingLevel = "REP"
        Else
            WritingLevel = "SRP"
        End If
        CallAutoComp (WritingLevel)
    End If

End Sub

Sub CallAutoComp(WritingLevel)
    Dim appAccess As Access.Application
    Dim test As Integer

    Set appAccess = New Access.Application
    Debug.Print appAccess.Eval("AutoComp(WritingLevel)") 
    'A test to see what is happening
    With appAccess
        .OpenCurrentDatabase "C:\Mypath\Compensation.ACCDB"
        .Visible = False ' Useful for debugging when true
        .Eval ("AutoComp(WritingLevel)")
    End With
    test = appAccess.Eval("AutoComp(WritingLevel)")
    With appAccess
        .CloseCurrentDatabase
        .Quit
    End With

End Sub

When I change the value in B8 I get this:

Run-time error 2425: The expression you entered has a function name that Microsoft Access can't find.

It does not like the following statements (same 2425 error for all three statements):

Debug.Print statement
.Eval ("AutoComp(WritingLevel))") statement
test = appAccess.Eval("AutoComp(WritingLevel)")

I have tried several version of single and double quotes and "&" surrounding WritingLevel with no success.

I am not sure if this is the best approach. This is the first time I have tried to cross platforms like this, but it would be a great help to a lot of people if I could get an answer to this problem. It would be great if I could just call the function in an easier way, such as one does for internal Excel functions. I have searched for days with different versions of my search string with no success.

When I replace AutoComp(WritingLevel) with AutoComp(SRP) or AutoComp(REP) it works fine.

Does this happen to work? appAccess.Eval("AutoComp(" & Chr(34) & WritingLevel & Chr(34) & ")")? Why is the code being handed off to Access? Does it need some data from its tables? How is the calc accomplished? You could possibly move this off to a recordset solution. — VBlades
WritingLevel is a variable in excel that does not exist in access. that's why access is unhappy. You will need to pass "SRP" or "REP" (aka "AutoComp(" & WritingLevel & ")"), or just make autocomp a part of the excel file and use recordsets to access the access db. — serakfalcon
Your Chr(34) solution works, thx much! Code is handed to Access because Access houses the large tables and function to traverse those tables, which is how the function returns the compensation result. — Rod

Johnny Bones Johnny Bones · Accepted Answer · 2014-07-08T02:14:00

Try using Application.Run instead. Something like:

Application.Run ("AutoComp", WritingLevel)

I've done something similar, but not exactly like what you're doing. In my case, the function name was a variable and the value passed to it was a constant, but theorhetically my code should work.

Calling Access VBA function from Excel

2 Answers