Explaining Private vs. Public Declarations
So What’s This Private and Public Stuff Mean?
The VBA terms Private and Public declare the access allowed to the term it’s attached to. Think of it in terms of a private company versus a public company. When trying to search for data on a private company you or I can’t really find much because only certain people have access to knowing their financials. Now if we wanted to know the depreciation expense of a company like Microsoft, we could find that in a split second because they are a public company and everyone has access to a public company’s financials. Keep this in mind as we dig into how VBA uses the terms Private and Public.
Private and Public are mostly used to either declare the scope of a variable or a subroutine (sub). You may also see the word “Dim” used to declare a variable. You can think of Dim as another way of stating Private; however there is a time and a place to use each one. I will touch on how to determine which word to use in the following sections.
What Does Private Mean?
Private Sub sets the scope so that subs in outside modules cannot call that particular subroutine. This means that a sub in Module 1 could not use the Call method to initiate a Private Sub in Module 2. (Note: If you start at the Application level, you can use Run to override this rule and access a Private Sub)
Private [insert variable name] means that the variable cannot be accessed or used by subroutines in other modules. In order to be used, these variables must be declared outside of a subroutine (usually at the very top of your module). You can use this type of variable when you have one subroutine generating a value and you want to pass that value on to another subroutine in the same module.
Dim [insert variable name] is used to state the scope inside of a subroutine (you cannot use Private in its place). Dim can be used either inside a subroutine or outside a subroutine (using it outside a subroutine would be the same as using Private).
What Does Public Mean?
Public Sub means that your subroutine can be called or triggered by other subs in different modules. Public is the default scope for all subs so you do not need to add it before the word “sub”. However, it does provide further clarity to others who may be reading your code. As a personal preference I do not type Public Sub unless I am creating an intricate program that has a bunch of subroutines with varying scopes (ie I have a mix of Public & Private subs).
Public [insert variable name] means that the variable can be accessed or used by subroutines in outside modules. These variables must be declared outside of a subroutine (usually at the very top of your module). You can use this type of variable when you have one subroutine generating a value and you want to pass that value on to another subroutine stored in a separate module.
Putting It All Together
Let’s look at an example of how Public and Private scopes interact with each other. For this example let’s presume that we insert two modules into a new workbook and place the below code in their respective module.
In Module 1
Dim x As Integer
Public y As Integer
Sub Start_Process()
x = 15
y = 12
Call Print_Values
End Sub
'=========================================================
Private Sub Display_Message()
MsgBox "We ran all three subroutines!"
End SubIn Module 2
Sub Print_Values()
Debug.Print x
Debug.Print y
Call Display_Message
End SubBefore we run any of the macros, note that there are two variables x and y that are dimensioned outside of a subroutine. This means that their values can carry over into other macros. The variable x has a private scope so only subroutines in the same module can access it’s value. The variable y has a public scope, meaning that subroutines inside and outside it’s module can access it’s value.
Let’s start by running Start_Process. All this macro does is give x and y a value and then initiates the Print_Values macro to start running. We can Call Print_Values even thought it’s not in the same module because it is a Public Sub.
Now let’s hop on over to Print_Values . In this macro we are going to debug print the values of x and y to the immediate window (ctrl + g). Notice that when you try to print variable x it outputs nothing. This is because x does not exist in Module 2. Therefore, a new variable x was created in Module 2 and since we did not give this new x variable a value it’s output was nothing.
Notice that when we print variable y’s value the number 12 is shown in the Immediate Window. This is because Module 2 subroutines have access to the public variables declared in Module 1.
Now the last line in Print_Values is going to give us an error. This is because we are trying to initiate Display_Message from Module 1. Since Display_Message was declared as a private sub, Print_Values does not have the ability to initiate it. There are a few things we can do to fix this:
- We could remove the word “Private” from Display_Message
- We could replace “Private” with “Public” in Display_Message
- We can use the Application level and instead of using Call we could write Application.Run “Display_Message “ (this method serves as an override in case we wanted to keep Display_Message private in the eyes of other outside module subroutines)
To Sum It All Up
I don’t believe this stuff it too difficult or confusing but it might be something that you didn’t realize before. I think the most powerful thing I take away from these concepts, is realizing that you can pass variables through to different subroutines and modules. A place I use this a lot is when I set a variable equal to one of my worksheets. I used to include the exact same set statement in all of my subroutines that needed access to a specific worksheet. After I learned about dimensioning public variables, I now use that set statement with a public variable and don’t have to worry about declaring for any of my other macros. Using this method can reduce the lines of code you write dramatically but it is VERY important that you understand how the scope affects your variable values.
Let Me Know!
Do you use private & public scopes at all in your coding? Can you think of any other ways to use scope that I didn’t cover? Let me know by leaving a comment below!
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.