Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Hiding Macros.

Hiding Macros

by Allen Wyatt
(last updated July 4, 2015)

4

Most readers already know that you can create functions and subroutines using VBA. This is no different than it is under VBA's namesake, Visual Basic. Normally, a macro shows up in the macro list when you display the Macros dialog box (press Alt+F8), unless one of three conditions is met:

  • The macro is a function. Functions typically return information, and they require information to be passed to them. Since running a macro from the macro list doesn't allow either of these things to happen, Excel figures there is no need to list it. User-defined functions, which are quite useful in Excel, are not displayed in the Macros dialog box because they are, after all, functions.
  • The macro is a subroutine with parameters. Excel assumes that since parameters are necessary, and you cannot provide parameters by choosing the subroutine from the macro list, there is no need to list it.
  • The subroutine has been declared Private. This means that the subroutine is only useful to code within the module in which it is declared.

The only type of macro listed in the Macros dialog box is a non-private subroutine with no parameters. In certain situations, however, you may not want those listed either. For instance, you may have created some universal subroutines that don't do anything useful if called on their own; they are designed to be called from other code. For instance, consider the following macro:

Sub MySub()
    MsgBox "We are running the macro"
End Sub

This macro will appear in the Macros dialog box. If you don't want it to appear, there are several solutions you can pursue, all of which become obvious from examining the three ways in which macros are excluded from the macro list. The first potential solution is to examine your code and find out if it is really "universal." Do you need the code from more than a single module? If you don't, then declare the subroutine Private; it will not appear in the Macros dialog box. Thus, the previous problem macro becomes the following:

Private Sub MySub()
    MsgBox "We are running the macro"
End Sub

The second way to hide the macro is to simply convert it to a function. This may sound odd, particularly if you don't want to return any values, but it is perfectly permissible. In VBA a function does not have to return a value. In the absence of explicitly declaring a return value, the function will return a default result (for example, Boolean returns False, String returns "", etc.) Thus, the problem procedure could be changed to a function and declared as shown here:

Function MySub() As Boolean
    MsgBox "We are running the macro"
End Function

This procedure doesn't show in the Macros dialog box and does not require arguments. It returns False by default, but this result can be ignored. Depending on the nature of the subroutine you are changing, it may be to your benefit to really allow the converted function to return True or False depending on the success of what is being done in the code. In this case, the converted function is a real function, and not really a dummy subroutine, since it is returning something of value.

The third potential solution is to use some dummy parameters with the subroutine. You don't need to do anything with them within the subroutine itself, but by including them the procedure is not listed in the macro list. In this scenario, the problem subroutine is changed to something like the following:

Sub MySub(Void As Integer)
    MsgBox "We are running the macro"
End Sub

Now the procedure is not listed in the macro list, but you need to change the way in which the subroutine is called. You must modify every instance so that a parameter is passed, even though it is never used.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9904) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Hiding Macros.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Using Manual Line Breaks with Justified Paragraphs

If you use justified paragraphs, you know that if you press Shift+Enter, it can lead to some odd spacing between words and ...

Discover More

Adding Diagonal Borders

Borders on all sides of a cell are easy to do in Excel. You can also create diagonal borders that run right through the ...

Discover More

Deleting MRU Entries

By default Word dutifully keeps track of the different files you've worked on in the past. If you want to delete an entry ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Using InputBox to Get Data

Need your macro to get some input from a user? The standard way to do this is with the InputBox function, described in this ...

Discover More

Replacing and Converting in a Macro

When you use a macro to process data you always run the risk of making that data unusable by Excel. This is especially true ...

Discover More

Using Seek In a Macro

When reading information from a text file, your macro may need to start reading at a place other than the beginning of the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 4 + 2?

2016-03-01 07:52:27

Richard

How can I disable the "Macros" button on the "View" ribbon so that my users cannot run macros from there. Also can I disable the "ALT+F8" shot cut to keep users from running macros that way? Thank you


2016-02-22 14:50:05

Gordon

Graham's idea with optional arguments works, but with a caveat. The optional argument(s) must not be of Variant type OR must have an an explicit default value provided. Otherwise, Excel is "smart" enough to realise that the arguments are optional and the macro will be included in the list anyway (at least in Excel 2013).

The following will work:
sub MySub(Optional NoMacro as Long)
sub MySub(Optional NoMacro = 0)

But the following will NOT (i.e. Excel will still list these in the macro list):
sub MySub(Optional NoMacro)
sub MySub(Optional NoMacro as Variant)


2016-01-14 11:14:11

Graham Stent

Hi there - very useful info - thanks. You can improve the "(Void As Integer)" solution by putting the word "Optional" in front of it and then you don't need to change anywhere that calls the macro as an argument is not required :-)


2015-09-23 21:03:32

Candice

Hi,

I have some really big codes entered on an excel file and have gone into marco > selected macro1 > edit and they are not displaying. The only thing in VBA editor is:

Sub Macro1()

End Sub

How can I get the codes back visible to edit?

Thank you


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.