Programming the Progress Bar problem

[Guest]

I am using the following code to genereate messages and control the progress
bar. My Question is, how do I fit this around my code so that the code I want
to run runs, and that the progress bar is also run.


Sub ProgressBarAdvancedDemo()
Dim sb As clsProgressBar, i As Long
Set sb = New clsProgressBar
sb.Show "Please wait", "Initializing...", 0

For i = 0 To 30 Step 5
sb.PercentComplete = i
WaitSeconds 1
Next i

sb.PreMessageFG_RGB = RGB(255, 255, 255)

For i = 35 To 70 Step 5
sb.PercentComplete = i
WaitSeconds 1
Next i
sb.ProgressBarFG_RGB = RGB(255, 0, 0)
sb.PreMessage = "Still doing nothing much"

For i = 75 To 90 Step 5
sb.PercentComplete = i
WaitSeconds 1
Next i

sb.PostMessage = "Almost there!"

For i = 95 To 100 Step 5
sb.PercentComplete = i
WaitSeconds 1
Next i

Set sb = Nothing
End Sub

 

[Bob Phillips]

You have to initiate the progress bar code from within your code, and then
at suitable steps and break points within that code, call the update code.

So, instead of this code

For i = 0 To 30 Step 5
sb.PercentComplete = i
WaitSeconds 1
Next i

sb.ProMessageFG_RGB = RGB(255, 255, 255)

you would have your code loop, and break out at suitable points to do the
sb.ProMessage

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

You would place your code where you have all the do loops now.

You code would have to either be done in a do loop or have many sequential
steps. If a do loop, within the loop, you would compute a percentage
complete and update the bar as the dummy code does now. If many sequential
steps, then you would intersperse code to update the progress bar.

If you are doing something like executing a single command (opening a big
file for exemple), your code would wait for that command to complete and you
would not be able to update a progress bar.

 

[Robin Hammond]

Jeff,

Not sure if this will help, or how committed to you are your current
solution, but there is an alternative prog bar class on my site and some
illustrative code on how to use it.

http://www.enhanceddatasystems.com/ED/Pages/ExcelProgressBar.htm

Robin Hammond
www.enhanceddatasystems.com

 

[Guest]

I have gone to Robin's site and updated to the latest progress bar code.
However when I try to run it and my code together my code takes 100x longer
to run. here is how I've tucked in my code.
Dim PB As clsProgBar
Dim nCounter As Integer
Dim lWaitCount As Long

Set PB = New clsProgBar

With PB
.Title = "Enhanced Datasystems Progress Bar"
.Caption2 = "This is caption 2"
.Caption3 = "This is caption 3"
.Show

For nCounter = 0 To 33
Call Start
.Progress = nCounter
.Caption1 = "Progress message " & CStr(nCounter)
For lWaitCount = 0 To 1000000
If UserCancelled1 = True Then GoTo EndRoutine
Next lWaitCount
Next nCounter

For nCounter = 33 To 66
Call Bring_In_LE
.Progress = nCounter
.Caption1 = "Progress message " & CStr(nCounter)
For lWaitCount = 0 To 1 '000000
If UserCancelled1 = True Then GoTo EndRoutine
Next lWaitCount
Next nCounter

For nCounter = 66 To 100
Call Bring_In_Actuals
.Progress = nCounter
.Caption1 = "Progress message " & CStr(nCounter)
For lWaitCount = 0 To 1 '000000
If UserCancelled1 = True Then GoTo EndRoutine
Next lWaitCount
Next nCounter
EndRoutine:

.Finish

End With

Set PB = Nothing

MsgBox "You have finished importing LE's & Actuals"
End Sub

 

[Robin Hammond]

Jeff,

Without knowing exactly what you are trying to do it's hard to advise, but
I've rationalised what your code does in two different routines below. The
first calls the Start and BringIn routines 33 times each as you do in your
posted code, which seems odd. The second calls them once. I'm not sure which
is appropriate. Have a look at the two examples below.

HTH

Robin Hammond
www.enhanceddatasystems.com

Sub ImportData()
'original structure which calls Start, BringInLE and BringInActuals within
loops
Dim PB As clsProgBar
Dim nCounter As Integer

Set PB = New clsProgBar

With PB

.Title = "Importing"
.Show
.Caption1 = "Starting Import..."
DoEvents

For nCounter = 0 To 33

Call Start
.Progress = nCounter
If UserCancelled1 = True Then GoTo EndRoutine

Next nCounter

.Caption1 = "Bringing in LE..." 'whatever that is

For nCounter = 33 To 66

Call Bring_In_LE
.Progress = nCounter
If UserCancelled1 = True Then GoTo EndRoutine

Next nCounter

.Caption1 = "Bringing in Actuals"

For nCounter = 66 To 100

Call Bring_In_Actuals
.Progress = nCounter

If UserCancelled1 = True Then GoTo EndRoutine

Next nCounter

EndRoutine:

.Finish

End With

Set PB = Nothing

MsgBox "You have finished importing LE's & Actuals"
End Sub

Sub ImportData2()
'revised version, assumes you only need to call Start, etc once
Dim PB As clsProgBar
Dim nCounter As Integer

Set PB = New clsProgBar

With PB

.Title = "Importing"
.Caption1 = "Starting Import..."
.Show
DoEvents

Start
If UserCancelled1 = True Then GoTo EndRoutine
.Progress = 30

.Caption1 = "Bringing in LE..." 'whatever that is
Bring_In_LE
If UserCancelled1 = True Then GoTo EndRoutine
.Progress = 60

.Caption1 = "Bringing in Actuals"
Bring_In_Actuals

EndRoutine:

.Finish

End With

Set PB = Nothing
MsgBox "You have finished importing LE's & Actuals"
End Sub

 

[Jane]

I'm a noob when it comes to programming. So please bare with me, how can I
run this code at the same time with my macro that I want run?

 

[Robin Hammond]

Jane,

two ways. You can either wrap the progress bar around the call to the macro
in the calling routine, or write the code into the macro.

e.g. PB in the calling routine as follows

Sub MainCallingRoutine
Dim PB as clsProgBar

set PB = New clsProgBar

with PB

.caption1 = "calling sub macro now"
.show
doevents

end with

Call SubRoutine

PB.Finish
Set PB = Nothing
End Sub

Sub SubRoutine
'do something here
End Sub

or the alternative approach to embed it in the macro itself

Sub SubRoutine
Dim PB as clsProgBar
Set PB = new clsProgBar
PB.Show

For nCounter = 1 to 100

'do something here
PB.Caption1 = "done " & nCounter & " iterations so far"
PB.Progress = nCounter

Next nCounter

PB.Finish
Set PB = Nothing

End Sub

The example on my site spells out that you have to include frmProgress and
clsProgBar in your project for this to work of course.

HTH,

Robin Hammond
www.enhanceddatasystems.com