Modules

When working in the Visual Basic Editor, each Excel workbook and add-in that’s open is shown as a project in the Project window. This will include the hidden Personal Macro Workbook which is named PERSONAL.XLSB if you have one.

Each project is shown as a collection of objects arranged as a folder tree. You can expand the tree by clicking the plus sign (+) to the left of an item in the Project window. When expanded, you can collapse the tree by clicking the minus sign (-) to the left of an item on the tree.

Your macro will be stored in the project object related to the workbook you selected when you recorded it.

A VBA module, in general, can hold three types of code:

• Declarations : You can declare variables in the module (usually at the top) that you want to be available for use in all procedures within the module. These are usually referred to as global variables.

• Sub procedure : A Sub procedure is a set of programming instructions that, when executed, performs a series of actions in Excel. All macros are saved as Sub procedures, but they can also be entered manually in the Visual Basic Editor.

• Function procedure : A function procedure is a set of programming instructions that performs a calculation and returns a single value (similar to a worksheet function in excel like SUM). Functions have to be coded manually.

A module can be used to store one type of code or a mix of all three. The type of code you use depends on the functionality you’re looking to create. You can also use different modules to store procedures for the same project, for example, as a way to organise them for easier maintenance. How you choose to arrange your code is down to personal preference.

How to Insert a New Module

To insert a new module, carry out the following steps:

1. Select the project’s name in the Project window.
2. On the Visual Basic Editor menu bar, click Insert and select Module from the menu (alternatively, you can right-click the project’s name and choose Insert > Module from the shortcut menu).

A new module will be added to the Modules folder under the selected Project.

There may be occasions when you want to remove a module, for example, if you created it by mistake or if it’s no longer needed.

To remove a module, follow these steps:

1. Select the module’s name in the Project window.
2. On the Visual Basic Editor menu bar, click File , and select Remove [module name] from the menu. Alternatively, you can right-click the module name and select Remove [module name] from the shortcut menu.
3. Excel will prompt you with a message asking if you want to export the code in the module before you delete it. Click the No button if you want to go ahead with the deletion.

Note : If you want to keep a copy of the code before removing the module, click the Yes button. Excel will display a dialog box, enabling you to save the code in a text file with a Basic File (.bas) extension.

To rename a module, do the following:

1. In the Project window, select the module you want to rename.
2. In the Properties window, in the Name field, enter a new name for the module (overwriting the old name) and press enter. The name must start with a letter and cannot have spaces. If you want to separate two words, you can use an underscore or capitalization.

Note : It’s not always necessary to rename modules from the default name given by the Visual Basic Editor. The name you give your procedure is what counts when it comes to executing a macro or a piece of code entered manually. However, if you have lots of modules in one project, you may want to name them as a way to organise your code, which would make it easier to maintain.