从 Excel 调用 Access VBA 函数

Calling Access VBA function from Excel

提问by Rod

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.accdbVBA function. Compensation.accdbdoes the computation, and passes the value back to the originating spreadsheet.

我正在建立一个补偿工作表。表格和关键信息位于访问数据库中Compensation.accdb，该数据库具有根据代理合同计算补偿的功能。如果代理是“REP”，Access VBA 函数会执行一个计算，如果代理是 SRP，它会执行另一个计算。几个独立的用户想要从 Excel 调用这个函数（顺便说一句，MS Office 2013）。这个想法是用户在他们的电子表格中更改或输入数量，电子表格调用Compensation.accdbVBA 函数。 Compensation.accdb进行计算，并将值传递回原始电子表格。

Here is the Compensation.accdbVBA code:

这是Compensation.accdbVBA代码：

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:

这是 Excel VBA 代码：

Public Sub Worksheet_Change(ByVal Target As Range) 
    'Is there a better way than this???
    Dim WritingLevel As String
    If Target.Address = "$B" 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:

当我更改 B8 中的值时，我得到以下信息：

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

运行时错误 2425：您输入的表达式具有 Microsoft Access 找不到的函数名称。

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

它不喜欢以下语句（所有三个语句都出现相同的 2425 错误）：

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

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

我曾尝试单引号和双引号的几个版本，"&"周围WritingLevel没有成功。

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.

我不确定这是否是最好的方法。这是我第一次尝试像这样跨平台，但如果我能得到这个问题的答案，对很多人都会有很大的帮助。如果我能以一种更简单的方式调用函数就好了，比如内部 Excel 函数。我已经用不同版本的搜索字符串搜索了几天，但没有成功。

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

当我替换AutoComp(WritingLevel)为AutoComp(SRP)或AutoComp(REP)它工作正常。

回答by Johnny Bones

Try using Application.Runinstead. Something like:

尝试改用Application.Run。就像是：

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.

我做过类似的事情，但并不完全像你在做什么。在我的例子中，函数名是一个变量，传递给它的值是一个常量，但理论上我的代码应该可以工作。

回答by user6870959

It also appears that the AccessDB function fails to assign a return value to itself if the argument is not "REP" or "SRP".
May want add: AutoComp = (-1) at beginning of function.

如果参数不是“REP”或“SRP”，AccessDB 函数似乎也无法为其自身分配返回值。
可能要在函数的开头加上：AutoComp = (-1)。