Why develop a graphical application?Created: March 20, 1992
ABSTRACT
This tutorial describes several techniques you can use to develop custom graphical applications using the MicrosoftÒ Excel spreadsheet with business graphics and database. Sample files are included with the article.
Some important questions to answer when you evaluate the MicrosoftÒ Excel spreadsheet with business graphics and database as a development environment are:
Why develop a graphical application?
Why use a spreadsheet for application development?
Why use Microsoft Excel?
A custom graphical application makes users more productive. Users who understand the basic skills for using graphical applications are well on their way to using a custom application that follows the same interface principles.
A graphical interface is open and intuitive. Menus and dialog boxes are easy to browse. Clicking a button requires only mouse skills. Character-based custom applications are usually more rigid, with menu choices organized hierarchically, and sometimes require memorization of keystrokes.
When you commit to application development in a graphical environment such as Microsoft WindowsÔ or the AppleÒ MacintoshÒ, you can take advantage of the broad hardware and software support it offers. You can integrate multiple applications in a single customized solution or share information between applications.
Spreadsheets are widely used end-user productivity tools, and a custom spreadsheet application takes advantage of the spreadsheet installed base. Jobs that users are typically trying to accomplish (such as data retrieval, analysis, and reporting) are suitable for spreadsheets. By applying the strengths of a spreadsheet to such a task, you can save development time and costs.
Microsoft Excel is the most widely used graphical spreadsheet, and it allows full graphical customization with a structured command language.
Microsoft Excel applications are extensible through the macro language.
You can run an external program as a dynamic link library (DLL) or an external code resource.
You can control other applications or share information via dynamic data exchange (DDE).
Because Microsoft Excel versions are available for Microsoft Windows and the Apple Macintosh, you can write one custom application that runs in both environments.
You create custom Microsoft Excel applications to make your work faster and easier. Custom applications can be designed “up front” and coded to meet a specification, or they can arise gradually as the work you’re doing with Microsoft Excel evolves to the point at which it can be generalized and automated. In either case, custom applications arise from the need to make a specific job more efficient.
In this discussion, we focus on the demands of a particular job and gradually introduce ways that Microsoft Excel can enhance job performance. In the end, we’ll have a custom Microsoft Excel application dedicated to that job. When we look at the macro code, you’ll understand what it does because you’ll be familiar with the job.
A Microsoft Excel job typically starts with a worksheet. Let’s look at a worksheet for tracking plant equipment repairs.
In this section, you’ll learn about:
Workspace files
Custom number formats
Custom styles
Names
Worksheet window panes
Custom print settings
Creating a workspace file makes it easy to open documents you want to use together and easy to set Microsoft Excel settings for a particular job. The REPAIR.XLW workspace file opens the REPAIR.XLS worksheet and ensures that certain Microsoft Excel settings, such as display of the Toolbar and status bar, are configured properly.
Try It
1.Copy the sample files from the disk into a directory named C:\EXCEL\EXCELAPP.
2.Start Microsoft Excel.
3.Close Sheet1.
4.Choose Open on the File menu.
5.Select [excelapp] in the Directories box, and then choose OK.
6.Select [demo1] in the Directories box, and then choose OK.
7.Select REPAIR.XLW in the Files box, and then choose OK.
The REPAIR.XLS worksheet is designed to track repairs done by a maintenance staff on plant equipment. Certain custom features are already stored in this worksheet.
In addition to using Microsoft Excel built-in number formats, you can create custom number formats to properly display numbers that relate to your job. In the repair worksheet, a custom number format was created to display the serial number of the repair form with eight digits and leading zeros.
The active cell (to the right of No.) is where the serial number identifying this repair order is stored. First, you’ll choose the Number command on the Format menu to show the custom number format that’s been defined and assigned to this cell. Then you’ll type a number to show how it’s formatted.
Try It
1.Choose Number on the Format menu.
The Format Number dialog box appears with the custom format 00000000 selected.
2.Choose Cancel.
3.Type 123, and then press ENTER.
The number is formatted as 00000123.
You can define any set of formatting options as a custom style. This makes the right formatting choices much easier in a predefined worksheet so that users can create presentable worksheets in a short time. Certain styles are already defined in the repair worksheet to help with formatting.
Notice that when the cell containing the form serial number is selected, the custom style FormNumber is displayed in the style box in the Toolbar. That way, you don’t need to understand custom number formats in order to use them.
Next, you’ll change the color of the cell containing the form serial number by applying a style. Then you’ll apply a style to change the color back.
Try It
1.Click the down arrow in the style box at the left end of the Toolbar, and then click YellowCell.
The cell pattern is changed to yellow. No other attributes are changed.
2.Click the down arrow in the style box, and then click WhiteCell.
The cell pattern is changed back to white.
Names are another way to customize a worksheet. By using names that refer to cells, users can build descriptive formulas that use words instead of cell references.
The repair worksheet uses many names. One name identifies all the cells in which data should be entered. By choosing the name from a list, the user can quickly select a range of cells.
The name wr03.DataCells refers to all cells in which data should be entered. The wr03 name prefix isn’t necessary. It’s just a naming convention that identifies this name as worksheet range number 3. You can go to the name and then scroll within a selected cell range.
Try It
1.Press F5.
The Formula Goto dialog box appears.
2.Select wr03.DataCells, and then click OK.
All the data entry cells are selected.
3.Press ENTER or the TAB key several times.
The active cell scrolls within the selection.
You can split a worksheet window to view different parts of a worksheet together and freeze window panes to lock certain regions of a worksheet on the screen.
In the repair worksheet, a frozen pane locks the entire displayed window vertically. That way the user can’t scroll down to see the hidden section of the worksheet, a range used for consolidation purposes. That hidden range can be used to roll up information from individual repair worksheets into a summary report.
Try to scroll the worksheet vertically. Even though the scroll bar moves, the data entry region of the worksheet is still displayed.
Try It
Click the down arrow on the vertical scroll bar.
The worksheet appears locked vertically.
You can set up a worksheet the way you want it printed, and all that information is stored with the document. Anyone who opens the worksheet can take advantage of the print settings.
In the repair worksheet, the print area is defined to print only the region that contains information. The paper orientation is set for landscape. Margins, headers and footers, and other print options are already customized.
Let’s take a look at how the worksheet looks in print preview. Notice the paper orientation and the headers and footers.
Try It
1.Choose Print Preview on the File menu.
A preview of the printed repair form appears.
2.Choose the Zoom button.
3.Choose the Close button.
In this section, you saw how workspace files let you open multiple documents and set Microsoft Excel options. Custom number formats and styles make formatting easier. Names help users select cells and write formulas. Worksheet window panes are useful for controlling worksheet display. Printing options can be customized and are saved with the worksheet.
Choose Close on the File menu. Don’t save changes to documents.
In this part of the demonstration, you’ll learn about:
Hidden worksheet windows
Linking worksheets
Worksheet formulas
You don’t always have to display an open window to use it. You can hide an open worksheet so that data stored in it is out of sight but immediately available.
This version of the REPAIR.XLW workspace file opens REPAIR.XLS and a hidden worksheet, LOOKUP.XLS. The lookup worksheet is used by the repair worksheet, but it doesn’t need to be displayed.
Unhide the LOOKUP.XLS worksheet, and notice the various tables of data stored in it. Then hide it again.
Try It
1.Choose Open on the File menu.
2.Select [..] in the Directories box, and then choose OK.
3.Select [demo2] in the Directories box, and then choose OK.
4.Select REPAIR.XLW in the Files box, and then choose OK.
5.Choose Unhide on the Window menu.
The Unhide command is available only when a hidden window exists.
6.Select LOOKUP.XLS, and then choose OK.
The LOOKUP.XLS worksheet is displayed.
7.Scroll the worksheet horizontally.
The worksheet contains six tables of data, all relevant information for tracking repairs to plant equipment.
8.Choose Hide on the Window menu.
The LOOKUP.XLS worksheet is hidden.
The ability to link worksheets is a powerful part of your custom application. You create links by creating a reference in one worksheet to information in another worksheet.
The repair worksheet contains formulas that link it to the lookup worksheet. This makes data entry easier. Instead of typing all information in the form, you can type a code to identify a repair technician, for example, and have relevant information appear in the form.
Next, you’ll see how the formulas display information from the lookup worksheet when a correct code is typed and how they display error messages when an incorrect code is typed.
Try It
1.Select the first cell in the Code column, under Work Hours.
This is the cell in which you type a code to identify the repair technician.
2.Type 1, and then press ENTER.
Schmidt, Larry appears in the Technician column, and 1 appears in the Shift column. Technicians work on three shifts.
3.Move to the next cell in the Code column, under Work Hours.
4.Type 99, and then press ENTER.
#N/A appears in the Technician column and in the Shift column. This Microsoft Excel error message tells you that no technician has the ID code 99.
5.Type 7, and then press ENTER.
Microsoft Excel has an extensive set of functions for use with worksheets. Many of them do a lot more than math, and they can add power to your worksheets in ways that you might think were possible only by programming.
The repair worksheet uses many functions. The IF function controls the display of values. INDEX and MATCH are used to find an exact match in the lookup worksheet based on ID codes you type. String functions are also used to manipulate text values.
Try It
1.Select a cell in the Technician column, under Work Hours.
The formula uses IF and ISBLANK to suppress the display of any value if the Code cell is blank. If the Code cell contains data, the INDEX and MATCH functions find an exact match in an employee list in the LOOKUP.XLS worksheet.
The Paste Function command on the Formula menu makes it easy for you to add worksheet functions.
2.Select one of the blank cells in the space below the form area.
3.Choose Paste Function.
The Paste Function dialog box appears.
4.Scroll down and select INFO().
The INFO function gives you information about Microsoft Excel. Notice that Paste Arguments is turned on and a preview of the INFO function arguments is displayed.
5.Choose OK.
=INFO(type_num) appears in the formula bar.
6.If necessary, double-click type_num in the formula bar to select the argument portion of the formula.
7.Type osversion, and then press ENTER.
The operating system version appears in the cell.
8.Choose Undo Entry on the Edit menu.
In this section, you saw how easy it is to work with separate but related worksheets in Microsoft Excel. You can hide open worksheets so that they’re not distracting. You can write formulas that link information between worksheets. While writing complex formulas, you can take advantage of the Paste Function command on the Formula menu rather than rely on your memory or the documentation.
Try It
Hold down the SHIFT key, and choose Close All on the File menu. Don’t save changes to the documents.
In this section, you’ll learn about:
Protecting worksheets
Templates
You can protect various elements of a worksheet for security or to prevent accidental change. With Microsoft Excel, you can protect worksheet cell contents and hide contents in the formula bar. You can protect graphical objects so that they can’t be sized or moved. You can also protect a window so that the window properties can’t be changed.
In the repair worksheet, cells in which the user shouldn’t enter data are protected. The Comments text box is protected so that you can type text inside it but it can’t be sized or moved. The window is also protected so that it can’t be sized or moved.
Try It
1.Choose Open from the File menu.
2.Select [..] in the Directories box, and then choose OK.
3.Select [demo3] in the Directories box, and then choose OK.
4.Select REPAIR.XLW in the Files box, and then choose OK.
Note that the repair worksheet is already protected and that no document control menu is in the upper-left corner. That means the worksheet can be closed only by using the Microsoft Excel menus. If you protect a window and then add a custom menu, you can totally control how a user closes the window.
5.Attempt to drag the title bar.
Nothing happens.
6.Attempt to drag edges or corners of the window.
Nothing happens.
The formulas in the worksheet are also protected and hidden from the user.
Try It
1.Select the yellow cell next to the cell labeled “Equip Serial No”, in the lower part of the worksheet.
Note that all cells in which you’re to enter data are formatted with the YellowCell style. This is the cell in which you type the serial number of the equipment to be repaired.
2.Type 001-002-003, and then press ENTER.
Equipment Code 001 appears with the description Presser. Vendor Code 002 appears with the description General Machinery, Inc.
3.Select the cells containing these values.
Note that the formula is not displayed in the formula bar.
4.Attempt to type text into one of the formula cells.
A Microsoft Excel message tells you that locked cells cannot be changed.
You can’t move or size the Comments text box, but you can type new text in it.
Try It
1.Scroll to display the Comments box completely.
2.Drag across the text in the Comments box.
3.Type new text.
4.Click on a cell outside the Comments box.
5.Attempt to drag the Comments box.
Nothing happens.
When you’ve fully customized a worksheet, chart, or macro sheet that you want to use repeatedly, you can save it as a template. When you open a template, a new version of the document is created. This makes it less likely that a user will replace a completed template with a finished version. You can customize Microsoft Excel with templates. If you copy a template into the XLSTART directory, in which EXCEL.EXE is stored, your template appears in the New dialog box on the File menu the next time you start Microsoft Excel.
The repair worksheet is completed and protected. To make it easier for users to create new repair worksheets, you can save the repair worksheet as a template.
The REPAIR.XLS worksheet has already been saved as the REPAIR.XLT template, using the File Save As command on the File menu. Next, you’ll close REPAIR.XLS and open REPAIR.XLT several times.
Try It
1.Choose Close on the File menu.
Don’t save changes to the documents.
2.Choose Open on the File menu.
3.Select REPAIR.XLT in the Files box, and then choose OK.
A worksheet titled REPAIR1 appears.
4.Choose Open.
5.Select REPAIR.XLT in the Files box, and then choose OK.
A worksheet titled REPAIR2 appears.
In this section, you learned how to user-proof worksheets. You can protect cells and graphical objects with various levels of protection, and you can protect a window. You can save a worksheet as a template so that it’s easy to create new versions of the template.
Try It
1.Hold down the SHIFT key and choose Close All on the File menu.
This closes the LOOKUP.XLS worksheet. You need to close the protected templates individually with the Close command on the File menu.
2.Choose Close. Don’t save changes.
REPAIR2 is closed.
3.Choose Close. Don’t save changes.
REPAIR1 is closed.
In this section, you’ll learn about:
Macro buttons
Assigning macros to graphical objects
Custom dialog boxes
Event macros
Try It
1.Choose Open on the File menu.
2.Select [..] in the Directories box, and then choose OK.
3.Select [demo4] in the Directories box, and then choose OK.
4.Select REPAIR.XLM in the Files box, and then choose OK.
An Auto Open macro is called when you open REPAIR.XLM. Among other things, this macro opens the LOOKUP.XLS hidden worksheet and sets the workspace.
5.Choose Open again.
6.Select REPAIR.XLT in the Files box, and then choose OK.
The Repair3 worksheet appears, containing several macro buttons. All buttons are formatted for red text.
Macro buttons are very easy for users to understand, and they’re also an easy way to create a custom interface on a worksheet. You create a macro button using the Toolbar and then assign a macro, located on a separate macro sheet, to the macro button.
In the repair worksheet, Print Form is a macro button that runs a macro to make printing easier. The macro hides the button and redefines the yellow style so that all cells are white—suitable for monochrome printing.
Click the Print Form button to set up the repair worksheet for printing and display print preview. Then exit from print preview.
Try It
1.Click the Print Form button.
A preview of the printed repair worksheet appears. Note that the button is hidden and all cells are white.
2.Choose the Close button.
The repair worksheet reappears with the button and yellow cells.
You’ve seen macro buttons and text boxes. You can also draw shapes on a worksheet using the Toolbar or import a graphical object drawn in another application into a Microsoft Excel worksheet. You can assign a macro to any graphical object, which turns that graphical object into a macro button.
In the repair worksheet, all buttons are indicated by red text. In addition to the Print Form button are 4 other buttons. These buttons are actually squares drawn over cell labels and formatted to be invisible. That way, the physical presence of buttons doesn’t distract the user in the worksheet, but cells with red text indicate their presence.
When you click one of the red text cells, a custom dialog box appears to help you enter codes.
Try It
1.Point to the CODE button under Downtime Hours (on the right side of the screen).
The mouse pointer changes to a hand, telling you that you’re pointing to a button.
2.Click the CODE cell.
A custom dialog box, Downtime Codes, appears, and a list box shows codes and descriptions.
3.Select 01-Shift Change, and then choose OK.
The code 01 appears in the Code column, under Downtime Hours. Shift Change appears in the Reason column, under Downtime Hours.
Custom dialog boxes are an important part of custom interfaces for your applications. You can create a custom dialog box that uses any of the controls you see in an ordinary Microsoft Excel dialog box. Among the possibilities are command buttons, check boxes, option buttons, list boxes, and drop-down list boxes.
You just saw how a custom dialog box makes it easy to select ID codes, which are hard to remember. In the repair worksheet, selecting available codes from a list saves users a lot of time.
A majority of macros are called by some user action, such as choosing the Run command on the Macro menu or clicking a macro button. With Microsoft Excel, you can also assign macros to run when a particular event occurs. For example, you can run a macro when a certain key is pressed, when a certain document is activated, or when data is received from another application.
In the REPAIR.XLM hidden macro sheet, three macros are triggered by events. One macro runs when REPAIR.XLM is opened, to perform setup. Another runs when REPAIR.XLM is closed, to perform cleanup. A third macro runs whenever a recalculation occurs, to check for errors in the repair worksheet.
A macro assigned to the recalculation event is called each time you enter new data in the repair worksheet. But you see the results of the macro only when erroneous data is entered.
Try It
1.Select the Work Center cell in the lower part of the worksheet.
2.Type 99, and then press ENTER.
The #N/A error appears in the Work Center Description cell. A custom alert message prompts you that 99 is not a Work Center code.
3.Choose OK.
The Work Center Codes dialog box appears.
4.Select 2-Warehouse, and then choose OK.
The code 2 appears in the Work Center cell, and Warehouse appears in the Work Center Description cell.
In this part of the demonstration, you learned about macro buttons and graphical objects—all of which can have macros assigned. You saw how custom dialog boxes make selecting options easy. You also saw how Microsoft Excel can have a macro that’s triggered by an event.
Try It
1.Hold down the SHIFT key, and choose Close All on the File menu.
This closes LOOKUP.XLS and REPAIR.XLM. The Auto Close macro in REPAIR.XLM runs, clearing event macro definitions and restoring workspace settings. You need to close the Repair template with Close on the File menu.
2.Choose Close. Don’t save changes.
In this section, you’ll learn about:
Custom menus
Macro sheet methodology
Macro functions
The REPAIR.XLA add-in macro sheet contains an Auto Open macro that installs a custom menu bar. An add-in is a special type of macro sheet that users cannot unhide. The macros in the add-in sheet do not appear in the Run dialog box on the Macro menu. Save a macro sheet as an add-in if you don’t want users to modify the macros in the sheet.
Try It
1.Choose Open on the File menu.
2.Select [..] in the Directories box, and then choose OK.
3.Select [demo5] in the Directories box, and then choose OK.
4.Select REPAIR.XLA in the Files box, and then choose OK.
A custom menu bar appears with the File, Data, and Update menus. The Data menu is currently unavailable.
Microsoft Excel offers full support for custom menus, which are an important part of creating a custom interface for your application. You can define your own menu bars that contain custom menus and commands. You can also insert or add custom commands to the built-in menu bars.
In the repair application, the Auto Open macro installs a custom menu bar and displays it when you open REPAIR.XLA. The three menus are File, Data, and Update. Notice that the Data menu is currently unavailable because its commands are used only when you have a repair form open.
Try It
1.Choose Open on the File menu.
The macro called displays the standard Open dialog box, but the file name defaults to *.REP so that only repair forms are displayed. When new repair forms are created, a macro assigns a hidden name to them. If that name is not present in a document you open, the application won’t let you open the document.
2.Select 00000002.REP in the Files box, and then choose OK.
The completed repair form 00000002.REP appears.
The repair application also offers an easy way for users to update the databases in the LOOKUP.XLS worksheet. This is part of making the application a complete solution. The maintenance of the application is just as transparent to the user as is data entry.
3.Choose Update Malfunctions.
The standard data form appears for the Malfunctions table on the LOOKUP.XLS worksheet.
4.Choose the New button.
A new record is created.
5.Type It just broke
6.Choose the Close button.
The Malfunctions table is updated to include the new entry. The new malfunction code can now be entered into a repair form.
7.Choose Data Malfunction Codes.
A list of malfunctions appears.
8.Scroll to the bottom and select “It just broke”, and then choose OK.
The new malfunction is entered in the worksheet.
9.Choose Close on the File menu.
10.Choose Exit on the File menu.
The macro called by the Exit command closes LOOKUP.XLS and REPAIR.XLA.
Next, you’ll see how to set up Microsoft Excel macro sheets to build applications. You need to take a structured approach.
Try It
1.Hold down the SHIFT key and choose Open on the File menu.
You need to hold down the SHIFT key while you choose Open to prevent the Auto Open macro from running.
2.Select REPAIR.XLA in the Files box, and then choose OK.
The REPAIR.XLA macro sheet is opened, and the Auto Open macro is suppressed.
In the REPAIR.XLA add-in macro sheet, major areas are divided into separate columns:
The first set of columns contains macros.
The second set of columns contains dialog box definitions used by macros to display custom dialog boxes.
The third set of columns contains menu definitions used by macros to display custom menus.
The fourth set of columns, unused in REPAIR.XLA, can be used to store lists.
Try It
Scroll vertically through columns A through C.
Notice that REPAIR.XLM contains functionally different types of macros in columns A through C:
The first area, labeled (M)acro (C)ommand (E)vent, is for macros called by events.
The second area, labeled (M)acro (C)ommand (U)ser, is for macros called by user actions—either custom menu commands or buttons. The user-called macros are listed left to right and top to bottom, as they appear in the custom menu.
The third area, labeled (M)acro (C)ommand (C)alled, is for subroutine macros called by a specific macro.
The fourth area, labeled (M)acro (C)ommand (S)ubroutine, is for generic subroutine macros that can be called by any number of macros.
The Microsoft Excel macro language is composed of functions. You’re already familiar with many of these functions as worksheet functions. But additionally, some functions, called macro functions, are specific to macro sheets.
The four general types of macro functions are:
Action-equivalent functions, which automate actions you can perform manually in Microsoft Excel. For example, the WORKSPACE function is equivalent to choosing the Workspace command on the Options menu.
Value-returning functions, which give you information about Microsoft Excel. For example GET.DOCUMENT(1) returns the name of the window that’s currently active.
Control functions, which allow you to control program flow. For example, IF allows you to branch program flow based on a condition. FOR and WHILE allow you to construct repeating loops.
Customizing functions, which allow you to customize the Microsoft Excel interface. For example, ALERT displays a custom error message. ADD.MENU adds a custom menu to a menu bar. DIALOG.BOX displays a custom dialog box.
In this section, you learned how to create a completely customized application in Microsoft Excel. You saw custom menus in the application. Then you saw an example of how to organize a macro sheet and learned a little bit about the macro language.
Try It
Hold down the SHIFT key and choose Close on the File menu.
This closes REPAIR.XLA and suppresses the Auto Close macro.