Scoping a procedure

In the preceding chapter, I note that a variable's scope determines the modules and procedures in which the variable can be used. Similarly, a procedure's scope determines which other procedures can call it.


By default, procedures are public — that is, they can be called by other procedures in any module in the workbook. It's not necessary to use the Public keyword, but programmers often include it for clarity. The following two procedures are both public:

Sub First()

Public Sub Second() ' ... [code goes here] ... End Sub

Naming Procedures

Every procedure must have a name. The rules governing procedure names are generally the same as for variable names. Ideally, a procedure's name should describe what its contained processes do. A good rule is to use a name that includes a verb and a noun (for example, ProcessDate, PrintReport, Sort_Array, or CheckFilename). Avoid meaningless names such as Dolt, Update, and Fix.

Some programmers use sentence-like names that describe the procedure (for example,

WriteReportToTextFile and Get_Print_Options_ and_Print_Report). Although long names are very descriptive and unambiguous, they are also more difficult to type.


Private procedures can be called by other procedures in the same module but not by procedures in other modules.

When you choose the Excel Tools^ Macro^ Macros command, the Macro dialog box displays only the public procedures.Therefore, if you have procedures that are designed to be called only by other procedures in the same module, you should make sure that the procedure is declared as Private. This prevents the user from running the procedure from the Macro dialog box.

The following example declares a private procedure, named MySub:

Private Sub MySub() ' ... [code goes here] ... End Sub

You can force all procedures in a module to be private — even those declared with the Public keyword — by including the following statement before your first Sub statement:

Option Private Module

If you write this statement in a module,you can omit the Private keyword from your Sub declarations.

Excel's macro recorder normally creates new Sub procedures called Macrol, Macro2, and so on. These procedures are all public procedures, and they will never use any arguments.

0 0

Post a comment