Workbook_SheetCalculate Event gets Nothing as parameter

[Zauberkind]

Greetings,
in an attempt to track (and optimise) worksheet calculation loops, in ThisWorkbook I wrote:

Code: Select all

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If Sh Is Nothing Then
            Call LogMsg("SheetCalculation with No Object")
    Else
            Call LogMsg("Recalculated " & Sh.Parent.Name & "::" & Sh.Name)
    End If
End Sub ' Workbook_SheetCalculate

The framework is the default event handler, and conforms to the rather sketchy documentation.
The event triggers on at least some worksheet calculations.
Problem: Sh is Nothing, so I can't tell which sheet is being calculated.
Caveat: putting logging into the calculation events of all sheets is inelegant and impractical.

Is there a solution? a Duh! moment on my part? wishing for the moon?

TIA for any insight.
ZK

[HansV]

Even if you know which sheet is recalculated, the event procedure doesn't tell you which cells are recalculated, so it's pretty useless fpr analyzing purposes.

See Improving Performance in Excel 2007 for some tips.

[Zauberkind]

I'm trying to track dependencies. ie: change a cell on SheetA causes recalculation on SheetB and SheetC, which causes a recalculation on SheetA, which causes a recalculation on SheetC, etc.
If I know the sheets, I can find the references.
Regards,
ZK

[HansV]

I don't know why you get Sh is Nothing - in the situation that you describe, the Workbook_SheetCalculate event occurs multiple times, with Sh equal to SheetB, SheetC and then SheetA.
Perhaps you could use Jan Karel Pieterse's RefTreeAnalyser (not free).

[Zauberkind]

This appears to an artifact of the Locals window in the VBE.
Sh appears as an object with no properties or structure.
The simplified version I posted seems to work, nonetheless.
I'll keep digging in my code. If I find something interesting, I'll post it here.
Regards,
ZK