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:

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

Public Sub HelloWorld()
   MsgBox "Hello World"
End Sub

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:

Private Sub HelloEveryone()
MsgBox "Hello Everyone"
End Sub

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

Procedures with Arguments

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

Sub Hello(strName as string)
   MsgBox "Hello " & strName
End Sub

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.

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.

Using Procedures between Modules in your VBA Project

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

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

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.

Private Modules

By default, modules are public.

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

Option Private 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!

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.

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:

Sub CallHelloFromPrivate()
'call a sub from a Private Module
   HelloWorld
End Sub

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.

Sub CallGoodMorning()
'run a private sub from a public module
   Application.Run ("GoodMorningWorld")
End Sub

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 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!


Learn More!