Excel VBA Private Sub vs Sub (Comparison & Differences)

The blog post compares Private Sub and Sub in VBA (Visual Basic for Applications) programming and explains their differences. The blog post will address issues like the use of Private Sub and Sub procedures within modules and objects, as well as their visibility and accessibility. The following explanations of the differences between Private Sub vs Sub in VBA will provide a clear understanding of Sub Procedures.

Download Practice Workbook

You can download the practice workbook from the following download button.

Excel VBA Private Sub vs Sub: 4 Differences

Two different types of procedures are used in VBA (Visual Basic for Applications) programming: Private Sub and Sub. Despite their apparent similarity, they each have unique qualities and goals. The following are the 4 key distinctions between Private Sub and Sub in VBA:

Difference 1: Declaration of Sub Procedures (Sub)

This step provides insights and guidelines on how to correctly declare Sub procedures, highlighting best practices, and examples to improve code and efficiency.

📌 Steps:

• In VBA, a program starts with Sub. By default, a Sub is Public Sub.
• You can try inserting the following code in your VBA. To do so, first, you need to launch VBA. Click here to see how to launch VBA and insert a Module in Microsoft Excel.

Sub Differences()
MsgBox "This is a public procedure."
End Sub
Public Sub Differences()
MsgBox "This is a public procedure."
End Sub
Private Sub Differences()
MsgBox "This is a private procedure."
End Sub

Difference 2: Accessibility of Private Sub vs Sub

The blog post’s this section will discuss how Private Sub procedures can only be accessed within the module in which they were defined. They cannot be called or accessed directly from other modules or workbook objects.

📌 Steps:

• You can clearly see the difference between the two sub-procedures when you put them in one module and try to call them from another module.
• Here in Module 2, you need to enter any of the following code and then try to call it from Module 3.

‘ Module 2

Private Sub PrivateProcedure()
MsgBox "This is a private procedure in Module 2."
End Sub
Sub PublicProcedure()
MsgBox "This is a public procedure in Module 2."
End Sub

• When you enter the Private Sub PrivateProcedure() code, you get the Compile error: in Module3 message like in the following image.

Sub CallProcedures()
PrivateProcedure ' Compilation error: PrivateProcedure is not accessible from Module 2.
End Sub

• Again, when you insert the code with Sub PublicProcedure() and try to call it from Module3 by entering that code, you get the message box like in the following image.

‘ Module 3

Sub CallProcedures()
PublicProcedure ' This will execute successfully and display the message box.
End Sub

Difference 3: Visibility in Macro Window

This section of the blog post covers Private Sub and Sub procedure visibility in the Macro window. In the Macro window, private sub procedures are typically invisible. They cannot be directly executed from the Macro window because they are hidden from the list of available macros.

📌 Steps:

• Like in the following image, Module 2 has two types of Subs: Private Sub and Public Sub. When Macros under the Developer tab is clicked, a Macro window appears. However, in that window, Private Sub is missing. This is one of the biggest differences between Private Sub and Public Sub.

Difference 4: The Difference in Object-Based Module

Object-based modules’ private sub procedures are frequently used as event handlers. They are particular to the associated object and react to activities taking place within it, like changing the worksheet’s selection or clicking a button on a user form. Other modules cannot directly access or call these Private Subs.

📌 Steps:

• Now to demonstrate another difference, you need to open a workbook event called open. In the following section, we have provided two kinds of events.

Workbook Events:

By Default:

Private Sub Workbook_Open()
MsgBox "Workbook Opened"
End Sub
Public Sub Workbook_Open()
MsgBox "Workbook Opened"
End Sub

• When you insert the code with Private Sub Workbook_Open() and try to call it from Module1 by entering the following code, you get the Compile error: message like in the following image.

Module:

Sub RunWorkbook_Open()
Call ThisWorkbook.Workbook_Open
End Sub

• Alternately, when you insert the code with Public Sub Workbook_Open() and try to call it from Module1 by entering that code, you get the message box like in the following image.

Frequently Asked Questions (FAQs)

• How does the visibility of a Sub affect its accessibility from other modules?

A Sub’s accessibility from other modules is impacted by its visibility. When a Sub is marked as “Private,” it can only be accessed from the module in which it is defined and cannot be called or used by other modules. However, if a Sub is declared without any visibility modifiers (the default), it is reachable from other modules and can be called and executed from outside the scope of the current module.

• When should I use a Private Sub?

When you try to encapsulate a code within a module, then use a Private Sub.

• When should I use a Public Sub?

When you try to make a code that can be accessed from all modules, then use a Public Sub.

Conclusion

The blog post explores the distinctions between Private Sub and Sub procedures (Private Sub vs Sub) in VBA in its conclusion. It covers topics like event handling, visibility, accessibility, encapsulation, parameters, and return values. Excel users can effectively use Private Subs for internal operations within a module and Subs for procedures that need accessibility from other modules or objects by understanding these distinctions.