Excel: Returning the Name of the Currently Running Macro Sheet

SUMMARY

In Microsoft Excel, you sometimes must get the name of the currently running macro sheet. This is necessary, for example, if you want to activate a macro sheet whose name may have been changed since it was written. Because the argument to the ACTIVATE() command is a text string, it is not changed when the document name is changed.

To get the name of the currently running macro sheet, enter the following three macro lines. Hold down the COMMAND key when entering the second line so that it is entered as an array formula. If it is correctly entered as an array, Excel places braces ({}) at the beginning and end of the formula.

   =SET.NAME("flag","run")
   =LOOKUP("\",IF(ISERROR(GET.DEF("""run""",DOCUMENTS())),,DOCUMENTS()))
   =DELETE.NAME("flag")

MORE INFORMATION

The formula works by setting a name on the active macro sheet in the first line, then going through all the open worksheets looking for that definition in the second line, as follows:

1. GET.DEF("""run""",DOCUMENTS()) looks for a name defined as "run" on each of the open documents. If the document does not contain a name defined as "run", GET.DEF() returns an error value.

2. IF(ISERROR(GET.DEF(... ...),,DOCUMENTS())) returns an array containing FALSE for each file that does not contain a name defined as "run", and the name of the document that has the defined name (the macro sheet).

3. LOOKUP("/",IF(... ...)) returns the greatest value less than or equal to the ASCII code for a forward slash. Thus, it looks through the array created above, which contains FALSE for each file that isn't the macro sheet, and the name of the macro sheet that is currently running. Since forward slash comes after any other valid filename characters, the LOOKUP() statement returns the name of the macro as the greatest value less than or equal to forward slash.

4. The DELETE.NAME() statement removes the flag defined on the macro sheet.