What is Macro worksheet in Excel?

Question

I have Excel 2013. To access VBA I accidentally pressed Ctrl+F11 instead of Alt+F11 and a new worksheet named ‘Macro1’ was created. This looks like an ordinary worksheet but the default column width of each column is much wider and the ‘View Code’ option is disabled in the context menu.

Nothing much is found on the Internet about purpose and relevance of this sheet. Does anyone know what this Macro sheet is about and what does it do?

Solution 1

It's an Excel 4.0 Macro sheet, part of the XLM macro language. It's included for backward compatibility to versions up to Excel 4. The VBA language was introduced in Excel 5, and is vastly superior in every way, so XLM has been phased out.

To use it (not recommended), you write a series of XLM commands in a column. It will be executed top down. To declare it, you select the top cell of that range, then under the 'insert' menu, under the 'name' submenu, pick 'define'. Give it a suitable name and hit 'add'. Then select it within the same dialog, and radio buttons will appear below. They will offer 'command', 'function', or 'none'. Choose command or function, as appropriate.

You may even call a VBA procedure from an Excel 4.0 XLM macro by using XLM's RUN function. For example, the following macro runs the Test subroutine contained in Module1 in workbook Book1.xls:

=RUN(Book1.xls!Module1.Test)

As said in this article :

thanks to Microsoft's practice of “strategic incompatibility” and utter contempt for the investment made by their customers, these rudimentary macros have required specific modifications for every single new version of Excel in the decade since they were originally released, and things have gotten worse, not better, since Microsoft introduced the new Visual Basic programming language for Excel (itself a cesspool of release-to-release incompatibility)

If you wish to know more about XLM, here are some resources:

• Working with Excel 4.0 macros
• Help File for Excel 4.0 Macros (requires winhlp32)

Solution 2

People have been looking for ways to "hide" data from users, but even using xlVeryHidden, you can see sheets if you go to the VBA editor. You can password protect the VBA project, but many people forget to do this. This XLM sheet won't be shown in the VBA project and when you loop through all sheets, it won't even be shown. So if you use xlVeryHidden with this sheet, you would be the only person who knows about it. You could use it for embedding passwords etc. and you could obfuscate any calls to it in complex VBA code. Just a thought :-)

Comments

[rajeev]

Seems I accidentally stumbled upon a feature from ancient MS Excel. Very interesting. Thanks.

[sifar]

how can one use xlVeryHidden with the Macrosheet if it is not visible in the VBA editor in the 1st place? Also, can you give an example of how one can obfuscate calls to it?

[ProfoundlyOblivious]

I learn something new every day, thank you for this little gem. But you should have stopped after the first sentence. Sheets and worksheets cannot be casually interchanged. All worksheets are sheets but not all sheets are worksheets. Paste this Sub ListMacroSheet():Dim sheet As Worksheet:For Each sheet In ActiveWorkbook.Sheets:Debug.Print sheet.Name:Next:End Sub and Sub MissedMacroSheet():Dim sheet As Worksheet:For Each sheet In ActiveWorkbook.Worksheets:Debug.Print sheet.Name:Next:End Sub into a code module of a macro enabled workbook with macro sheets and compare output.

[ProfoundlyOblivious]

If a password needs to be kept secret then never embed it in a workbook or VBA project because protection doesn't exist. Hidden sheets are easily found and passwords are easily broken. Ironically obfuscation can be harder to circumvent but it provides less security than a pile of bricks dumped outside your front door. Thieves can either move the bricks or find an easier way in; but at least there is a chance to catch a thief moving bricks out of the way, no such opportunity exists with obfuscation. @sifar Workbook(Index).Sheet(Index).Visible = 2 is very hidden, 0 hidden, -1 visible

[Andrew Robson]

@ProfoundlyOblivious You are quite correct. I only learned the difference between the 2 collections recently (Worksheets and Sheets). However, most casual users would probably be unaware of these methods to reveal hidden sheets. They would most likely just look in the Project window for sheets. But you are absolutely correct that there is no fail-safe way to protect and hide sheets and information you don't want most users to see. Any safe can be cracked. But in most cases doing something to make it difficult is better than nothing at all.