How do I make sure public VBA methods don't show up in the list of Excel macros?

Question

In Excel VBA (2003), I've noticed that any Public or Friend Sub method in either a module or ThisWorkbook that doesn't have any arguments will show up as a Macro that can be run by the user. I.e. when the user goes to Tools --> Macro --> Macros... (or Alt+F8) the method will show up in the list that can be run.

For sanity's sake (organization, maintenance, etc) I do need to separate the code into modules. I'm hoping to find a non-hacky way to hide some methods from the user, but still allow them to be visible to other code modules. Note that all the code is contained within the same application, so no external code is called.

My current work-around is to use Functions that return a boolean instead of Subs, and just ignore the return value. eJames suggested the option of using an optional argument in the Sub, which will also hide the method from the list of macros.

Neither of these feel quite right, however, so any advice about how to structure a non-trivial Excel VBA application would be much appreciated.

Answer 1

One solution is to give the method an Optional argument.

Public Sub myPublicSub(Optional dummy_var As Integer)
    ...
End Sub

Answer 2

Add the following to the top of your module:

Option Private Module

From MSDN:

When a module contains Option Private Module, the public parts, for example, variables, objects, and user-defined types declared at module level, are still available within the project containing the module, but they are not available to other applications or projects.

Answer 3

Just a small addendum to Jason Z's answer: methods hidden by Option Private Module are still visible if you use Application.Run() to invoke the method.