Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Calculate fires for every worksheet in book?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Calculate fires for every worksheet in book?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Calculate fires for every worksheet in book?
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.Name < Range("A1").Parent.Name) Then Exit Sub If ActiveSheet Is Me Then 'Do goalseek End if -- Jim "Lurker" wrote in message ... | 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 | | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Calculate fires for every worksheet in book?
Thanks for such a comprehensive reply. It gives me a clear way forward.
Best regard (and Happy Christmas!) A Lurker "Jim Rech" wrote in message ... 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.Name < Range("A1").Parent.Name) Then Exit Sub If ActiveSheet Is Me Then 'Do goalseek End if -- Jim "Lurker" wrote in message ... | 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 | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SetLinkOnData only fires once? | Excel Programming | |||
Worksheet_Change fires even when no change? | Excel Programming | |||
Which Fires First? | Excel Programming | |||
code error after autorecover fires | Excel Programming | |||
Restrict Worksheet_Calculate() to its original worksheet !!! | Excel Programming |