macro status indicator

A

Annika

I have a macro used in Excel that takes about 5 minutes to
execute. I am worried that users will become impatient, or
think that their computer has frozen in the mean time, and
will shut the program down before the code has finished
executing.
Now, when you download files from the internet, they
always have little indicator boxes, showing you how much
of the file has been downloaded.

Does anyone know a VBA code for displaying such an
indicator?

I have a message box to let users know how long it will
roughly take, but an indicator box of some sort would be
great.
 
D

Dave Peterson

I like to use the statusbar:

application.statusbar = "Now processing the input"
'your input code
application.statusbar = "now formatting the pivottable"
'your formatting code
application.statusbar = false
'reset it back to normal (at the end)

You may want to ensure that the statusbar is visible and tell the users to look
at the lower left corner for updates.

(not as pretty as the indicator boxes, but a lot simpler.)
 
J

Jim Cone

I like to use the StatusBar also. This version even looks like a progress
bar...

'The progress bar is 60 characters wide.
'If you change the width of the bar then you must change all references to
"60".
'Assumes, that if count of data is less than 100, that a progress bar is not
necessary.

'---------------------------------------------------
Sub ImitationProgressBar
Dim RowMsg as String
Dim TempPlace as Long
Dim RngCount as Long
Dim Factor As Long
Dim R as Long

'Set it up
TempPlace = 2
RngCount = 1000 'You determine the number of iterations to be used in the
loop below.

If RngCount > 99 Then
RowMsg = "[" & String$(60, 46) & " ] " 'Fill with "."
Factor = WorksheetFunction.RoundUp(RngCount / 60, 0)
Else
'Shows a filled (completed progress bar).
Application.StatusBar = "[ " & String$(60, 73) & " ]" 'Fill with "I"
'Set Factor large enough so StatusBar display is ignored.
Factor = 999
End If


For R = 1 to RngCount

'Additional code goes here.

If R Mod Factor = 0 Then
Mid$(RowMsg, TempPlace, 1) = Chr$(73)
Application.StatusBar = RowMsg & Format$(R / RngCount, "#00%")
TempPlace = TempPlace + 1
End If
Next 'R
End Sub
'--------------------------------------------------

Regards,

Jim Cone
San Francisco, CA
**************************************
 
M

Mike

My favorite way to use the status bar is to simply show
the percentage complete.

For a = 1 to MaxValue
Application.StatusBar = 100 * (a / MaxValue) & " %
complete"
Do Stuff Here
Next


-----Original Message-----
I like to use the StatusBar also. This version even looks like a progress
bar...

'The progress bar is 60 characters wide.
'If you change the width of the bar then you must change all references to
"60".
'Assumes, that if count of data is less than 100, that a progress bar is not
necessary.

'---------------------------------------------------
Sub ImitationProgressBar
Dim RowMsg as String
Dim TempPlace as Long
Dim RngCount as Long
Dim Factor As Long
Dim R as Long

'Set it up
TempPlace = 2
RngCount = 1000 'You determine the number of iterations to be used in the
loop below.

If RngCount > 99 Then
RowMsg = "[" & String$(60, 46) & " ] " 'Fill with "."
Factor = WorksheetFunction.RoundUp(RngCount / 60, 0)
Else
'Shows a filled (completed progress bar).
Application.StatusBar = "[ " & String$(60, 73) & " ]" 'Fill with "I"
'Set Factor large enough so StatusBar display is ignored.
Factor = 999
End If


For R = 1 to RngCount

'Additional code goes here.

If R Mod Factor = 0 Then
Mid$(RowMsg, TempPlace, 1) = Chr$(73)
Application.StatusBar = RowMsg & Format$(R / RngCount, "#00%")
TempPlace = TempPlace + 1
End If
Next 'R
End Sub
'--------------------------------------------------

Regards,

Jim Cone
San Francisco, CA
**************************************
Annika said:
I have a macro used in Excel that takes about 5 minutes to
execute. I am worried that users will become impatient, or
think that their computer has frozen in the mean time, and
will shut the program down before the code has finished
executing.
Now, when you download files from the internet, they
always have little indicator boxes, showing you how much
of the file has been downloaded.

Does anyone know a VBA code for displaying such an
indicator?

I have a message box to let users know how long it will
roughly take, but an indicator box of some sort would be
great.
Click to expand...


.
Click to expand...
 
J

John Tjia

Also add the notation at each step perhaps: "Step 1 of 7:" (or however
many milestones you have), "Step 2 of 7:" etc.
 
A

andrestrategy

Hello,

I have a macro thar tun other macro and would like to display th
progress bar. I do not understand how to do with the example. How can
do it.

Thank you for your help

Andr
 
N

Norman Jones

Hi Andre,

One way:

Application.StatusBar = "Data being processed, please be patient..."
' Your code
Application.Statusbar = False
 
G

Guest

andrestrategy said:
Hello,

I have a macro thar tun other macro and would like to display the
progress bar. I do not understand how to do with the example. How can I
do it.

Thank you for your help

André
Click to expand...

I typically just leave one cell left open for use as a status message. Then as
steps get completed I write text to those cells. It can be "Stage 2 complete"
or "54%" or whatever you want to put there.

Bill
 
Top