CAD PANACEA HAS MOVED TO http://cadpanacea.com
30 May 2006
VBA Progress Bar          
Here's a quick tip for making a VBA progress bar without adding a reference to the MSComCtl.ocx control, or any other custom control.

Add a Textbox to your form
Position and size it to it's maximum size.
Make note of the width of the textbox when you are done.

Add the following code to your form Initialize event.


TextBox1.Width = 0
TextBox1.BackColor = vbBlue '(or the color of your choice)


Now in the procedure for which you want to show progress, follow this:

Divide the width of the textbox by the number of iterations, this is the amount by which the progress bar will grow for each iteration.

Then in your iteration loop, increment the width of the textbox by the above result.

Here is an example.


Private Sub CommandButton1_Click()
Dim i As Integer, x As Double
Dim num As Integer, start As Double
num = 150 '(this will typically be a variable in your procedure)
x = 206 / num
For i = 1 To num
'-------------------------------
' this dummy code is just to give
' the progress bar time to work
' in this example
start = Timer
Do While Timer < start + 0.02
DoEvents
Loop
' end dummy code
'-------------------------------
' This changes the progress bar
TextBox1.Width = i * x
Next i
End Sub


num will typically already be in your procedure as the number of items in a collection or array that you are processing.

x is the width of the textbox divided by num

For i = 1 to num is just a sample loop. You will have your own.

Setting the textbox width to i * x at each iteration is what makes the "Progress Bar" actually work as shown below.




I received a very good comment from Nick Zeeben. "...the DoEvents inside the loop is rather cruical to allowing windows to break in and repaint the dialog otherwise the progress bar doesnt have a chance to redraw."

Thanks Nick.

Labels:


PermaLink       Posted 5/30/2006 08:12:00 PM     
3 COMMENTS!


Comment from: Anonymous Nick Zeeben
Date: June 13, 2006 at 8:09:00 PM CDT  

You might want to point out the fact that the DoEvents inside the loop is rather cruical to allowing windows to break in and repaint the dialog otherwise the progress bar doesnt have a chance to redraw.


Comment from: Blogger The Alsir
Date: April 13, 2007 at 4:51:00 PM CDT  

"It is much better to use Me.Repaint rather than the alternative of DoEvents, because the latter is very resource-hungry."


http://word.mvps.org/FAQs/Userforms/CreateAProgressBar.htm


Comment from: Anonymous Anonymous
Date: November 25, 2008 at 2:46:00 PM CST  

I was curious...i had several functions to run and i am not sure where exactly to put them inside this code example

Post a comment