How to Run a Private Sub in VBA (3 Methods)

In this article, I am sharing with you how to run a private sub in VBA. In VBA, a private sub is a sub procedure that can only be accessed from within the same module in which it is declared. To run a private sub, you simply need to call it from within the module in which it is defined. In the following, you will find an overview of how to run a private sub in VBA.

What Does Private Sub mean in Visual Basic?

A private sub refers to a subroutine or method that is declared with the “Private” access modifier. This means that the sub can only be accessed and called within the same class or module where it is defined. Private subs are typically used for encapsulation purposes where certain procedures are intended. By making a sub private, you can ensure that it is not accidentally called or modified from outside the intended scope. By default, subs in Visual Basic are declared as private. So if you don’t specify an access modifier, the sub will be private.

How to Run a Private Sub in VBA: 3 Easy Methods

In the following, we will explain 3 simple methods to run a private sub in VBA.

Suppose we have a dataset of Student Name, their Department, and Obtained Marks. Now we will run a private sub using this table.

1. Use Application.Run Command

The main advantage of running a private sub using Application.Run command is that it allows you to execute a private sub from outside its module. This can be useful when you want to reuse a private sub in multiple parts of your code.

Steps:

• First, open the VBA window and insert a module if you don’t know it.
• Second, open a module to place the following private subroutine code and save it.

Private Sub Find_Highest_Mark()
top_Mark = WorksheetFunction.Max(Range("D5:D14"))
MsgBox "The highest mark is: " & top_Mark
End Sub

• Then opening another module we will place the below code where you will see we have called the previously saved private subroutine through Application.Run command.

Private Sub Running_Module1()
Application.Run "Module1.Find_Highest_Mark"
End Sub

• Finally, we will get our final result through a MsgBox with confirmation of running a private sub. Simple isn’t it?

Read More: How to Call a Sub in VBA in Excel

2. Use Option Private Module Feature

Option Private Module in VBA is used to prevent a subroutine from being accessed by other modules. Using the Option Private Module command, you can ensure that the subs can only be called from within the same module that contains a private sub. Thereby preventing unauthorized access from other parts of the program.

Steps:

• Similarly, open a module and write the below code and save it.

Option Private Module
Public Sub Find_Lowest_Mark()
Lowest_Mark = WorksheetFunction.Min(Range("D5:D14"))
MsgBox "The lowest mark is: " & Lowest_Mark
End Sub

• After that, we will open another module and call the previously saved private sub by using the code.

Private Sub Running_Module3()
Call Module3.Find_Lowest_Mark
End Sub

• As a result, we will get a MsgBox confirming the private sub is running properly.

• Here, we declared the contents as private. Thus no macros will be visible in the Macro Let’s check.
• Simply, click the Macros option from the Developer tab.

• Inside the Macros window, you will not find any saved macros as we announced the module as private.

Read More: Excel VBA to Call Private Sub from Another Module

3. Using Dummy Variable

The purpose of using a dummy variable is to satisfy the requirement of the calling code to provide a parameter. A dummy variable is used for calling a code that can provide a value without affecting the behavior of the subroutine. As we are using a private sub thus the module will not be visible in the macros window.

Steps:

• In the same fashion, open a module, put the below code, and save it.

Public Sub Coloring_Highest_Mark(Optional byDummy As Byte)
top_Mark = WorksheetFunction.Max(Range("D5:D14"))
Range("D5:D14").Find(top_Mark).Interior.Color = vbYellow
End Sub

• Now, insert another module and place the below code where we will call the previously saved private sub.

Private Sub Running_Module5()
Call Module5.Coloring_Highest_Mark
End Sub

• In conclusion, we have successfully colored the cell indicating running a private sub from another module.

• In order to check whether the contents of the module is private or not you can visit the Macros There you will find no saved module as we declared the contents as private.

Things to Remember

• If you want to use events like Worksheet_Change and Worksheet_Open then you can use the Run command. Option Private Module and Dummy Variable methods are traditional ways of running a private sub.
• While calling the private sub make sure that the saved code is within the same module. Otherwise, it won’t work.
• At the time of running a private sub make sure that any parameters passed to the private sub have the correct data types.

Frequently Asked Questions

• Can a private sub be called from another module or class in VBA?

No, a private sub can only be called from within the same module or class where it is defined.

• Why should I use the Option Private Module in VBA?

Option Private Module is used in VBA to prevent objects, variables, and subs declared in a module from being accessed by other modules.

• What is a dummy variable in VBA?

A dummy variable is a parameter in VBA that is used as a placeholder or dummy value in a sub or function.

Download Practice Workbook

You can download our practice workbook from here for free!

Conclusion

In summary, running a private sub in VBA involves calling the subroutine from or within the same module where it is defined. This helps to ensure that the code is secure and does not interfere with other modules. Take a tour of the practice workbook and download the file to practice by yourself. Please inform us in the comment section about your experience.

Related Articles

• Excel VBA Call Sub from Another Sheet
• How to Call Private Sub in Excel VBA
• Excel VBA Private Sub vs Sub (Comparison & Differences)
• Excel VBA to Call Sub with Parameters
• How to Use Excel VBA Sub to Return Value
• Excel VBA Call Sub from Another Module
• How to Use Excel VBA to Call Private Sub from Userform
• VBA to Call Sub From Another Workbook in Excel