Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Heavy/Slow Calculations
I have a workbook that, at certain times, has to make several thousand
calculations. I could just about go to lunch by the time this process completes. Is there some simple way to speed this up? It seems like I recall an article some place called a million calculations in a few seconds???? -- Thanks Shawn |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Heavy/Slow Calculations
Hi Shawn,
If not aleady doing so, wrap your code something like: Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With Cells.PageBreak = xlPageBreakNone 'Your code With Application .Calculation = CalcMode .ScreenUpdating = True End With I assume that your code is written to avoid selections. For specialist advice and information on matters pertaining to calculation, visit Charles Williams's DecisionModels site at: http://www.decisionmodels.com/optspeedd.htm --- Regards, Norman "Shawn" wrote in message ... I have a workbook that, at certain times, has to make several thousand calculations. I could just about go to lunch by the time this process completes. Is there some simple way to speed this up? It seems like I recall an article some place called a million calculations in a few seconds???? -- Thanks Shawn |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Heavy/Slow Calculations
The calculations aren't occuring in VBA but in various formulas embeded in
linked cells. -- Thanks Shawn "Norman Jones" wrote: Hi Shawn, If not aleady doing so, wrap your code something like: Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With Cells.PageBreak = xlPageBreakNone 'Your code With Application .Calculation = CalcMode .ScreenUpdating = True End With I assume that your code is written to avoid selections. For specialist advice and information on matters pertaining to calculation, visit Charles Williams's DecisionModels site at: http://www.decisionmodels.com/optspeedd.htm --- Regards, Norman "Shawn" wrote in message ... I have a workbook that, at certain times, has to make several thousand calculations. I could just about go to lunch by the time this process completes. Is there some simple way to speed this up? It seems like I recall an article some place called a million calculations in a few seconds???? -- Thanks Shawn |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Heavy/Slow Calculations
Hi Shawn,
The calculations aren't occuring in VBA but in various formulas embeded in linked cells. I was mislead by the fact that you posted to the programming group. However, the suggestion that you visit Charles Williams site stands. --- Regards, Norman "Shawn" wrote in message ... The calculations aren't occuring in VBA but in various formulas embeded in linked cells. -- Thanks Shawn "Norman Jones" wrote: Hi Shawn, If not aleady doing so, wrap your code something like: Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With Cells.PageBreak = xlPageBreakNone 'Your code With Application .Calculation = CalcMode .ScreenUpdating = True End With I assume that your code is written to avoid selections. For specialist advice and information on matters pertaining to calculation, visit Charles Williams's DecisionModels site at: http://www.decisionmodels.com/optspeedd.htm --- Regards, Norman "Shawn" wrote in message ... I have a workbook that, at certain times, has to make several thousand calculations. I could just about go to lunch by the time this process completes. Is there some simple way to speed this up? It seems like I recall an article some place called a million calculations in a few seconds???? -- Thanks Shawn |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Heavy/Slow Calculations
Sorry about posting in the wrong place. I generally do have programming
questions and my shortcut leads me straight here. Actually, the program in question is one I wrote several years ago. A "canned" report sheet referrences a very, very, very large table of data. It allows the user to manipulate several variables (date, proivder, county, type) which then casues the "canned report sheet" to change its results. There are 1,000s of calculations occuring when the provider changes a variable. I may just need to re-write the whole thing and see where my programming leads me considering the knowledge I have gained over the years. -- Thanks Shawn "Norman Jones" wrote: Hi Shawn, The calculations aren't occuring in VBA but in various formulas embeded in linked cells. I was mislead by the fact that you posted to the programming group. However, the suggestion that you visit Charles Williams site stands. --- Regards, Norman "Shawn" wrote in message ... The calculations aren't occuring in VBA but in various formulas embeded in linked cells. -- Thanks Shawn "Norman Jones" wrote: Hi Shawn, If not aleady doing so, wrap your code something like: Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With Cells.PageBreak = xlPageBreakNone 'Your code With Application .Calculation = CalcMode .ScreenUpdating = True End With I assume that your code is written to avoid selections. For specialist advice and information on matters pertaining to calculation, visit Charles Williams's DecisionModels site at: http://www.decisionmodels.com/optspeedd.htm --- Regards, Norman "Shawn" wrote in message ... I have a workbook that, at certain times, has to make several thousand calculations. I could just about go to lunch by the time this process completes. Is there some simple way to speed this up? It seems like I recall an article some place called a million calculations in a few seconds???? -- Thanks Shawn |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Heavy/Slow Calculations
Shawn,
I did a similar conversion in one of my workbooks where I drove the calculations off of a command button, but you could use the worksheet_change event to run a vba loop(s) to update your table(s). You might test for certain edit cells if you don't want updates on every single change, or give yourself a maintenance mode to make numerous changes and then calculate at when you're exit edit mode. I use a vertical scan loop with an embedded horizontal scan loop where the calculations take place. Whatever makes sense for your data set. As I recall, pulling all of those formulas out of the individual cells and replacing with vba code also reduced my file size as a hidden benefit. Roy "Shawn" wrote: Sorry about posting in the wrong place. I generally do have programming questions and my shortcut leads me straight here. Actually, the program in question is one I wrote several years ago. A "canned" report sheet referrences a very, very, very large table of data. It allows the user to manipulate several variables (date, proivder, county, type) which then casues the "canned report sheet" to change its results. There are 1,000s of calculations occuring when the provider changes a variable. I may just need to re-write the whole thing and see where my programming leads me considering the knowledge I have gained over the years. -- Thanks Shawn "Norman Jones" wrote: Hi Shawn, The calculations aren't occuring in VBA but in various formulas embeded in linked cells. I was mislead by the fact that you posted to the programming group. However, the suggestion that you visit Charles Williams site stands. --- Regards, Norman "Shawn" wrote in message ... The calculations aren't occuring in VBA but in various formulas embeded in linked cells. -- Thanks Shawn "Norman Jones" wrote: Hi Shawn, If not aleady doing so, wrap your code something like: Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With Cells.PageBreak = xlPageBreakNone 'Your code With Application .Calculation = CalcMode .ScreenUpdating = True End With I assume that your code is written to avoid selections. For specialist advice and information on matters pertaining to calculation, visit Charles Williams's DecisionModels site at: http://www.decisionmodels.com/optspeedd.htm --- Regards, Norman "Shawn" wrote in message ... I have a workbook that, at certain times, has to make several thousand calculations. I could just about go to lunch by the time this process completes. Is there some simple way to speed this up? It seems like I recall an article some place called a million calculations in a few seconds???? -- Thanks Shawn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slow Calculations | Excel Discussion (Misc queries) | |||
Slow Calculations | Excel Discussion (Misc queries) | |||
Slow Calculations with No Data | Excel Discussion (Misc queries) | |||
Calculations run slow in Excel 2002 SP-1 | Excel Discussion (Misc queries) | |||
Slow Calculations | Excel Discussion (Misc queries) |