Macro to Find the Name of the Active Cell on a Worksheet

SUMMARY

The following macro will return the name of the active cell on a worksheet, assuming a name has been defined on the active cell. The returned name will be found in cell A6 of the macro sheet in the form of text.

   A1: Cell_Name
   A2: =GET.DOCUMENT(1)
   A3: =LEN(A2)+1
   A4: =REFTEXT(ACTIVE.CELL())
   A5: =LEN(A4)
   A6: =GET.DEF(RIGHT(A4,A5-A3),A2)
   A7: =RETURN()

MORE INFORMATION

The above macro is necessary because the macro function GET.DEF() will only accept references in R1C1 style text without the leading document name. For example, GET.DEF("Sheet1!R1C1") will return an error because GET.DEF() cannot interpret the "Sheet1!" portion of the reference. However, the statement GET.DEF("R1C1","Sheet1") will return the text of the name assigned to cell A1 on Sheet1 (assuming there is a name assigned to that cell). The macro code in cells A1:A5 split the reference of the active cell so that the proper arguments can be supplied to the GET.DEF function.

REFERENCES

"Microsoft Excel User's Guide." Version 3.0, pages 106-107.