The Unload statement in VBA (Visual Basic for Applications) is used to remove a form from memory. When you use Unload, the form is closed and the resources allocated to it are released. This is particularly useful in managing memory and ensuring efficient operation of your VBA application, especially when working with multiple forms.

Here’s how to use the Unload statement:

Unload object

object: This is the name of the form that you want to unload.

Example

Suppose you have a form named UserForm1 and you want to close it with a button click event. You would use the Unload statement like this:

Private Sub CommandButton1_Click()
    Unload Me  ' If the button is on UserForm1
    ' or
    Unload UserForm1  ' Can be used from anywhere in the code
End Sub

Me refers to the current form where the code is running. In this case, if the code is in UserForm1, Unload Me will unload UserForm1.

Considerations

After unloading a form, you cannot reference its properties or controls until you reload it (usually with the Load statement or by showing it again).
If you try to unload a form that is not loaded, VBA will throw an error. You might want to check if a form is loaded before attempting to unload it.

Checking if a Form is Loaded

You can write a function to check if a form is loaded before unloading it. Here’s a simple way to do it:

Function IsFormLoaded(FormName As String) As Boolean
    Dim frm As Object
    For Each frm In VBA.UserForms
        If frm.Name = FormName Then
            IsFormLoaded = True
            Exit Function
        End If
    Next frm
    IsFormLoaded = False
End Function

Usage

If IsFormLoaded("UserForm1") Then
    Unload UserForm1
End If

This approach helps to manage forms effectively in your VBA projects, ensuring that you’re only interacting with forms when they’re available and conserving resources by unloading them when they’re no longer needed.

Follow us on Social Media

facebooklinkedin

Unlock Your Potential

Excel

Access

Power BI

Help us grow the project