Calling Access VBA function from Excel
excel, ms-access, vba
Solution
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.
Problem
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.