Creating a standalone progress indicator

This section describes how to set up a standalone progress indicator - that is, one that is not initiated by displaying a UserForm - to display the progress of a macro. The macro simply clears the worksheet and writes 20,000 random numbers to a range of cells:

Sub GenerateRandomNumbers()

' Inserts random numbers on the active worksheet Const RowMax As Integer = 500 Const ColMax As Integer = 40 Dim r As Integer, c As Integer

If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub

Cells.Clear

Cells(r, c) = Int(Rnd * 1000) Next c Next r End Sub

After you make a few modifications to this macro (described below), the UserForm, shown in Figure 15-3, displays the progress.

1

_ si

JiJ

Hl

«J

m

«1

m

m

J

ilj

m

111 'LI

14)

***

99*

17

J.

m

tn

TO

tu

Eli

M*

JU

s?

r>r

m

J

m

DdJ

H

llT

iii

ta;

7X

4JU

mj

a

îlî

Mi

fciJ

MI

t

♦A

)*■

Scî

*

u

1*

«1

T

M

1*1 1HK

^w» --

w

ft r u

>

il*

Hi

m

1U

w.

K

itl

M)

m

Pi

m

13

►77

4 ki

U [

m

rip

Ul

1U

m

u;

hi

1J

un

hji

7J h

Al

11

4A

ft*

:u

11

LU

Vu

tu

tu

iU

un

«T

«

lut

M

«j

ÎJt

)T

«n

in

»f

Hi

■1«

l!

in

ÎX

W

tn

ut

SA]

14

m

m «1

m

in

ta

ni

Ml

n

U!

? ■m

Lit

Bl

by

tji

lii

u

W4

a?

Hl

lit

ru

UT

Ht

VM

■ H

171

«

¿i

171

Vfi

U

m

»1

[U

m;

eu

1M

'ti

Mi

■ H ^-.-vri

■ '

Figure 15-3: A UserForm displays the progress of a macro.

Figure 15-3: A UserForm displays the progress of a macro.

CD- This example, named ® progress indicatorl.xlsm, is available on the companion ROM CD-ROM.

BUILDING THE STANDALONE PROGRESS INDICATOR USERFORM

Follow these steps to create the UserForm that will be used to display the progress of your task:

1. Insert a new UserForm and change its Caption property setting to Progress.

2. Add a Frame control and name it FrameProgress.

3. Add a Label control inside the Frame and name it LabelProgress. Remove the label's caption and make its background color (BackColor property) something that will stand out. The label's size and placement don't matter for now.

4. Add another label above the frame to describe what's going on (optional). In this example, the label reads, Entering random numbers...

5. Adjust the UserForm and controls so that they look something like Figure 15-4.

pTogress indicatorl.xtsm - UserForml jU-ierForm} - JTI £ i ; Progress

I I interfigranclenirwnbefs.

pTogress indicatorl.xtsm - UserForml jU-ierForm} - JTI £ i ; Progress

I I interfigranclenirwnbefs.

Figure 15-4: This UserForm will serve as a progress indicator.

You can, of course, apply any other type of formatting to the controls. For example, I changed the SpecialEffect property for the Frame control to make it "sunken."

CREATING THE EVENT HANDLER PROCEDURES FOR THE STANDALONE PROGRESS INDICATOR

The trick here involves running a procedure automatically when the UserForm is displayed. One option is to use the Initialize event. However, this event occurs before the UserForm is actually displayed, so it's not appropriate. The Activate event, on the other hand, is triggered when the UserForm is displayed, so it's perfect for this application.

Insert the following procedure in the code window for the UserForm. This procedure simply calls a procedure named GenerateRandomNumbers when the UserForm is displayed. This procedure, which is stored in a VBA module, is the actual macro that runs while the progress indicator is displayed.

Private Sub UserForm_Activate() Call GenerateRandomNumbers End Sub

The modified version of the GenerateRandomNumber procedure (which was presented earlier) follows. Notice that additional code keeps track of the progress and stores it in a variable named PctDone.

Sub GenerateRandomNumbers()

' Inserts random numbers on the active worksheet Dim Counter As Integer Const RowMax As Integer = 500 Const ColMax As Integer = 40 Dim r As Integer, c As Integer Dim PctDone As Single

If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub

Cells.Clear

Counter = 1

Cells(r, c) = Int(Rnd * 1000) Counter = Counter + 1 Next c

PctDone = Counter / (RowMax * ColMax)

Call UpdateProgress(PctDone) Next r

Unload UserForm1 End Sub

The GenerateRandomNumbers procedure contains two loops. Within the inner loop is a call to the UpdateProgress procedure, which takes one argument (the PctDone variable, which represents the progress of the macro). PctDone will contain a value between 0 and 100.

Sub UpdateProgress(Pct) With UserForm1

.FrameProgress.Caption = Format(Pct, "0%") .LabelProgress.Width = Pct * (.FrameProgress.Width - 10) .Repaint End With End Sub

CREATING THE START-UP PROCEDURE FOR A STANDALONE PROGRESS INDICATOR

All that's missing is a procedure to display the UserForm. Enter the following procedure in a VBA module:

Sub ShowUserForm() With UserForm1

.LabelProgress.Width = 0 .Show End With End Sub

Tip An additional accoutrement is to make the progress bar color match the workbook's current theme. To do so, just add this statement to the ShowUserForm procedure:

.LabelProgress.BackColor = ActiveWorkbook.Theme. _ ThemeColorScheme.Colors(msoThemeAccenti)

HOW THE STANDALONE PROGRESS INDICATOR WORKS

When you execute the ShowUserForm procedure, the Label object's width is set to 0. Then the Show method of the UserForm1 object displays the UserForm (which is the progress indicator). When the UserForm is displayed, its Activate event is triggered, which executes the GenerateRandomNumbers procedure. The GenerateRandomNumbers procedure contains code that calls the UpdateProgress procedure every time the r loop counter variable changes. Notice that the UpdateProgress procedure uses the Repaint method of the UserForm object. Without this statement, the changes to the label would not be updated. Before the GenerateRandomNumbers procedure ends, the last statement unloads the UserForm.

To customize this technique, you need to figure out how to determine the percentage completed and assign it to the PctDone variable. This will vary, depending on your application. If your code runs in a loop (as in this example), determining the percentage completed is easy. If your code is not in a loop, you might need to estimate the progress completed at various points in your code.

0 0

Responses

  • Raimo
    How to make a stand alone vba userform?
    8 years ago

Post a comment