Why VBA global variables loses values when closing UserForm?

Question

I have a macro code behind Worksheet. When button is clicked on the sheet, new user form is initialised and showed to user. If user closes the windows with red X, or form is closed with "hide" function/method, all global variables that are behind Worksheet loses their values. Is it possible to preserve this values?

Worksheet code behind:

Private MeasurementCollection As Collection
Dim CurrentMeasurement As measurement
Dim NewMeasurement As measurement

Private Sub Worksheet_Activate()
    Initialize
End Sub

Public Sub Initialize()
    Set NewMeasurement = New measurement
    Dim DropDownDataQueries As Collection
    Set DropDownDataQueries = DBQueries.GetAllUpdateQueries
    For i = 1 To DropDownDataQueries.Count
        Dim Values As Collection
        Set Values = DataBase.GetData(DropDownDataQueries(i))

        With Me.OLEObjects("Combo" & i).Object
            For Each value In Values
                .AddItem value
            Next value
        End With
    Next i
End Sub

Private Sub UpdateDB_Click()
    UpdateGeneralData
    If (CurrentMeasurement Is Nothing) Then
        MsgBox ("Message text")
    Else
        Dim form As UpdateComentForm
        Set form = New UpdateComentForm
        form.Show
    End If
End Sub

Private Sub Combo1_Change()
    If Application.EnableEvents = True Then
        If (Combo1.value <> "") Then
            NewMeasurement.DN = Combo1.value
        Else
            NewMeasurement.DN = 0
        End If
    End If
End Sub

UserForm code

Private Sub UpdateDBData_Click()
    If (Komentar.value <> "") Then
        Me.Hide
    Else
        MsgBox ("Prosimo napišite vzrok za spremembe podatkov v belo polje!")
    End If
End Sub

Private Sub UserForm_Terminate()
    Me.Hide
End Sub

Can you provide the code that you have so far and what you have tried? — Daniella, Jul 28 '14 at 21:34
No I do not call End statement anywhere. I tried to hide user form inside Terminate event, but still the same issue... — Gašper Sladič, Jul 28 '14 at 21:40
NewMeasurement variable is reset. It is the object of class module. I get an error, when I want to assign a value to variable that is inside this object, after UserForm is closed. Error states: Object variable or with block variable not set. — Gašper Sladič, Jul 28 '14 at 21:43
On what line do you get the error? (I guess has nothing to do with the global variable being reset) — stenci, Jul 28 '14 at 21:44
I get the error inside the Combo1_Change event, which is event of ComboBox. Inside the event I want to assign the combo1.value to NewMeasurement.DN variable which is of type integer (I updated the code above). — Gašper Sladič, Jul 28 '14 at 21:47
are your variables declared in a module or the form? I do not see where you declare your global vars, you need to show more code. — Sorceri, Jul 28 '14 at 21:47
@Sorceri There are three global variables declared in the first three lines of the code shown. — GSerg, Jul 28 '14 at 21:48
is it in a module, not a sheet module but a user created module??? — Sorceri, Jul 28 '14 at 21:52
read this - http://stackoverflow.com/questions/3815547/what-is-the-difference-between-dim-global-public-and-private-as-modular-field — Sorceri, Jul 28 '14 at 21:55

score 7 · Accepted Answer · answered Jul 28 '14 at 21:46

7

Experiments show that the module-level variables are cleared upon exiting a procedure that involves calling = New Form, provided that the form designer window is opened somewhere in the IDE.

Close all user forms designer windows you might have open in the VBA IDE and try again.

answered Jul 28 '14 at 21:46

GSerg

76,472
17
159
346

Huh, this solved the problem. Thank you very much for your help. Could not figure this out on my own. – Gašper Sladič Jul 28 '14 at 22:02
This did not solved it for me. Seems to be a faulty behavior if it were the case. – Adam L. S. Jan 06 '23 at 09:42

score -1 · Answer 2 · answered Jul 28 '14 at 21:49

-1

NewMeasurement as been declared but never assigned.

You could do something like Dim NewMeasurement As New measurement to create an instance of the object.

answered Jul 28 '14 at 21:49

stenci

8,290
14
64
104

it is assigned inside the Initialize subroutine, which is called from inside the Worksheet_Activate event. Sorry, I have a lot of code behind so I did not post everything, because it would not point out the main problem. Added the missing code in the original post! – Gašper Sladič Jul 28 '14 at 21:52
`Initialize` is called from `Worksheet_Activate`. If you edit a form after the `Worksheet_Activate` has been executed, then the global variables are lost. Are you sure to execute `Initialize` before opening the form, after any editing? – stenci Jul 28 '14 at 21:54
Everything works fine. After form is activated, I execute database query and fill the values inside the CurrentMeasurement variable and to the Sheet. Then if one of the cells are changed, I put the new value inside the NewMeasurement (history tracking). Then I push the UpdateDB button, that fires the event and opens the UserForm. When user form is closed, NewMeausrement and CurrentMeasurement variables are lost. – Gašper Sladič Jul 28 '14 at 22:00
Can you check if `NewMeausrement` is defined as soon as you start the `UpdateDB`, before opening the form? I have the feeling that they are lost before the form is opened. – stenci Jul 28 '14 at 22:02

Why VBA global variables loses values when closing UserForm?

2 Answers2