Status Progress Bar

[JT]

I found the code below in this forum and would like to try it but I'm having
some difficulty getting it to work.

Suppose I have a spreadsheet with a 100 lines of data and Im doing 'stuff'
to each line. I'd like to have the StatusProgressBar increase as each line
is processed.

I'm guessing:
lCounter = the row being processed
lMax = 100 (for 100 lines)
lInterval = 1 (since I want to change it each time a new row is processed)

I'm not sure of:
(1) what "strText" should be or how it should appear in the code
(2) how to call this code in the code that is processing the 100 lines

Any examples or suggestions on how to make this work would be greatly
appreciated. Thanks for the help........

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub StatusProgressBar(lCounter As Long, lMax As Long, lInterval As Long,
Optional strText As String)

Dim lStripes As Long

If lCounter Mod lInterval = 0 Or lCounter = lMax Then

lStripes = Round((lCounter / lMax) * 100, 0)
Application.StatusBar = strText & String(lStripes, "|") & String(100 -
lStripes, ".") & "|"

End If

End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[FSt1]

hi
as is, i don't think that code will work. all the declarations are null and
there is no loop.
see my post to premanand, 12 posts down at this group "Programming" entitled
"showing progress bar while running macros"

there are 3 links to sites that have much better progress meters

regards
FSt1

 

[RB Smissaert]

as is, i don't think that code will work.

Hey, not so quick, of course it works, it is used inside your loop!

Option Explicit

Sub StatusProgressBar(lCounter As Long, _
lMax As Long, _
lInterval As Long, _
Optional strText As String)

Dim lStripes As Long

If lCounter Mod lInterval = 0 Or lCounter = lMax Then
lStripes = Round((lCounter / lMax) * 100, 0)
Application.StatusBar = strText & String(lStripes, "|") & _
String(100 - lStripes, ".") & "|"
End If

End Sub


Sub test()

Dim i As Long

For i = 1 To 60000
Cells(i, 1) = "just testing"
StatusProgressBar i, 60000, 10, " Looping through the range - "
Next i

End Sub


RBS

 

[RB Smissaert]

Here an updated version.
A bit more flexibility as you can set the length of the progressbar and it
will also
take up a lot less time, so it will slow down the loop less.

Sub StatusProgressBar(lCounter As Long, _
lMax As Long, _
bReset As Boolean, _
Optional lInterval As Long = 1, _
Optional strText As String, _
Optional lLength As Long = 100)

'lCounter the loop counter passed from the procedure
'lMax the maximum of the loop counter
'bReset do this at the very first iteration, eg i = 0
'lInterval the update interval of the statusbar
'strText any text preceding the progressbar
'lLength lenght in characters of the progressbar
'---------------------------------------------------------
Dim lStripes As Long
Static lLenText As Long
Static strBuffer As String
Static lOldStripes As Long

If bReset Then
lLenText = Len(strText)
strBuffer = strText
strBuffer = strBuffer & String(lLength, ".")
strBuffer = strBuffer & "|"
lOldStripes = 0
End If

If lCounter Mod lInterval = 0 Or lCounter = lMax Then
lStripes = Round((lCounter / lMax) * lLength, 0)
If lStripes - lOldStripes > 0 Then
Mid$(strBuffer, lLenText + 1 + lOldStripes) = "|"
lOldStripes = lStripes
Application.StatusBar = strBuffer
End If
End If

End Sub


Sub test()

Dim i As Long
Dim strText As String

strText = " Looping through the range - "

For i = 1 To 6000000
StatusProgressBar i, 6000000, i = 1, 6000000 \ 200, strText
Next i

End Sub


RBS

 

[JT]

Thanks for the help. I'm going to try it right now. Thanks again for the
help!

 

[RB Smissaert]

This line:
Mid$(strBuffer, lLenText + 1 + lOldStripes) = "|"

May need to be:
Mid$(strBuffer, lLenText + 1 + lOldStripes) = String(lStripes - lOldStripes,
"|")


RBS