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 you create are public by default. This means they can be used throughout the modules within your application, and database users will find the subroutines available to use in any VBA code that may write in the database. They will also be able to access public functions in your code and either use them in their own code or their own SQL queries.

The one exception to this is forms/reports. Forms represent dialog forms and have their own modules. Reports also have modules. A public subroutine or function on a form can be called from other modules within your code by referencing the form object-for example, Form_Form1.MysubRoutine. If you do not reference the subroutine to the form name as shown earlier, or the subroutine is made private, you will get a compile error when this is run.

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. More importantly, it cannot be seen and run by the Access user in SQL queries. 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. It first looks in the current module where the code is executing. If it cannot find a procedure of that name there, it 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.