Private Sub vs Sub in Excel VBA: All You Need to Know

When writing procedures in Excel VBA, you’ll often come across both Sub and Private Sub. While they may look similar, they serve different purposes, mainly when it comes to scope and accessibility. Choosing between them affects how your macros are triggered, whether they’re visible to other modules, and how they behave in the VBA environment.

In this article, you’ll learn the key differences between Sub and Private Sub in VBA. We’ll explore practical examples to compare them side by side, explain when to use each one, and help you avoid common issues related to procedure visibility.

Sub HighlightPassedStudents()
    Dim ws As Worksheet
    Dim i As Long
    Set ws = Sheets("Sheet1")
    For i = 2 To 6
        If ws.Cells(i, 3).Value = "Pass" Then
            ws.Cells(i, 3).Interior.Color = RGB(198, 239, 206)
        End If
    Next i
End Sub

What Are Sub and Private Sub in Excel VBA?

In Excel VBA, a Sub (short for Subroutine) is a block of code created to perform a specific task without returning a value. A regular Sub is public by default, meaning it can be called from other modules, executed from the Macros dialog box, or triggered using a button or shortcut.

A Private Sub, on the other hand, is limited in scope. It can only be accessed within the same module where it is defined. This is mainly useful for procedures that support internal operations or respond to events like Worksheet_Change or Workbook_Open.

The main difference is in accessibility. Public Subs are visible across the entire project and appear in the macro list, while Private Subs are hidden from the list and cannot be triggered from outside their module.

Create a Global Macro Using a Standard Sub

A regular Sub procedure in VBA is public by default, meaning it can be accessed from anywhere in your project. It also appears in the Macros dialog (Alt + F8), which allows you to run it manually without writing additional code. This makes it useful for general-purpose tasks like formatting, data cleanup, or automation that users may want to trigger on demand.

In this example, we’ll demonstrate how a standard Sub can process a dataset and apply formatting to highlight students who passed. The procedure loops through a specific range, checks the value in the Result column, and highlights the passing entries with a green background. This macro can be called from other modules or run manually, making it flexible and easy to integrate into your workbook.

Now this is the dataset we will use:

Steps:

➤ Press  Alt  +  F11  to open the VBA Editor.
➤ Go to Insert >> Module and paste the following code:

Sub HighlightPassedStudents()
    Dim ws As Worksheet
    Dim i As Long
    Set ws = Sheets("Sheet1")
    For i = 2 To 6
        If ws.Cells(i, 3).Value = "Pass" Then
            ws.Cells(i, 3).Interior.Color = RGB(198, 239, 206)
        End If
    Next i
End Sub

➤ Press  F5  key to run the macro and return to Excel to verify the formatting applied to passing students.

Encapsulate Logic Using a Private Sub

A Private Sub in VBA limits access to the procedure from outside its own module. Unlike regular Subs, a Private Sub does not appear in the Macros dialog and cannot be called from other modules. This makes it ideal for helper routines or internal logic that should not be exposed to the rest of the workbook.

In this example, we’ll create a Private Sub that highlights students who failed. Because it’s private, it must be triggered from a separate Public Sub. This structure demonstrates how you can protect internal procedures while still making them accessible in a controlled way.

We’ll use the same dataset and logic as the previous example, but with different criteria and color formatting.

Steps:

➤ Press  Alt  +  F11  to open the VBA Editor.
➤ Go to Insert >> Module and paste the following code:

' Private procedure that highlights failed students
Private Sub HighlightFailedStudents()
    Dim ws As Worksheet
    Dim i As Long
    Set ws = Sheets("Sheet2")
    For i = 2 To 11
        If ws.Cells(i, 3).Value = "Fail" Then
            ws.Cells(i, 3).Interior.Color = RGB(255, 199, 206) ' Light red
        End If
    Next i
End Sub
' Public procedure that calls the private one
Sub RunPrivateHighlight()
    HighlightFailedStudents
End Sub

➤ Press  F5  key to run the macro and return to Excel.

You will see that the rows with “Fail” in the Result column are now filled with light red, just like in the previous example for “Pass” rows. This confirms that a Private Sub can successfully process data, but only when called from within its own module using a Public Sub.

Use a Public Sub to Call Private Helper Procedures

When writing VBA macros, it’s good practice to separate your logic into smaller, focused procedures. One effective approach is to use a Public Sub as the entry point and keep the helper routines as Private Sub procedures. This improves code readability, enforces modular design, and keeps internal logic hidden from the user interface.

In this example, we’ll create a macro called HighlightResults that highlights both passed and failed students in different colors. The main Sub is public, so it appears in the Macros dialog and can be triggered easily. Inside it, we call two Private Subs that handle the individual tasks of highlighting “Pass” and “Fail” values separately.

This approach ensures only the main procedure is exposed while the helper logic remains encapsulated.

Steps:

➤ Press  Alt  +  F11  to open the VBA editor.
➤ Go to Insert >> Module and paste the following code:

Public Sub HighlightResults()
    HighlightPassFail
End Sub
Private Sub HighlightPassFail()
    Dim ws As Worksheet, i As Long
    Dim cellValue As String
    Set ws = Sheets("Sheet3")
    For i = 2 To 11
        cellValue = Trim(UCase(ws.Cells(i, 3).Value))  ' Trim and uppercase once
        Select Case cellValue
            Case "PASS"
                ws.Cells(i, 3).Interior.Color = RGB(198, 239, 206)  ' Green fill
            Case "FAIL"
                ws.Cells(i, 3).Interior.Color = RGB(255, 199, 206)  ' Red fill
            Case Else
                ws.Cells(i, 3).Interior.ColorIndex = 0  ' Clear any fill
        End Select
    Next i
End Sub

➤ Press  F5  key to run the macro and return to Excel.

The macro will apply green fill to rows with “Pass” and red fill to rows with “Fail” in the Result column, showing how multiple private routines can work behind a single public interface.

Attempt to Call a Private Sub from Another Module

A Private Sub in VBA is restricted to the module in which it’s declared. If you try to call it from another module, VBA will trigger a compile-time error. This restriction is intentional, it protects internal procedures from being accessed or misused outside their defined scope.

In this example, we’ll declare a Private Sub called PrivateGreeting in Module1 and attempt to call it from a different module (Module2). This test clearly shows the visibility limitations of Private Sub procedures.

This setup is useful when you want to encapsulate helper code that shouldn’t be directly accessible from the rest of your project.

Steps:

➤ For Module 1,  press  Alt  +  F11  , go to Insert >> Module, and paste this:

' This is a private sub only accessible within this module
Private Sub PrivateGreeting()
    MsgBox "Hello from a private sub"
End Sub

➤ For Module 2, go to Insert >> Module again to add another module, then paste:

' Attempt to call the private sub declared in Module1
Sub TestPrivateCall()
    PrivateGreeting ' ← Will trigger compile error
End Sub

Result:

➤ Try running TestPrivateCall from Module2.
➤ You’ll receive the error:

“Sub or Function not defined”

This behavior reinforces how Private access limits procedure scope and protects encapsulated logic. Screenshot the error dialog to illustrate this restriction for documentation or tutorials.

Trigger an Event-Driven Private Sub Using a Public Wrapper

Event procedures in VBA, such as Worksheet_Change, must be declared as Private Sub and reside in the appropriate object module (e.g., a worksheet or workbook). These subs are not designed to be run directly, they are triggered automatically by user actions or code that changes cell values.

In this example, we’ll create a Worksheet_Change event in the code module for a sheet and use a public macro in a standard module to trigger it. This combination demonstrates how private event procedures work in response to changes and how public subs can serve as indirect entry points.

This is useful for automating actions like alerts, validations, or dynamic formatting in response to data updates.

Steps:

➤ In the Sheet Module, press  Alt  +  F11  , double-click Sheet4 under “Microsoft Excel Objects“.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("C2:C11")) Is Nothing Then
        MsgBox "Result updated to: " & Target.Value
    End If
End Sub

➤ In a Standard Module, go to Insert >> Module and paste this macro:

Sub TriggerUpdate()
    Sheets("Sheet4").Range("C2").Value = "Fail"
End Sub

➤ Run TriggerUpdate by pressing  F5  key and the Worksheet_Change event will run automatically.
➤ You’ll see a message box displaying the updated value in cell C2 from Pass to Fail.

This example highlights the natural role of Private Sub in event handling, and how public procedures can work together to initiate those actions programmatically.

Frequently Asked Questions

Can I change a Private Sub to a Public Sub in VBA?

Yes. You can make a Private Sub public by removing the word “Private” from its declaration. This will make it accessible from other modules and visible in the Macros dialog for manual execution.

Why doesn’t my Private Sub appear in the Macro dialog?

Private Subs are hidden from the Macro dialog by design. This prevents users from running them directly. They can only be executed within their own module or through a Public Sub that specifically calls them.

Can a Private Sub call another Private Sub in a different module?

No. Private Subs are scoped only to the module where they’re declared. To reuse code across modules, declare the procedure as Public or create a Public “wrapper” Sub to call the private one indirectly.

Are event procedures always Private in VBA?

Yes. Event procedures like Worksheet_Change or Workbook_Open are automatically Private because they’re triggered by specific actions in Excel rather than run manually. Declaring them Public would serve no purpose and could cause unexpected behavior.

Should I use Sub or Private Sub more often?

It depends on your project. Use standard Subs for macros or procedures you want accessible across your workbook. Use Private Subs for helper code, internal operations, or events that shouldn’t be exposed to users.

Wrapping Up

In this tutorial, we learned the key differences between Sub and Private Sub in Excel VBA, how they control accessibility, and when each should be used. We explored practical coding examples showing how Public Subs can act as entry points, while Private Subs work behind the scenes for helper tasks or event handling. By understanding and applying the right scope for your procedures, you can keep your VBA projects organized, maintain cleaner code, and prevent unintended access to sensitive logic.