Excel VBA Macro stops running when another program is activated

[Brody]

Using a dual processor machine running windows 2000 using excel 2000, I run a
vba script that takes a while to complete. The task was taking exceptionally
long to complete, so I looked at the task manager to see how much CPU power
excel was using.

When the VBA editor is the active window, excel uses 50% of the processing
power (1 full processor), however, as soon as another non excel window is
activated, the cpu usage for excel drops to 0% (ie it stops running).

How do I get excel to run VBA scripts when it is NOT the active application?

Thank you

 

[John.Greenan]

This is what I think is happening - the other non-excel window is bound to
the same CPU as the excel window and so it's blocking excel from running.
So, what you have to do is bind the non-excel window to one CPU and excel to
the other CPU. You do this manually by opening Task Manager, Processes and
then right clicking on the process and then "Set Affinity". There is a way
to do this programatically using VBA and the Windows API but I do not have
that code with me.

Give that a try and post back, if it works or not.

 

[Brody]

Thanks for the help, unfortunately it hasn't solved the problem yet.

I tried setting the affinity to both and either cpu with no effect. The
non-excel window doesn't have to use much cpu power (ie just activating the
task manager window will stop excel), both cpu's are idle until I re-activate
the excel window.

If there are any other suggestions I would appreciate it.

Thanks

 

[John.Greenan]

Hi Brody,

This sounds like the code may be a bit screwy. Can you post some? Does the
code use focus ??

 

[Brody]

Here is the most common loop in the code:

-----------------------------------------------------------------
Worksheets.Application.ScreenUpdating = False

Dim cur_cell As Range

Set cur_cell = Cells.Find(What:="A", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)
While Not cur_cell Is Nothing
cur_cell.Offset(, -1).Activate
cur_cell.Delete Shift:=xlToLeft
Set cur_cell = Cells.Find(What:="A", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)
Wend
Worksheets.Application.ScreenUpdating = True

 

[Brody]

I have been able to work around the problem, although I do not know the
source. When starting the macro from the VB editor, the problem persists,
however, if the macro is started from the excel spreadsheet, I am able to
switch active windows to another application without causing the macro to
pause running.

Brody