3 Ways to Call a Private Sub from Another Module

Introduction | Application.Run | Option Private Module | Dummy Variable | Final Thoughts

Introduction

This is a question that gets asked a lot. You have this fancy macro you want to use over and over, but you don’t want the macro to show up in the Macros Dialog Box (Alt+F8). How do you call the macro from a different module while keeping the macro hidden?

I have three solutions for you, presented in order of my most preferred to least preferred method. Here’s a little secret: Solution 1 lets you call Excel Application Events, like Worksheet_Change and Workbook_Open from other modules!

Solution 1

Application.Run

Let’s set the stage. Open your Excel VBA Editor and create two modules: Module1 and Module2.

We’ll illustrate Application.Run with a simple example. In Module1, paste the following code:

Private Sub HelloWorld()
'Module1
MsgBox "Hello World", , "wellsr.com"
End Sub

If the procedure were public, you could call it from Module2 by typing Call Module1.HelloWorld. Unfortunately, with a Private Sub, all you’ll get is this hideous compile error:

Ready for the solution? In Module2, paste the following macro:

Private Sub PrivateCallDemo()
'Module2
Application.Run "Module1.HelloWorld"
End Sub

Make powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

Execute this and you’ll successfully display the much prettier Hello World message box.

If you don’t believe me that the macro remains private, here’s a screenshot of the Macros Dialog Box (Alt+F8):

We’re straying off topic a bit here, but I’m sure you’re curious! You can pass arguments to a module using Application.Run by separating each argument with a comma. For example, suppose we need to pass the variable TotalSales to a Private Sub titled CalculateProfit stored on Module1. This is how you would do it with Application.Run:

Private Sub PrivateCallDemo2()
Application.Run "Module1.CalculateProfit", TotalSales
End Sub

This solution is also a clever way to trigger those private Application Events, like Worksheet_Change and Workbook_Open. The following VBA macro triggers the Worksheet_Change event for Sheet1 by passing it a generic range.

Private Sub PrivateCallDemo3()
    Application.Run "Sheet1.Worksheet_Change", Range("A1")
End Sub

Application.Run is the only solution that explicitly uses a “Private Sub.” All other solutions just make your Public Subs invisible to the Macros Dialog Box.

Solution 2

Option Private Module

Use the Option Private Module solution if you want to maintain the traditional VBA Call statement. We’ll illustrate this approach with the same two modules: Module1 and Module2. In Module1, paste the following code:

Option Private Module
Public Sub HelloWorld2()
'Module 1
MsgBox "Hello World", , "wellsr.com "
End Sub

Option Private Module must appear at the top of your module - before any procedures. Notice how the actual procedure uses the Public keyword. Normally this would cause your VBA macro to appear in the Macros Dialog Box. However, since we declared the contents of the module as private, no Module1 macros will show up. Here’s evidence:

Now, you can call your macro from another module (Module2 in our example) just like you would call a Public Sub.

Private Sub PrivateCallDemo4()
'Module2
Call Module1.HelloWorld2
End Sub

Note: The Call keyword is optional when calling a module

Solution 3

Dummy Variable

Many people keep their macros hidden by passing their public procedures optional dummy variables. Here’s how it works using our original two modules. Paste the following code into Module1:

Public Sub HelloWorld3(Optional byDummy As Byte)
'Module 1
MsgBox "Hello World", , "wellsr.com "
End Sub

Although you can ask for a dummy variable of any data type, I recommend passing it a Byte, since it’s the smallest data type. When a procedure requires a variable, it will not show up in the Macros Dialog Box - even if the variable is optional.

Since the procedure is Public, you would call it just like you would any other procedure from an external module.

Private Sub PrivateCallDemo5()
'Module2
Call Module1.HelloWorld3
End Sub

Again, note: The Call keyword is optional when calling a module

We’ve reached the end of the line. You now know how to keep your macros hidden while still allowing you to call them from other modules.

I recommend using the Application.Run solution since it’s the most versatile. Remember, it’s the only solution that will let you call the Excel Application Events, like Worksheet_Change and Workbook_Open. On the other hand, if you’re accustomed to the traditional way of calling procedures, the Option Private Module or the Dummy Variable approach may suit you better.

For more VBA tips, techniques, and tactics, subscribe to our VBA Insiders email series using the form below.

After you subscribe, share what you’re automating on Twitter and Facebook.