3 Ways To Hide & Call Hidden Macros In VBA

Share this

April 15, 2019

3 Ways To Hide Call Hidden Macros In VBA


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:

  1. Declare the macro/sub using the Private keyword.
  2. Make the entire module Private (Option Private Module).
  3. 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:

  1. Use the Application.Run method.
  2. Call the macro directly.
  3. 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)


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


Modules In Sample Workbook

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


Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it.

Page last updated: 13/06/2019

Page last modified: August 23, 2020

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

Christos E. Samaras

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Add Content Block
>