Worksheet_Calculate fires for every worksheet in book?

[Lurker]

Hi,

I'm moving from Excel 97 to Excel 2007 and some things have broken :-(

I have a workbook containing many worksheets. Some of those worsheets have a
Worksheet_Calculate routine more or less identical to this, a simple
GoalSeek.
----------------------------------------
Option Explicit
Private Sub Worksheet_Calculate()
Dim isOn As Boolean
isOn = Application.EnableEvents
Application.EnableEvents = False
On Error Resume Next
' A2 = input initial
' H3 = left asymptote
' F2 = initial calculated from goalseek by changinging left asymptote
Range("F2").GoalSeek Goal:=Range("A2"), ChangingCell:=Range("H3")
Application.EnableEvents = isOn
End Sub
-------------------
My problem is that when ANY worksheet in the workbook recalculates, ALL the
Worksheet_Calculate routines run.

I may be (probably!) being obtuse, but I can't see a reason for this
happening or a way to stop it. I don't think(!) this happened in Excel 97,
it certainly wasn't noticeable whereas now it takes a LONG time for the
calculations to finish.

The only other macro in the Workbook is (because in its principal
application this workbook is an invisible source of worksheets to be
copied)...
------------
Private Sub Workbook_BeforeClose(anArg As Boolean)
If Not (ThisWorkbook.Windows(1).Visible) Then ThisWorkbook.Saved = True
End Sub
-------------

Any hints?
Is there a setting somewhere I need to change?
Or do I have to modify all the Worksheet_Calculate() procedures to exit if
the worksheet isn't active?
If the latter, any suggestion on a neat way? (About the best I've come up
with so far is
If (ActiveSheet.Name <> Range("A1").Parent.Name) Then Exit Sub

Thanks in advance,
(sorry for the necessary anonymity)

A Lurker

 

[Jim Thomlinson]

A calculation is executed at the application level which means that the
calculation is executed on all open workbooks and all of the worksheets in
those books. While the calculation may not always be executed on some
workbooks or sheets depending on the dependancy tree and whether there are
any volatile functions the application is in charge of the calculation.

Specifically to your problem the issue is most likely that 2007 executes
code a lot slower than previous versions. SP1 is supposed to have improved
the rate of code execution but I have heard from a number of credible sources
around there here parts that the improvment is not that big...

 

[Jim Rech]

I tried to reproduce your scenario-

-2 worksheets
- Each with Worksheet_Calculate code
- Formulas on each referring only to itself.

When I made an entry on either sheet only its Worksheet_Calculate code ran.

Then I changed the Worksheet_Calculate code on sheet 1 to be more like
yours, that is, making a change on sheet 1 itself. Still no problem - only
sheet1 Worksheet_Calculate code ran.

Then I added a volatile function to sheet 2 (=Offset(A1,...) specifically)
and then I had the problem - both sheets' Worksheet_Calculate code ran when
I made a change to sheet 1.

When I changed the Worksheet_Calculate on sheet 1 back to just beeping
rather than making a change the problem stopped.

So I needed both Worksheet_Calculate code that changed sheet 1 and a
volatile function on sheet 2 to have the problem.

Interestingly I found this behavior was the same in Excel 97...

So, unless I'm missing something, you're probably going to have to live with
this. So:

If ActiveSheet Is Me Then
'Do goalseek
End if

--
Jim
| Hi,
|
| I'm moving from Excel 97 to Excel 2007 and some things have broken :-(
|
| I have a workbook containing many worksheets. Some of those worsheets have
a
| Worksheet_Calculate routine more or less identical to this, a simple
| GoalSeek.
| ----------------------------------------
| Option Explicit
| Private Sub Worksheet_Calculate()
| Dim isOn As Boolean
| isOn = Application.EnableEvents
| Application.EnableEvents = False
| On Error Resume Next
| ' A2 = input initial
| ' H3 = left asymptote
| ' F2 = initial calculated from goalseek by changinging left asymptote
| Range("F2").GoalSeek Goal:=Range("A2"), ChangingCell:=Range("H3")
| Application.EnableEvents = isOn
| End Sub
| -------------------
| My problem is that when ANY worksheet in the workbook recalculates, ALL
the
| Worksheet_Calculate routines run.
|
| I may be (probably!) being obtuse, but I can't see a reason for this
| happening or a way to stop it. I don't think(!) this happened in Excel 97,
| it certainly wasn't noticeable whereas now it takes a LONG time for the
| calculations to finish.
|
| The only other macro in the Workbook is (because in its principal
| application this workbook is an invisible source of worksheets to be
| copied)...
| ------------
| Private Sub Workbook_BeforeClose(anArg As Boolean)
| If Not (ThisWorkbook.Windows(1).Visible) Then ThisWorkbook.Saved = True
| End Sub
| -------------
|
| Any hints?
| Is there a setting somewhere I need to change?
| Or do I have to modify all the Worksheet_Calculate() procedures to exit if
| the worksheet isn't active?
| If the latter, any suggestion on a neat way? (About the best I've come up
| with so far is
| If (ActiveSheet.Name <> Range("A1").Parent.Name) Then Exit Sub
|
| Thanks in advance,
| (sorry for the necessary anonymity)
|
| A Lurker
|
|

 

[Lurker]

Thanks for such a comprehensive reply. It gives me a clear way forward.

Best regard (and Happy Christmas!)

A Lurker