Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. 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

Written by Allen Wyatt (last updated November 28, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


3

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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9904) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. 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

Finding Unknown Links

There are several things to try when finding unknown links in Excel.

Discover More

Inserting a Cross-Reference to the First Style on a Page

A common way to set up a header is to have it refer to the first occurrence of a heading on the page. (Think how the ...

Discover More

Losing Formatting

When you save a workbook, you expect Excel to remember the formatting you applied in the worksheets in that workbook. If ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Recovering Macros from Corrupted Workbooks

Workbooks get corrupted from time to time; that's a fact of life in an Excel world. If those corrupted workbooks contain ...

Discover More

Sheets for Months

One common type of workbook used in offices is one that contains a single worksheet for each month of the year. If you ...

Discover More

Page Numbers in VBA

When you print a larger worksheet, Excel breaks the printout across several pages. You may want to know, before you ...

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}] (all 7 characters, in the sequence shown) 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 8 + 5?

2020-12-02 10:13:43

Rick

I use (Optional Dummy as Boolean) in all of mine


2020-11-29 17:32:43

Peter

Thanks, this will help me declutter my macro list.


2020-11-28 10:26:00

J. Woolley

The "third potential solution" mentioned in the Tip should be modified as follows:

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

Now it is NOT necessary to include a parameter when calling MySub.


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.