调用返回String数组的VBA函数时触发类型不匹配编译错误

问题背景 #

编写了VBA函数GetOfficeEmailAddressesForGroupID，代码如下：

Public Function GetOfficeEmailAddressesForGroupID(lngGroupID As Long) As String()

    ' this procedure returns an array containing email addresses
    ' from the field [Office-Email] in the query qryPL_Email_from_Full_PGAL
    ' with the GroupID = lngGroupID as specified in the function argument
    
    'note that for some GroupID's, there may be only 1 email address
    
    Dim cnn             As ADODB.Connection
    Dim rs              As ADODB.Recordset
    
    Dim strSQL          As String
    
    Dim astrEmails()    As String
    Stop
    
    On Error GoTo Error_Section
    
    strSQL = "SELECT [Office-Email] " _
                & "FROM [qryPL_Email_from_Full_PGAL] " _
                & "WHERE [GroupID] = " & lngGroupID
    
    Set cnn = New ADODB.Connection
    
    Set cnn = CurrentProject.Connection
    
    Set rs = New ADODB.Recordset
    
    With rs
    
        .ActiveConnection = cnn
        .CursorLocation = adUseServer
        .CursorType = adOpenKeyset
        .LockType = adLockReadOnly
        
        .Open strSQL, , , , adCmdText
        
        astrEmails = Split(![Office-Email], "; ", vbDatabaseCompare)
    
    End With

Exit_Section:

    GetOfficeEmailAddressesForGroupID = astrEmails
    
    If rs.State <> adStateClosed Then
    
        rs.Close
        
    End If
    
    If cnn.State <> adStateClosed Then
    
        cnn.Close
        
    End If
    
    Set rs = Nothing
    Set cnn = Nothing
    
    Exit Function

Error_Section:

    MsgBox "Error: # " & Err.Number & " Description: " & Err.Description
    
    If rs.State <> adStateClosed Then
    
        rs.Close
        
    End If
    
    If cnn.State <> adStateClosed Then
    
        cnn.Close
        
    End If
    
    Set rs = Nothing
    Set cnn = Nothing
    
    GetOfficeEmailAddressesForGroupID = astrEmails

End Function

该函数接收Long类型参数lngGroupID，返回String数组，用于从查询qryPL_Email_from_Full_PGAL中获取指定GroupID对应的[Office-Email]字段值并拆分为数组。

在立即窗口执行调用：

? GetOfficeEmailAddressesForGroupID(1)

未进入函数代码就弹出编译错误：

Compile Error:

Type mismatch

错误原因 #

VBA立即窗口中的?（等同于Print语句）无法直接输出数组类型的返回值。函数返回的是String()数组，而Print仅支持输出标量值，因此触发类型不匹配的编译错误。

解决方法 #

方法1：通过变量中转后遍历输出 #

在立即窗口先将函数返回值赋值给数组变量，再遍历打印每个元素：

Dim emails() As String
emails = GetOfficeEmailAddressesForGroupID(1)
For Each email In emails
    Debug.Print email
Next

方法2：修改函数返回类型（按需选择） #

如果业务场景允许不返回数组，可将函数改为直接返回拼接好的字符串，这样就能用?直接调用：

Public Function GetOfficeEmailAddressesForGroupID(lngGroupID As Long) As String
    ' 保留原查询逻辑，仅修改返回部分
    GetOfficeEmailAddressesForGroupID = ![Office-Email]
End Function

方法3：添加辅助打印函数 #

编写专门用于输出数组的辅助函数，后续直接调用该函数验证结果：

Sub PrintArray(arr As Variant)
    If IsArray(arr) Then
        Dim elem As Variant
        For Each elem In arr
            Debug.Print elem
        Next
    Else
        Debug.Print arr
    End If
End Sub

在立即窗口执行：

PrintArray GetOfficeEmailAddressesForGroupID(1)

函数本身的潜在优化点 #

• 如果查询可能返回多条记录，当前代码中![Office-Email]只会取第一条记录的值，需确保每个GroupID对应唯一的[Office-Email]，或者修改逻辑处理多条记录的情况。
• 若查询无结果，astrEmails会是未初始化的数组，建议在Exit_Section前添加判断，初始化空数组：

If rs.EOF Then
    ReDim astrEmails(0 To -1) ' 初始化空数组
Else
    astrEmails = Split(![Office-Email], "; ", vbDatabaseCompare)
End If

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