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!

If ScreenUpdataing is false then Excel will not redraw, so that will keep the
statusbar from updating, as far as I know. You might want to try some other
way to show status, such as a userform with either a message or a progressbar
on it.

Also, if you haven't done it, put a DoEvents statement inside any of the
larger loops you have in the code. This will help avoid the total freezeup
as your code processes.
--
- K Dales


" wrote:

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!

For what it is worth, I have this same issue. It seems to occur mostly on
large processes (those that take more than a few minutes).

This is a new issue in Office 2003, since I have code developed in earlier
versions and this never happened until the '03 upgrade. I have not yet been
able to recover this functionality, but I will try the "ScreenUpdating" angle
next time.


" wrote:

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!

I'd try the doEvents, but I think screenupdating won't stop the statusbar from
changing.



K Dales wrote:

If ScreenUpdataing is false then Excel will not redraw, so that will keep the
statusbar from updating, as far as I know. You might want to try some other
way to show status, such as a userform with either a message or a progressbar
on it.

Also, if you haven't done it, put a DoEvents statement inside any of the
larger loops you have in the code. This will help avoid the total freezeup
as your code processes.
--
- K Dales

" wrote:

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!



--

Dave Peterson

Wow! Inserting a single Doevents command before application.statusbar
SOLVED it like magic. Thank you all so much!


quartz wrote:
For what it is worth, I have this same issue. It seems to occur mostly on
large processes (those that take more than a few minutes).

This is a new issue in Office 2003, since I have code developed in earlier
versions and this never happened until the '03 upgrade. I have not yet been
able to recover this functionality, but I will try the "ScreenUpdating" angle
next time.


" wrote:

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!