S
samer.kurdi
I have a macro that imports 3 different sheets of data with
40,000-50,000 rows each, updates 30 accounts through array formulas,
then converts the result to text for better performance and to make the
end sheet usable. The process takes about half an hour for a month's
worth of data, which is ok. I am, however, facing a strange problem as
follows:
I am using the Application.StatusBar command to generate messages that
tell me how far along the macro has gone; something like "Updating acct
1 of 30, day 11/19/2005" in the routine. The problem is that it works
for a while, but then invariably excel goes into some sort of "trance"
where it stops updating the message. The 'task manager', in fact, will
list excel as "Not responding"... however, if I wait it out Excel will
invariably finish updating and "come back to life". Still, I do not
like the uncertainty of waiting for 10-20 minutes not knowing whats
going on or how far along the updating process it has gone, and I don't
like interrupting the macro execution to find out (sometimes even that
takes ages to kick in).
I am guessing that the application.statusbar messages are interrupted
when other events happen outside the Excel environment (an email
notification, an instant message, opening or closing folders or other
application while it is running). I have tried shutting down anything
that might cause this but still run into the problem of the statusbar
not displaying messages after a while.
Any ideas as to how to "force" excel to keep updating the statusbar
messages? Note that I AM using Application.ScreenUpdating = False and
Application.DisplayAlerts = False in order to speed up execution --
could this have anything to do with whats happening?
I appreciate any ideas on this... thanks!
40,000-50,000 rows each, updates 30 accounts through array formulas,
then converts the result to text for better performance and to make the
end sheet usable. The process takes about half an hour for a month's
worth of data, which is ok. I am, however, facing a strange problem as
follows:
I am using the Application.StatusBar command to generate messages that
tell me how far along the macro has gone; something like "Updating acct
1 of 30, day 11/19/2005" in the routine. The problem is that it works
for a while, but then invariably excel goes into some sort of "trance"
where it stops updating the message. The 'task manager', in fact, will
list excel as "Not responding"... however, if I wait it out Excel will
invariably finish updating and "come back to life". Still, I do not
like the uncertainty of waiting for 10-20 minutes not knowing whats
going on or how far along the updating process it has gone, and I don't
like interrupting the macro execution to find out (sometimes even that
takes ages to kick in).
I am guessing that the application.statusbar messages are interrupted
when other events happen outside the Excel environment (an email
notification, an instant message, opening or closing folders or other
application while it is running). I have tried shutting down anything
that might cause this but still run into the problem of the statusbar
not displaying messages after a while.
Any ideas as to how to "force" excel to keep updating the statusbar
messages? Note that I AM using Application.ScreenUpdating = False and
Application.DisplayAlerts = False in order to speed up execution --
could this have anything to do with whats happening?
I appreciate any ideas on this... thanks!