Solved: Progress Bar Help

[faye]

I've a marco that takes about a few minutes to complete and i wanted to implement a progress bar to indicate the process of the macro.

I've tried several variations of progress bar(including the ones in the KBase) and all worked on part of the codes. When i attempts to run it on the entire macro, everything went wrong. I really have no idea what's wrong.

Basically, my macro opens other workbooks and extracts data from it (stores in an array) and paste it onto the targeted workheet. The macros contains several extensive looping that slows down the system. The progress bar is not moving when the macro is running. Is it because of the system processing speed?

Please help.

Thanks in advance!

[johnske]

I've a marco that takes about a few minutes to complete and i wanted to implement a progress bar to indicate the process of the macro.

I've tried several variations of progress bar(including the ones in the KBase) and all worked on part of the codes. When i attempts to run it on the entire macro, everything went wrong. I really have no idea what's wrong.

Basically, my macro opens other workbooks and extracts data from it (stores in an array) and paste it onto the targeted workheet. The macros contains several extensive looping that slows down the system. The progress bar is not moving when the macro is running. Is it because of the system processing speed?

Please help.

Thanks in advance!

Without seeing your code... You have to put code in the loop to update the progress bar at the appropriate point(s) in the loop, such as after opening a workbook or pasting your data. You then usually have to put a "DoEvents" immediately after the code to ensure the progress bar is updated.

Progress bars will always slow the entire procedure down to some extent, the only real advantage being that at least you know something is happening and it's not a case of "Microsoft Excel not responding"

HTH,
John

[faye]

Thanks for the reply. I've several loops that ranges from 0 to 6, so is it possible to include in the codes?

Currently, I'm using the codes written by you that is found in one of the post you've replied

[VBA] Sub DoingSomething()

'declaring my variables here

With UserForm1
.Show
.ProgressBar1 = 1
End With
DoEvents

'code to do some things here

UserForm1.ProgressBar1 = 10
DoEvents

'more code to do some other things here

UserForm1.ProgressBar1 = 20
DoEvents

'more code here

UserForm1.ProgressBar1 = 30
DoEvents

'more code

UserForm1.ProgressBar1 = 40
DoEvents

' "
' "
' "
' "
'and so on till it's 100% done

End Sub
[/VBA]
thanks

[johnske]

Hi faye,

Your best bet is to remove all data you don't want made public from the main workbook then zip it and attach it (got to "manage attachments") so someone can get a better look at what all your code's doing and the best way to do this.

Regards,
John

[faye]

hi, i've attached the workbook. Sorry for the messy codes.

Thanks for the help.

[Zack Barresse]

Take a look at mvidas' Hall of Fame post on an IE Progress Bar, here: http://vbaexpress.com/forum/showthread.php?t=5833

[johnske]

Hi faye,

You're on the right track, have a look at the attachment. You could also consider updating ProgressBar2 now from within the smaller loops you have. (Alternatively, you can just delete it)

I've shuffled things around quite a bit, so you may not get quite the right results (Check them!)

Some general things... The command button wasn't working because you'd renamed the button

Either put your code in the same module used for the progress bar, or else call it from in there (I've put the main code in a standard module and called it from the userform module)

"Step 1" is the default step, there's no need to specify it

Try to keep all your variant declarations near the head of your code

Variant type "Long" is preferred over variant type "Integer"

Declaring variables in this form Dim a, b, c, d As Long declares a, b, c as Variant type variables and only d is declared as variant type "Long" in Visual Basic.

If you want them all declared as "Long" type variables you must do it explicitly like this: Dim a As Long, b As Long, c As Long, d As Long (note also that using variant type variables can slow code execution down quite considerably).

HTH,
John

EDIT: Sorry, forgot the attachment - here tis

[faye]

WOW. It worked and thanks for the all the advise. You guys are great!

Thank you