ReDim Preserve doesn't Preserve at all

I need my code to perform actions as follows:

I type a value in myComboBox (I want to remove this value from myListBox)
I click on the Remove_CommandButton
the code search for a correspondent value in myListBox and remove it
after removing it, I store the value in myComboBox inside myArray (because I need to do something else with it)
the code clears myComboBox

The issue I get is that ReDim Preserve does NOT preserves the previous values inside myArray. I just get the last value that I've typed in myComboBox while the other entries are set to Null (or "").

I cannot understand why this is happening, but probably the solution is easier that what I think... I just cannot figure it out!

Private Sub Remove_CommandButton_Click()

Dim i, myCount As Integer
Dim msgString As String
Dim myArray() As Variant

For i = 0 To myListBox.ListCount - 1
    With myListBox
       If .List(i, 0) = myComboBox.text Then
                .RemoveItem (i)
                myListBox2.List = myListBox.List
                ReDim Preserve myArray(myCount + 1)
                myArray(myCount) = CStr(myComboBox.text)
                myCount = myCount + 1
                msgString = Join(myArray(), vbCr)
                MsgBox msgString
       End If
    End With
Next i
myComboBox.Clear

End Sub

Thank you in advance for your time and support!!

Solution

I assume you need a list of "removed" items later, eg when a different button is pressed. I see the following issues:

(1) Main problem is that MyArray is a local variable in the trigger routine. Once the code is ready, the content is gone. When you click the button again, an empty MyArray will be created again and there is nothing to preserve.

(2) When you want to loop over a list using an index and plan to remove values from that list, you need to loop backwards (because after removing an item, the indexes of the following items change).

(3) As far as I see, you always write into the second last element, not the last (you resize to myCount + 1 but write into index myCount.

So first, be sure that your list of items survives the call. This can be done by declaring it at the top of the code (of the form), right after the command Option Explicit (that you hopefully use), which makes it a global variable inside the form module that lives as long as the form itself lives.

Next thing is that you are using a local variable myCount that is also lost when the routine finished, and starts at 0 the next time. You could also declare it as global, but in fact you don't need it. You can check the size of an array with LBound and UBound and use that to resize the array and write into the latest element. Only thing there you need to know is that an dynamic array has no lower and upper bound before the first Redim-statement. That's the reason I use error handling in the following code.

Dim MyArray() As Variant

Private Sub UserForm_Activate()
    Erase MyArray   ' Make sure you start with empty array
End Sub

Private Sub Remove_CommandButton_Click()
    Dim i As Long
    For i = myListBox.ListCount - 1 to 0 Step -1
        With myListBox
           If .List(i, 0) = myComboBox.text Then
               .RemoveItem i   
                myListBox2.List = myListBox.List
                On Error Resume Next
                ReDim Preserve MyArray(LBound(MyArray) To UBound(MyArray) + 1)
                If Err.Number = 9 Then ReDim MyArray(1 To 1)  ' Array was not initialized
                On Error GoTo 0
                myArray(UBound(myArray)) = myComboBox.text
           End If
        End With
    Next i
    myComboBox.Clear

End Sub

An (IMHO better) alternative to an array could be a Collection or a Dictionary, those types are build for lists of data that can grow. But again, the list needs to be declare outside the routine:

Dim myList as Collection
Private Sub UserForm_Activate()
    Set myList = new Collection()
End Sub

Private Sub Remove_CommandButton_Click()
    ...
    myList.Add myComboBox.text
    ...
End Sub

Note that the line

Dim i, myCount As Integer

will declare i as Variant, in VBA you need to specify the type for every variable explicitly

Dim i As Integer, myCount As Integer

Note also that the data type Integer is obsolete, you always should use Long.

Dim i As Long, myCount As Long

When calling a Sub in VBA, don't use parenthesis for the parameter or use the Call-statement

  .RemoveItem (i)   ' <-- Do not use this...
  .RemoveItem i     ' <-- ...but this