Progress bar for multiple routines

[tx12345]

hi

i have looked at many threads about progress bars, but still not sure where the code fits in.

A simple outline of my code looks like this:

sub dothis()

'copy something
'open a workbook
'paste something

Do Until "A1".Value = "100"

'run time consuming macro (s)

Loop
'close a workbook
'the end

first question is, in this basic example, where does one put the progress bar code? that;d be a big step in the right direction.


this particular macro actually runs 7 processes looping 100 times each, all with non-looping code in between

to keep it simple, i was thinking of having a separate userform pop up just prior to each loop along the path. not as nice as one long seamless progress bar, but it is better than nothing.

if there was some way to make the progress of the progress bar dependent on the completion of each routine, this would also be an idea. but even better, if there were a way to not only account for the completion of each macro, but also include the progress of the loops along the way, that'd be amazing

any ideas would be really great.

tx







' cop

 

[erik.van.geit]

Hi,

1st question is:
"Can you avoid this time-consuming procedure and replace it with a quick-trick?"
when seeing code like
Do Until "A1".Value = "100"
I think you can probably enhance your code to be faster.
2nd question is:
"did you consider using the statusbar ?" I think, progressbars can slow down the code even more ...

the progressbar needs to be within the loop anyway

feel free to ask more
Erik

 

[tx12345]

hi, and thanks for the reply

the routine in bewtween the do and the loop is pretty complex; lots of stuff going on. to change it would mean tearing down the entire file(s) and rebuilding from scratch. maybe in the future.

the status bar idea sounds like a possibility, but i run all my exel stuff with userforms, the application is always hidden.

if there is a way to do use a status bar with xcel hidden i'd be interested

or, for a start, what the code in the loop ought to be.

tx

 

[erik.van.geit]

an example with statusbar

Option Explicit

Sub throw_die()
'Erik Van Geit
'060403
'chance to get at least a 6 when throwing to dies
Dim i As Long
Dim j As Long

Const loops = 5000000

    With Application
    .DisplayStatusBar = True
        For i = 1 To loops
        If Int(Rnd * 6) + 1 = 6 Then j = j + 1 Else If Int(Rnd * 6) + 1 = 6 Then j = j + 1
        If i Mod 1000 = 0 Then .StatusBar = Round(i / loops, 2) * 100 & "%"
        Next i
    .StatusBar = False
    End With
    
MsgBox "loops:" & vbTab & loops & vbLf & "got a six:" & vbTab & j & _
vbLf & "chance:" & vbTab & Round((j / loops) * 36, 0) & "/36"

End Sub

of course if you're application is hidden, you can't see the statusbar

statusbar or progressbar: anyway you need two values
the number of loops to do (loops in my example)
the current item which is handled (i)

are you sure of this ?

lots of stuff going on. to change it would mean tearing down the entire file(s) and rebuilding from scratch. maybe in the future.

it's up to you to decide how much time you want to spend getting the macro quicker, compared to the extratime it will need to run

best regards,
Erik