excel performance

[miao jie]

right now I'm write a function with VBA in Excel which open a big data array
in memory. it takes around 20 minutes. This is OK and no problem.
But when I run it on my pc it is not possible to do something else (email,
internet, …) as soon as I start another program then this function comes in a
deadlock. XLS file does not respond anymore and I have to kill the process.
Even when the screensaver pops up the program does not continue anymore.
That means we only can run the program on a dedicated PC without an active
Screensaver.
I do not know that we can avoid this. Does anyone know a solution? thanks in
advance!

 

[Harald Staff]

Hi

If you put DoEvents in your code on useful places, it will stop and give way
to the operating system on those spots:

Sub Awful()
Dim L As Long, M As Long
For L = 1 To 3000000
For M = 1 To 200000
Next
Next
End Sub

Sub Good()
Dim L As Long, M As Long
For L = 1 To 3000000
DoEvents
For M = 1 To 200000
Next
Next
End Sub

HTH. Best wishes Harald