Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 86 Display a "Please Wait" Message

Have you ever had one of those macros that seem
to take forever to complete? If this is a problem with your macro,
you can have Excel display a "Please
Wait" message to the user.

Most users expect code to run and
complete almost instantaneously. Sadly, this doesn't
always happen. Recorded macros in particular generally take much
longer to complete than well-written VBA code. To add to the problem,
Excel VBA code is generally slower than a lot of other types of code.

Fortunately,
you can use a bit of extra code to create a "Please
Wait" message so that users know the code is running
and Excel has not locked up on them! Unfortunately, one of the more
popular ways to let users know code is running is via the use of an
Excel progress meter.

There are two problems with this method.
First, the progress meter can slow down your code even more,
compounding the issue. Second, your slow code must be caused by a
loop, and you cannot use the macro recorder to create a loop.

We prefer using VBA code, such as the
following DoIt macro, which uses a rectangle from
the Drawing toolbar.

Sub DoIt( )
Application.ScreenUpdating = True
With Sheet1.Shapes("Rectangle1")
.Visible = msoTrue = (Not Sheet1.Shapes("Rectangle1").Visible)
End With
'Toggling sheets Forces Rectangle1 to show while code is running
Sheet2.Select
Sheet1.Select
End Sub

To use this
code, add a rectangle from the Drawing toolbar to any sheet in the
appropriate workbook. While the rectangle is selected, click in the
Name box and name the rectangle Rectangle1 (if
it's not already called that).

Enter the text you want displayed while
your code is running, and format, position, and size the rectangle as
desired. Enter the preceding DoIt macro into a
standard module of your workbook. If necessary, change
Sheet1 in the code to the CodeName of the sheet on
which you placed Rectangle1. (For more information on CodeNames, see
[Hack #83].) Then select Tools
Macro Macros and run DoIt
from within Excel. This will hide Rectangle1 completely.

At the very start of the slow code, place the following:

Run "DoIt"
Application.ScreenUpdating = False

The use of
Application.ScreenUpdating =
False stops screen flicker and speeds up macros.
At the very end of the slow code, simply place
Run "DoIt". Then run
your macro as usual.