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.

Two Modules

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:

Fails to Call Private Sub

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

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

wellsrPRO

Coming Fall 2017

Auto-Import Macros Directly from wellsr.com

Absolutely FREE when you join the waitlist


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

VBA Hello World

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

VBA Macros Dialog Box

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 PrivateCallDemo()
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 PrivateCallDemo()
    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 HelloWorld()
'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:

VBA Macros Dialog Box

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

Private Sub PrivateCallDemo()
'Module2
Call Module1.HelloWorld
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 HelloWorld(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.

VBA Macros Dialog Box

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

Private Sub PrivateCallDemo()
'Module2
Call Module1.HelloWorld
End Sub

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

Final Thoughts

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.

As always, if you like what you see, subscribe to my email list, share this article with a friend on Twitter, Facebook, or Google+, submit a comment below and follow me on Google+! Come back often to see more great VBA ideas.

Remember, if you’re working on an Excel VBA project, but you can’t quite figure out how to finish it, don’t forget you can ask for my help via my VBA Consulting service page.


wellsrPRO

Coming Fall 2017

Auto-Import Macros Directly from wellsr.com

Absolutely FREE when you join the waitlist


About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.