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

Hiding Macros

by Allen Wyatt
(last updated November 7, 2015)

1

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 (3291) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) 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

Working With OLE Graphics

An explanation of the way Word imports graphics.

Discover More

Pay Attention to Case when Searching for ASCII Codes

Word allows you to search for specific ASCII codes in a document. If you use codes to search for alphabetic characters, you ...

Discover More

Mouse Click Event in VBA

Need to know if a particular cell is clicked with the mouse? Excel has no particular event handler for clicking in this way, ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (menu)

Develop Macros in Their Own Workbook

If you develop macros and edit them quite a bit, you may be running the risk of causing problems with the macros or with your ...

Discover More

Automating Copying Macros

You can manually copy macros from one workbook to another, but what if you want to automate the copying process? Here's some ...

Discover More

List of Macro Shortcuts in All Open Workbooks

Need a list of macro shortcut keys? It's not as easy in Excel as in some other Office applications, such as Word. It can be ...

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 seven less than 7?

2015-11-09 05:30:03

John

There's also another way - use 'Option Private Module' at the top of the module.


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon 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.