UserForms & Memory Management

[CaliKidd]

I'm a novice VBA programmer and I have a few questions about userforms and memory management.

I understand that Load and Unload put a userform into or out of memory and that Hide/Show conceals or reveals a form already in memory (and loads/unloads, if necessary).

My questions, however, are:
1. In a Userform_Terminate () sub, is it necessary to put an Unload Me command or is it unnecessarily redundant?
2. Imagine I have two userforms - 1 and 2. Userform1 is filled out and a NEXT command button is clicked which hides userform1 and shows userform2. Userform2 is filled out and a SAVE command button unloads userform2 when clicked using an Unload Me command. Will userform1 still be loaded into memory? I'm just wondering if I need to place both an Unload userform1 and an Unload userform2 into the SAVE command button's object code?
3. In a similar vein, imagine the user goes to userform2, but does not click the SAVE button, but decides to close the form instead by clicking on the 'X' in the upper right hand corner of the form window. Will userform1 still be loaded into memory?

 

[CaliKidd]

Any help?

I'm not clear on the persistence of userforms and what takes them out of memory (other than the unload command). Does clicking on the 'X' do the job even if a userform_terminate sub is not explicitly defined in the VBA code?

Also, does anyone know how much memory a userform takes up? All of the books I've read only mention how much memory is allocated for variable and constant types.

 

[Juan Pablo González]

When the X is clicked, it fires the QueryClose event, where you can Cancel the user action or do nothing... if nothing is done, then the Terminate event is fired, which ends up unloading the form from memory.

However, in your example, if Userform1 was only hidden, then it would still be in memory, because you only unloaded Userform2.

It is much better to declare instances of the userforms, because they are basically another class, that way, you can get finer control of when they are unloaded

Option Explicit

Sub Test()
    Dim f1 As UserForm1
    Dim f2 As UserForm2
    
    'Loads it into memory, still invisible
    Set f1 = New UserForm1
    Set f2 = New UserForm2
    
    'Show f1
    f1.Show vbModeless
    
    'Hide, still in memory
    f1.Hide
    
    f2.Show vbModeless
    
    'Unload f2
    Unload f2
    
    'Only f1 is in memory at this point, so unload it
    Unload f1
    
    'Note that at this point, both f1 and f2 are unloaded, but are NOT nothing
    If Not f1 Is Nothing Then
        MsgBox "Still valid reference, but can't work with it"
        
        'This line will error, causes automation error
        f1.Show vbModeless
    End If
    
    'Kill references
    Set f1 = Nothing
    Set f2 = Nothing
End Sub

If you don't explicitly unload f1 and f2 in this sub, they would still be held in memory, which you can check by putting this in the immediate pane

?Userforms.Count

 

[CaliKidd]

Thank you, Juan. Your answer was clear and makes a lot of sense. I did not realize that when a form was unloaded, that it was NOT set to Nothing. I assumed it was, but now that I know this, I will use your approach so unneeded things don't linger in memory.