Page 113 - Excel Progamming Weekend Crash Course

h540629 ch07.qxd 9/2/03 9:33 AM Page 88

88 Saturday Morning

A property procedure defines a property in a class.
An event procedure is executed when an event, such as a keypress or mouse click,
occurs.
Sub and function procedures, the most fundamental types, are covered in this session.
Methods, property procedures, and event procedures are more specialized and are covered
in later sessions.

Methods and property procedures are covered in Session 26. Event procedures
are covered in Session 27.
Cross-Ref

Sub Procedures

A sub procedure is defined using the Sub and End Sub keywords. The syntax is:
Sub ProcName(arguments)
...
End Sub

The procedure name follows the usual VBA naming rules and must be unique within its
scope. Arguments (data that is passed to the procedure) are covered in the next section.
Use an empty set of parentheses when the procedure takes no arguments. Within the proce-
dure you can include essentially any VBA code, with the following exceptions:
Another procedure definition
A class definition
A user-defined type definition
An enumeration definition

The length of code in a procedure is unlimited, but good programming practice dictates
that procedures be kept to reasonable lengths. What is reasonable? There’s no single correct
answer to that question, but in my experience I have almost never found it necessary to
create a procedure continuing more than 30 to 40 lines of code. When a procedure reaches
that length, it almost always can, and should, be broken into two or more separate proce-
dures. This results in code that is easier to read, understand, and debug.
When planning your procedures, you should try to create a separate procedure for each
discrete task that needs to be performed. For example, suppose you are writing a program
that calculates both the amount of a mortgage a person can afford based on his or her
income and other debts, and the monthly payments on that mortgage for different interest
rates. You could certainly place all the code in one procedure, but it would be preferable to
separate the program into at least two procedures — one to determine the mortgage amount
and the other to calculate the monthly payments. By keeping tasks that are logically sepa-
rate in their own procedures, you reduce the chance of unintended code interactions and
the resulting bugs and errors. Also, procedures are more likely to be reusable in other pro-
grams when designed this way.