Excel 2019 Power Programming with VBA. Michael Alexander
Читать онлайн книгу.
play a major role in the user interfaces that you design for your applications. Figure 1.2 shows an example of a custom dialog box.
FIGURE 1.2 A dialog box created with Excel's UserForm feature
A custom dialog box is known as a UserForm. A UserForm can solicit user input, get a user's options or preferences, and direct the flow of your entire application. The elements that make up a UserForm (buttons, drop-down lists, check boxes, and so on) are called controls—more specifically, ActiveX controls. Excel provides a standard assortment of ActiveX controls, and you can also incorporate third-party controls.
After adding a control to a dialog box, you can link it to a worksheet cell so that it doesn't require any macros (except a simple macro to display the dialog box). Linking a control to a cell is easy, but it's not always the best way to get user input from a dialog box. Most of the time, you want to develop VBA macros that work with your custom dialog boxes.
Using ActiveX controls on a worksheet
Excel also lets you add UserForm ActiveX controls to a worksheet's drawing layer (an invisible layer on top of a sheet that holds pictures, charts, and other objects). Figure 1.3 shows a simple worksheet model with several UserForm controls inserted directly in the worksheet. This sheet contains the following ActiveX controls: a CheckBox, a ScrollBar, and two sets of OptionButtons. This workbook uses no macros. Rather, the controls are linked to worksheet cells.
FIGURE 1.3 You can add UserForm controls to worksheets and link them to cells.
ON THE WEB
This workbook is available on this book's website. The file is named worksheet controls.xlsx.
Perhaps the most common control is a CommandButton. By itself, a CommandButton doesn't do anything, so you need to attach a macro to each CommandButton.
Using dialog box controls directly in a worksheet often eliminates the need for custom dialog boxes. You can often greatly simplify the operation of a spreadsheet by adding a few ActiveX controls (or form controls) to a worksheet. These ActiveX controls let the user make choices by operating familiar controls rather than making entries in cells.
Access these controls by using the Developer ➪ Controls ➪ Insert command (see Figure 1.4). If the Developer tab isn't on the Ribbon, add it by using the Customize Ribbon tab of the Excel Options dialog box.
FIGURE 1.4 Using the Ribbon to add controls to a worksheet
The controls come in two types: form controls and ActiveX controls. Both sets of controls have their advantages and disadvantages. Generally, form controls are easier to use, but ActiveX controls are a bit more flexible. Table 1.1 summarizes these two classes of controls.
TABLE 1.1 ActiveX Controls versus Form Controls
| ActiveX Controls | Form Controls | |
| Excel versions | 97, 2000, 2002, 2003, 2007, 2010, 2013, 2016, 2019 | 5, 95, 97, 2000, 2002, 2003, 2007, 2010, 2013, 2016, 2019 |
| Controls available |
CheckBox, TextBox, CommandButton, OptionButton, ListBox, ComboBox, ToggleButton, Spin Button, ScrollBar, Label, Image (and others can be added)
|
GroupBox, Button, CheckBox, OptionButton, ListBox, DropDown (ComboBox), ScrollBar, Spinner
|
| Macro code storage | In the code module for the sheet | In any standard VBA module |
| Macro name |
Corresponds to the control name (for example, CommandButton1_Click)
|
Any name you specify |
| Correspond to | UserForm controls | Pre–Excel 97 dialog sheet controls |
| Customization | Extensive, using the Properties box | Minimal |
| Respond to events | Yes | Click or Change events only |
Executing the development effort
After you identify user needs, determine the approach you'll take to meet those needs, and decide on the components that you'll use for the user interface. Next, it's time to get down to the nitty-gritty and start creating the application. This step, of course, constitutes a great deal of the total time you spend on a particular project.
How you go about developing the application depends on your personal style and the nature of the application. Except for simple fill-in-the-blanks template workbooks, your application will probably use macros. Creating macros in Excel is easy, but creating good macros is difficult.
Concerning Yourself with the End User
In this section, we discuss the important development issues that surface as your application becomes more and more workable and as the time to package and distribute your work grows nearer.
Testing the application
How many times have you used a commercial software application, only to have it bomb out on you at a crucial moment? Most likely, the problem was caused by insufficient testing that didn't catch all of the bugs. All nontrivial software has bugs, but in the best software, the bugs are simply more obscure. As you'll see, you sometimes must work around the bugs in Excel to get your application to perform properly.