[ACCEPTED]-Making sure public VBA methods don't show up in the list of Excel macros-vba

Accepted answer
Score: 54

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.

Score: 8

One solution is to give the method an Optional argument.

Public Sub myPublicSub(Optional dummy_var As Integer)
End Sub


Score: 6

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.

Score: 3

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.

Score: 1

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