[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 Optional argument.
Public Sub myPublicSub(Optional dummy_var As Integer)
...
End Sub0
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
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.