Here’s an overview of 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
We have a dataset of Student Name, their Department, and Obtained Marks. We will run a private sub using this table.
Method 1 – Use the Application.Run Command
Steps:
- Open the VBA window and insert a module.
- Insert 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
- In another module, place the following code where we have called the previously saved private subroutine through the Application.Run command.
Private Sub Running_Module1()
Application.Run "Module1.Find_Highest_Mark"
End Sub
- Run this module.
- We will get our final result through a MsgBox.
Read More: How to Call a Sub in VBA in Excel
Method 2 – Use the Option Private Module Feature
Steps:
- Open a module and insert the below code, then 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
- 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
- We will get a MsgBox confirming the private sub is running properly.
- We declared the contents as private. Thus, no macros will be visible in the Macro list.
- 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.
Method 3 – Using a Dummy Variable
Steps:
- 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
- Insert another module and place the following code where we will call the previously saved private sub.
Private Sub Running_Module5()
Call Module5.Coloring_Highest_Mark
End Sub
- We have successfully colored the cell indicating running a private sub from another module.
- To check whether the contents of the module is private, you can go to the Macros list.
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.
- When 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 the Practice Workbook
Related Articles
- Excel VBA Call Sub from Another Sheet
- How to Call Private Sub in Excel VBA
- Excel VBA to Call Sub with Parameters
- 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
