Save information in a dictionary when using a button from userform multiple times-CodePudding

I want to create a dictionary where I save pipe diameters and its corresponding lenght using the following user form:

Every time I click "Add pipe" button, the information goes to the listbox and to a dictionary, with the code below.

The problem is that the dictionary seems to be emptied every time the button is pressed and does not save every diameter and length added. Is there any way to solve it?

Public myDic As Dictionary
Dim diam As Single, myLen As Single
Dim a As Boolean

Private Sub button_add_Click()

    Set myDic = New Dictionary 'crea un nuevo objeto de diccionario
    myDic.CompareMode = vbTextCompare
    
    a = False
    diam = f_diam.Value 'Data from user form
    myLen = f_len.Value 'Data from user form
    comparador = "DN" & diam & "*"
    tramo = "DN" & diam & " - " & myLen & " m"
    
    'Check if diameter is already in the list box
    For i = 0 To ListBox.ListCount - 1
        If ListBox.List(i) Like comparador Then
            a = True
            pregunta = MsgBox("DN" & diam & " already exists." & vbCrLf & "Do you want to update it with the new length?", vbYesNo   vbExclamation, "MOS-Database - Overwrite?")
            If pregunta = vbYes Then
                ListBox.List(i) = tramo 'Modify list box
                myDic.Item(diam) = myLen 'Modify dictionary
               Exit For
            Else
                Exit Sub
            End If
        End If
    Next i
    
    'If not in the list, add to list and dictionary
    If a = False Then
        ListBox.AddItem (tramo)
        myDic.Add Key:=diam, Item:=myLen
    End If

End Sub



Private Sub check_button_Click()

    For Each Key In myDic.Keys
        Sheets("Prueba").Cells(i   1, 1) = Key
        Sheets("Prueba").Cells(i   1, 2) = myDic(Key)
    Next Key

End sub

CodePudding user response：

As Brian shows in the comments, your problem is that you create a new dictionary in the moment you click the button. The old dictionary is thrown away and it's information is lost.

Easiest ways to solve this:

(1) Declare and create the dictionary object at the top of your code:

Public myDic As New Dictionary

In this case, remove the first statement (Set myDic = New Dictionary) from the button-click routine

-- or --

(2) Just create the dictionary object if needed:

Private Sub button_add_Click()

    If myDic is Nothing Then
        Set myDic = New Dictionary 'crea un nuevo objeto de diccionario
    End If
    (...)