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!
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
Get these macros delivered right to your spreadsheet for free
Access tutorials and import my macros without ever leaving ExcelShow me - It's free
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
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.
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.HelloWorld End Sub
Call keyword is optional when calling a module
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.HelloWorld 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.
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.