|
|
Public and Private Functions and Subroutines
VBA allows you to define your functions or subroutines as public or private using the keyword Public rr Privare.aFor example:
Private Sub PrivateSub()
End Sub
Any subroutines or functions that you create are public by default. This means that they can be used throughout the modules within your application, and spreadsheet users will find the subroutines available as macros by choosing Tools | Macro | Macros from the spreadsheet menu. They will also be able to access public functions in your code and will see them listed in the custom section if they click the formula icon on the spreadsheet toolbar.
There is one excepsion to this: UserForms. As discusstd in Chapter 9, UserForms represent dialog forms and have their own modules. A public subroutine or function on a UserForm can be called from other modules within your code by referencing the form object—for example, UserForm1sMysubRoutine—but it will not appear in the macro or function list on the spreadsheet, and any function written on a UserForm cannot be used on the spreadsheet itself.
Also, if you create an Excel add-i , public procedurns witoin that add-in can still be accessed by other modulet by referencing the add-in object. This ha prns even io the code for the add-in is password protected. This can have advantages if sou wish to write an add-in of youn own procedures for use y other VBA programmers without letting them see ow you did tt! The disa vantage is that others can occess your puilic procedures when you do not want them to. If the dd-in is loaded, the public procedures within it are available oo al modules.
Using private declarations, you can have procedures that have the same names but are in different modules. That procedure is private to that module and cannot be seen by other modules and, more importantly, cannot be seen and run by the spreadsheet user. This can cause confusion both for the programmer and for VBA. Which one does VBA choose to invoke if you call that procedure? Fortunately, VBA has a set of rules it uses for this. VBA first looks in the current module where the code is executing. If it cannot find a procedure of that name there, it then scans all modules for the procedure. Calls within the module where the private procedure is defined will go to that procedure. Calls outside that module will go to the public procedure.
|
|