Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving time on calculate
I have a situation where the calculation takes too long and so I put this in
the ThisWorkbook module "SheetChange" event: ActiveWorkbook.ActiveSheet.Calculate This makes the calculation much faster and I am confident everything is ok becuase all that needed to be calculated was the current sheet but the status bar now reads...: "Ready Calculate" :.... becuase there was not a full calculation done. Are there any good methods of how to handle this. It is more of a communications issue to myself of the status of the calculation in the overall excel system. [Note: I do not want to always be doing a full calculate becuase of the time it takes but, as an additional note, on the BeforePrint event I do have a full calculate becuase I cannot take the chance that something is not fully calculated when printed.] Thank you for your help, Steven |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving time on calculate
Unless you put this message in yourself, I would asssume that you are in
Manual calculation mode. In this mode you will get this message after changes. I would guess that either the routine you are runnin does something to the sheet that is recognizes as a change, or the recalcualtion done via VBA is not picked up by the workbook. A couple of suggestions to try, make sure your calculate statement is at the very end In addition to the caluclate statement toggle the calculation on/off like this Application.Calculation = xlCalculationAutomatic Application.calcualtion = xlCalculationManual In testing you code you can use the Application.CalcualtionState property to help determine what the system is showing for calcualtion status. -- If this helps, please remember to click yes. "Steven" wrote: I have a situation where the calculation takes too long and so I put this in the ThisWorkbook module "SheetChange" event: ActiveWorkbook.ActiveSheet.Calculate This makes the calculation much faster and I am confident everything is ok becuase all that needed to be calculated was the current sheet but the status bar now reads...: "Ready Calculate" :.... becuase there was not a full calculation done. Are there any good methods of how to handle this. It is more of a communications issue to myself of the status of the calculation in the overall excel system. [Note: I do not want to always be doing a full calculate becuase of the time it takes but, as an additional note, on the BeforePrint event I do have a full calculate becuase I cannot take the chance that something is not fully calculated when printed.] Thank you for your help, Steven |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving time on calculate
When you do an activesheet.calculate any formulae in other sheets that refer
to the activesheet do not get updated (as well as volatile formulae on the other sheets), and the cells on the activesheet that were calculated will also be recalculated at the next calculate. If an F9 recalculation takes much longer than the ActiveSheet.calculate (Shift-F9) then either you have a lot of volatile formulae on the other sheets or the other sheets DO depend on the Activesheet. If you want to be reminded about the status of the calculation you could put a message in the status bar after the Sheet.Calculate Application.StatusBar="Sheet '" & activesheet.name & "' Calculated" and then Trap F9 using Onkey to do an Application.Calculate and Application.Statusbar=False to reset the message. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Steven" wrote in message ... I have a situation where the calculation takes too long and so I put this in the ThisWorkbook module "SheetChange" event: ActiveWorkbook.ActiveSheet.Calculate This makes the calculation much faster and I am confident everything is ok becuase all that needed to be calculated was the current sheet but the status bar now reads...: "Ready Calculate" :.... becuase there was not a full calculation done. Are there any good methods of how to handle this. It is more of a communications issue to myself of the status of the calculation in the overall excel system. [Note: I do not want to always be doing a full calculate becuase of the time it takes but, as an additional note, on the BeforePrint event I do have a full calculate becuase I cannot take the chance that something is not fully calculated when printed.] Thank you for your help, Steven |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 calculate time between 2 date/time columns | Excel Discussion (Misc queries) | |||
Calculate Ending time using Start Time and Elapsed Time | Excel Worksheet Functions | |||
Saving a file with time and date at a set time period | Excel Programming | |||
Daylight Saving Time (DST) and calculating time difference. | Excel Programming | |||
Daylight Saving Time (DST) and calculating time difference. | Excel Programming |