Public and Private Functions and Subroutines


VBA allows you to define your functions or subroutines as public or private using the keyword Public or Private . For 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 exception to this: UserForms. As discussed 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, UserForm1.MysubRoutine ‚ 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-in, public procedures within that add-in can still be accessed by other modules by referencing the add-in object. This happens even if the code for the add-in is password protected. This can have advantages if you wish to write an add-in of your own procedures for use by other VBA programmers without letting them see how you did it! The disadvantage is that others can access your public procedures when you do not want them to. If the add-in is loaded, the public procedures within it are available to all 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.




Excel VBA Macro Programming
Excel VBA Macro Programming
ISBN: 0072231440
EAN: 2147483647
Year: 2004
Pages: 141

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net