Chapter 5: Creating Sub and Function Procedures

---

The first several chapters of this book, but particularly Chapter 4, used sample procedures to illustrate how you can use Visual Basic for Applications (VBA) to make Microsoft Excel do your bidding. The goal in Chapter 4 was to introduce the programming constructs you can use to create and control how your VBA code operates, whether that means repeating a bit of code a set number of times using a For…Next loop, operating on every member of a collection using a For Each…Next loop, or repeating the code until a condition is met. There was a lot of hand waving at that stage when it came to putting your code into a usable package, but now it’s time to get down to specifics.

In this chapter, you’ll learn how to add Sub and Function procedures to your code modules, determine whether the procedures will be available from other workbooks, control whether Excel remembers the values generated by a procedure until the Excel program is closed, and learn how to transfer values to and from procedures so you can use the results elsewhere in your programs.

Defining Sub Procedures

There are two types of procedures you can create in Excel VBA: a Sub procedure and a Function procedure. What’s the difference between the two types of procedures? It’s whether or not the procedure returns a value you can use elsewhere in the program. For example, a Sub procedure set up to check whether a given purchase would put a customer over their credit limit might look like the following:

Private Sub CheckCreditLimit()
    If ActiveCell.Value + Range ("C3").Value > Range ("C2").Value Then
        MsgBox("The purchase would exceed the customer's credit limit.")
    End If
End Sub 

When you read through the procedure, you’ll notice that there is no statement that sends any value to any outside procedure. As mentioned in Chapter 4, the message box is used only to send some output to the screen; no value is passed to any other procedures.

For more information on passing values between procedures, see “Passing Arguments to Procedures” later in this chapter.

With the overview out of the way, it’s time to offer a more formal description of what constitutes a Sub procedure.

[Private | Public] [Static] Sub name [(arglist)] 
[statements]
[Exit Sub]
[statements]
 End Sub 

Table 5-1 describes the elements of a Sub procedure.

Table 5-Elements: of a Sub Procedure:

Element	Description
Public	An optional element that indicates the Sub procedure is accessible to all other procedures in all modules. If used in a module that contains an Option Private statement, the procedure is not available outside the project.
Private	An optional element that indicates the Sub procedure is accessible only to other procedures in the module where it is declared.
Static	An optional element that indicates the Sub procedure’s local variables are preserved between calls. The Static attribute doesn’t affect variables that are declared outside the Sub procedure, even if they are used in the procedure.
name	A required element that indicates the name of the Sub (for example, Sub AvailableCredit). The name does need to follow the standard variable naming conventions.
arglist	An optional list of variables representing arguments that are passed to the Sub procedure when it is called. Multiple variables are separated by commas.
statements	An optional group of statements to be executed within the Sub procedure.

To add a procedure to a code module, follow these steps:

1. Click Tools, Macro, Visual Basic Editor to display the Visual Basic Editor.

2. If necessary, click Insert, Module to create a new code module (or if you want to create a new module for organizational purposes).

   

3. Click Insert, Procedure to display the Add Procedure dialog box.

4. Type the name of your procedure in the Name box.

5. Select the Sub option button.

6. Click OK.

   Note 

   There are other options available to you in the Add Procedure dialog box—you’ll learn about those possibilities a little later in this chapter.

After you finish the preceding procedure, the outline of a procedure appears in the active code module, as shown in Figure 5-1.

Figure 5-1: As soon as you click OK in the Add Procedure dialog box, the skeleton of your new procedure appears in the active code module.

You can then fill in the details of your procedure using the Visual Basic Editor to pick objects, built-in functions, properties, events, and so on using the Object Browser. The following code listing contains a procedure that checks the contents of the active cell and, when the value matches any of the tests in the If…Then statement, changes the cell’s font color to the named color.

Sub AvailableCredit()
    With ActiveCell
        If .Value = "" Then Exit Sub
        If .Value <= 1000 Then .Font.Color = vbRed
        If .Value > 1000 Then .Font.Color = vbBlack
        If .Value > 4999 Then .Font.Color = vbBlue
        If .Value > 9999 Then .Font.Color = vbGreen
    End With
End Sub

The colors listed in the preceding code are represented by VBA constants, but there are many millions of specific colors available to you. For more information on using colors to format the contents of items in your workbook, see Chapter 10, “Formatting, Excel Objects.”

It’s interesting to notice that the seemingly equivalent procedure that follows, which uses a Select Case statement to test the values in the active cell, actually generates an incorrect result.

Sub AvailableCreditCase()
Remaining = ActiveCell.Value
Select Case Remaining
Case ""
            Exit Sub
    Case Is >= 10000
            ActiveCell.Font.Color = vbGreen
    Case Is <= 9999
    ActiveCell.Font.Color = vbBlue
Case Is <= 4999
ActiveCell.Font.Color = vbBlack
Case Is <= 1000
            ActiveCell.Font.Color = vbRed
    End Select
End Sub 

Inside Out
The Pitfalls of Case Statements and Conditional Formats

If you compare the If…Then and Select Case versions of the AvailableCredit routines side by side, you might notice that the If…Then statements check for values greater than some other value (for example, If .Value > 5000 Then .Font.Color = vbBlue), whereas all but the last Case statement checks for values in a definite range. You should use definitive rules in a Select Case statement because the instant Excel finds a case that’s true, it exits the Select Case statement. So, if you were to evaluate a cell value of 5500 using the If…Then statement listed in the preceding example, the procedure would go through the following steps:

1. Is the cell blank? No, so take no action.

2. Is the value less than 1000? No, so take no action.

3. Is the value greater than 1000? Yes, so change the font color to black.

4. Is the value greater than 5000? Yes, so change the font color to blue.

5. Is the value greater than 10,000? No, so take no action.

   The routine changed the font color an extra time (first to black, and then to blue), but you got the right result and the extra step is not a problem for a simple program on a computer that can perform millions of calculations per second. However, because the rules in the following Select Case statement are constructed in the same order, the cell’s contents would be displayed in black type, not blue.

   Select Case Remaining
   Case ""
               Exit Sub
   Case Is < 1000
               ActiveCell.Font.Color = vbRed
   Case Is >= 1000
   ActiveCell.Font.Color = vbBlack
       Case Is >= 5000
           ActiveCell.Font.Color = vbBlue
   Case Is >= 10000
               ActiveCell.Font.Color = vbGreen
       End Select

   You get incorrect results because the routine quits when it finds the cell value is less than or equal to 9999. You’ll run into the same problem when you create conditional formats, which you do by clicking Format, Conditional Formatting and using the controls in the Conditional Formatting dialog box to create your rules. The rules in the following graphic correspond to the incorrect order noted earlier and would also result in an improperly formatted cell value.

   

   Of course, if you were to reverse the order of the Case statements (ignoring the first case, which checks for a blank cell), the most restrictive case would come first, the second most restrictive next, and so on. And that’s the trick to creating effective Select Case and If…Then statements: after you check for a blank cell, you should always check for the most restrictive set of values. Also bear in mind that the comparison operator and the statement you use determine the order in which the sets become more or less restrictive. In a Select Case statement, if you want to check whether values are greater than other values, you need to check for the higher values first (for example, you ask “is the value greater than 10,000” before asking “is the value greater than 5000”); if you check whether values are less than other values, you need to check for the lower values first (for example, you ask “is the values less than 1000” before asking “is the value less than 5000”).

   Tip 

   You’re limited to three conditions for conditional formatting
   The conditional format in the graphic also points out one of the advantages of VBA: you are allowed only three conditions in the Conditional Formatting dialog box.

Defining the Scope of a Sub Procedure

The first element of a Sub procedure, the optional Public or Private declaration, determines the scope of the procedure. Simply put, a procedure with a Private scope can be referred to only by other procedures in the same module, whereas procedures with a Public scope can be referred to by any procedure in any module.

Note 

Unless otherwise stated, every procedure is a Public procedure.

As an example, consider the code module from CreditLineInfo.xls, displayed in Figure 5-2, which contains one Private procedure and one Public procedure.

Figure 5-2: Using the Public and Private keywords helps limit the availability of your macros when appropriate.

When you click Tools, Macro, Macros to open the Macro dialog box from within any workbook, you will only be able to view, run, or edit the AvailableCreditCase procedure. You could, however, run the AvailableCredit procedure from another procedure in the same code module (but not from a procedure in another module, even if that module is attached to the same workbook).

Note 

You’ll learn more about running procedures from within other procedures later in this chapter.

If you’re writing a set of macros you don’t want to be seen (or run) by anyone who knows how to open the Macro dialog box, you can put an Option Private Module statement in the declarations section at the top of the code module to make every procedure, even those that use the Public keyword, private. The macros will still be available in the code module, however.

For example, one procedure in Figure 5-3 has the Public keyword in the Sub statement, but the Option Private Module line in the declarations section at the top of the module takes precedence.

Figure 5-3: Adding the Option Private Module line to a module’s declaration section hides all procedures in a module from view.

To display the declarations section of a code module, you click the down arrow at the right edge of the Procedure list box and click Declarations. When you do, a new section, delineated by a line, will appear in your code module.

You may then type the Option Private Module declaration in the proper section.

Tip 

Use the keyboard to enter the declarations section
You can also enter the declarations section of a code module by moving the insertion point to the left of the first line of code in the module, pressing Enter, and moving back up to the newly blank first line. When you do, the value in the Procedure list box changes to Declarations and you can begin typing the declaration. You can also start typing the declaration in the first position of the first line of the module and press Enter. When the Visual Basic Editor recognizes you put a declaration on the first line, it will create a Declarations section and put in a line marking the end of the section.

Inside Out
The Good News Is That You Didn’t Break It…

There might be times when you want to change the scope of a procedure from Public to Private, perhaps after you’ve finished testing the procedure and you don’t need to run it from the Macro dialog box any more. Another situation where you might want to make a procedure private is if you attached the macro to a toolbar button or menu item and, while you still want to allow your colleagues to run the macro from the custom button or menu item, you don’t want just anyone who knows how to use the Customize dialog box to assign the macro to another button or, worse, edit it in the Visual Basic Editor. If someone is determined to get into the code and you don’t have the workbook password-protected, you can’t really stop them, but you can make it more difficult by taking the procedure Private. And, as the title of this Inside Out sidebar implies, making a Public procedure Private doesn’t break the link between the toolbar button, menu item, or object to which you assigned the macro. Clicking the macro trigger, whatever it is, will still run the macro.

Running a Sub Procedure from Within Another Procedure

After you have created a Sub procedure, you will want to run it. As mentioned before, you can run the procedure by linking the macro to a trigger, but you can also run the macro by calling it from within another macro. For example, if you want to run an existing procedure, such as NOW, which returns the current date and time, you can do so.

Sub ShowTheTime
    MsgBox (NOW())
End Sub

The same mechanism works for running a Sub procedure.

To call a Sub procedure from another procedure, type the name of the procedure and include values for any required arguments. The Call statement is not required, but if you use it, you must enclose any arguments in parentheses.

With the definition in mind, take a look at the following Sub procedure, which converts a quantity of American dollars into Swedish krona at the current exchange rate, as drawn from the Microsoft Money Central Web site and stored in cell C35:

Sub Krona()
    sngInKrona = ActiveCell.Value * Range("C35").Value
    MsgBox("The value of $" & ActiveCell.Value & " is " _
     & sngInKrona & " krona.")
End Sub

You can find currency quotes on the Web by visiting http://moneycentral.msn.com/ and typing “currency rates” into the Search box. For more information on getting information from the Web into your Excel worksheets, see Chapter 24, “Excel and the Web.”

If you want to run this Sub procedure from another procedure, you could do so using one of these three techniques:

• Type the name of the procedure and any arguments it takes. (If there are no arguments, type an empty pair of parentheses.)

• Type the Call keyword, and then type the name of the procedure and any arguments it takes. (If there are no arguments, type an empty pair of parentheses.)

• Use the Application object’s Run method to run the macro. This method is useful if you want to use your VBA code to determine which of several macros to run, and to assign the name of the macro you decide upon to a variable.

You’ve already seen the first technique in action, but it’s actually the same as the second technique. When Excel encounters an unfamiliar word followed by a set of open and close parentheses, it searches the available modules for Public procedures of the same name. You used to be required to put the Call keyword before the procedure name, but that’s no longer mandatory. Even so, some programmers choose to put the Call keyword in front of procedures they have created to ensure that they (and anyone else who examines the code in the module) will understand that the procedure is not part of the standard Excel library.

As you might expect, you’re not limited to calling procedures from within the same code module or even the same workbook. If you want to reach out to use procedures in other modules, you can do so. In fact, if the Excel VBA engine doesn’t find the procedure you call in the same module as the calling procedure, it will search the other modules in the active workbook and, if it still hasn’t found the procedure you called, will go on to all other open workbooks in an attempt to find the code.

When you know the name of the module that contains the procedure you want to run and the module is in the active workbook, you can put the name of the module in front of the procedure name using dot notation, as in the following brief Sub procedure:

Sub CallOut()
    Call CurrencyModule.Krona()
End Sub

When you create a new code module, the Visual Basic Editor gives it the name Module1, Module2, and so on. You should strongly consider renaming the modules in your projects to make their contents more obvious.

To rename a module, follow these steps:

1. Open the workbook to which the module is attached, and click Tools, Macro, Visual Basic Editor.

2. In the Project window, click the name of the module you want to rename.

3. In the Properties window, select the existing module name next to the Name property, type the new module name, and then press Enter.

   Caution 

   You can run into trouble if you have procedures with the same name in two or more workbooks. You can’t have two procedures with the same name in a module, but because you can use dot notation to specify which module’s procedure you want, you can avoid any problems. That said, if you have several procedures with the same name in different modules and you attempt to call one of them without specifying the module that contains the procedure (for example, CurrencyModule.Krona), the Visual Basic Editor will display an Ambiguous name detected error and halt the execution of the code that attempted to call the procedure.

If you know that the procedure you want to run is available in a module attached to another workbook and for some reason don’t want to copy the code to your current workbook, you can call the procedure from the other workbook in one of two ways: by using the Application.Run method and specifying the name of the workbook, or by creating a reference to the workbook that contains the procedure.

Using the Application.Run method to run a procedure in another workbook requires you to name only the other workbook and the procedure you want to run. You name the workbook and procedure using a syntax that looks a lot like the reference you create when you link from a worksheet cell to a cell in another workbook.

If you wanted to call the ConvertToKrona procedure from the workbook ExchangeRates.xls, you would use the following statement:

Application.Run "'ExchangeRates.xls'!ConvertToKrona" 

One limitation of the Application.Run method is that the workbook that contains the called procedure must be open to allow Excel to look into it to see what procedures are available. That requirement is the reason that the Macros dialog box can display macros in the current workbook, any open workbook, or all open workbooks: the program designers chose not to let Excel reach into workbooks unless they were already open and ready for use.

You can, however, make it possible to call procedures in any workbook, open or not, by creating a reference to the procedure. Just as using the Application.Run method to call a procedure from another workbook is similar to creating a link from a worksheet cell to a cell in another workbook, creating a reference to a procedure in another workbook is very similar to linking or embedding an outside file in an Excel workbook. But, instead of using the Insert Object dialog box in Excel, you use the Tools, References dialog box in the Visual Basic Editor (shown in Figure 5-4).

Figure 5-4: The References dialog box lists all the available resources to which you can link and subsequently call procedures.

When you first display the References dialog box, it doesn’t list the projects available in the Excel workbooks on your computer (and any network drives to which you have access), but you can go looking for them using the Add Reference dialog box.

To add a reference to an Excel workbook to the list in the References dialog box, follow these steps:

1. Click Tools, References.

2. Click Browse.

3. Click the Files of Type down arrow, Microsoft Office Excel Files (*.xls;*.xla).

4. Navigate to the directory containing the workbook, click the workbook, and click Open.

After you create the reference, the name of the project in the workbook you just selected appears in the References dialog box. What’s interesting about the creation process is that most projects are named VBAProject (the default). And, just as you should change the name of your code modules so that they are more descriptive than Module1, you should also change the name of your projects so that they give you some clue as to their contents. One possibility would be to change the project name so that it’s the same (or close to the same) as the workbook name. If you don’t, you’ll see a list of VBAProject references in your list and will have no idea which one is which. The procedure for renaming a project is similar to that of renaming a module: in the Project window, click the name of the project and then change the Name property in the Properties window.

Inside Out
Recursion and Public Use: Two Procedural Pitfalls

When you call a procedure from within the same procedure, the procedure is said to be recursive. For example, if you do any work with probability and statistics you know about FACT, which finds the factorial of a number (for example, 3! = 3 * 2 * 1 = 6, 5! = 5 * 4 * 3 * 2 * 1 = 120, and so on). The programmer who wrote the FACT function put in a test to ensure the procedure didn’t continue to call itself after it reached 1, and it’s a good thing. If the procedure didn’t check its progress, it would continue to churn through multiplications by 0, -1, -2, and so on, until the computer ran out of memory and crashed. If you should need to write a procedure that calls itself, be sure to add some logical test in an If…Then or Do…While loop to prevent any sort of infinite mischief.

Another interesting procedural pitfall could happen if you use the Static keyword to preserve the values of the variables in a procedure between calls. Assume you’re working with a worksheet that keeps track of your daily sales, and the workbook has the macro described earlier in this chapter that maintains a running total of your sales. What happens if one of your colleagues examines another workbook that calls the same procedure? For example, you might have given your colleague one of last month’s workbooks to learn how you set up the office before you go on a much-deserved vacation away from e-mail messages and wireless phones. If that colleague examines the workbook, sees what the macro code does, types a large negative value into a cell and runs the procedure, the next update on your computer will not reflect the true value of sales. Now, it’s good to note that this sort of error would be of minimal harm if you didn’t do anything other than monitor the values to keep an eye on things. Also, if the sales total were far smaller than you remembered, you would probably realize that the lack of a large negative value in your copy of the worksheet indicated that something odd was happening in another copy of the workbook.

That said, if you use any sort of running total or other static variables as an integral part of your business, you should strongly consider putting the update procedures in a module with Option Private Module featured prominently at the top.

Retaining Values Between Procedure Calls

The second optional element in a procedure declaration is whether or not to make the procedure retain the values generated by previous executions of the procedure. One example of a procedure where you might want to keep a running total for a value is a procedure that wrote sales for a day into a worksheet. Sure, you could write the values for a day to a file or an array and add up the totals, but it’s much simpler to display the running total for a day by ensuring the procedure remembers the values that came before.

As you probably guessed from the available keywords in the Sub procedure definition statement, the keyword you want to use is Static. As an example, consider the following procedure, which attempts to maintain a running total of a day’s sales using the intTotal variable:

Sub RunningTotal()
    intTotal = intTotal + ActiveCell.Value
    Range("B10").Value = intTotal
End Sub

The benefit of this procedure, if it were working properly, is that you wouldn’t need to run a query or even some sort of fancy refreshable summation formula in cell B10 to update the sales total in your worksheet. But, the procedure as written always generates the same answer: the value in the active cell. The problem is that the variable intTotal is re-created every time you run the procedure, so its value is set to 0 every time. How do you fix the procedure so that it keeps a real running total? By adding the Static keyword in front of the Sub statement, as in the following listing:

Static Sub RunningTotal()
    intTotal = intTotal + ActiveCell.Value
    Range("B10").Value = intTotal
End Sub

When you add the Static keyword to the Sub statement, Excel knows to create a durable storage space for each variable and to maintain that space and its contents until you close the workbook.

Note 

You can also reset the value of the variables in a static procedure by displaying the procedure in the Visual Basic Editor and clicking Run, Reset.

---