Wait Message While Macro Is Working

[Sisyphus]

XlWait is a little UserForm which you can show to the user of your macro asking him to wait while your macro is doing its job. The attached workbook is entirely made for the purpose of demonstration. It contains an event procedure in Sheet1's code sheet that responds to the button on the worksheet and calls the procedure WaitMan.Test_Wait. This procedure in turn calls the procedures

1. WipeSheet
2. WriteNumbers
3. WriteResult and, finally,
4. XlWait.EndXlWait

XlWait is the UserForm already above mention. Essentially it contains these two procedures:

1. ShowXlWait and
2. EndXlWait (already mentioned above)

Each of the procedures WriteNumbers and WriteResults has a call of the sub CallXlWait which in turn is calling XlWait.ShowXlWait. Inserting CallXlWait as a buffer makes for simpler syntax. XlWait.ShowXlWait needs 3 parameters.

1. A message
   for example, "Calculating results"
2. A definition of the entire task
   for example, 64000 rows
3. And a marker of how far the program has progressed to-date
   for example, row No. 32000

From this information XlWait.ShowXlWait will produce a message like "Calculating results ... 50% done" and show a bar of a length equal to the percentage. In the attached workbook XlWait is being called after each row. In order to save time it executes only each 8th time. This is a parameter that can be set in CallXlWait. When the code ends XlWait.EndXlWait removes the UserForm.

The good news is that you can drag the form XlWait, along with all its code, into your own project. It needs no modification. You can use the sub CallXlWait or its code to call XlWait.ShowXlWait. However, you will have to determine the criteria of progress depending upon the realities in your own project and feed numbers to the form that its code can convert into a report.