A code module can contain one or more procedures where a procedure is a subroutine or function.
Provides a way of organising your code
It is good practice to create several small subroutines or functions rather than one extremely long one.
To add a new module to a project, select the project name and select (Insert > Module).
You can also right click the project and choose (Insert > Module) from the shortcut menu.
You will also see the module appear in the project explorer window
If you need to remove a module select the module in the Project Explorer window and select (File > Remove ???)
where ??? Is the name of the module.
Alternatively you can also use the shortcut menu and select Remove ???/
You will be asked if you want to export the project before removing it.
More more details about importing and exporting modules, please refer to the Importing & Exporting page.
If you want to delete all the macros from a workbook then it is much easier to do this using the Visual Basic Editor.
This way you can use the shortcut menu to quickly remove a code module from the project.
Select the code module you want to delete and press the right mouse button and select "Remove Module".
You will be asked if you want to export the module first. Select "No".
It is not sufficient just to delete the VBA code from the code modules.
You must also remove any empty code modules from your workbook.
The Enable/Disable Macros dialog box will be displayed if there are any code modules in the workbook.
Workbook and Worksheet -
Userform Modules -
Class Module -
There are three different types of code module, standard, class and userform.
Code can be added to Events procedures which can be found within workbook, documents etc.
Option Private Module
When used in host applications that allow references across multiple projects, Option Private Module prevents a module's contents from being referenced outside its project.
Option Private Module
If used, the Option Private statement must appear at module level, before any procedures.
When a module contains Option Private Module, the public parts, for example, variables, objects, and user-defined types declared at module level, are still available within the project containing the module, but they are not available to other applications or projects.
Option Private is only useful for host applications that support simultaneous loading of multiple projects and permit references between the loaded projects.
You can easily drag and drop modules between projects.
After making significant code changes it is always worth exporting your modules, deleting them from the project and then importing them again.
You cannot have two procedures or functions with the same name in the same module although you can in different modules.
When you record a macro a new standard module will be inserted automatically for you.
© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited