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.

Be sure to include a DoEvents or Me.Repaint inside the loop.