VBA Load Statement

The Load statement is a lesser known but extremely useful VBA statement that can greatly improve both these aspects of your code. In this post, we will dive into the purpose, syntax, examples, and important notes and remarks of the Load statement in order to fully understand its power and potential. So let’s get started!

VBA Load Statement

Purpose of the Load Statement

The Load statement is primarily used to initialize an object or user form and make it available for use in your code. It can be used to create and load new instances of a class or to reload an existing one that has been previously unloaded. This is especially useful when dealing with objects or user forms that are heavy in resources and cannot be loaded until needed, thus saving memory and improving performance.

Syntax of the Load Statement

The syntax for the Load statement is as follows:

    Load object

Here, “object” can represent any valid object in your VBA project, such as a form, control, or class. It is important to note that the object must first be declared and instantiated before being used in the Load statement. Additionally, the Load statement can only be used on an object that has been previously unloaded using the ‘Unload’ statement.

Examples of the VBA Load Statement

Now let’s take a look at some practical examples of the Load statement in action.

Example 1: Loading a User Form

In this example, we have a user form called “frmDataEntry” that we want to load and display to the user when a specific button is clicked. The code for this would be as follows:

Private Sub cmdLoadForm_Click()
    Load frmDataEntry 'load the form
    frmDataEntry.Show 'display the form to the user
End Sub

Example 2: Loading a Class

In this example, we have a class called “Employee” that we want to create and initialize in our VBA project. The code for this would be as follows:

Private Sub cmdLoadClass_Click()
    Dim emp As Employee 'declare the class object
    Set emp = New Employee 'instantiate the class
    Load emp 'load the class
End Sub

Example 3: Reloading a User Form

In this example, we have a user form called “frmDataEntry” that has been previously unloaded and we want to reload it when a specific button is clicked. The code for this would be as follows:

Private Sub cmdReloadForm_Click()
    Load frmDataEntry 'reload the form
    frmDataEntry.Show 'display the form to the user
End Sub

Example 4: Loading Multiple Instances of a Class

In this example, we have a class called “Employee” and we want to create and initialize multiple instances of it in our VBA project. The code for this would be as follows:

Private Sub cmdLoadMultiClass_Click()
    Dim emp(5) As Employee 'declare an array of class objects 
    Dim i As Integer
    For i = 0 To 4
        Set emp(i) = New Employee 'instantiate each class object
        Load emp(i) 'load each class object
    Next i
End Sub

Example 5: Loading a Control on a User Form

In this example, we have a user form called “frmDataEntry” with a control called “txtName” that we want to load and make visible when a specific button is clicked. The code for this would be as follows:

Private Sub cmdLoadControl_Click()
    Load frmDataEntry 'load the form
    frmDataEntry.txtName.Visible = True 'make the control visible
    frmDataEntry.Show 'display the form to the user
End Sub

Important Notes and Remarks

• The Load statement can only be used on an object that has been previously unloaded using the ‘Unload’ statement.
• Using the Load statement on an already loaded object will result in an error.
• The Load statement can also be used with the ‘Me’ keyword to refer to the current form or class module.
• When using the Load statement with a user form, it is important to also include the ‘Show’ or ‘ShowModal’ statement to display the form to the user.

In conclusion, the Load statement is a powerful and versatile tool in the VBA programmer’s arsenal. It not only improves the efficiency of your code but also enhances the user experience by loading and unloading resources as needed. By understanding its purpose, syntax, and practical examples, you can greatly improve your VBA coding skills and take your applications to the next level.
I hope you found this post informative and useful. Have you used the Load statement in your VBA projects? How has it helped you streamline your code and enhance the user experience?

I would love to hear your feedback and views in the comments below. Happy coding!