How to initialise an array properly without erasing values saved to it

Question

I have a userform with 2 listboxs (listbox1 & listbox2) that can exchange items through an 'Add' & 'Remove' buttons. Note that listbox1 is initialized from a range in excel while listbox2 is empty at first.

I tried adding two functions 'SaveItems()' and 'LoadSavedItems()' so that after closing the userform, upon reopening my listbox get populated with last saved values.

Here is the code I wrote:

Private saveditems_lstbox1() as Variant
Private saveditems_lstbox2() as Variant


Private Sub SaveItems()
    
    If ListBox1.ListCount <> 0 Then
        ReDim saveditems_lstbox1(ListBox1.ListCount - 1)
        For i = 0 To ListBox1.ListCount - 1
            saveditems_lstbox1(i) = ListBox1.List(i)
        Next i
    End If
    
    If ListBox2.ListCount <> 0 Then
        ReDim saveditems_lstbox2(ListBox2.ListCount - 1)
        For i = 0 To ListBox2.ListCount - 1
            saveditems_lstbox2(i) = ListBox2.List(i)
        Next i
    End If
    
End Sub


*****

Private Sub LoadSavedItems()
    
    Dim i As Long 
         
    If Not IsEmpty(saveditems_lstbox1) Then   
        ListBox1.Clear
        For i = LBound(saveditems_lstbox1) To UBound(saveditems_lstbox1)
            ListBox1.AddItem saveditems_lstbox1(i)
        Next i
    End If

    If Not IsEmpty(saveditems_lstbox2) Then    
        ListBox2.Clear
        For i = LBound(saveditems_lstbox2) To UBound(saveditems_lstbox2)
            ListBox2.AddItem saveditems_lstbox2(i)
        Next i
    End If

End Sub


***


Private Sub UserForm_Initialize()
   If IsEmpty(saveditems_lstbox1) Then 
        Dim Arr
        Dim last_row_A As Long
        
        last_row_B = Cells(Rows.Count, "B").End(xlUp).Row
        Arr = Range("A" & 1 & ":A" & last_row_A)

        ListBox1.List() = Arr
    
    Else    
    LoadSavedItems


End sub

Problem: If I initialize the variant 'Saveditems_lstbox1 & 2' inside the UserForm_Initialize() function, I erase the saved values ... If I don't initialize them I get an out of bound error !

Edit: I tried the code below to see if my 'SavedItems_lstbox1' is initialized and it returned False !

Public Function IsArrayInitialized(arr() As Variant) As Boolean
    On Error Resume Next
    Dim dummy As Variant
    dummy = arr(LBound(arr))
    IsArrayInitialized = (Err.Number = 0)
    On Error GoTo 0
End Function

Any hint or clue will be appreciated, thank you !

mits · Answer 1 · 2023-05-14T19:06:05.027

0

You should declare saveditems_lstbox1() and saveditems_lstbox2() in a general module.

Move these
Private saveditems_lstbox1() as Variant Private saveditems_lstbox2() as Variant to a general module but not private.

Also call SaveItems in the Userform_Terminate event.

Another solution is to store the listbox's values in certain cells in a worksheet that are out of sight eg. AA1.

EDIT
Try this.
Change the code in the UserForm_Initialize() event to this:

Dim Arr
Dim last_row_B As Long
    If IsEmpty(saveditems_lstbox1) Then 
        last_row_B = Cells(Rows.Count, "B").End(xlUp).Row
        Arr = Range("B" & 32 & ":B" & last_row_B)

        ListBox1.List() = Arr
    
    ElseIf Ubound(saveditems_lstbox1) = -1 Then    
        last_row_B = Cells(Rows.Count, "B").End(xlUp).Row
        Arr = Range("B" & 32 & ":B" & last_row_B)

        ListBox1.List() = Arr
    Else
        LoadSavedItems
    End If

edited May 14 '23 at 19:06

answered May 14 '23 at 16:57

mits

876
3
11
20

Thank you for your time, what exactly do you mean by a general module ? Should I create an empty module just to declare these two variants ? – Keyser Soze May 14 '23 at 17:07
2

Exactly. You need public variables to do the job. If you declare them in the userform they are unloaded too. – mits May 14 '23 at 17:11
I did so, and weirdly i still get the out of bound error at exactly the same place 'within LoadSavedItems function' – Keyser Soze May 14 '23 at 17:21
The 'SaveItems()' is called when I click on another button called 'Validate' so I guess no need to call it in UserForm_Terminate – Keyser Soze May 14 '23 at 17:29
Any more knowledge to share please ? – Keyser Soze May 14 '23 at 17:36
1

This is weird indeeed. Add a Stop statement before LoadSavedItems to stop code execution and at that point hit View/Locals Window to see what is exactly the state of the variable at that point. This might be helpful. – mits May 14 '23 at 18:27
This way you can see if there are any items in the array, how many, or what are they. – mits May 14 '23 at 18:40
1

If it is empty then something is wrong about the SaveItems. Did you hit the validate button before the Userform is closed? At least once. – mits May 14 '23 at 18:42
Please check the edit, itried a debugging method and it seems like my variants weren't initialized properly – Keyser Soze May 14 '23 at 18:44
Your variables are only initialised if you hit the Validate button at least once. Seems like something is wrong with the button. Either it is not calling SaveItems() right, or you are not hitting it. – mits May 14 '23 at 18:49
But i get the error when I open the userform ... I don't even have a chance to click validate – Keyser Soze May 14 '23 at 18:51
@Keyser Soze Did you delete/comment the (same) declarations from the user form code module? – FaneDuru May 14 '23 at 19:03
@KeyserSoze I edited the answer. Try this please. – mits May 14 '23 at 19:09

How to initialise an array properly without erasing values saved to it

1 Answers1