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()

End Sub

End Sub


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

Note When a user displays the Macro dialog box, Excel shows 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 those procedures are declared as Private. Doing so prevents the user from running these procedures from the Macro dialog box.

The following example declares a private procedure named MySub:

End Sub

Tip 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 Macro1, Macro2, and so on. These procedures are all public procedures, and they will never use any arguments.



0 0

Post a comment