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 CodeName to Reference Sheets.) 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
False stops screen flicker and speeds up macros. At the very end of the slow code, simply place
"DoIt". Then run your macro as usual.