What is Macro worksheet in Excel?

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)

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 :-)