When writing VBA macros, the concept of Private or Public is important. It defines how VBA code within one module can interact with VBA code in another module. This concept applies to both Private Subs and Private Functions.
As a simple analogy – on social media, you can set parts of your profile so that everybody can see it (Public), or only those you allow, such as friends or followers, to see it (Private). The Private vs. Public concept in VBA is similar, but since we’re talking about VBA here, it’s not quite as straightforward.
Before we launch into the difference between Public and Private, we first need to understand what Modules are and how they work.
Table of Contents
Modules are the place where VBA code is written and stored. There are many different module types in Excel, and we use each module for a different purpose.
Worksheet Modules are generally used to trigger code related to that specific worksheet. Each worksheet contains its own module, so if there are 6 worksheets, then we have 6 Worksheet Modules.
In the screenshot above, the VBA code is contained within the Worksheet Module of Sheet1. As we have used the Worksheet_Activate event, the code is triggered only when Sheet1 is activated. Any event-based code (such as worksheet activation) in a Worksheet Module only applies to the worksheet in which the code is stored.
The Workbook Module is generally used to trigger code related to workbook-level events.
In the screenshot above, we have used the Workbook_Open event. Therefore, the VBA code will run when a workbook is opened. Every workbook has its own module.
UserForm Modules generally contain code that relates to UserForm events. Each UserForm has its own module.
In the screenshot above, the VBA code will run when the user clicks on CommandButton1 in the UserForm.
Standard Modules are not related to any specific objects and do not have any events related to them. Therefore, standard Modules are not triggered by user interaction. If we are relying on triggered events, we need the Workbook, Worksheet, or UserForm Modules to track the event. However, that event may then call a macro within a Standard Module.
TIP: Find out how to run a macro from another macro here: Run a macro from a macro (from another workbook)
The screenshot above shows a code that password protects the ActiveSheet, no matter which workbook or worksheet.
Other Module Types
The final type of VBA module available is a Class Module. These are for creating custom objects and operate very differently from the other module types. Class Modules are outside the scope of this post.
Public vs. Private
The terms Public and Private are used in relation to Modules. The basic concept is that Public variables, Subs, or Functions can be seen and used by all modules in the workbook, while Private variables, Subs, and Functions can only be used by code within the same module.
Declaring a Private Sub or Function
To treat a Sub or Function as Private, we use the Private keyword at the start of the name.
Private Sub nameOfSub()
Private Function nameOfFunction()
Declaring a Public Sub or Function
To treat a Sub or Function as Public, we can use the Public keyword. However, if the word Public or Private is excluded, VBA treats the sub/function as if it were public. As a result, the following are all Public, even though they do not all include the keyword.
Public Sub nameOfSub()
Public Function nameOfFunction()
Let’s look at Subs and Functions in a bit more detail
Sub procedures (Subs)
When thinking about the difference between a Public Sub and a Private Sub, the two primary considerations are:
- Do we want the macro to appear in the list of available macros within Excel’s Macro window?
- Do we want the macro to be run from another Macro?
Does it appear in the Macro window?
One of the most important features of Private subs is that they do not appear in Excel’s Macro window.
Let’s assume Module1 contains the following two macros:
Private Sub NotVisible()
MsgBox "This is a Private Sub"
Public Sub IAmVisible()
MsgBox "This is a Public Sub"
The Macro dialog box only displays the Public sub.
I don’t want you to jump to the conclusion that all Public Subs will appear in the Macro window, as that is not true. Any Public sub which requires arguments, also does not appear in this window, but it can still be executed if we know how to reference it.
Can the code be run from another macro?
When we think about Private Subs, it is best to view them as VBA code that can only be called by other code within the same module. So, for example, if Module1 contains a Private Sub, it cannot be called by any code in another module.
Using a simple example, here is a Private Sub in Module1:
Private Sub ShowMessage()
MsgBox "This is a Private Sub"
Now let’s try to call the ShowMessage macro from Module2.
Running CallAPrivateMacro generates an error, as the two macros are in different modules.
If the ShowMessage macro in Module1 were a Public Sub, it would execute correctly.
There are many ways to run a macro from another macro. One such method allows us to run a Private Sub from another Module. If we use the Application.Run command, it will happily run a Private sub. Let’s change the code in Module2 to include the Application.Run command:
Instead of an error, the code above will execute the ShowMessage macro.
Working with object-based module events
Excel creates the Worksheet, Workbook, and UserForm Module events as Private by default, but they don’t need to be. If they are changed to Public, they can be called from other modules. Let’s look at an example.
Enter the following code into the Workbook Module (notice that I have changed it to a Public sub).
Public Sub Workbook_Open()
MsgBox "Workbook Opened"
We can call this from another macro by using the object’s name followed by the name of the Public sub.
This means that we can run the Workbook_Open event whenever we need to. If the sub in the Workbook Module is Private, we can still use the Application.Run method noted above.
VBA functions are used to return calculated values. They have two primary uses:
- To calculate a value within a cell on a worksheet (known as User Defined Functions)
- To calculate a value within the VBA code
Like Subs, Functions created without the Private or Public declaration are treated as Public.
Calculating values within the worksheet (User Defined Functions)
User Defined Functions are worksheet formulas that operate similarly to other Excel functions, such as SUMIFS or XLOOKUP.
The following code snippets are included within Module1:
Public Function IAmVisible(myText As String)
IAmVisible = myText
Private Function NotVisible(myText As String)
NotVisible = myText
If we look at the Insert Function dialog box, the IAmVisible function is available as a worksheet function.
Functions must be declared in a Standard Module to be used as User Defined Functions in an Excel worksheet.
Function within the VBA Code
Functions used within VBA code operate in the same way as subs; Private functions should only be visible from within the same module. Once again, we can revert to the Application.Run command to use a Private function from another module.
Let’s assume the following code were entered into Module2:
MsgBox Application.Run("NotVisible", "This is a Private Function")
The code above will happily call the NotVisible private function from Module1.
Variables hold values or references to objects that change while the macro runs. Variables come in 3 varieties, Public, Private and Dim.
Public variables must be declared at the top of the code module, directly after the Option Explicit statement (if you have one), and before any Subs or Functions.
The following is incorrect and will create an error if we try to use the Public Variable.
MsgBox "Public Variable is not first"
Public myPublicMessage As String
The correct approach would be: (The Public variable is declared before any subs or functions):
Public myPublicMessage As String
MsgBox "Public Variable is first"
As it is a Public variable, we can use and change the variable from any module (of any type) in the workbook. Look at this example code below, which could run from Module2:
myPublicMessage = "This is Public"
Private Variables can only be accessed and changed by subs and functions within the same Module. They too, must also be declared at the top of the VBA code.
The following demonstrates an acceptable usage of a Private variable.
Private myPrivateMessage As String
myPrivateMessage = "This is Private"
Most of us learn to create variables by using the word Dim. However, Dim variables behave differently depending on how they are declared.
Dim variables declared within a Sub or Function can only be used within that Sub or Function. In the example below, the Dim has been declared inside a Sub called CreateDim, but used within a sub called UseDim. If we run the UseDim code, it cannot find the Dim variable and will error.
myDimMessage = "Dim inside Sub"
If a Dim variable is created at the top of the module, before all the Subs or Functions, it operates like a Private variable. The following code will run correctly.
myDimMessage = "Dim inside Sub"
Does this really matter?
You might think it sounds easier to create everything as Public; then it can be used anywhere. A logical, but dangerous conclusion. It is much better to control all sections of the code. Ask yourself, if somebody were to use your macro from Excel’s Macro window, should it work? Or if somebody ran your function as a User Defined Function, should it work? Answers to these questions are a good guiding principle to help decide between Public and Private.
It is always much better to limit the scope of your Subs, Functions, and variables initially, then expand them when required in specific circumstances.
Discover how you can automate your work with our Excel courses and tools.
The complete program for saving time by automating Excel.
Excel Automation Secrets
Discover the 7-step framework for automating Excel.
Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.