Use the current sheet name in Excel formulas

A couple of weeks ago I was working on a series of reports at work when I came across an interesting Excel problem: the worksheets – and they were many, a couple of dozen at least – each contained the name of the sheet as title for the current report. So each time I created a new sheet and started a new report I had to change the title in a cell and to rename the sheet with that title. It would seem like a minor thing, but doing that dozens of times is tedious, not to mention you could forget to change the title on some sheets. I soon started to wonder if there is a way to do this automatically…

Naturally, there is no built-in function to copy the name of a sheet in a cell in Excel, but you can build one using Visual Basic. At first I tried using the property ActiveSheet.Name, but I soon discovered that it has an interesting side-effect: because it uses the active sheet, if you were to link to the result from another sheet it would show the name of the sheet you are currently viewing instead of the sheet where the original formula was placed. After some more digging around online I found a solution that works properly. The final code is below:

Function Sheet_Name() As String
	 Application.Volatile
	 If TypeOf Application.Caller Is Range Then
		 Sheet_Name = Application.Caller.Worksheet.Name
	 Else
		 Sheet_Name = "#N/A"
	 End If
End Function

The function can be used directly without any arguments because it simply checks in which sheet it is placed, the user doesn’t need to specify that. Application.Volatile insures that the formula updates every time you rename the sheet, otherwise it would only update when you edit the cell.

If you are wondering how to use the code, you can follow this easy tutorial that can be applied to every Office version, despite being labeled for 2003. I tested this custom function in Office 2007 and 2013, but it should theoretically work in other versions as well. Just keep in mind that, when using the new file formats introduced in Office 2007, VBA and macros are only available with the .xlsm file format.