Sheet.Calculate limits??

  • Thread starter Thread starter Luz
  • Start date Start date
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
 
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
 
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
 
Back
Top