Excel VBA Macro Programming

A further helping hand that Microsoft built into Excel is default dialogs. Each time you use an Excel spreadsheet, you are calling up dialogs to enter parameters; this might be to format a cell or to do an advanced filter. When you take one of these actions, a dialog appears to allow you to make choices. If you take the action Format Cells from the Excel menu, a dialog will appear. This is one of many built-in dialogs within Excel that your code can access.

Within Excel VBA, there is a Dialogs collection you can use to display any one of the Excel dialogs from within your code. The downside to this is that you cannot manipulate the appearance of the dialog in the same way you can with the Common Dialog control, and you cannot access the return parameters directly.

However, these dialogs can still play an important part in your code where it is not necessary to interpret the user's action. All the code in these default dialogs is already written to take care of the user's actions, and all you need to do is to show the dialog. It then runs as if the user had selected it from the menu themselves .

In the previous section, we discussed using the Common Dialog control to show a printer dialog, but you still have to write your own code to do the printing. A shortcut is to use the Excel dialog to do your printing using the Application object and Show methods :

Sub Printer_Dialog() Application.Dialogs(xlDialogPrint).Show End Sub

When you run this macro, your screen will look like Figure 10-6.

Figure 10-6: Using the Excel default Print dialog

This is far more suited to Excel because Excel does all the work whatever selection the user makes ‚ it even offers a Print Preview button! As previously mentioned, you cannot capture the selection the user is making, but in the case of printing, this is not absolutely necessary.

To get an extensive list of available dialogs you can type Application.Dialogs in the code windows . Almost any dialog you can get in Excel from the normal Excel menu is available to use in your program in this way. Experiment around with the constants to see all the dialogs that can be displayed.