How to return an array of records in Excel VBA Function?

Question

Consider code below. How can I return a multidimensional array in the below method?

My SQL Statement returns this:

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

GetOrder only returns the first row. I want the array to contain all rows. When I use GetOrder(i), which is how I think I should populate an array, I get an error of "ByRef argument type mismatch". My return type is Variant, which I think should cover Array type. When I change Function return to be Array, I get another error, which makes me think that is the wrong direction to pursue.

How can I return an array of records without errors?

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

You could use the GetRows method.

Brian M Stafford
– Brian M Stafford

2021-03-22 19:31:14 +00:00
Commented Mar 22, 2021 at 19:31 — Brian M Stafford
– Brian M Stafford, Commented Mar 22, 2021 at 19:31

PeterT · Accepted Answer · 2021-03-22 19:32:04Z

2

You are re-creating the Array with each iteration of the loop. So the function is returning the last record from the SQL recordset. Try something like this:

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

answered Mar 22, 2021 at 19:32

PeterT

8,5971 gold badge19 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to return an array of records in Excel VBA Function?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related