How can I get a reference to the worksheet an event was generated in?

In a worksheet I can attach code to an event like so:

Private Sub Worksheet_Calculate()
  ' .. code here
End Sub

How can I get a reference to the worksheet the event was generated in?

I wan开发者_开发技巧t to have a 100% secure way, so I don't have to worry about code breaking when the name worksheet changes etc.

ActiveSheet is not correct because it is not guaranteed that any sheet is active upon (re)calculate.

---

You can use caller:

Application.Caller.Worksheet.Name

---

You can use the Codename property:

Private Sub Worksheet_Calculate()
Debug.Print Me.CodeName
End Sub

By default it's the same name as the worksheet, but you can change it in the Properties Window of the VBE - it's the Name property. The user can't change it. (Copying a sheet adds a number to the name, e.g., Sheet1 becomes Sheet11.

You can also use it in the workbook-level event:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Sh.CodeName = "TheSheetICareAbout" Then
Debug.Print Sh.Name
End If
End Sub

It's hard to be more helpful as you don't say how you'll use the reference.

---

Some more information on code names:

http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm