Page 111 - Microsoft Office Excel 2003 Programming Inside Out

Chapter 5

Creating Sub and Function

Procedures

Defining Sub Procedures . . . . . . . . . . . . .85 Organizing for Success. . . . . . . . . . . . . . 104
Defining Function Procedures. . . . . . . . . .98

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 collec­
tion 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 proce­
dure 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

85
Part 2: Visual Basic for Applications