Modules, Functions and Subroutines

Modules are containers where you write your code. Functions and subroutines are the two different ways of creating a piece of working code.

Modules

Modules are code sheets that are specific to your application. They are not fired off directly by events in the database, but have to be called directly. They are a means of creating procedures in a general manner, rather than specifically running in an object like a form or a report. You can call them in a number of ways:

• Use a custom ribbon command or a custom toolbar command.
• Insert a VBA control from the Control toolbox into the form or report directly and attach your code to this. For example, you might enter code for a user's actions on a command button or a combo box.
• Run the code from a form. Define your own forms, which the user can employ to make selections and take options. When the user clicks the OK button on the form, your macro runs and picks up the user preferences.
• Call your code from another VBA procedure. Code can form subroutines or functions that can then be used within other macros written on the same spreadsheet. For example, say you have to search a string of text for a particular character and you write a subroutine to do this, using a parameter to pass the text string to the subroutine. You can use this subroutine as a building block by calling it from anywhere else within other procedures in exactly the same way you would a normal VBA keyword.
• Click directly on your code and press F5. This is for development work only. For example, if you are working on a subroutine in isolation, you may wish to run it only to see how it works.

A VBA project normally uses at least one module to store the necessary functions and subroutines known as procedures. To insert a new module, simply select Insert | Module from the VBE menu, and the new module will appear. Note that this contains only a general area initially. It has no events, as with the form and report code sheets.

You can enter subroutines or functions here and make them public or private. The distinction between public and private is to decide whether other modules within the same workbook can access the procedure. If the code is private, it can only be used in the current module where it resides. If it is public, it can be used by any other procedure in any other module in the database. Should you have a subroutine you do not want used elsewhere in the code, make the subroutine private. The default is always public.