Return to VBA Code Examples

VBA Private vs Public Procedures (Subs & Functions)

This tutorial will explain the difference between public and private declarations in VBA and how to specify modules as private.

Public vs. Private Sub Procedures

Procedures (Sub and Functions) can be declared either Private or Public in VBA. If they are Public, it means that you would be able to see them from within the Excel Macro Window and they can be called from anywhere within your VBA Project.  If they are Private, they cannot be seen in the Excel Macro Window and are only available to be used within the Module in which they are declared (using normal methods, see the bottom of this article for ways to access private procedures from other modules).

Public functions can be called like built-in Excel functions in the Excel worksheet.

Note: Variables and Constants can also be Public or Private.

Excel Macro Window

By default, Excel Macros (most VBA Procedures) are visible to workbook users in the Macro Window:

vba publicvsprivate macro window

These are considered Public procedures. You can explicitly define procedures as public by adding “Public” before the Sub statement:

If you don’t define the procedure as Public, it will be assumed Public.

To declare a procedure as Private, simply add “Private” before the procedure sub statement:

The second procedure would not be visible in the Macro window to Excel users, but can still by used in your VBA code.

vba publicvsprivate 2

Procedures with Arguments

Sub procedures can have arguments. Arguments are inputs to the sub procedure:

If a sub procedure has arguments, it will never appear in the Macro Window regardless of if its declared Public because there is no way to declare the arguments.

Functions also will never appear in the Macro Window, regardless of if they are declared Public.

vba publicvsprivate macro window 2

Public functions in Excel are able to be used directly in a worksheet as a ‘User Defined Function’ (UDF). This is basically a custom formula that can be called directly in a worksheet. They can be found in the ‘User Defined’ category in the ‘Insert Function window or can be typed directly into a cell.

vba publicvsprivate excel function

Using Procedures between Modules in your VBA Project

Public procedures can be called from any module or form within your VBA Project.

vba publicvsprivate call sub

Attempting to call a private procedure from a different module will result in an error (Note: see bottom of this article for a work around).

vba publicvsprivate call private sub

 

Note: Public procedures and variables in class modules behave slightly differently and are outside the scope of this article.

Different modules, can store procedures with the same name, provided they are both private.

If two or more procedures have the same name and are declared public you will get an ‘Ambiguous Name detected’ compile error when running code.

vba publicvsprivate ambigious

Private Modules

By default, modules are public.

To make a module private, you put the following keyword at the top of the module.

If you declare a module as private, then any procedures in the module will not be visible to Excel users. Function procedures will not appear in the Insert Function window but can still be used in the Excel sheet as long as the user knows the name of the function!

vba publicvsprivate private function excel

Sub procedures will not appear in the Macro Window but will still be available to be used within the VBA project.

Accessing a Private Procedure from a Different Module

As mentioned above, Private Procedures are inaccessible in other code modules by “normal” methods. However, you can access private procedures by using the Application.Run command available in VBA.

Consider the following 3 modules.

vba publicvsprivate multi modules

 

Module 2 is a Private Module with a Public Sub Procedure, whereas Module3 is Public module with a Private Sub Procedure.

In Module1, we can call Hello World  – the Option Private Module at the top does not prevent us from calling the Sub Procedure – all it serves to do is hide the Sub Procedure in the Macro Window.

We also do not need the Call statement – it is there to make the code easier to read.

The code could also look like this below:

We can also run the HelloWorld Sub Procedure by using the VBA Application.Run command.

In Module3 however, the GoodMorningWorld procedure has been declared Private.   You cannot call it from another module using ‘normal’ means ie the Call statement.

You have to use Application.RunCommand to run a Private Sub from another module.

Notice the when you use the Application.RunCommand command, you have to put the Sub Procedure name within inverted commas.

If we do try to use the Call statement to run the GoodMorningWorld Sub Procedure, an error would occur.  

vba publicvsprivate multi modules error

 

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!