Page 117 - Microsoft Office Excel 2003 Programming Inside Out

Creating Sub and Function Procedures

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 work-
book, 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 Chapter 5
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.

91
Part 2: Visual Basic for Applications