Private and Public Procedures in VBA

We have practiced the Sub Procedures and Functions in VBA. Today, we will learn about the scope of procedures. The Private and Public Procedures in VBA is one of the important concepts of programming. We would also learn about a Private Module in VBA.

So let us start learning.

Public and Private Sub Procedures

Procedures can either be declared as Private or Public. A Public Sub Procedure can be called from all the modules of the workbook. The procedure would also appear in the list of macros until it requires arguments. A Public Sub Procedure that requires parameters would not appear in the Macro list as there is no way to pass the parameters to the Sub Procedure.

A Private Sub Procedure is never visible in the list of available macros. It can be called using the Call statement only within the module in which it is defined.

Press Alt and F8 keys to see the list of available macros

List of Macros

To open the list of available macros, you can press Alt and F8 keys or go to the View tab->Macro Button->View Macros.

So you see that this is a Public Sub Procedure named HelloVBA which is defined in Module1 in VBE as follows.

Public Sub HelloVBA()
MsgBox "Hello VBA"
End Sub

We use the Public keyword to make it public. When we declare a Sub Procedure using the Private keyword, it would not appear in the list of macros.

Private Sub HelloVBA()
MsgBox "Hello VBA"
End Sub

We can only Call this in the other procedures defined in the same module.

When we do not specify the scope, then by default a Sub Procedure or a Function is Public.

Arguments and Procedures

When we specify a list of arguments in a Sub Procedure, it is never appearing in the list of macros even if it is a public sub procedure.

This Sub Procedure is not appearing in the list of available macros. This is so because there is no method to pass these parameters using the Macro list.

However, it can be called from any module of the VBA project.

When we run calling_print_msg, we see this result.

However, if print_msg is declared as a Private Sub Procedure, then excel throws an error when we run calling_print_msg (module2) as the private procedure is not accessed from another module (module2).

Private and Public Functions

A Function never appears in the list of available macros.

The function can either be private or public.

A Function when declared as Public, is used as User Defined Function and can be accessed from the Insert Function dialog box. For instance:-

Here we have a function that can extract numbers from a text string.

Public Function getnumber(celRef As String) As Long
Dim strLen As Integer

strLen = Len(celRef)

For i = 1 To strLen
    If IsNumeric(Mid(celRef, i, 1)) Then
        result = result & Mid(celRef, i, 1)
    End If
Next i

getnumber = result
End Function

Now we will use this function in our workbook. There are two methods.

• Get the text strings in the cells which contain numerical values in between.

• Type =GetNum and you will see the function in drop-down list in cell G2.

• Use this formula and copy it down the range G2:G4.

=getnumber(A2)

Another way to access this public function is:-

• Select the cell where you want to use the function.
• Go to the Formulas tab on the ribbon. Click on the Insert Function button.
• Choose the User Defined Function in the Category.

A Private function would neither appear in the drop down list while you are halfway typing the name or in the Insert Function dialog box.

You can still use a private function in the workbook if you remember its full name.

Private Modules in VBA

By default, a module is Public. A module can be made private by writing this statement at the top of module code.

Option Private Module

When a module is declared as Private, then any procedure it defines would not be visible to the user. However, the procedure would be accessible in the VBA project.

Any Function defined in private module would not appear in Insert Function dialog box nor in the drop down list while typing function name in worksheet cell. We would only use it if we know the complete name.

Example – Private Module Sub Procedure

We would insert a module. The first module is module1 and second module is module2. Make the module2 private and then define a sub procedure named HeyVBA.

This Sub Procedure would not appear in the list of macros to the user, but we can call it from any other module (module1) of the VBA project.

Calling a Private Sub Procedure from Different Module

We cannot call a private Sub Procedure from the different module using the Call statement or what we define as a normal method to call a procedure.

We would use the Application.Run command to do this. The syntax to call a private procedure from a different module is:-

Application.Run("Procedure_Name")

Let us say we have a private sub procedure in Module2 ( A public module by default )

We would use the following code to call the private sub procedure named IamHungry defined in Module2 from Module1.

When you run the calling() Sub Procedure, this is the result.

This brings us to an end.

Thank you for reading.