Fog Creek Software
Discussion Board




Controling the re-calc cycle in Excel

When a user hits f9 to re-calc the spreadsheet, all is fine and well.

However, if I have more then one spreadsheet open, then ALL the sheets re-calculate.

The problem is that I want only the current sheet to re-calculate and not all of the other sheets. Or, even better is if each sheet would wait until the other sheet is finished before it starts its re-calc cycle.

I realize that shift-f9 will just re-calc the current sheet. However, I still wondering if there is a way to turn off the concurrent of the sheets when they re-calc. I want them to re-calc one at a time, one after another. Not all at once.

If this is not possible, then I would not mind having a way to change f9 to function like shift f9 *just* for this sheet.


I do have this question posted in a excel newsgroup, but nothing came up yet.

Any ideas here?

--
Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
http://www.attcanada.net/~kallal.msn

Albert D. Kallal
Friday, May 16, 2003

Having done a few Excel Add-ins I wish it had a better event model.

Excel does have a WorkSheet_Calculate event but that gets triggered AFTER the calc so you can't cancel it.

You can create a macro and assign it to a button to run the calc one sheet at a time.

Just loop through each sheet in each open workbook and call:  Worksheets(x).calculate

I don't think you can reassign the F9 key to run your macro though.

Good Luck!

Dave

DJ
Friday, May 16, 2003

Yes, I think the solution is to teach users about f9

I have a sheet with some code that reads data from a mainframe. This code is a function in a bunch of cells that returns values from a mainframe. At the start of a re-calc cycle, I connect to the mainframe, and this includes a logon via a user id. After the re-calc cycle is finished, then in the calculate event, I log the user off. This way, I am only using ONE user logon during a re-calc cycle. The number of additional logons to that system is limited. This way, while working on, and editing the spreadsheet I am not using a precious user logon from the mainframe. I only use that logon during the re-calc cycle. That way other Excel users can also work, and we don't have to have a huge number of extra user logons for the mainframe.

If I have 5 spreadsheets open, and I hit f9, then ALL of the spreadsheets start a re-calc cycle, and it causes 5 logons to occur. This bad for many reasons, not to mention gobbling up 5 users license all at once. In fact, 5 users is ALL the extra logons we have! If each user just works with one spreadsheet, then those 5 extra logons can service a LOT of excel users, since I only use a single logogn during the re-calc cycle.

So, I need a way to turn off the concurrent recalc of all the sheets, and have them re-calc one at a time.

Albert D. Kallal
Friday, May 16, 2003

What about using:
Worksheets(i).EnableCalculation = False
for all the worksheets except the active worksheet. You will need to set it back to True for the new active worksheet (and False for all the others) when the user switches worksheets.

One disadvantage with this approach is I think it forces a recalculation when you change the value to True.

Philip Dickerson
Friday, May 16, 2003

With recalc turned off, replace all occurances of "=" with "=".

This is enough to trigger a recalc in each individual cell that had a formula, without starting a global recalc. It also lets you recalc a range easily.

Joel Spolsky
Friday, May 16, 2003

You can trap the F9 key when the worksheet opens:


Application.OnKey({F9},"CalcAllSheetsOneAtATime")


Then this code should do the trick:

Public Sub CalcAllSheetsOneAtATime()

Dim WS As Worksheet
Dim WB As Workbook

  For Each WB In Application.Workbooks
    For Each WS In WB.Worksheets
      WS.Calculate
    Next
  Next

End Sub


Dave

DJ
Friday, May 16, 2003

Actually you have to put it in the Activate event

Private Sub Workbook_Activate()
 
  Application.OnKey "{F9}", "CalcAllSheetsOneAtATime"

End Sub


Also you should restore the F9 Key when you close the workbook

Private Sub Workbook_BeforeClose()
 
  Application.OnKey "{F9}", ""

End Sub

DJ
Friday, May 16, 2003

Thanks Dave.

That looks about perfect for what I need.

I was not aware of the onKey feature. 

Gee, just how much time does one invest in learning Excel?

With the above list of suggestions, I can now control how f9 behaves in this sheet, and even make excel re-calc the sheets in sequential order also.

It seems that these programmable office applications can just about always solve the question I ask. I mean, I can’t ever remember Word, or Excel not being able to do what I wanted it to do. Rather remarkable these tools are.

Hence, I am working with a spreadsheet that has a function in some cells that read values directly from a legacy pick data base. (using a com add in here allows this without the need for odbc).

Thank you very much Dave, and to all of you who offered some suggestions. It is appreciated.

Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
http://www.attcanada.net/~kallal.msn

Albert D. Kallal
Friday, May 16, 2003

*  Recent Topics

*  Fog Creek Home