Analyze workbook calculation time

[nvu1991]

Hi, I developed this tool analyze my workbook to identify which formula is too slow. This code works and I tested many times. But I don't have much low level knowledge of Excel in general. So can someone look at the code and tell me what's the limitation(s) of this code?

Private Declare PtrSafe Function getFrequency Lib "kernel32" _
      Alias "QueryPerformanceFrequency" ( _
      ByRef Frequency As Currency) _
      As Long
 
Private Declare PtrSafe Function getTime Lib "kernel32" _
      Alias "QueryPerformanceCounter" ( _
      ByRef Counter As Currency) _
      As Long


Sub WBCalculateTime()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Dim beginTime As Currency
Dim endTime As Currency
Dim perSecond As Currency
Dim totalTime As Currency
Dim wbSummary As Workbook
Dim wbSource As Workbook
Set wbSource = ActiveWorkbook
Dim path As String
path = Application.ActiveWorkbook.path
Dim fileName As String
fileName = Left(wbSource.Name, Len(wbSource.Name) - 5)
Set wbSummary = Workbooks.Add
wbSummary.Sheets(1).Name = "Summary_"
Dim sumSh As Worksheet


getFrequency perSecond


For Each wSheet In wbSource.Worksheets
    
    Set sumSh = wbSummary.Sheets.Add(After:=wbSummary.Worksheets(wbSummary.Worksheets.Count))
    sumSh.Name = "_" + wSheet.Name
    
    For Each cCell In wSheet.UsedRange
        If IsEmpty(cCell.Value) = False Then
            getTime beginTime
            cCell.Calculate
            getTime endTime
            endTime = 1000# * (endTime - beginTime) / perSecond
            totalTime = totalTime + endTime
            sumSh.Cells(cCell.Row, (cCell.Column - 1) * 3 + 1).Value = cCell.Address
            sumSh.Cells(cCell.Row, (cCell.Column - 1) * 3 + 3).Value = "'" + Left(cCell.Formula, 254)
            sumSh.Cells(cCell.Row, (cCell.Column - 1) * 3 + 2).NumberFormat = "#,##0.00"
            sumSh.Cells(cCell.Row, (cCell.Column - 1) * 3 + 2).Value = endTime
        End If
    Next cCell
    
Next wSheet


Set sumSh = wbSummary.Sheets("Summary_")
sumSh.Range("A1").Value = "Total run time: " & Format(totalTime / 1000, "#,##0")
Application.AlertBeforeOverwriting = False
wbSummary.SaveAs fileName:=path & "\" & fileName & "_Timed" & ".xlsx", AccessMode:=xlExclusive, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges


Application.ScreenUpdating = True
End Sub

 

[bobsan42]

For me - the limitation is that the output does not actually allow any analysis to be made.
To analyse data you have to put it all together on one sheet structured in the same way. Only then you can make any sense of it.
For example I would put everything in the summary sheet in 4 columns (Sheetname, address, time, formula) -then you can really analyse the data in column TIME - put it in a pivot table or chart, find MIN MAX and AVERAGE etc.

Also you are calculating time for cells which contain Values only, and not Formulas. The cells w/o formulas should be skipped IMHO.

And formulas calculating time is essential for workbook loading time (if this is what you are after), but is not the only factor to be taken into account.

 

[nvu1991]

Also you are calculating time for cells which contain Values only, and not Formulas. The cells w/o formulas should be skipped IMHO.

As I tested, all cells with formula will be recored. The check

If IsEmpty(cCell.Value) = False Then

should collect all non-empty cells.

For example I would put everything in the summary sheet in 4 columns (Sheetname, address, time, formula) -then you can really analyse the data in column TIME - put it in a pivot table or chart, find MIN MAX and AVERAGE etc.

I am trying to do that too. However, I have not found a way to group same formula. For example:

=If(A$4>$A1,"Long","Short") 
=If(B$4>$A1,"Long","Short") 
=If(A$4>$A2,"Long","Short")

should be grouped under 1 formula. If anyone have a method, I would be happily incorporate that into the code.

And formulas calculating time is essential for workbook loading time (if this is what you are after), but is not the only factor to be taken into account.

What other factor(s) is that?
Also, minimizing calculation time should surely increase responsiveness of the workbook (other being the same), correct?

 

[bobsan42]

Well there are 3 types of cells - Blanks(or Empty), Cells with values (constant text, number, date, etc.) , cells with formulas (which actually need to be recalculated)
So only checking for empty cells will also lead to calculating cells with constant values in them - which is not necessary. so you can use a check like:

if not intersect(cCell,cCell.SpecialCells(xlCellTypeFormulas)) is nothing then

About the grouping I am not sure exactly what you mean and I am not familiar with you workbook and data structure so at this point I cannot give you any other advice. Still I think the one I gave is good - put them all in one place. Then worry about grouping them. Use conditional formatting or pivot table/chart to analyse the output to narrow it down.

Calculating one formula will require its predecessors to also be recalculated, which in turn will require theirs to be recalculated and so on ...
The factors for speeding up the workbook can be many. In general it will depend on the type, amount and complexity of:
- data
- formatting
- conditional formats
- formulas
- external links (to external files which can be opened or closed)
- pivot charts and tables
- graphic objects
- user-defined functions (esp. the way they are written) and VBA code
- ...

A factor is the location of the workbook - local, network drive or VPN.
And of course the behavior will depend on the available system resources and the current system load.

So Yes in theory reducing the formulas recalculation time will reduce the workbook loading time, but it is just a part of it - large or small depend on the workbook and the things above and more.
If you want to improve a particular workbook you should start looking at the full loading time and check the effect of changing a factor at a time. Maybe in the end it will still be about optimizing the formulas

 

[nvu1991]

What's the best measurement to qualify the responsiveness of a workbook? I think calculation time should be it. The code cannot identify the cause of the slow down but show the effect. I think that should point out the affected area which should help narrow down the solution to a slow workbook. Is that generally logically sound? Or I should change the approach to this problem?

So only checking for empty cells will also lead to calculating cells with constant values in them - which is not necessary

I thought about that but I want to keep the structure the same in the result workbook so it's easier to understand. Having the constant there help identify title/label of a row/column/cell that has formulas.

 

[bobsan42]

The best measurement is the feeling you get when you use the workbook and you know a bit more about it - size, complexity, amount of data, etc.
Once you feel there is a problem with productivity you should start looking for the problem one step at a time.
Normally I would start by putting only the necessary data and formulas in a new workbook (remove all extras and formatting) to see the behavior - CALCULATION goes way beyond simple cell by cell calculation.

I still disagree with you on the subject of the output but in the end whatever works for you.