How do I let Excel breath?

[Jaymond Flurrie]

I run a long, demanding VBA code task with Excel and I would like to let my user to see that something is happening. Excel does application.statusbar nicely for a while until it "chokes" and stops updating. What's the smartest way to let Excel "breath"?

I tried this:

If x Mod 250 = 0 Then
                Application.StatusBar = "Running the task, " & x & "/" & lTotal & " = " & Format(x / lTotal, "0.00%") & " done"
                If x Mod 5000 = 0 Then
                    Application.Wait (10)
                End If
            End If

but it still "chokes". Other ideas?

 

[Rasm]

I have very large charts - scattered all over the sheet - so I have a counter - when it reaches 5K (cells process) then I display my statusbar (like % thing you have) - I have to read the data in arrays as the range value cannot exceed 255 characters - I then reset counter and let it start all over. The max number of points in most charts are 32K - so I start new charts if I have more that 32K points - it may take Excel several minutes - but it does not choke. But be carefull not to have arrays that are to large - or in my case try to stuff arrays into charts above the limits - the Excel does freeze - without even getting an error. Not sure if this helps

 

[Jaymond Flurrie]

I have very large charts - scattered all over the sheet - so I have a counter - when it reaches 5K (cells process) then I display my statusbar (like % thing you have) - I have to read the data in arrays as the range value cannot exceed 255 characters - I then reset counter and let it start all over. The max number of points in most charts are 32K - so I start new charts if I have more that 32K points - it may take Excel several minutes - but it does not choke. But be carefull not to have arrays that are to large - or in my case try to stuff arrays into charts above the limits - the Excel does freeze - without even getting an error. Not sure if this helps

Hmmm... I have to try that too. I found out that switching application.screenupdating off and on helps too.

I just coded a arraysplitter that uses 20x5000 rows instead of 1x100000 rows. Excel seemed to freeze every now and then with those 100000, but I haven't tested enough to say what it says about that 20x5000.

 

[Ruddles]

Application.Wait (10)

I don't think this does what you think it does. Are you trying to pause for 10ms?

I think it's waiting until 10th January 1900. (See http://msdn.microsoft.com/en-us/library/aa213656(v=office.11).aspx.)

Although I'm fairly sure this isn't the cause of your problem.