Introduction
A common practice of many VBA developers is to restrict access in the code that they don’t want the users to “see” in the Macros menu of Excel (i.e. they create hidden macros). A typical example of this is when you have a long procedure that is consisted of many intermediate steps and each step is a separate macro (e.g. an extensive calculation broken into 5 macros).
In that case, you may want to hide the macros of intermediate steps from the user’s eyes since either are useless on their own (e.g. within a sequence of macros, an intermediate macro is only useful when it is called in the right order), or they might even break things if they are accidentally called (e.g. delete a sheet that has important data). The procedure that was just described is technically a variation of encapsulation, as it is commonly known in object-oriented programming.
So, assuming that you want to make your macros “invisible”, how are going to do that? Well, there are three ways:
- Declare the macro/sub using the Private keyword.
- Make the entire module Private (Option Private Module).
- Add a dummy, optional parameter in the macro/sub declaration.
You just created your super-secret macros following one of the suggested ways, but, how can you call them from other macros? More importantly, what will happen if the hidden macros are in one module and the caller macros are in a different one? In that case, you can follow one of the solutions below:
- Use the Application.Run method.
- Call the macro directly.
- Ignore the dummy variable.
In the next sections, we will see a few examples of macros that were “hidden” using the above suggestions, as well as the corresponding ways to call these macros, despite being invisible from the Macros menu.
Examples of hidden macros/subs (invisible from the Macros menu)
Declare the macro/sub using the Private keyword
The next two subs (Module1 in the sample workbook) were written using the Private keyword.
Option Explicit
'------------------------------------------------------------------
'1. Private macros (explicitly defined with the private keyword).
'------------------------------------------------------------------
Private Sub My_Private_Macro()
MsgBox "My Private Macro!!!", vbInformation, "Module 1"
End Sub
Private Sub My_Private_Macro_With_Arguments(My_Number_1 As Double, My_Number_2 As Double)
MsgBox "My Private Macro With Arguments!" & vbNewLine & "Result: " & My_Number_1 * My_Number_2, vbInformation, "Module 1"
End Sub
Make the entire module Private (Option Private Module)
The following macros were explicitly declared as Public ones (note: even if you omit the Public keyword, VBA recognizes by default the macros as public). However, at the beginning of the module (Module2 in the sample workbook), there is the “Option Private Module” statement, which automatically makes the code in the entire module invisible.
Option Explicit
'Notice the next line:
Option Private Module
'-------------------------------------------------------------
'2. Macros in a private module (Option Private Module is on).
'-------------------------------------------------------------
Public Sub My_Macro_In_Private_Module()
MsgBox "My Macro In Private Module!", vbInformation, "Module 2"
End Sub
Public Sub My_Macro_In_Private_Module_With_Arguments(My_Number_1 As Double, My_Number_2 As Double)
MsgBox "My Macro In Private Module With Arguments!" & vbNewLine & "Result: " & My_Number_1 * My_Number_2, vbInformation, "Module 2"
End Sub
Add a dummy, optional parameter in the macro/sub declaration
Finally, the third option is to use a dummy, optional parameter in the macro declaration, as it is shown in the macro below (Module3 in the sample workbook). The parameter Dummy_Variable is never used. It is a good practice to declare the optional parameter using a small data type, e.g. Byte (= 1 byte) or Boolean (= 2 bytes).
Option Explicit
'-----------------------------------------------------------------------------------------------
'3. A public macro that has an optional dummy variable that makes it hidden in the Macros menu.
'-----------------------------------------------------------------------------------------------
Public Sub Macro_With_Dummy_Variable(Optional Dummy_Variable As Byte)
MsgBox "My Macro With Optional Dummy Variable!", vbInformation, "Module 3"
End Sub
Examples of calling the hidden macros/subs
Use the Application.Run method
Among the three solutions presented here, the Application.Run method is the only one that works in all cases of hidden macros. More importantly, it can also be used to call private workbook/worksheet events.
'----------------------------------------------------------------------------------------------
'1. Calling private macros that were explicitly defined with the private keyword.
' This is the only way to call private macros, as well as workbook/worksheet private events.
'----------------------------------------------------------------------------------------------
Sub Calling_Private_Macro()
Application.Run "Module1.My_Private_Macro"
End Sub
Sub Calling_Private_Macro_With_Arguments()
Application.Run "Module1.My_Private_Macro_With_Arguments", 10, 5
End Sub
Sub Calling_Private_Worksheet_Event()
'Note that Sheet1 is the codename of the sheet, not its visible name (Main).
Application.Run "Sheet1.Worksheet_SelectionChange", Range("B12")
End Sub
Call the macro directly
This is the “standard” way to call a macro. You can use the module name if there is any name ambiguity within the entire VBA code (e.g. two macros named Macro1, but contained in different modules).
'------------------------------------------------------------------------------
'2. Calling macros that are in a private module (Option Private Module is on).
'------------------------------------------------------------------------------
Sub Calling_Macro_With_Option_Private_Module_On()
Call Module2.My_Macro_In_Private_Module
'Note: the next line will FAIL since in mPrivate1 the macros were defined with the private keyword!
'Call Module1.My_Private_Macro
End Sub
Sub Calling_Macro_With_Option_Private_Module_On_And_Arguments()
Call Module2.My_Macro_In_Private_Module_With_Arguments(20, 30)
End Sub
Ignore the dummy variable
A variation of the “standard” way of calling a macro by ignoring the optional argument.
'--------------------------------------------------------
'3. Calling a macro that has an optional dummy variable.
'--------------------------------------------------------
Sub Calling_Macro_With_Dummy_Variable()
Call Macro_With_Dummy_Variable
'Of course, this also works!
'Call Module3.Macro_With_Dummy_Variable
End Sub
In the Downloads section that follows, you will find a sample workbook with all the ways of hiding and calling the hidden macros described above. Which is your preferred one?
Downloads
The file can be opened with Excel 2007 or newer. Please enable macros before using it.
Page last updated: 13/06/2019