Adnan Masruf
In this article, we will discuss VBA codes to call Private Sub from another module. A Private Sub is a subroutine that can be accessible from the module where it is written. A Private Sub does not appear in the macro list of the Macros tab. If we want to use the sub, we will have to go inside the module where we wrote the sub and then execute it. However, we can access the Private Sub from another module as well. This will allow us to hide the Private Sub from the macros list and at the same time access it from a publicly available subroutine.
Download Practice Workbook
You can download the practice book here.
What Is Private Sub in Excel VBA?
In Excel VBA, a private sub is a subroutine that is only accessible within the module where it is declared. Subroutines are sections of code that perform a specific task and can be called from other parts of the program. A private sub is a subroutine that is declared using the “Private” keyword, which means that it is not visible or accessible outside of the module where it is declared. Private subs in Excel VBA are often used to perform actions that are specific to a particular worksheet or workbook, such as formatting data or updating calculations. By declaring a sub as private, you can help ensure that the code remains secure and functions as intended.
Here’s an example of a private sub in Excel VBA:
Private Sub PrivateSub()
MsgBox “Hello World”
End SubOne has to write the word “Private” in front of the name of the subroutine to make it a Private Sub.
VBA to Call Private Sub from Another Module in Excel: 3 Easy Ways
Today, we are going to talk about VBA codes to call Private Sub from another module. We will use 3 different methods to do the task. Here, we have a dataset of the attendance of employees in an office. We will run 3 VBA codes, which are Private Subs, by accessing them in 3 different ways to manipulate this dataset.
1. Using Application.Run Method
In the first method, we will use the Application.Run method to call a VBA Private Sub from another module. We will use the Private Sub below to calculate the average attendance for each employee in our dataset.
Private Sub PrivateSub1()
Dim Rng As Range
Set Rng = Application.Selection
For i = 1 To Rng.Rows.Count
Sum = 0
For j = 1 To Rng.Columns.Count
Sum = Sum + Rng.Cells(i, j)
Next j
Rng.Cells(i, Rng.Columns.Count + 1) = Round(Sum / Rng.Columns.Count, 0)
Next i
End Sub
VBA Code Breakdown
Private Sub PrivateSub1()- The word “Private” in front of the subroutine name indicates that it is a Private Sub. In other words, its scope is limited to that module only.
Dim Rng As Range
Set Rng = Application.Selection- The first line defines a Range variable “Rng”
- We will assign the value for that variable by selecting a range from the worksheet as indicated by the second line.
For i = 1 To Rng.Rows.Count
Sum = 0
For j = 1 To Rng.Columns.Count
Sum = Sum + Rng.Cells(i, j)
Next j
Rng.Cells(i, Rng.Columns.Count + 1) = Round(Sum / Rng.Columns.Count, 0)
Next i
End Sub- The first line For i = 1 To Rng.Rows.Count initiates a loop that runs for each row in the Rng range, where i is the row index variable.
- We initialized the Sum variable to zero before the inner loop starts.
- The second loop For j = 1 To Rng.Columns.Count runs for each column in the current row, where j is the column index variable.
- Inside the inner loop, the code adds the value of the current cell to the Sum variable: Sum = Sum + Rng.Cells(i, j)
- After the inner loop finishes, the average value of the current row is calculated by dividing the Sum by the number of columns in that row: Sum / Rng.Columns.Count.
- Then, we rounded result is to the nearest integer. We used the Round function with the second argument set to 0 because we want no decimal point in the average values.
- Finally, we stored the calculated average value in the last column of the current row using the Cells(i, Rng.Columns.Count + 1) = assignment statement.
Now, we will use the following code which contains the Application.Run method to call the Private Sub above.
Sub Call_with_ApplicationRun()
Application.Run "Module1.PrivateSub1"
End Sub
The Application.Run method calls the “PrivateSub1” subroutine from Module1.
As we execute the “Call_with_ApplicationRun” code by launching VBA Macro editor the code calculates the average attendance for each employee.
2. Applying Option Private Module Statement
When we include the Option Private Module statement at the top of a module, it restricts the visibility of the procedures, functions, and variables within that module to only within that module. In other words, it makes the module and its members private and inaccessible from outside the module. We will however access it from another module.
We will mark the employees from our dataset with red whose average attendance is less than 18 days. Then, we will use the following code to do so,
Option Private Module
Sub PrivateSub2()
Dim Rng As Range
Set Rng = ThisWorkbook.Worksheets("OptionPrivateModule").Range("F5:F12")
For i = 1 To Rng.Rows.Count
If Rng.Cells(i, 1) < 18 Then
Range(Rng.Cells(i, 1).Offset(0, -4), Rng.Cells(i, 1)).Interior.Color = vbRed
End If
Next i
End Sub
VBA Code Breakdown
Option Private Module- This line makes the subroutine a Private Sub.
Dim Rng As Range
Set Rng = ThisWorkbook.Worksheets("OptionPrivateModule").Range("F5:F12")- These lines declare a Range variable “Rng” and sets the F5:F12 range of the “OptionPrivateModule” worksheet as its value.
For i = 1 To Rng.Rows.Count
If Rng.Cells(i, 1) < 18 Then
Range(Rng.Cells(i, 1).Offset(0, -4), Rng.Cells(i, 1)).Interior.Color = vbRed
End If
Next i
End Sub- The first line For i = 1 To Rng.Rows.Count initiates a loop that runs for each row in the Rng range. Here, i is the row index variable.
- The If statement checks if the value of the current row (i.e., Rng.Cells(i, 1)) of the F5:F12 range is less than 18.
- If the condition is true, the code selects a range that starts from four columns to the left of the current cell and ends at the current cell: Range(Rng.Cells(i, 1).Offset(0, -4), Rng.Cells(i, 1)). Then, it sets the interior color of the selected range to red using the Color property: .Interior.Color = vbRed
After that, we will run the following code to call the Private Sub from Module 2. We will use the Module2.PrivateSub2 command inside the code.
As we run the code, Excel will mark the information of the employees whose average attendance is less than 18 with red.
3. Using Optional Variables in Sub Procedures
In this section, we will pass an optional variable inside our subroutine to make it behave like a Private Sub. We will create a Public Sub and then pass the optional variable as Byte to convert it to Private Sub. Here, we have a dataset that contains the average attendance of some employees at an office. We will find out the employees whose average attendance was greater than or equal to 18 and display them in a MsgBox.
We will the following code to display the employees’ names
Sub PrivateSub3(Optional byDummy As Byte)
Dim Rng As Range
Set Rng = ThisWorkbook.Worksheets("OptionalVriables").Range("F5:F12")
BestEmployee = ""
For i = 1 To Rng.Rows.Count
If Rng.Cells(i, 1) >= 18 Then
BestEmployee = BestEmployee & Rng.Cells(i, 1).Offset(0, -4) & " ,"
End If
Next i
MsgBox "The employees who were the most regular in the office are: " _
& Left(BestEmployee, Len(BestEmployee) - 1)
End Sub
VBA Code Breakdown
Sub PrivateSub3(Optional byDummy As Byte)- This line passes an optional variable “byDummy” to the subroutine. We set the variable as Byte Because the Byte type variable takes the least amount of data in the memory.
BestEmployee = ""
For i = 1 To Rng.Rows.Count
If Rng.Cells(i, 1) >= 18 Then
BestEmployee = BestEmployee & Rng.Cells(i, 1).Offset(0, -4) & " ,"
End If
Next i- This VBA code block is iterating through a range of cells (stored in the variable Rng). It also checks the value of the cell in the first column of each row. If the value is greater than or equal to 18, the code is appending the value of a cell four columns to the left (i.e., the cell in the same row but four columns to the left of the current cell) to a string variable called BestEmployee. Then, we used the “&” symbol to concatenate the string with the value of the cell, and added the “,” string to separate each employee’s name.
- At the end of the loop, BestEmployee will contain a comma-separated list of the names of employees whose age is greater than or equal to 18, as determined by the values in the first column of the range.
MsgBox "The employees who were the most regular in the office are: " _
& Left(BestEmployee, Len(BestEmployee) - 1)- This VBA code block displays a message box using the MsgBox function to show the names of employees who were the most regular in the office. The message displayed in the message box includes a string literal followed by the value of the variable BestEmployee. This variable is a comma-separated list of employee names generated in the previous code block. We used the “&” symbol to concatenate the string literal with the value of the BestEmployee. Then, we used the Left function to extract a substring from the BestEmployee variable that excludes the final comma separator (i.e., the last character in the string). Here, we used the Len function to determine the length of the BestEmployee string, and the -1 argument to exclude the final character from the extracted substring.
Finally, we run the following code to call the VBA code above. It will display the list of the most regular employees.
As we run the code, a MsgBox will display the names of the employees whose average attendance was greater than or equal to 18.
Conclusion
In this article, we have discussed VBA codes to call Private Sub from another module. This article will provide users with a decent knowledge about Private Subs in Excel VBA. It also will allow users to access Private Subs and use them in performing different operations. Also, if you want to see more Excel content like this, please visit our website, and unlock a great resource for Excel-related content.
