CODEWITHSUNDEEP
arraysvbaexcelcombobox
Petr Kateřiňák
Petr Kateřiňák

Reputation: 107

VBA part of two dimensional Array into combobox.List

in my code I get by SQL query two-dimensional array inside Variant queryResult.

queryResult = RetrieveRecordset("SELECT * FROM Materials;")

Array then looks like:

enter image description here

And if I try to fill up Combobox 

cbSTPScrewedMaterial.List = queryResult

It fills only values 1 and Plastic, but I wish to get values from Plastic to Hard Plastic -> queryResult(1) on image above, but when I type

cbSTPScrewedMaterial.List = queryResult(1)

VBA echoes me Runtime Error 9: Subscript out of range. Do you have idea, what I'm doing wrong and how to solve it?

Upvotes: 0

Views: 1434

Answers (1)

user3598756
user3598756

Reputation: 29421

queryResult is a two-dimensional array with 2 rows and 5 columns

set your combobox ColumnCount property to 5

cbSTPScrewedMaterial.ColumnCount = 5

and have them all shown

Furthermore queryResult(1) is trying to access a two-dimensional array as one-dimensional one, thus resulting in an error

you could use a function to return a one-dimensional array out of a given two-dimensional array row like follows 

Option Explicit
Function GetQueryRow(arr As Variant, iRow As Long) As Variant
    Dim j As Long
    ReDim arrRow(LBound(arr, 2) To UBound(arr, 2)) As Variant

    For j = LBound(arr, 2) To UBound(arr, 2)
        arrRow(j) = arr(iRow, j)
    Next j
    GetQueryRow = arrRow
End Function

to be used like:

Dim queryResult As Variant
Dim queryRow As Variant

queryResult = RetrieveRecordset("SELECT * FROM Materials;")
queryRow = GetQueryRow(queryResult, 1)

or "directly"

Dim queryRow As Variant

queryRow = GetQueryRow(RetrieveRecordset("SELECT * FROM Materials;"), 1)

or even "more directly"

MsgBox GetQueryRow(RetrieveRecordset("SELECT * FROM Materials;"), 1)(1)

Upvotes: 2

Related Questions