VBA part of two dimensional Array into combobox.List

Question

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

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

Array then looks like:

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?

user3598756 · Accepted Answer · 2016-10-21 08:43:54Z

2

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)

answered Oct 21, 2016 at 8:43

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

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

1 Comment

Petr Kateřiňák Over a year ago

Thanks a lot, I have seen rows as collumns and columns as rows, that was that problem. Thank you :-)

Collectives™ on Stack Overflow

VBA part of two dimensional Array into combobox.List

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related