Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Calculate vs. F9
One of my colleagues has a spreadsheet that contains a great deal of
complicated calculations. This is in XL2003. When he hits F9 to force Excel to calculate, there is a noticable pause - 3 seconds or so - for all the recalcing to occur. He also has a macro in which he uses the Application.Calculate and it runs a lot quicker. His concern is that VBA is not waiting for the calculation to complete before moving to the next line. Is there a difference in what Excel does when you programmatically calculate vs. using F9? I'm asking this on behalf of a co-worker and have not seen the workbook in question. -- Hmm...they have the Internet on COMPUTERS now! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Calculate vs. F9
Check out this link. Everything you ever wanted to know about calculations
but were affraid to ask... http://www.decisionmodels.com/calcsecrets.htm -- HTH... Jim Thomlinson "MDW" wrote: One of my colleagues has a spreadsheet that contains a great deal of complicated calculations. This is in XL2003. When he hits F9 to force Excel to calculate, there is a noticable pause - 3 seconds or so - for all the recalcing to occur. He also has a macro in which he uses the Application.Calculate and it runs a lot quicker. His concern is that VBA is not waiting for the calculation to complete before moving to the next line. Is there a difference in what Excel does when you programmatically calculate vs. using F9? I'm asking this on behalf of a co-worker and have not seen the workbook in question. -- Hmm...they have the Internet on COMPUTERS now! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Calculate vs. F9
Thanks. That's a lot of useful information; however, it doesn't answer the
root questions of my post - If Application.Calculate and F9 are the same (which, according to that link, they are), why does it take so much longer for the F9, and how do we know that VBA is waiting for the Application.Calculate to be completed before it goes to the next line of code? -- Hmm...they have the Internet on COMPUTERS now! "Jim Thomlinson" wrote: Check out this link. Everything you ever wanted to know about calculations but were affraid to ask... http://www.decisionmodels.com/calcsecrets.htm -- HTH... Jim Thomlinson "MDW" wrote: One of my colleagues has a spreadsheet that contains a great deal of complicated calculations. This is in XL2003. When he hits F9 to force Excel to calculate, there is a noticable pause - 3 seconds or so - for all the recalcing to occur. He also has a macro in which he uses the Application.Calculate and it runs a lot quicker. His concern is that VBA is not waiting for the calculation to complete before moving to the next line. Is there a difference in what Excel does when you programmatically calculate vs. using F9? I'm asking this on behalf of a co-worker and have not seen the workbook in question. -- Hmm...they have the Internet on COMPUTERS now! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Calculate vs. F9
The length of time it takes to recaluclate depends on the number of dirty
cells. The more cells that have been flagged as dirty the longer the calculation will take. If you hit F9 twice in a row, the first calulation will take a while but the second calc should be almost instantaneous as all of the dirty flags will have been removed (assuming less than 65,535 dependancies and few volatile functions). As for the code continuing execution prior to the calculations finishing, the calculation will complete prior to the code continuing on. -- HTH... Jim Thomlinson "MDW" wrote: Thanks. That's a lot of useful information; however, it doesn't answer the root questions of my post - If Application.Calculate and F9 are the same (which, according to that link, they are), why does it take so much longer for the F9, and how do we know that VBA is waiting for the Application.Calculate to be completed before it goes to the next line of code? -- Hmm...they have the Internet on COMPUTERS now! "Jim Thomlinson" wrote: Check out this link. Everything you ever wanted to know about calculations but were affraid to ask... http://www.decisionmodels.com/calcsecrets.htm -- HTH... Jim Thomlinson "MDW" wrote: One of my colleagues has a spreadsheet that contains a great deal of complicated calculations. This is in XL2003. When he hits F9 to force Excel to calculate, there is a noticable pause - 3 seconds or so - for all the recalcing to occur. He also has a macro in which he uses the Application.Calculate and it runs a lot quicker. His concern is that VBA is not waiting for the calculation to complete before moving to the next line. Is there a difference in what Excel does when you programmatically calculate vs. using F9? I'm asking this on behalf of a co-worker and have not seen the workbook in question. -- Hmm...they have the Internet on COMPUTERS now! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Calculate vs. F9
If you have a lot of VBA user-defined functions Application.calculate runs a
lot faster than F9 (unless you trap F9 with an Application.Onkey statement and redirect to Application.Calculate) see http://www.decisionModels.com/calcsecretsj.htm for more details Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "MDW" wrote in message ... Thanks. That's a lot of useful information; however, it doesn't answer the root questions of my post - If Application.Calculate and F9 are the same (which, according to that link, they are), why does it take so much longer for the F9, and how do we know that VBA is waiting for the Application.Calculate to be completed before it goes to the next line of code? -- Hmm...they have the Internet on COMPUTERS now! "Jim Thomlinson" wrote: Check out this link. Everything you ever wanted to know about calculations but were affraid to ask... http://www.decisionmodels.com/calcsecrets.htm -- HTH... Jim Thomlinson "MDW" wrote: One of my colleagues has a spreadsheet that contains a great deal of complicated calculations. This is in XL2003. When he hits F9 to force Excel to calculate, there is a noticable pause - 3 seconds or so - for all the recalcing to occur. He also has a macro in which he uses the Application.Calculate and it runs a lot quicker. His concern is that VBA is not waiting for the calculation to complete before moving to the next line. Is there a difference in what Excel does when you programmatically calculate vs. using F9? I'm asking this on behalf of a co-worker and have not seen the workbook in question. -- Hmm...they have the Internet on COMPUTERS now! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Calculate vs. F9
Thanks.
I asked him to send me the file, and he said he couldn't because it used an add-in that I didn't have. That helped clarify things, and I ended up finding info about the perfomance issue he http://www.decisionmodels.com/calcse...tm#performance ("Automatic and Function key Calculation slower than VBA calculation") -- Hmm...they have the Internet on COMPUTERS now! "Jim Thomlinson" wrote: The length of time it takes to recaluclate depends on the number of dirty cells. The more cells that have been flagged as dirty the longer the calculation will take. If you hit F9 twice in a row, the first calulation will take a while but the second calc should be almost instantaneous as all of the dirty flags will have been removed (assuming less than 65,535 dependancies and few volatile functions). As for the code continuing execution prior to the calculations finishing, the calculation will complete prior to the code continuing on. -- HTH... Jim Thomlinson "MDW" wrote: Thanks. That's a lot of useful information; however, it doesn't answer the root questions of my post - If Application.Calculate and F9 are the same (which, according to that link, they are), why does it take so much longer for the F9, and how do we know that VBA is waiting for the Application.Calculate to be completed before it goes to the next line of code? -- Hmm...they have the Internet on COMPUTERS now! "Jim Thomlinson" wrote: Check out this link. Everything you ever wanted to know about calculations but were affraid to ask... http://www.decisionmodels.com/calcsecrets.htm -- HTH... Jim Thomlinson "MDW" wrote: One of my colleagues has a spreadsheet that contains a great deal of complicated calculations. This is in XL2003. When he hits F9 to force Excel to calculate, there is a noticable pause - 3 seconds or so - for all the recalcing to occur. He also has a macro in which he uses the Application.Calculate and it runs a lot quicker. His concern is that VBA is not waiting for the calculation to complete before moving to the next line. Is there a difference in what Excel does when you programmatically calculate vs. using F9? I'm asking this on behalf of a co-worker and have not seen the workbook in question. -- Hmm...they have the Internet on COMPUTERS now! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Calculate vs. F9
Thanks... I neglected to mention that one... I was assuming it was a smart
calc issue. By the way, excellent web site. -- HTH... Jim Thomlinson "Charles Williams" wrote: If you have a lot of VBA user-defined functions Application.calculate runs a lot faster than F9 (unless you trap F9 with an Application.Onkey statement and redirect to Application.Calculate) see http://www.decisionModels.com/calcsecretsj.htm for more details Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "MDW" wrote in message ... Thanks. That's a lot of useful information; however, it doesn't answer the root questions of my post - If Application.Calculate and F9 are the same (which, according to that link, they are), why does it take so much longer for the F9, and how do we know that VBA is waiting for the Application.Calculate to be completed before it goes to the next line of code? -- Hmm...they have the Internet on COMPUTERS now! "Jim Thomlinson" wrote: Check out this link. Everything you ever wanted to know about calculations but were affraid to ask... http://www.decisionmodels.com/calcsecrets.htm -- HTH... Jim Thomlinson "MDW" wrote: One of my colleagues has a spreadsheet that contains a great deal of complicated calculations. This is in XL2003. When he hits F9 to force Excel to calculate, there is a noticable pause - 3 seconds or so - for all the recalcing to occur. He also has a macro in which he uses the Application.Calculate and it runs a lot quicker. His concern is that VBA is not waiting for the calculation to complete before moving to the next line. Is there a difference in what Excel does when you programmatically calculate vs. using F9? I'm asking this on behalf of a co-worker and have not seen the workbook in question. -- Hmm...they have the Internet on COMPUTERS now! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace application.RTD property by Application.RTDServers collect | Excel Programming | |||
Application.Calculate & VLOOKUP | Excel Programming | |||
Application.Calculate & VLOOKUP | Excel Programming | |||
"Application.Calculate" does not always update/recalculate the for | Excel Programming | |||
macro to close excel application other than application.quit | Excel Programming |