在Excel宏中调用返回SQL查询结果的UDF时遇参数类型不匹配求助

我有一个可作为表格公式运行的UDF，能执行SQL查询与更新操作。但使用下方宏代码调用该UDF获取记录数时，触发「ByRef参数类型不匹配」错误，需要修正方案。

原UDF代码 #

Function SqlExec(SqlStmt As String, Trigger As Integer)

Dim CString As String

CString = _
    "Provider=SQLOLEDB.1;" & _
    "Password=P@ssw0rd;" & _
    "Persist Security Info=True;" & _
    "User ID=sa;Initial Catalog=GlasPacLX_Testdb;" & _
    "Data Source=192.168.19.110\gts;" & _
    "Use Procedure for Prepare=1;" & _
    "Auto Translate=True;" & _
    "Packet Size=4096;" & _
    "Workstation ID=TWOSEATER;" & _
    "Use Encryption for Data=False;" & _
    "Tag with column collation when possible=False"

Trigger = Sheets("Code").Range("Trigger").Value

If Trigger = 1 Then

Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
    
    Set Cn = New ADODB.Connection
    Set Rs = New ADODB.Recordset

Cn.Open CString
Rs.Open SqlStmt, Cn, adOpenForwardOnly
SqlExec = Rs.GetRows
Rs.Close
Set Rs = Nothing
Cn.Close
Set Cn = Nothing

End If

End Function

原宏代码（运行报错） #

Sub SqlExecCount()

Trigger = 1
SqlStmt = "SELECT COUNT(DocumentNumber) FROM Document"

MsgBox SqlExec(SqlStmt, Trigger)

End Sub

错误原因与修正方案 #

核心问题 #

1. 参数传递类型不匹配：UDF的Trigger参数默认按ByRef传递，宏中未声明Trigger的变量类型，默认是Variant类型，与UDF要求的Integer类型冲突。
2. 返回值处理错误：UDF返回的是GetRows生成的二维数组，MsgBox无法直接显示数组内容。
3. 参数逻辑错误：UDF中直接覆盖了传入的Trigger参数，可能引发意外的参数修改。

修正后的UDF代码 #

Function SqlExec(SqlStmt As String, ByVal Trigger As Integer) As Variant
    Dim CString As String
    Dim localTrigger As Integer
    
    CString = _
        "Provider=SQLOLEDB.1;" & _
        "Password=P@ssw0rd;" & _
        "Persist Security Info=True;" & _
        "User ID=sa;Initial Catalog=GlasPacLX_Testdb;" & _
        "Data Source=192.168.19.110\gts;" & _
        "Use Procedure for Prepare=1;" & _
        "Auto Translate=True;" & _
        "Packet Size=4096;" & _
        "Workstation ID=TWOSEATER;" & _
        "Use Encryption for Data=False;" & _
        "Tag with column collation when possible=False"

    ' 使用局部变量读取单元格值，避免修改传入参数
    localTrigger = Sheets("Code").Range("Trigger").Value
    
    If localTrigger = 1 Then
        Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
        
        Set Cn = New ADODB.Connection
        Set Rs = New ADODB.Recordset

        Cn.Open CString
        Rs.Open SqlStmt, Cn, adOpenForwardOnly
        SqlExec = Rs.GetRows
        Rs.Close
        Set Rs = Nothing
        Cn.Close
        Set Cn = Nothing
    End If
End Function

修正后的宏代码 #

Sub SqlExecCount()
    ' 明确声明变量类型，与UDF参数类型匹配
    Dim Trigger As Integer
    Dim SqlStmt As String
    Dim result As Variant
    
    Trigger = 1
    SqlStmt = "SELECT COUNT(DocumentNumber) FROM Document"
    
    result = SqlExec(SqlStmt, Trigger)
    ' 提取二维数组中的数值（GetRows返回(列索引, 行索引)的二维数组）
    If IsArray(result) Then
        MsgBox result(0, 0)
    Else
        MsgBox "未获取到有效结果"
    End If
End Sub

关键修正点 #

• 给UDF的Trigger参数添加ByVal关键字，强制按值传递，避免类型不匹配问题。
• UDF中改用局部变量localTrigger读取单元格值，不修改外部传入的参数。
• 宏中明确声明所有变量的类型，确保与UDF参数类型一致。
• 对UDF返回的数组进行处理，提取具体数值后再用MsgBox显示。

内容的提问来源于stack exchange，提问作者user1636620