The worksheet calculates too often

[Stefano]

While investigating the performances of a workbook I added a Debug.Print N
inside a function so I can see when the function code runs. N is a static
long that is increased every time. It prints also a text that allows me to
see what cell is calling the function (useful when the same function is used
in many cells.)

I use the function 5 times in 2 sheets, for a total of 10 times.

First weird thing: when I open the workbook the Immediate window shows more
than executions. The function is executed once for some cells, but 2 or 3
times for others.
I tried to close Excel and open the workbook twice: once I had 22
executions, then 20. I was expecting 10.

Second weird thing: the functions are calculated when I change a cell
referred in one function (good,) when I change a cell not referred in any
function (bad,) and when I create a new workbook and change a cell in the new
workbook (worse.)
For example: I press Ctrl+N to create a new workbook (nothing appears in the
immediate window) and press the Delete button, the Immediate window shows 14
calculations.

Thanks,
Stefano

 

[Charles Williams]

Its quite common for a function to be calculated multiple times.
see
http://www.decisionmodels.com/calcsecretsj.htm

for some possible explanations and advice on what to do about it.

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

 

[Stefano]

Charles, the document you mentioned was very useful. I knew most of the
things described, but it was nice to read all of them in a single well
organized document.

After reading all the points suggested in the document, I only did one
change: I added the IsEmpty() check, but the the problem is still there.

For example if I open the file with the macro, then open the VBA editor,
then create a new Excel workbook, then press "1", then arrow down, "1", arrow
down, etc., every 3-4 cells modified in the new workbook the formulas in the
other workbook are recalculated. Sometimes only a few, sometimes all of them
many times.

I can't think of any event triggered by the change of a formula in a just
created workbook that would fire the calculation of another workbook.

Any idea?

This is an example of my function:

Function GetUsedMaterial(SheetStockList As Range) As String
CheckMultipleRuns "Before GetUsedMaterial('" &
SheetStockList.Worksheet.Name & "'!" & SheetStockList.Address & ")"
If IsEmpty(SheetStockList) Then Exit Function
CheckMultipleRuns "After GetUsedMaterial('" &
SheetStockList.Worksheet.Name & "'!" & SheetStockList.Address & ")"
[...]
End Function

This is the function that checks for multiple runs. It prints on the debug
window when a function with the same range runs twice in the same second (I
could do better, but it's good enough for a quick debug test):

Sub CheckMultipleRuns(Txt As String)
Static N As Long, AllRuns As String, ThisRun As String, TLastRun As Single

If Timer - TLastRun > 1 Then
TLastRun = Timer
AllRuns = ""
End If

ThisRun = Time & Txt
If InStr(AllRuns, ThisRun) Then
Debug.Print ThisRun, N
Else
AllRuns = AllRuns & ThisRun
End If
End Sub

Thanks,
Stefano

PS: Why when I search for "The worksheet calculates too often" this post
doesn't come up? Is there something I need to know about how to search in
this forum?

 

[Charles Williams]

I can't think of any event triggered by the change of a formula in a just
created workbook that would fire the calculation of another workbook.

Excel does not calculate at workbook level: it calculates all open workbooks
not just the active workbook.
So if you have 2 workbooks open and trigger a recalculation in one of them
the other workbook will also be recalculated, even if there are no
references from the other workbook to the first workbook.

see http://www.decisionmodels.com/calcsecretsg.htm


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

 

[Stefano]

Thanks Charles, I went through many of your pages, but I didn't find anything
that would explain what I see.

The weird thing is that if I press the Delete button on any cell:
- press once, wait one second, press once again -> all my functions are
executed once
- press twice, very quickly -> the volatile functions are executed once, the
other functions are executed twice
- press twice, quickly, but not as before -> some of the non volatile
functions are executed once, others twice.

I wasn't able to reproduce the problem with a smaller model.
I tried creating some slow functions, either volatile or not, and they make
Excel non responsive, that is the calculation always runs once, and through
the end.

In the real case intead, it looks like the calculations starts in
background, the interface is responsive, and:
- If it has time to finish it runs only once;
- If it has no time to finish (because i press a key) it interrupts and
restarts.

Does it make sense?

Thanks,
Stefano

 

[Charles Williams]

In the real case intead, it looks like the calculations starts in
background, the interface is responsive, and:
- If it has time to finish it runs only once;
- If it has no time to finish (because i press a key) it interrupts and
restarts.

Yes calculation restarts after an interruption (you can use VBA to mask
against an interruption).

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

 

[Stefano]

Thanks Charles, I spent one day studying this weird behavior, and now I feel
better because:

1) My UDFs are calculated at every change (regardless of the current
workbook) because they refer to cells containing the volatile function
OFFSET()

2) The range referred by the OFFSET changes very seldom (it is in a hidden
sheet,) so I will make my own non volatile MyOffset that will recalculate
only when I really need it.

3) I know Excel better

4) Using IsMissing() saves 5-10% of recalculations

Thanks,
Stefano