Time Based Progress Indicator??

[mcdowell]

I have a need to utilize a progress indicator which is time based.
Converting ascii "text" files to spreadsheets takes appx 5 min per
file. The conversion process can be for 1 or more files (up to 5). At
start time the number of files is determined. If processing 1 file
only the run time = 5 min, if 2 files run time = 10 min, etc. I would
like to display a progress indicator utilizing the system clock to
reflect the time left.

I have a UserForm "fmProgress" which has 2 "stacked" labels wiith
different colors.... That's as far as I can go! I need help with
writing code to make the top should grow (left to right) to cover the
bottom as a way to reflect the progress. I am also in the dark when
it comes to working with the system clock and using it to increment the
growth of the top label. If you have example code constructed (or
could construct same) I would greatly appreciate it!

I know that some of you have "been there, done that", hence I ask for
your assistance. Thank you very much.

 

[Tom Ogilvy]

VBA execution is not multithreaded. You can not start a proceedure to
manage your userform while at the same time the main procedure is opening
files. The best you can do is open the files in a loop and add a line
within the loop to update the progress bar. You might explore
application.ontime, but since it is not preemptive, I am not sure it would
work here since excel would be busy opening the files.

 

[mcdowell]

Tom.... My throught was that I could "Load" the form as Modeless and
periodically throughout the processing have it updated via a sub...
i.e. "ProcessUpdate". The UserForm "fmProcress" would load early in
the processing via an entry..... fmProgress.Show vbModeless and at
strategic intervals (time appx 15 to 30 seconds apart) it would be
updated with a sub "ProcessUpdate" either by a Call or via....
fmProgress.ProcessUpdate.

I believe that if all the code for the form is in the module behind the
form and the update sub is a Public sub that this approach will work.
If the code is clock based then the change in the width of the top
label should reflect an accurate picture of the progress of the
processing. Any suggestions for code?

John

 

[mcdowell]

The below Code comes from an Excel pro. It seems to run OK, however the
Userform "fmProgress" is a blank white form instead of the red label
over the blue label as called for in the form design. All the code is
in the Form module. I have the Public Sub UpdateProgress set to Public
in order to call in in the processing macro using
fmProgress.UpdateProgress. Anyone have any suggestions as to what the
problem is??

Option Explicit
Private iNumberOfFiles As Single
Private dStartTime As Date
Private lngTwipPerSec As Long

Public Sub UpdateProgress()
On Error Resume Next ' Error would be in size of label

Dim lng As Long

lng = lngTwipPerSec * DateDiff("s", dStartTime, Time)
' Move the lables
lblTimeRemaining.Move lng, 0, Me.Width - lng ' - 150
lblTimeElapsed.Move 0, 0, lng
End Sub

Private Sub InitSetup()
Dim i As Integer
Dim i2 As Integer

' Set the number of files
iNumberOfFiles = Worksheets("Sort").Range("A17").Value

' Set the sizes & Move the lables
lblTimeElapsed.Move Me.Left, 0, 0, Me.Height
lblTimeRemaining.Move Me.Left, 0, Me.Width, Me.Height

' Set the Start Time
dStartTime = Time

' Set the twip per second
lngTwipPerSec = Me.Width / (iNumberOfFiles * 5 * 60)

End Sub

 

[Tom Ogilvy]

Best to ask the "Excel pro".

You designed the form, so there is no way for anyone to know what is red and
what is blue, what you are trying to do or why it doesn't appear as you
wish. Try throwing in a Doevents after the call to the initialize and
update code.

whenever you call the update routine, it should adjust the progress in
proportion to the elapsed time relative some guessed at total time
This certainly is not time based - it isn't periodic with respect to time -
it is event based - called after the opening of each file. So you are
making an assumption on how long it will take to open all the files.

 

[Cesar Zapata]

Did you try the progress indicator example from JWalk?

http://j-walk.com/ss/excel/tips/tip34.htm


it uses a form and labels. change the code to suit your needs.



hope it helps