Return to VBA Code Examples

What is a VBA Module?

What is a VBA Module?

A VBA module is used to store any VBA code that you have written in the VBE (Visual Basic Editor).

vba what is a module 1

The modules are contained within a VBA Project and when the file is saved – be it an Excel workbook, Word document or Access database, the module or modules are saved within that file – that file is essentially the parent application of the module.

vba what is a module 2

Modules can also be exported out of the parent file and saved as their own individual files.  This is useful when you want to re-use code in a different file, and therefore perhaps import that module into a new file.

Type of modules

The modules are organised into 3 different types.

  1. Standard modules – most of your code will go into this type of module. When you record a macro, it gets put into a standard module.    When you write a general procedure to be used throughout your workbook, it also normally goes into a standard module.
  2. Object modules – these type of modules hold the code the is unique to that individual workbook or worksheet. Most of the code in these type of modules are known as EVENTS.   An event can occur when a workbook is opened or closed for example, or when a sheet is clicked (the Click Event),. The module can also contain code that is written by yourself and used by the events.  The module behind a custom form that you create is also an Object module.
  3. Class modules – this module is used to create objects at run time. Class module are used by Advanced VBA programmers and will be covered at a later stage.

When your write your VBA code, you will usually use more than one module.  It is good coding practice to ‘group’ your code into relevant modules – for example put all the global variables in one module, all public functions in another module etc.

Inserting a module or form into your code

To insert a new module into your code, click on the Insert option on the menu bar, and click Module.

VBA 18 PIC 01

Or, click on the Insert Module button which you will find on the standard ribbon.

VBA 18 PIC 02

To insert a new user form into your code, select the UserForm option.

VBA 18 PIC 03

A new UserForm will appear in the Project Explorer and will be shown in the Code Window on the right.

VBA 18 PIC 04

You can also insert a Class Module

VBA 18 PIC 05

A class module is used to insert objects into your VBA project.

VBA 18 PIC 06

Entering Code into a VBA Module

Once you have created your module, you will want to start typing your procedure. Most procedures are Sub Procedures.

A sub procedure begins with a Sub statement and ends with an End Sub statement.  The procedure name is always followed by parentheses.

Sub gridlines()
ActiveWindow.DisplayGridlines  = False
End Sub

When entering a sub procedure, you only need to type “Sub” followed by the procedure name (no spaces). Then press enter and the VBA Editor will add the parenthesis and End Sub statement.

enter procedure into code module

Creating a new procedure – Alternative Method

The easiest way to create a procedure is by typing directly into the Module Window, but if you forget how to create a procedure, you can go to Insert > Procedure instead:

VBA 18 PIC 07

The following dialog box will appear

VBA 18 PIC 08

This dialog box is a good way to learn about the options for Procedures.

  1. Type the name of your new procedure in the name box – this must start with a letter of the alphabet and can contain letters and number and be a maximum of 64 characters.
  2. You can have a Sub procedure, a Function procedure or a Property procedure. (Properties are used in Class modules and set properties for ActiveX controls that you may have created).
  3. You can make the scope of the procedure either Public or Private. If the procedure is public (default), then it can be used by all the modules in the project while if the procedure is private, it will only be able to be used by this module.
  4. You can declare local variables in this procedure as Statics (this is to do with the Scope of the variable and makes a local procedure level variable public to the entire module). We will not use this option.

When you have filled in all the relevant details, click on OK.

VBA 18 PIC 09

You then type your code between the Sub and End Sub statements.

Sub vs Function Procedures

You may have noticed that there are 2 types of procedures you can create – a SUB PROCEDURE or a FUNCTION PROCEDURE.

If you have recorded a macro in Excel or Word, your macro will be put into a Standard module and will be put into a sub procedure. The macro recorder can only record sub procedures.  A Sub procedure does things.  They perform actions such as formatting a table, creating a pivot table, or  changing the view settings of your active window.  The majority of procedures written are Sub procedures.  All macros are Sub procedures.

A Function procedure returns a value.  This value may be a single value, an array, a range of cells or an object.  Functions usually perform some type of calculation.   Functions in Excel (UDFs) can be used with the Function Wizard.

Removing a module or form from the Project Explorer

Right-click on the module or form you wish to remove to show the right click short cut menu.

VBA 18 PIC 10

Click Remove (in this case UserForm1…)

OR

Click on the File menu, and then click on Remove (UserForm1).

VBA 18 PIC 11

A warning box will appear asking if you want to Export the form or module before you remove it.  Exporting the form or module enables you to save it as an individual file for use in a different Excel project at some other time.

VBA 18 PIC 12

More often than not when you remove a module or form it is because you do not need it, so click No.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! automacro

Learn More!!

Exporting a module or form from the Project Explorer

Right-click on the module or form you wish to export to show the right click short cut menu.

VBA 18 PIC 13

OR

Select the module/form you wish to Export, and click on the File menu.  Then select Export File.

VBA 18 PIC 14

Select the location you wish to save the module to, and then click Save.

VBA 18 PIC 15

NOTE: when you Export a form or a module, it saves it as an individual file on your PC, but it DOES NOT remove it from your VBA Project.

Importing a VBA module or form into your VBA Project

Right click on your VBA Project and then click Import File.

VBA 18 PIC 16

Select the file you wish to import, and click Open.

VBA 18 PIC 17

The module or form you have imported will now appear in your Project Explorer.

VBA 18 PIC 18