Page 123 - Microsoft Office Excel 2003 Programming Inside Out

Creating Sub and Function Procedures

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 wire-
less 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 Chapter 5
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 indi
cated 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.

97
Part 2: Visual Basic for Applications