Progress Bar

[Jeff]

Hi -
I have a macro that takes quite some time. There are several loops that run
inside of it to delete or consolidate rows (which can be up to 40,000 rows).
The macro can take about 20 seconds to 1.5 minutes. I would like to create a
Progress Bar to let the user know that how long to expect (and that the macro
is actually running...). I have viewed the website
http://www.enhanceddatasystems.com/ED/Pages/ExcelProgressBar.htm , but I do
quite understand how to incorporate this into my macro.

If anyone has any suggestions, I would really appreciate it.

Thank you in advance.

 

[Ivyleaf]

Hi -
I have a macro that takes quite some time.  There are several loops thatrun
inside of it to delete or consolidate rows (which can be up to 40,000 rows).  
The macro can take about 20 seconds to 1.5 minutes.  I would like to create a
Progress Bar to let the user know that how long to expect (and that the macro
is actually running...).  I have viewed the websitehttp://www.enhanceddatasystems.com/ED/Pages/ExcelProgressBar.htm, but I do
quite understand how to incorporate this into my macro.

If anyone has any suggestions, I would really appreciate it.  

Thank you in advance.

Hi Jeff,

If you can place your loop code, or at least an abbreviated version it
would probably make it easier to suggest the best method. Also, have
you considered just using a statusbar notification? Even 1.5min isn't
rediculously long, and as a user as long as I had some sort of
indication that things were still ticknig along I would be happy. I'd
be reluctant to slow things down too much more by adding even more
code such as a form with a progressbar etc. Different if it was a 2
hour peocess... then the overhead of the progressbar would be
negligible in effect compared to the overall run if you know what I
mean.

Here is some code for a StatusBar progress message:

Sub StatusText()
Dim i As Long, Target As Long

Target = 1000000
For i = 1 To Target
If i * 100 Mod Target = 0 Then
Application.StatusBar = i * 100 _
/ Target & "% Complete"
End If
DoEvents
Next
Beep
Application.StatusBar = False

End Sub

 

[Jeff]

Hi -
the status bar would work fine too. Where would I put this in my code so
that it is some-what accurate?

Thanks!

 

[Ivyleaf]

Hi -
the status bar would work fine too.  Where would I put this in my code so
that it is some-what accurate?

Thanks!
--
Jeff











- Show quoted text -

Hi Jeff,

If you only had one loop, I could answer that easily. Since you say
you have several though, it is a bit tricky to say without seeing the
structure.

The idea behind the code that I posted is that 'Target' is the total
number of iterations of the loop. 'i' (the counter) is the current
loop number and therefore i * 100 Mod Target will only equal 0 every
1% (if that makes sense). If you changed that line to i * 10 Mod
Target, the statusbar would update in 10% intervals.

If you have multiple loops, it gets harder since you have to try and
work out what portion of the whole process each loop is and break it
down that way. This could be easy or impossible.

The other option that could be more feasible would be to have multiple
status segments. For example, in the first loop you could have the
status bar read: "Loading data - 10%" through to 100%, then in your
next loop start from zero again but have it read "Analysing Stuff -
10%" through to 100% again and so on. At least this way the count will
be accurate and the user will still know that at least something is
happening. If your loops are complex and you have tried to segment the
time you run a good risk of reverting to the old Windows 95 progress
bar syndrome where it used to fly up to 99% in chunks, then seemingly
sit there forever while you held your breath and hoped it was still
going .

If you're still doubtful, post your code if you can and we'll try to
suss it out.

Cheers,
Ivan.