31

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.

braX
  • 11,506
  • 5
  • 20
  • 33
AR.
  • 39,615
  • 9
  • 44
  • 52
  • 1
    wow great, using @Jason Z answer could also decreases size of my excel file (117 kb.) (excel 2003) – Mahdi Jazini Jun 09 '15 at 05:43
  • That's surprising. I believe it may have happened due to saving workbook in compile vs non-compile mode. Using Option Private Module effectively has no direct effect on file size. – jainashish Oct 11 '18 at 03:32

5 Answers5

56

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.

Jason Z
  • 13,122
  • 15
  • 50
  • 62
  • 1
    I had shield away from this at first because it can't be used in 'object' modules like ThisWorkbook or sheets... but that problem was me having methods where I shouldn't. Thanks for this. – AR. Nov 17 '08 at 22:35
  • wow great, using this method can also decreases size of your excel file (for me: 117 kb.) (excel 2003) – Mahdi Jazini Jun 09 '15 at 05:44
  • 1
    Perhaps we should all have `Option Private Module` at the tops of our code modules as a rule – except where the module contains procedures called by buttons. – ChrisB Mar 22 '19 at 21:18
8

One solution is to give the method an Optional argument.

Public Sub myPublicSub(Optional dummy_var As Integer)
    ...
End Sub
e.James
  • 116,942
  • 41
  • 177
  • 214
  • Thanks, eJames. I had meant to mention that as an option in my question too. In my mind, that 'smells' even worse than turning the public Sub ino a function. :) – AR. Nov 17 '08 at 20:27
8

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.

Ant
  • 5,150
  • 2
  • 33
  • 41
3

Also worth noting a side effect of both Option Private Module and adding Optional Params is that the Sub will no longer work as a target of a shortcut keybinding.

Restated: If you have a keybinding to set a keyboard shortcut to launch a Macro. That Macro cannot be hidden. Otherwise the keybinding will not work.

Sam
  • 31
  • 2
  • 1
    Not quite true. If you use e.James' method above of a dummy argument you can still hide the sub from the "macros" dialog and have a keybinding. You can either bind the key then add the dummy argument, or do the keybinding/unbinding during Workbook_Open/BeforeClose or App_WorkbookActivate events. – blackworx Jul 24 '13 at 09:52
1

My methods have already been listed however, ChrisB made the following statement:

Perhaps we should all have Option Private Module at the tops of our code modules as a rule – except where the module contains procedures called by buttons

Even Private routines can be called from a button if the Macro is assigned to the button before making it private.

ExpertNovice
  • 51
  • 3