It sounds like you are refering to the CodeName property of a sheet.
If in the Project Explorer window shows "Sheet1 (SheetAlpha)" then
"Sheet1" is the CodeName of the sheet, "SheetAlpha" is the Name of the sheet (which is shown on the tab. Both of these message boxes will show the same result.
What I am trying to "gain from this" is that I want the formula in that cell to remain effective regardless of what the tab name for the sheet might be. If there is code that would accomplish this, I would be happy to use it, or, as you suggest, a UDF. I am having difficulty devising one or the other of these.