Writing Functions and Subroutines

Problem

You want to write VBA code to perform some task or calculation but you're not sure where to begin.

Solution

Open the VBA IDE, create a code module for your workbook, and then start writing your custom procedures (functions and subroutines).

Discussion

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.

You can actually write a subroutine and call it main, using it as a starting point for subsequent code and calls to other procedures.

 

Subroutines

VBA subroutines have the basic form shown in Example 2-1.

Example 2-1. VBA Subroutine

Public Sub MySubroutineName(Param1 As Integer, Param2 As Double)
 ' Your code goes here...
End Sub

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:

• The name must start with a letter.
• Names can't include the characters @, &, $, #, or !. They can't include spaces or periods either.
• Names can be up to 255 characters in length.
• You should avoid names that are the same as built-in VBA functions and keywords.
• You can't reuse names in a single code module. For example, you can't give two different subroutines the same name if they reside in the same code file.

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 .

By default, arguments passed into procedures as parameters are passed by reference. This means the actual value of the argument is not passed to the function; rather, a pointer to the argument is passed. A pointer is a data type that stores the memory address of the object being pointed to. This gives the procedure direct access to the argument, which means the procedure can change the value of the argument. This may be what you want, but you need to be careful not to inadvertently change the value of an argument. To pass an argument by value, use the ByVal keyword in the parameter list. For example, to force Param1 to be passed by value in Example 2-1, you would write ByVal Param1 As Integer. Doing so ensures the argument passed in as Param1 will be preserved irrespective of how its value is used in the procedure. The drawback of passing parameters by value is performance degradation, since the actual value has to be copied in memory when the procedure is called.

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

MySubroutineName 4, 2.87
Call MySubroutineName(4, 2.87)

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.

Functions

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

Public Function Calc_q(y1 As Double, y3 As Double) As Double
 Calc_q = 1 / ((Abs(y3 - y1)) ^ 0.74)
End Function

.
.
.

 x = 1.24 * Calc_q(3.75, 2.87) + y1
.
.
.

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.