[ACCEPTED]-Making sure public VBA methods don't show up in the list of Excel macros-vba
Add the following to the top of your module:
Option Private Module
From 7 MSDN:
When a module contains Option Private Module, the 6 public parts, for example, variables, objects, and 5 user-defined types declared at module 4 level, are still available within the 3 project containing the module, but they 2 are not available to other applications 1 or projects.
One solution is to give the method an
Public Sub myPublicSub(Optional dummy_var As Integer) ... End Sub
Just a small addendum to Jason Z's answer: methods 2 hidden by
Option Private Module are still visible if you use 1
Application.Run() to invoke the method.
Also worth noting a side effect of both 7 Option Private Module and adding Optional Params is that the 6 Sub will no longer work as a target of a 5 shortcut keybinding.
Restated: If you have 4 a keybinding to set a keyboard shortcut 3 to launch a Macro. That Macro cannot be 2 hidden. Otherwise the keybinding will not 1 work.
My methods have already been listed however, ChrisB 7 made the following statement:
Perhaps we 6 should all have Option Private Module at 5 the tops of our code modules as a rule – except 4 where the module contains procedures called 3 by buttons
Even Private routines can be called 2 from a button if the Macro is assigned to 1 the button before making it private.
More Related questions