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...
--
HTH...

Jim Thomlinson


"Lurker" wrote:

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