Chapter 3: Modules, Functions, and Subroutines

Modules are 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 on the spreadsheet, such as a new worksheet being added or a workbook being closed, 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 workbook or worksheet. You can call them in a number of ways:

• Use a custom menu command or a custom toolbar command. See Chapter 11 for more on customizing toolbars .

• Insert a VBA control from the Control toolbox into the spreadsheet 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.

• Choose Tools Macro Macros from the Excel menu, select the macro name from the list and click Run. However, for a professional application this is not recommended. A user will not want to have to select a macro from a list box where they have to know the macro name . Most users want things to happen in the most straightforward way possible, usually through clicking something once.

• Run the code from a UserForm. In Chapter 9, you will learn how to define your own forms, which can be used by the user 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 macro. 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 as you would a normal VBA keyword.

• Write your code as a function and call it directly by inserting the function into a cell, just as you would with the built-in functions in Excel. In the section ‚“Writing a Simple Function, ‚½ later in this chapter, you will learn how to write a simple spreadsheet function that can be used directly in a cell .

• 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.

All these methods are dealt with in further detail later on in the book.

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. There are no events on it, as there were on the workbook and worksheet 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 workbook where it resides. If it is public, it can be used by any other procedure in any other module in the workbook. If you have a subroutine that you do not want to be used elsewhere in the code, make the subroutine private. The default is always public.