0

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 Sub SaveItems()

    
    Dim i As Long
   
    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
    If Not UBound(saveditems_lstbox1) - LBound(saveditems_lstbox1) + 1 = 0 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
    If Not UBound(saveditems_lstbox2) - LBound(saveditems_lstbox2) + 1 = 0 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()

    'MsgBox "Bound 1:  " & UBound(saveditems_lstbox1) # I get outbound error in this msgbox
    
    If Not UBound(saveditems_lstbox2) - LBound(saveditems_lstbox2) + 1 = 0 Then
        LoadSavedItems
    Else
        
        Dim Arr
        Dim last_row_B As Long
        
        last_row_B = Cells(Rows.Count, "B").End(xlUp).Row
        Arr = Range("B" & 32 & ":B" & last_row_B)
        ListBox1.List() = Arr
    End If

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: After trying this code below, it said that arrays weren't initialized

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 !

Keyser Soze
  • 262
  • 3
  • 11
  • Because `IsEmpty` returns False for an array that hasn't been dimensioned, and `LBound`/`UBound` throw an exception for an array that hasn't been dimensioned. – GSerg May 14 '23 at 16:43
  • Where do you declare saveditems_lstbox1() saveditems_lstbox2()? If you declare them in the Userform's code module then every time the Userform is unloaded their values are lost. You should declare them in a general module. Another solution is to store the listbox's values in certain cells in a worksheet that are out of sight eg. AA1. – mits May 14 '23 at 16:55
  • Thanks for your time, what do you mean by a general module ? I should create a new module just to declare the two arrays ? – Keyser Soze May 14 '23 at 16:59
  • @KeyserSoze That depends on whether the arrays must survive the destruction and recreation of your form (which is not the same as showing and hiding). Irrespective to that, you cannot test the array for being empty the way you do it, which is the source of your problem. – GSerg May 14 '23 at 17:10
  • @GSerg I want the arrays to survive destruction/Recreation if possible. I declared the variants in a general module as public and still I'm getting out of bound error ... I tried replacing 'IsEmpty()' with 'Ubound() - LBound() + 1 = 0' and same result – Keyser Soze May 14 '23 at 17:35
  • Have you read [my comment](https://stackoverflow.com/questions/76248596/save-load-listbox-items-of-a-userform-in-vba-out-of-bound-error?noredirect=1#comment134461335_76248596) and consulted the link at the top? – GSerg May 14 '23 at 17:37
  • @GSerg I did man ... But i'm afraid my knowledge is limited I can't get a grasp on it – Keyser Soze May 14 '23 at 17:40
  • @GSerg The link shows you how to check if an array is intialized, I already did so in a general module. What else am i to do ? – Keyser Soze May 14 '23 at 17:41
  • 1
    That's all. Use that check instead of your `IsEmpty`. – GSerg May 14 '23 at 17:58
  • @GSerg I replaced the check, and I'm still out of bounds at the same line ... – Keyser Soze May 14 '23 at 17:59
  • Should I initalize my two variants or just declare them in the general module ? – Keyser Soze May 14 '23 at 18:00
  • 1
    Please show your current code. – GSerg May 14 '23 at 18:01
  • @GSerg Please check the edited code, note that public declaration of the two variants was done in another module – Keyser Soze May 14 '23 at 18:06
  • I think I'm lacking a 'SavedItems_lstbox1 = Array() ' & 'SavedItems_lstbox2 = Array()' somewhere. Do I need to initialize them ? – Keyser Soze May 14 '23 at 18:12
  • I don't see you using any array checking methods from the suggested duplicate. The `UBound(saveditems_lstbox1) - LBound(saveditems_lstbox1) + 1` will of course throw the same exception. You wouldn't need a check in the first place if it didn't. – GSerg May 14 '23 at 18:19
  • @GSerg please check edit in post, i tried an array checking method and it said the public variants weren't initialized ! – Keyser Soze May 14 '23 at 18:41
  • 1
    What surprises you? They aren't indeed, but isn't it the same condition that you wanted to originally detect with `If Not IsEmpty(saveditems_lstbox1)`? What stops from replacing `If Not IsEmpty(saveditems_lstbox1)` with `If IsArrayInitialized(saveditems_lstbox1)`? – GSerg May 14 '23 at 18:44

0 Answers0