Appendix A: Complete Enterprise Information System

In Chapter 5, “Explore Data Objects,” you learned how to retrieve external data into a PivotTable report; in Chapter 6, “Explore Graphical Objects,” you learned how to work with graphical objects; in Chapter 7, “Control Visual Basic,” you learned how to create loops; in Chapter 8, “Extend Excel and Visual Basic,” you learned how to create subroutines; and in Chapter 9, “Launch Macros with Events,” you learned how to use ActiveX controls and event handlers. In this appendix, you’ll see what can happen when you use Microsoft Office Excel 2007 and Microsoft Visual Basic for Applications (VBA) to put all these pieces together.

Included with the practice files for this book is a bonus application. This application is a simple enterprise information system (EIS). It uses one of Lucerne Publishing’s databases in an easy-to-use, visually powerful way to display order information for each state in the Lucerne Publishing territory.

On The CD-Important 

Before you complete this appendix, you need to install the practice files from the book’s companion CD to their default locations. See “Using the Book’s CD” on page xv for more information.

USE the EIS.xlsm _file. This practice file is located in the Documents\MSP\ExcelVBA07SBS folder.

BE SURE TO copy the EIS.xlsm workbook into the trusted location you created in Chapter 1.

OPEN the EIS.xlsm workbook.

Important 

Whenever you receive a macro-enabled workbook from someone else-even someone you trust-you should first open the workbook without enabling macros and use the Project Explorer window in Visual Basic to review the code-particularly any event handler procedures attached to worksheets or workbooks. Once you are comfortable that the code is safe, you can enable macros or put the workbook in a trusted location.

When the EIS workbook opens, the application changes the Excel window size and displays an introductory animation that leaves you with a colored, shaded map of the western United States.

1. When the animation finishes, click the Replay label in the bottom-left corner. While the animation runs, click the Cancel button.

   The animation stops, and the procedure that controls it jumps directly to its end and displays the map. Animations are good for attracting attention, but they can be annoying to an impatient user. It’s usually a good idea to provide a mechanism for bypassing a lengthy animation.

2. Click the map for California.

   The screen switches to display quarterly orders for a two-year period, complete with a graph.

   

3. Look at the caption at the top of the application.

   The caption says Lucerne Publishing EIS rather than Excel. The caption contributes to the custom appearance of the application.

4. Look at the text at the top-left corner of the worksheet to see the label that says Main. Then move the mouse over the text.

   The font changes to signal to the user where to click. This is a simple way to navigate within the application. Rather than change the Ribbon, which you can’t do directly with VBA, you can add a label.

5. Click the Main label to return to the map.

6. Move the mouse over the notch where Nevada interlocks with Arizona. When the mouse is over the Nevada part of the notch, click to display Nevada data. Then return to the main sheet, and click when the mouse is over the Arizona part of the notch.

   You can click anywhere within the exact border of the state to show the data for that state.

7. Try selecting a cell on the worksheet.

   This is a “look but don’t touch” screen. The application makes use of a worksheet, but from the user’s perspective, it could be a completely custom application.

8. Press Ctrl+N to create a new workbook.

   The Excel window returns to the normal appearance it had before the application started.

   With VBA macros, you can’t prevent the user from opening a new workbook, but you can make your application behave properly when it happens.

9. Change the Excel application border to a noticeably different size and shape.

10. On the View tab of the Ribbon, click Switch Windows, and then click Lucerne Publishing EIS.

    The window changes back to the look of the application.

11. Press Ctrl+Tab (the shortcut for switching windows) to see the application switch back to the size and shape you assigned. Then press Ctrl+Tab to return to the application.

12. Click Main to return to the main sheet. Then click the Exit label in the top-left corner of the workbook to close the application workbook.

This application goes beyond what you are likely to do with macros, but it may give you a sense of the limits of what a macro can do. Explore the code within it. Some of the code will be new, but after you have completed the chapters in this book, much of it will be familiar to you. This application may give you some interesting ideas to apply in the macros you create. Have Fun!

Tip 

As mentioned in Chapter 1, “Make a Macro Do Simple Tasks,” if you want to create a full-fledged application using Excel as a platform, you’ll probably want to use Microsoft Visual Studio instead of VBA.