如何在Excel VBA函数中返回记录数组？

Question

考虑下面的代码。如何在下面的方法中返回多维数组？

我的SQL语句返回以下内容：

+-----+-------------+
| id  | description |
+-----+-------------+
| 111 | AAA-11      |
| 222 | BBB-2222    |
+-----+-------------+

GetOrder仅返回第一行。我希望数组包含所有行。当我使用GetOrder(i)时，我认为我应该用它来填充数组，我得到了一个"ByRef参数类型不匹配“的错误。我的返回类型是Variant，我认为它应该包含数组类型。当我将函数return改为be Array时，我得到了另一个错误，这让我认为这是一个错误的方向。

如何才能无错误地返回记录数组？

Function GetOrder(OrderNo As Long) As Variant

Const CONN = "DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=;DATABASE=;UID=;PWD=; OPTION=3"

Const SQL = "select * from items where category_id = ?"

Dim dbConn As ADODB.connection, dbCmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim param As ADODB.Parameter, n As Long

Set dbConn = New ADODB.connection
dbConn.Open CONN

Set dbCmd = New ADODB.Command
With dbCmd
    .ActiveConnection = dbConn
    .CommandType = adCmdText
    .CommandText = SQL
    Set param = .CreateParameter("P1", adInteger, adParamInput, 0)
    .Parameters.Append param
End With

Set rs = dbCmd.Execute(n, OrderNo)

Dim i As Integer
i = 0
Do While Not rs.EOF
   GetOrder = Array(rs(0).Value, rs(1).Value) ' I want GetOrder to be an array of records
   rs.MoveNext
   i = i + 1
Loop
   
dbConn.Close

End Function

Answer 1

您将在循环的每次迭代中重新创建Array。因此，该函数返回SQL记录集中的最后一条记录。尝试如下所示：

Dim results As Variant
Redim results(1 to rs.Recordcount, 1 to rs.Fields.Count)
Dim i As Long
Dim j As Long
i = 1
Do While Not rs.EOF
    For j = 1 To rs.Fields.Count
        results(i, j) = rs(j - 1).Value
    Next j
    i = i + 1
Loop
dbConn.Close
GetOrder = results