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 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.