Private vs Public Subs, Variables & Functions in VBA

Public Vs Private

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

On social media you can to 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 straight forward.

Before we launch into the difference between Public and Private, we first need to understand what Modules are, and how they function.

Modules

Modules are the place where we enter and store VBA code.

Worksheet Modules

Worksheet Modules are generally used to trigger code which relate to that specific worksheet.  Each worksheet contains its own module, so if there are 6 worksheets, then you will have 6 Worksheet Modules.

Worksheet Module

In the screenshot above, the VBA code is contained within the Worksheet Module of Sheet1 and is triggered only when Sheet1 is activated.  Any code in a Worksheet Module based on worksheet events applies only to the worksheet in which the code is stored.

Workbook Module

The Workbook Module is generally used to trigger code which relates to workbook level events.

Workbook Module

In the screenshot above, the VBA code will run when a workbook is opened.  Every workbook has its own module.

UserForm Module

UserForm Modules generally contains code which relates to UserForm events.  Each UserForm contains its own module.

UserForm Module

In the screenshot above, the VBA code will run when the user clicks on the button in the UserForm.

Standard Modules

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 events being triggered, then the Workbook, Worksheet or UserForm Modules may call a macro within a Standard Module.

Standard Module

The screenshot above shows a code which when run, will password protect the ActiveSheet, no matter which workbook or worksheet it is.

Other Module Types

The final type of VBA module available is a Class Module.  These are for creating custom objects and operate very differently to 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.

Let’s look at each of these in turn.

Sub procedures (Subs)

When thinking about the difference between Public and Private subs, the two main areas to consider:

  • Does it appear in the list of available macros within Excel’s Macro window?
  • Can the code be run from another Macro?

Where Private or Public is excluded, VBA will always treat the sub as if it were Public.

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"

End Sub
Public SubIAmVisible()

MsgBox "This is a Public Sub"

End Sub

The Macro window will only display the Public sub.

Macro Windows excludes Private Subs

I don’t want you to jump to the conclusion that all Public Subs will appear in the Macro window.  Any Public sub which requires arguments, will also 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 which can only be called by other VBA code within the same module.  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"

End Sub

Now let’s try to call the ShowMessage macro from Module2.

Sub CallAPrivateMacro()

Call ShowMessage

End Sub

It will generate an error, as the two macros are in different modules.

Private Sub Error Message

But don’t worry, there are many ways to run a macro from another macro. 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:

Sub CallAPrivateMacro()

Application.Run "ShowMessage"

End Sub

Instead of an error, the code above will execute the ShowMessage macro.

Code executes correctly

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.  Lets look at an example.

Enter the following code into the Workbook Module (notice that I have changed it to a Publc sub).

Public Sub Workbook_Open()

MsgBox "Workbook Opened"

End Sub

We can call this from another macro by using the name of the object followed by the name of Public sub.

Sub RunWorkbook_Open()

Call ThisWorkbook.Workbook_Open

End Sub

This means that we can run the Workbook_Open event without needing to actually open the workbook.  If the sub in the Workbook Module is Private, we can still use the Application.Run method noted above.

Functions

VBA functions are used to return calculated values.  They have two main uses:

  • To calculate a value within a cell on a worksheet (known as User Defined Functions)
  • To calculate a value within the VBA code

Functions created without the Private or Public declaration are treated as if they are Public.

Calculating values within the worksheet (User Defined Functions)

User Defined Functions are worksheet formulas which operate the same way as other Excel functions, such as SUMIF or VLOOKUP.

The following code snippets are included within Module1:

Public Function IAmVisible(myText As String)

IAmVisible = myText

End Function
Private Function NotVisible(myText As String)

NotVisible = myText

End Function

If we now look at the Insert Function window, the IAmVisible function is available for use:

UDF Visible

Functions must be declared in a Standard Module to be used as User Defined Functions in Excel.

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.

Module2:

Sub CallAPrivateFunction()

MsgBox Application.Run("NotVisible", "This is a Private Function")

End Sub

The code above will happily call the NotVisible private function from Module1.

Variables

Variables are used to hold values or references to objects which change while the macro runs.  Variables come in 3 varieties, Public, Private and Dim.

Public Variables

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.

Module1:

Option Explicit

Sub SomethingElseAtTheTop()
MsgBox "Public Variable is not first"
End Sub

Public myPublicMessage As String

The correct approach in Module1 is this (The Public variable is declared before any subs or functions):

Option Explicit

Public myPublicMessage As String

Sub SomethingAfterPrivateVariables()
MsgBox "Public Variable is first"
End Sub

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:

Sub UsePublicVariable()

myPublicMessage = "This is Public"
MsgBox myPublicMessage

End Sub

Private Variables

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.

Module1:

Option Explicit

Private myPrivateMessage As String


Sub UsePrivateVariable()

myPrivateMessage = "This is Private"
MsgBox myPrivateMessage

End Sub

Dim Variables

Most of us learn to create variables by placing the word Dim at the start.  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 in 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.

Sub CreateDim()

Dim myDimMessage

End Sub
Sub UseDim()

myDimMessage = "Dim inside Sub"

MsgBox myDimMessage

End Sub

If a Dim variable is created at the top of the module, before all the subs or functions, it will operate like a Private variable.  The following code will run correctly.

Option Explicit

Dim myDimMessage


Sub UseDim()

myDimMessage = "Dim inside Sub"

MsgBox myDimMessage

End Sub

Does this really matter?

You might be thinking that 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.

2 thoughts on “Private vs Public Subs, Variables & Functions in VBA

Leave a Reply

Your email address will not be published. Required fields are marked *