Today, we will learn about modules in VBA. We are going to know about different types of modules and when to use them. We would learn to insert and remove a module from the VBA Project.
So let us begin.
What is a Module in VBA?
Developers use VBA module to put the piece of code written in VBA language. VBA is Microsoft’s Programming language used to automate office tasks including Excel, Word, and Access.
We put the code written in VBA in the module inside the Visual Basic Editor (VBE). When we save an excel file, the modules are saved along with the excel file. The scope of a Module is the workbook.
Modules have .bas extension (Basic).
Types of Modules
There are three different types of Modules available in MS Excel.
- Standard Module – When we record a macro, the code is written in the standard module. A standard module can also contain the Sub Procedures and Functions that we want to use in our workbook.
- Object Module – Most of the time, it contains the Event codes that are executed when an event is triggered. The Events are associated with objects. For Example, the code is executed when a Command Button is clicked. This would be the click event for the object command button. Userforms are also implemented using Object Modules.
- Class Module – With the help of the class module, we can write code to make a class and then make an object ( an instance of a class ) with the class that has its methods and properties. In short, a class module enables us to make custom objects.
Inserting Different Types of Modules
We are now going to insert different types of modules in our Workbook’s VBA project.
Standard Module
Standard Module is automatically created when we record a macro. However, we can insert a standard module on our own to create Sub Procedures and Functions that we can later on use in workbook. Follow these steps to insert a Standard Module.
- Press Alt and F11 keys to open the VBE.
- Select the VBA project from the Project Explorer at the left.
- Go to the Insert tab on the ribbon and click on Module. This would insert a standard module in the selected VBA project.
Object Module
This includes the special module tied with the workbook object and to each sheet of the workbook.
Class Module
Follow these steps to insert a Class Module in Excel.
- Select the VBA project to which you want to add a Class Module.
- Go to the Insert tab on the ribbon.
- Choose Class Module.
Removing an Existing Module
You can learn to remove an existing VBA module.
- Go to the Project Explorer in VBE.
- Right Click on the Module that you wish to remove. Click on the Option Remove Module.
- A dialog box would appear asking if you want to export the module before deleting it.
- Click on Yes and then save the module with a name and .bas file extension. This will export the module form you VBA project and save the module file into your computer locally.
- You can click on No if you no longer need the module in future also. This is going to permanently delete the module.
This brings us to an end.
Thank you for reading.