3

Of late I have been writing macros which require substantial amount of time to execute. (upwards of 5 minutes but some are significantly longer). One of the things I found useful in such cases (other than to wait) is to have Application.StatusBar tell me if it is moving or hung.

However, many times, the Application.Statusbar gets frozen at some value while the program moves ahead. How can this be rectified? Is there anyway we can prevent this so that the statusbar keeps moving as long as the program is moving? Thanks in advance.

Community
  • 1
  • 1
Peekay
  • 238
  • 11
  • 21
  • 1
    Use [DoEvents](http://stackoverflow.com/questions/2169975/is-it-better-to-show-progressbar-userforms-in-vba-as-modal-or-modeless/2170083#comment2115727_2170083). – GSerg Mar 24 '14 at 09:43
  • You mean simply add "DoEvents" after Application.StatusBar = i line?. Thanks. Not sure I understood the Modal Modeless discussion in the link posted by you. – Peekay Mar 24 '14 at 15:33
  • 1
    Yes, I mean simply that. This will make the macro much slower, so you want to use DoEvents only every so often, not on each iteration. – GSerg Mar 24 '14 at 16:07
  • Ok. Is there also a way of flashing the screen once in a while when Screenupdating is set to False? (I dont want to introduce another if else logic to say if i is a multiple of 100, then screenupdating is true and then again set to false. This will mean that every i will again be checked whether it is a multiple of 100). Some kind of "listen" functionality which gets triggered when i takes some value is what I am looking for – Peekay Mar 24 '14 at 16:12
  • If you do not understand the difference between modal and modeless, this answer of mine may help. http://stackoverflow.com/a/20315264/973283 – Tony Dallimore Mar 25 '14 at 01:05
  • 1
    NB: Be careful with `DoEvents`. It will tell the application to process all of the other actions in the queue before continuing on. This could include another run of your macro, so users could accidentally run the process twice if they click the button twice. – CodeJockey Mar 03 '17 at 15:57

1 Answers1

1

I found that the freeze happens at my code when I have many items in the loop. I got around that by using the following code:

For RowNum = 1 To TotalRows
  For ColNum = 1 To TotalCols
    'code
      If ColNum = 1 Then
        If RowNum Mod 50 = 0 And ColNum = 1 Then
          Application.StatusBar = Format(RowNum / TotalRows, "0%")& " Completed." 
        End If
      Else: Exit For
      End If
  Next ColNum
Next RowNum

The loop updates the status bar once every 50 rows. This helped the code run a lot faster and eliminated the screen freezing up

user7994116
  • 11
  • 1