Keep information in array when using a form multiple times

Question

I have a form in which, every time a button is clicked, an array is filled in with the information of a textbox.

How can I keep the elements in the array and not have it empty every time I use the form? This is the code I wrote:

Dim myArray() As String

Sub button_Click()
    
    If Len(Join(myArray)) = 0 Then
        k = 0
    Else
        k = UBound(myArray)
    End If

    ReDim Preserve myArray(k To k + 1)
    myArray(k) = "Number " & k

End Sub

Declare the array in a standard module: Public myArray() As String, if you need to use it from different places (modules)... Or declare it as Static inside the sub, if you need it only for that specific Sub. And, of course, delete/comment the declaration from the form. But, before that, can you explain in words what you try accomplishing? At the first glance it looks that an error will be raised when run the code second time... Do you want incrementing the numeric part after each click? — FaneDuru

Irene Ortiz de Guzmán Irene Ortiz de Guzmán · Accepted Answer · 2022-07-28T11:44:21

I did what @FaneDuru recommended. I declared in a standard module Public myArray() As String and this is the generic code that will be inside the form below, which also had erros:

Dim k As Integer

Private Sub butt_add_Click()

    If Len(Join(myArray)) = 0 Then
        k = 0
        ReDim myArray(0)
        myArray(k) = textBox.Value
    Else
        k = UBound(myArray) + 1
        ReDim Preserve myArray(k)
        myArray(k) = textBox.Value
    End If
    
    Sheets("Prueba").Cells(k + 1, 1) = myArray(k)

All in all, you can store the information of a text box in an array, which increments the number of elements as many times as the button is pressed.

The last line of the code is just for checking everything works properly.

Keep information in array when using a form multiple times

1 Answers