Any way of making this progress indicator more efficient?

[PTP86]

Hi

I've got a macro that has a set of loops like this

For i = 1 to 10
    For j = 1 to 1000
         Do this
    Next j
Next i

And I've been using Application.StatusBar = .......
to indicate the progress

First approach
--------------
At first I measured progress in terms of i and so had the Application.StatusBar command outside the j loop. It was annoying how the amount of time it took for j to get through 1000 loops (i.e. for the progress indicator to show anything has progressed) varied so much between each execution of the macro.

Second approach
-----------------
So I thought it would be worth experimenting with putting the Application.Statusbar command inside the j loop instead. It seems a lot smoother / not half as slow as it did before......But this can't be right can it?

If I test these two approaches with the progress indicator on the most simple of macros to fill 100,000 cells with numbers 1 to 100,000, then it takes 7 seconds using the first approach and 120 seconds using the second approach.

It seems like updating the status bar 1000's of times is what slows it down

So I was wondering, is there a way of having it report the progress on the j's from 1 to 1000 without making it update the status bar for every j e.g. maybe only update the status bar for every 50 j's ?


Thanks

 

[xenou]

Hi PTP86,

You can use the Mod operator at whichever value seems best:

For i = 1 to 10
    For j = 1 to 1000
   
    [COLOR="RoyalBlue"]    If j Mod 50 = 0 Then[/COLOR]
            Application.StatusBar = i & " / " & j
    [COLOR="RoyalBlue"]    End If[/COLOR]
		 
        'More Code
    
    Next j
Next i

HTH,
ξ