Fill listbox with array and add next 2 array into

Question

I open many workbooks in for cycle and load data to dynamic array. Now I need to get the values from these arrays into the listbox, but don't know how...

Dim rng as Variant
Set rng = wsDat.Range(Cells(2, 1), Cells(lastRow, 1).Offset(0, 10)) 'for more ws

myArray1 = rng.Value 'result of ws1 data
myArray2 = rng.Value 'result od ws2 data
.
.
.

Me.myListbox.List = myArray1
Me.myListbox.List ??? add myArray2

Side note: you need to qualify the worksheet for `Cells(2, 1)` and `Cells(lastRow, 1)`. See [this](https://stackoverflow.com/questions/8047943/excel-vba-getting-range-from-an-inactive-sheet) and [this](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells). — BigBen, May 20 '21 at 16:37
@BigBen Thank you... Is it right now? Dim wsDat As Worksheet Set rng = wsDat.Range(wsDat.Cells(2, 1), wsDat.Cells(lastRow, 1).Offset(0, 10)) — excel222, May 20 '21 at 16:49
Yes now the `Cells` calls are qualified with the appropriate `Worksheet`. — BigBen, May 20 '21 at 16:50
Do you need to load the list box with the values of **each array**? I mean to load it as many times as many sheets exists? Or to load the list box with **content of all these arrays**? Looking to "Me.myListbox.List ??? add myArray2" I would suppose that you need to add in the list box all these arrays content. In such a case, you cannot use `list` property to add items. You should join somehow the arrays and load the `list` list box property at once at the end of the code. I can prepare a piece of code if my understanding/supposition is correct. — FaneDuru, May 20 '21 at 18:05

FaneDuru · Answer 1 · 2021-05-21T08:41:08.297

Supposing that you need to load in the list box all content of those arrays, you cannot use list property in order to add items in that way. The next .list = array erases the previous list items. You can use it once and then add items by iteration, or, more elegant, joining the arrays and load the joined one using list at the end. To test this last way, please try the next code:

 Sub JoinArraysLoadList()
    Dim ws As Worksheet, lastRow As Long, myArray, myArrTot
    
    For Each ws In ActiveWorkbook.Sheets
            lastRow = ws.cells(ws.rows.count, 1).End(xlUp).row
            myArray = ws.Range(ws.cells(2, 1), ws.cells(lastRow, 1).Offset(0, 10)).Value
            If Not IsArray(myArrTot) Then 'if myArrTot did not receive any range value:
                myArrTot = myArray
            Else                                    'join myArrTot with the newly extracted array:
                'the arrays need to be transposed, in order to add elements (allowed only on the second dimension)
                myArrTot = JoinArrays(Application.Transpose(myArrTot), Application.Transpose(myArray))
            End If
    Next
    Me.myListbox.List = myArrTot
 End Sub

 Function JoinArrays(arrT, arr) As Variant
    Dim i As Long, j As Long, nrRows As Long
    
    nrRows = UBound(arrT, 2) 'the existing number of rows (now, columns...)
    ReDim Preserve arrT(1 To UBound(arrT), 1 To nrRows + UBound(arr, 2))
    For i = 1 To UBound(arr)
        nrRows = nrRows + 1 'increment the next row to be loaded
        For j = 1 To UBound(arr, 2)
            arrT(j, nrRows) = arr(i, j)
        Next j
    Next i
    JoinArrays = Application.Transpose(arrT)
 End Function

Edited:

A more faster/efficient variant should be the next solution:

Sub JoinArraysLoadListJgArr()
    Dim wb As Workbook, ws As Worksheet, lastRow As Long
    Dim cnt As Long, noEl As Long, myArray, myArrTot, jgArr
    
    Set wb = ActiveWorkbook          'use here the necessary workbook
    ReDim jgArr(wb.Sheets.count - 1) 'redim the jagged array at the maximum necessary dimensioned (0 based array)
    For Each ws In wb.Sheets
            lastRow = ws.cells(ws.rows.count, 1).End(xlUp).row
            myArray = ws.Range(ws.cells(2, 1), ws.cells(lastRow, 1).Offset(0, 10)).Value
            noEl = noEl + UBound(myArray)       'counting the number of each array rows
            jgArr(cnt) = myArray: cnt = cnt + 1 'loading each sheet array in the jagged one
    Next
    If cnt < wb.Sheets.count Then
        ReDim Preserve jgArr(cnt - 1) 'preserve only the existing elements (if a sheet or more have been skipped)
    End If
    myArrTot = JoinJgArr(jgArr, noEl)
    Me.myListbox.List = myArrTot
 End Sub
 
 Function JoinJgArr(jgA, N As Long) As Variant
    Dim k As Long, i As Long, j As Long, iRow As Long, arrFin
    
    ReDim arrFin(1 To N, 1 To UBound(jgA(0), 2)) 'redim the array to take the jagged array elements
    
    For k = 0 To UBound(jgA)                  'iterate between the jagged array elements
        For i = 1 To UBound(jgA(k))           'iterate between each jagged array element rows
            iRow = iRow + 1                   'counting the rows or the final array to be filled!!!
            For j = 1 To UBound(jgA(k), 2)    'iterate between each jagged array element columns
                arrFin(iRow, j) = jgA(k)(i, j)'put values in the final array
            Next j
        Next i
    Next k
    
    JoinJgArr = arrFin
 End Function

@T.M. Thanks! I had the function joining arrays, but when tried to define the above solution, I could imagine a faster, more efficient one. Since OP does not say anything, I lost my enthusiasm in building a better version. Now, if somebody (important...) remarked the code, I will create the optimized version. Putting each sheet array in a jagged array and counting each array rows will make the code use less resources. Not necessary to transpose and stress the memory with more `ReDim Preserve` lines. Having solution in my had, it should take some minutes to put it in practice. I hope... :) — FaneDuru, May 21 '21 at 08:27
@FaneDuru Thank you, but it is to complicated for my purpose. I'll probably have to copy it to the worksheet in Activeworkbook first and then add in Listbox.list. I don't know if it will be possible easy way. — excel222, May 21 '21 at 09:18
@excel222: If I would be you, I will firstly start with describing of **what my purpose is**... In principle, do you need to load that specific list box with **all sheets content of their first ten columns**? If yes, what kind of list bos is it about? Is it a sheet ActiveX type? I mean does it exist on a sheet or on a form? According to your answer of the above questions, I will tell you how to use the code. It should be easy... — FaneDuru, May 21 '21 at 09:45
@FaneDuru Thank you very much for your interest. Every month I have several workbooks in which I record goods for customers. Now I'm trying to load data from these workbooks to UserForm ListBox. In this listbox I select the items and make an invoice. In the original workbooks, the invoiced items must be marked as "done" or "storno". I am a beginner in VBA and this project is difficult for me. That's why I try to go through small parts and learn simple codes. — excel222, Jun 03 '21 at 08:31
@excel222: According to your comment, you can use the code like it is. I mean, save the code I posted (the faster version) in a standard module, create a button on the UserForm and put a single code line in its `Click` event: `JoinArraysLoadListJgArr`. Firstly test it and, if you want learning, run the code line by line (pressing F8 being in VBE), reading the comments and see what happens... But if you will send feedback after other two weeks, my enthusiasm in helping you will be diminished... :) — FaneDuru, Jun 03 '21 at 08:42

Fill listbox with array and add next 2 array into

1 Answers1