Progress bar for multiple routines

tx12345

Board Regular
Joined
Aug 28, 2006
Messages
165
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:

Code:
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Board Regular
Joined
Aug 28, 2006
Messages
165
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
an example with statusbar
Code:
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
 

Forum statistics

Threads
1,137,059
Messages
5,679,368
Members
419,823
Latest member
Mercy kiara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top