Sheet.Calculate limits??

L

Luz

Hello,
I have a macro that does the following (Excel 2000):
Sheets("MySheet").select
ActiveSheet.EnableCalculation = False

<Write 1000's of v-lookup formulas in sheet>
<e.g. i have one instance with ~ 12000 formulas>

ActiveSheet.EnableCalculation = True
ActiveSheet.Calculate

<Overwrite all formulas with values through copy &
paste-values commands...>


The problem: the last 20-30 rows (about 400-600 formulas)
do not calculate before the code proceeds with the
overwrite.
How can this happen?
How can it be fixed?

Thanks,
Luz
 
B

BrianB

Strange. This happens when connecting Excel to external applications bu
I have not come across this problem. The macro runs faster than th
application can complete the process.

A fix is supposed to be to add the line
DoEvents
in your case, after the calculate command, but I have never found thi
to work very well - so, in addition, ("belt and braces") I add a Wai
command to stop the macro working for a few seconds, tweaking th
amount of time to suit. eg. 4 seconds
Application.Wait Now + TimeValue("00:00:04")

Hope this helps
 
L

Luz

Thanks very much!
My program does not interact with any other applications,
so I have been quite puzzled by this. I thought about the
wait command, but though I'd ask to see if there was
something better. I guess not... But the DoEvents is a
new one for me and so I'll try it. Thanks so much!
Luz
 
Top