Appending new positions to a form listbox

Question

I have a UserForm in Excel with a ListBox, which must show the result of a computation. The problem is when I try to fill the Listbox using a recursive loop the information which was there earlier is replaced with new data. How can I append new information to the content in the ListBox and not lose earlier information?

My current code:

Dim Form As HistoryFRM, ARR(), i,  ArrHistory()
.....
Set Form = New HistoryFRM
With Form
    .Show vbModeless
    .LBHistory.ColumnCount = 6
    For i = 0 To UBound(ARR)
        ArrHistory = SQL_Editor("SELECT * FROM [Table] WHERE [ID]='" & ARR(i) & "';")
        .LBHistory.Column = ArrHistory
    Next i

End With

Answer 1

If I understand you correctly, you want to fill six columns with the array you're getting from the data base. Both the List and Countproperties replace the content of their lists when an array is assigned. The AddItem method allows you to append new items to the list, but in one dimension, only.

In order to append a new array of items, I believe you first need to read the current list into an array, append the new items to that array, then write the entirety back to the ListBox. Here's an example:

Dim arr()
Dim lb As ListBox
Dim numCols As Long
Dim rowCount As Long, colCount As Long
Dim numNewRecs As Long, newRecCount As Long

Set lb = Me.ListBox1
'You need to know how many new records are coming in
'Substitute this determination here:
numNewRecs = 2
numCols = lb.ColumnCount - 1

'Dimension the array for the current list plus the new records
ReDim arr(lb.ListCount - 1 + numNewRecs, numCols)
'Get the current list
For rowCount = 0 To lb.ListCount - 1
    For colCount = 0 To numCols
        arr(rowCount, colCount) = lb.List(rowCount, colCount)
    Next
Next
'Append the new records
For newRecCount = rowCount To rowCount + numNewRecs - 1
    For colCount = 0 To numCols
        arr(newRecCount, colCount) = "New data" & CStr(newRecCount)
    Next
Next
'Populate the ListBox
lb.List = arr()