Recipe 2.2. Writing Functions and Subroutines
You want to write VBA code to perform some task or calculation but you're not sure where to begin.
Open the VBA IDE, create a code module for your workbook, and then start writing your custom procedures (functions and subroutines).
When working with Excel and VBA, you write custom code in functions and subroutines. Unlike in traditional application programming, we're not going to write a main program from which we manage the application loop, making calls to other functions and subroutines, and so on. We will, however, make heavy use of custom VBA procedures and call them from other VBA procedures. Moreover, we're going to call our VBA procedures from within Excel itself. Therefore, we're sort of attaching our custom code to Excel's main program and invoking our code within cell formulas or in response to certain events (for example, when a user presses a button). Essentially, the VBA procedures you write are extensions of Excel. This extensibility is what makes Excel, in my opinion, such a powerful computation tool.
VBA subroutines have the basic form shown in Example 2-1.
Example 2-1. VBA Subroutine
Subroutines start with a scope qualifier, Public or Private, followed by the Sub keyword, followed by the subroutine name, which is then followed by an optional parameter list. After the declaration comes the body of the subroutine, which is a collection of code statements that you supply. A subroutine definition is closed with End Sub. Let's consider each part of the declaration in more detail.
The scope qualifier specifies whether the subroutine has global or local scope. If you specify Public, then the subroutine has global scope and can be called from any code module. On the other hand, if you specify Private, then the subroutine has local scope and can only be called from the code module within which it is defined. If you don't specify anything, VBA assumes Public.
The Sub keyword is mandatory and lets VBA know you are indeed declaring a subroutine procedure. Note that VBA will show keywords in a different color from that used for your code statements. I have my version set to display keywords in blue and code statements in black. This is the default, but you can change this if you'd like. Select Tools images/U2192.jpg border=0> Options... from the main menu bar and select the Editor Format tab to customize the editor.
After the Sub keyword, you must supply a subroutine name. I recommend you use descriptive names wherever possible; it doesn't matter if the names are long. Gone are the programming days of trying to compress names down to a meager eight characters! Just keep the following naming rules in mind when making up subroutine names:
By the way, these naming rules apply to all names in VBA, including function names, variable names, constants, and parameters . I should also point out that VBA is not case sensitive. It is, however, considered good style to use both upper- and lowercase characters to make your code more readable. For example, MySubroutineName is more readable than mysubroutinename. To make things even clearer, you could use the underscore character (e.g., My_Subroutine_Name).
Parameters are optional for subroutines. If you don't include parameters you still have to include the open and closing parenthesis (i.e.,( and )). Parameters may be passed into the subroutine and used in your code. A parameter list must include the parameter name followed by a type specifier in the form ParamName As DataType. You must separate the parameter name from the data type specifier using the As keyword. Separate multiple parameters in the list with commas as illustrated in Example 2-1. Data types are types such as Integer, Double, Boolean, and Byte. See Recipe 2.3 for more information on data types .
After you've defined a subroutine, you can call it from other places in your code simply by writing the name of the subroutine followed by any parameters. Alternatively, you may use the Call keyword. Both approaches are illustrated in Example 2-2.
Example 2-2. Calling subroutines
When using the Call approach, you must enclose the parameter list in parentheses. In many cases you won't actually call subroutines directly in code. Instead, you'll assign them to buttons or other controls included in a spreadsheet (see Recipe 9.3). Note that you cannot call subroutines from cell formulas. You can do so with functions , as discussed in a moment.
To actually add a new subroutine to a code module, you can either use the Add Procedure dialog box or simply start typing the subroutine in a code window. Personally, I find it more efficient to just type the subroutine directly. The Add Procedure dialog box, shown in Figure 2-3, allows you to specify what type of procedure you want to add, along with a few options.
Figure 2-3. Add Procedure dialog box
To access the Add Procedure dialog box, select Insert images/U2192.jpg border=0> Procedure... from the main menu bar.
VBA functions are similar to subroutines except for two key differences. First, functions are declared using the Function keyword instead of Sub. Second, functions actually return a value and can therefore be used within expressions. Example 2-3 shows an example function declaration, along with how the function may be called as part of an expression.
Example 2-3. Example VBA function
In this example, the function is named Calc_q and it takes two Double parameters. Since this is a function that returns a value, you need to specify the data type of the value returned by the function. Thus, you have to include the As DataType qualifier at the end of the function declaration. In this example, the return type is a Double. The example statement setting the value for the variable x shows how the Calc_q function may be included in an arithmetic expression. Moreover, you can include function calls in cell formulas, whereas you can't with subroutines.
You can, of course, include other statements in the body of a function, in addition to the single statement illustrated in Example 2-3. The important thing to remember is that the function exits, returning a value, on a code line where the function name is set to some value.
The naming rules that apply to subroutines also apply to functions.
Adding a function is similar to adding a subroutine. You can either type it in directly as I prefer to do, or you can use the Add Procedure dialog.
Recipe2.2.Writing Functions and Subroutines