Application.Calculate

[Alex St-Pierre]

Hi!
I have an excel file that calculate 10 000 simulations. For each simulation,
I use the Application.Calculate (takes 3 seconds) to refresh the result.
Thereafter, I copy the interesting result inside a cell and I do it for all
simulations. The problem I have is that Application.Calculate doesn't refresh
all the workbook. File= 130 mo.
Is there anything else I can write to ensure all calculations have been done?
Thank you!
Alex

 

[JP]

Hi,
Are you using "application.volatile = false"?

Does the status bar say "Calculate" even after you do a manual
calculation?

 

[Alex St-Pierre]

Hi!

Are you using "application.volatile = false"? I don't know this function
Does the status bar say "Calculate" even after you do a manual
calculation?

It takes 3 seconds to calculate as when I do an "Application.Calculate".
When this is done, the status bar indicate doesn't indicate calculate..
Sometimes, I do a F9 and even after the calculation, the results have changed
but the data is not correct. Then, if I go in the cell that make the
"=average(C2:M1000)" and edit the function, the value is changing when I
press Enter.. This happens not often but should not happens. I don't know if
there's a way to be sure all calculations are done in VBA?
Thanks a lot!
Alex

 

[JP]

Alex,
You could try "Application.CalculateFull" in your routine, that will
force a full calculation of all the data in all open workbooks. But it
sounds like Excel is having trouble keeping track of all your
formulas, you might want to try some ways to make your data more
compact so Excel isn't working so hard.

Hope this helps,
JP

 

[Alex St-Pierre]

Yeah.. The problem is the calculation are so complex. There's no equation..
we must find the solution using simulation. Is there a way to make a break
between each record or a wait .. I'll try it.
Thanks!
Alex
--
Alex St-Pierre

Alex,
You could try "Application.CalculateFull" in your routine, that will
force a full calculation of all the data in all open workbooks. But it
sounds like Excel is having trouble keeping track of all your
formulas, you might want to try some ways to make your data more
compact so Excel isn't working so hard.

Hope this helps,
JP

 

[Alex St-Pierre]

I tried a wait but the problem is that there's no excel calculation during
that time.. I would like to let excel calculate but the wait seems to stop
the excel calculation... Any idea?
Thanks!
Alex
--
Alex St-Pierre

Alex,
You could try "Application.CalculateFull" in your routine, that will
force a full calculation of all the data in all open workbooks. But it
sounds like Excel is having trouble keeping track of all your
formulas, you might want to try some ways to make your data more
compact so Excel isn't working so hard.

Hope this helps,
JP

 

[Alex St-Pierre]

Does the status bar say "Calculate" even after you do a manual
calculation?
Yes,.. it's always written Calculate... (the calculation are set to manual)

 

[JP]

Maybe if you posted the code or the formulas you are using and/or some
more detail about what exactly you are doing, we could be of more
assistance.

--JP

 

[Niek Otten]

Hi Alex,

You could try to rebuild the dependency tree with CTRL+ALT+SHIFT+F9.

For lots of information about calculations in Excel, visit Charles William's site:


www.decisionmodels.com

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi!
| >Are you using "application.volatile = false"?
| I don't know this function
| >Does the status bar say "Calculate" even after you do a manual
| >calculation?
| It takes 3 seconds to calculate as when I do an "Application.Calculate".
| When this is done, the status bar indicate doesn't indicate calculate..
| Sometimes, I do a F9 and even after the calculation, the results have changed
| but the data is not correct. Then, if I go in the cell that make the
| "=average(C2:M1000)" and edit the function, the value is changing when I
| press Enter.. This happens not often but should not happens. I don't know if
| there's a way to be sure all calculations are done in VBA?
| Thanks a lot!
| Alex
| --
| Alex St-Pierre
|
|
| "JP" wrote:
|
| > Hi,
| > Are you using "application.volatile = false"?
| >
| > Does the status bar say "Calculate" even after you do a manual
| > calculation?
| >
| >
| >
| > On Oct 4, 2:49 pm, Alex St-Pierre
| > > Hi!
| > > I have an excel file that calculate 10 000 simulations. For each simulation,
| > > I use the Application.Calculate (takes 3 seconds) to refresh the result.
| > > Thereafter, I copy the interesting result inside a cell and I do it for all
| > > simulations. The problem I have is that Application.Calculate doesn't refresh
| > > all the workbook. File= 130 mo.
| > > Is there anything else I can write to ensure all calculations have been done?
| > > Thank you!
| > > Alex
| > > --
| > > Alex St-Pierre
| >
| >
| >

 

[JRForm]

Alex St-Pierre,

I agree with JP - can you post the code you are using. It sounds like a lot
of variables are in the spreadsheet. Do the calculations work when you do a
manual update?