none
tracing calculations RRS feed

  • Question

  • I am trying to figure out if there is a way to trace or track what calculations are being made when a cell is calculated.  I've seen other questions of this sort but I didn't find any answers which could help in my situation.

    BACKGROUND:  I have a workbook that contains about 5 sheets and I am trying to improve it's performance.  As part of that I am using an add-in which adds a custom button to the toolbar which, when clicked, recalculates the currently selected range and displays how long that takes.  I can also add code to my VBA functions so that the time to run a specific function is also displayed if that function is run when the range is calculated.  (So the results would display something like: "Sheet1!$C$5 100 ms, Proc:CountWeeks 50 ms" if cell C5 calls the CountWeeks UDF.)  This add-in has worked fine in other workbooks/sheets and while I realize the timing functions cause a little overhead, this has been very useful.

    Now in the workbook in question, the results I am getting for calculating any cell, even a blank/empty cell, are orders of magnitude greater than the functions being called.  For example, if I time the calculation of a blank cell, it is taking around 250-300 msecs.  If I time a calculation of a single cell calling one UDF, the UDF might take around 10-50 msecs but the cell is taking a total of 250-300 msecs again.  Basically, when any cell is calculated something else is being calculated or running that is taking a bunch of time.  I've tried removing any conditional formatting as a test and that hasn't helped.  I've also tried removing any obvious VOLATILE functions which might be recalculated but that hasn't helped either.

    So maybe this is being caused by some kind of garbage collection routines or something like that but is there any way that I can track which cells/formulas are being calculation when I call Range.Calculate?  Maybe I have some cells that have VOLATILE formulas that I didn't realize so they are being calculated also. 

    Wednesday, September 10, 2014 9:47 PM

Answers

  • You can trap and store calls to UDFs with something like this

    '''' ThisWorkbook module
    
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ' note this fires after a sheetChange has triggered calulations and UDF calls
        If gbDebug Then
            RecordCalls "SheetChange", Target
        End If
    
    End Sub
    '
    ''''' code in normal module ''''''
    
    Public gFunCalls() As String
    Public glIndex As Long
    Public gbDebug As Boolean
    
    Sub StartDebug()
        Erase gFunCalls: glIndex = 0
        gbDebug = True
    End Sub
    Function foo(arg)
       ' Application.Volatile  ' << change
        If gbDebug Then
            RecordCalls "foo", Application.caller
        End If
        foo = arg + 1
    
    End Function
    
    Sub RecordCalls(func As String, caller As Range)
        On Error GoTo errH
        glIndex = glIndex + 1
        gFunCalls(1, glIndex) = func
        gFunCalls(2, glIndex) = caller.Parent.Name & "!" & caller.Address(0, 0)
    
        Exit Sub
    errH:
        If Err.Number = 9 Then
            If glIndex = 1 Then
                ReDim gFunCalls(1 To 2, 1 To 1000) As String
            Else
                ReDim Preserve gFunCalls(1 To 2, 1 To glIndex + 999) As String
            End If
            Resume
        End If
    End Sub
    
    Sub Dump()
    Dim cnt As Long
    Dim arr() As String
        If glIndex = 0 Then
            Exit Sub
        End If
        cnt = glIndex
        ReDim arr(1 To cnt, 1 To 2)
        For i = 1 To cnt
            arr(i, 1) = gFunCalls(1, i)
            arr(i, 2) = gFunCalls(2, i)
        Next
        
        Application.EnableEvents = False
        Range("A1").CurrentRegion.Clear ' may trigger volatile UDFs
        Range("A1").Resize(cnt, 2).Value = arr ' ditto
        Application.EnableEvents = True
        
    ' if dumping to same workbook Volatile UDFs will
    ' trigger on each cell change, so running this routine
    ' might increase the count by a factor of three
        If cnt <> glIndex Then
            MsgBox "Before Dump: glIndex = " & cnt & vbCr & _
            "After Dump: glIndex = " & glIndex
        End If
        StartDebug
    End Sub
    
    Volatile functions should be used in UDFs and only when necessary. But keep in mind some worksheet functions are volatile.


    • Marked as answer by Kmart92 Monday, September 15, 2014 3:46 AM
    Thursday, September 11, 2014 12:35 PM
    Moderator

All replies

  • Formula : Formular Auditing : Evalute Formula

    jdweng

    Thursday, September 11, 2014 3:27 AM
  • The add-in itself is probably adding the overhead...

    It probably initializes something, it calls some functions internally and therefore it creates some overhead that way.

    Thursday, September 11, 2014 9:29 AM
  • You can trap and store calls to UDFs with something like this

    '''' ThisWorkbook module
    
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ' note this fires after a sheetChange has triggered calulations and UDF calls
        If gbDebug Then
            RecordCalls "SheetChange", Target
        End If
    
    End Sub
    '
    ''''' code in normal module ''''''
    
    Public gFunCalls() As String
    Public glIndex As Long
    Public gbDebug As Boolean
    
    Sub StartDebug()
        Erase gFunCalls: glIndex = 0
        gbDebug = True
    End Sub
    Function foo(arg)
       ' Application.Volatile  ' << change
        If gbDebug Then
            RecordCalls "foo", Application.caller
        End If
        foo = arg + 1
    
    End Function
    
    Sub RecordCalls(func As String, caller As Range)
        On Error GoTo errH
        glIndex = glIndex + 1
        gFunCalls(1, glIndex) = func
        gFunCalls(2, glIndex) = caller.Parent.Name & "!" & caller.Address(0, 0)
    
        Exit Sub
    errH:
        If Err.Number = 9 Then
            If glIndex = 1 Then
                ReDim gFunCalls(1 To 2, 1 To 1000) As String
            Else
                ReDim Preserve gFunCalls(1 To 2, 1 To glIndex + 999) As String
            End If
            Resume
        End If
    End Sub
    
    Sub Dump()
    Dim cnt As Long
    Dim arr() As String
        If glIndex = 0 Then
            Exit Sub
        End If
        cnt = glIndex
        ReDim arr(1 To cnt, 1 To 2)
        For i = 1 To cnt
            arr(i, 1) = gFunCalls(1, i)
            arr(i, 2) = gFunCalls(2, i)
        Next
        
        Application.EnableEvents = False
        Range("A1").CurrentRegion.Clear ' may trigger volatile UDFs
        Range("A1").Resize(cnt, 2).Value = arr ' ditto
        Application.EnableEvents = True
        
    ' if dumping to same workbook Volatile UDFs will
    ' trigger on each cell change, so running this routine
    ' might increase the count by a factor of three
        If cnt <> glIndex Then
            MsgBox "Before Dump: glIndex = " & cnt & vbCr & _
            "After Dump: glIndex = " & glIndex
        End If
        StartDebug
    End Sub
    
    Volatile functions should be used in UDFs and only when necessary. But keep in mind some worksheet functions are volatile.


    • Marked as answer by Kmart92 Monday, September 15, 2014 3:46 AM
    Thursday, September 11, 2014 12:35 PM
    Moderator
  • There is an overhead for processing the calculation chain etc which does not show up in calculating an individual cell.

    I would suggest you profile the calculation of the workbook then drill down to the worksheets then drill down to the formulas.

    You can do this in a simplistic way using my advice at

    http://msdn.microsoft.com/en-us/library/office/ff700515%28v=office.14%29.aspx

    For a more professional tool see

    http://www.decisionmodels.com/FastExcelV3Profiler.htm


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

    Thursday, September 11, 2014 10:04 PM
  • Thanks Peter!  I'm going to give your routines a try to see if they can help figure out where the lag is coming from or what's being called in the calculation chain.

    I do realize that there is some overhead for processing the calculation chain and calling the add-in.  However, I use the same add-in with another workbook and the same amount of overhead doesn't happen in that workbook.  I've got my own profiling code and it is showing this large amount of lag/overhead even when I calculate an empty cell.  I *think* it is caused by a complicated formula that I use to create a dynamic range for a list of names.  (It sorts the names and removes duplicates and uses OFFSET so I think that might be it.)  When I replaced that formula with a static range definition, the amount of overhead is reduced but is still much higher than any of the other sheets in my other workbooks that use this add-in.

    At least now I can try Peter's routines to hopefully trace what is going on.

    Monday, September 15, 2014 3:51 AM