What you typed was my original function  and the one that I ran as a macro  with some minor modifications.

The problem is that Excel won't recognize  the user  defined function "sheetname". Whenever I enter "=sheetname()" into a cell, Excel returns  "#Name?"

I'm guessing the error is probably related to Excel itself and not the vba  code.