1.10 Example Programs for You to Try

This section gives a brief description of the most interesting examples in this book. The figures should serve as invitations to you to go ahead and fire them up and try them out. At the same time, this section should give some indication as to just how extensive the possibilities of VBA programming are.

Install the example files in a directory on your hard drive as described in the Appendix.

Note	If the programs correspond in some measure to ideas that you have for using Excel, then you can read the details in the indicated sections. A cross-reference as to which example files are described where in the book can be found in the Appendix.

Calendar and Holidays

In many Excel applications the problem arises of dealing correctly with holidays. The file 05\Holidays.xls shows how the occurrence of holidays is calculated. In addition, there is a small program for producing a calendar for any given year.

Figure 1-14: A calendar produced with Excel

A Macro for Using the Euro

Does your spreadsheet need to convert German marks into euros (or some other European currency)? This process cannot be completely automated, but in 05\Euro.xls you will find some procedures to help you with the task.

Figure 1-15: Toolbar for the euro conversion tool

Design Your Own Forms

Excel offers the possibility to fashion forms, display them, and evaluate them with program code. A large number of such forms can be found in the file 07\Userform.xls. The forms can be invoked with a mouse click.

Figure 1-16: A user -designed form

" Intelligent " Billing Form for a Mail-Order Business

Creating invoices can be greatly simplified through the use of "intelligent" forms. The template 09\Speedy.xlt provides a simple example. More refined variations on this theme are presented in the example files DBCars.xls and Vertret.xls.

Figure 1-17: An intelligent form

Automated Data Presentation with Graphs

The extensive possibilities for creating graphs and charts in Excel are often used for presenting large quantities of data in graphic format. This process lends itself, of course, to automation. In 10\Chart.xls is demonstrated how (using simulated test data) daily and monthly records of data can be created.

Figure 1-18: An automated chart

Balance Sheet for a Car-Sharing Club

As a member of a car-sharing club, you do not own your own automobile, but rather you borrow one from your club when you need it. The example 11\DB_Share.xls shows how an "intelligent" form (namely, 09\Share.xlt) can be extended to create a simple database application. With DB_Share you manage the fleet of cars , the names and addresses of members , and print out invoices for individual trips.

Figure 1-19: This form is linked to a small database application

Analysis of Survey Data

The evaluation of survey data is a labor- intensive occupation ”so why not let Excel help out? In the directory 12\survey you will find an example for an Excel-implemented survey form together with macros for automated analysis.

Figure 1-20: Analysis of survey data

Data Analysis with Excel (Pivot Tables)

Excel is an excellent tool for analyzing data, regardless of whether they reside in an Excel file or in an external database. The file 13\Pivot.xls provides a host of examples of pivot tables and how to program them.

Figure 1-21: A simple pivot table

ActiveX Automation

The example 14\ActiveX-Access.xls shows how Access ”externally controlled via ActiveX automation ”prints out a report on products in stock ordered by category from the Northwind database. It is, of course, assumed that you have access to Access!

Figure 1-22: Controlling external programs with Excel

Not only can Excel control external programs via ActiveX automation, the reverse direction is also possible. The Visual Basic program 14\Vb6\Chart\ExcelChart.exe uses the chart functions of Excel to display its own data in an Excel chart.

Figure 1-23: Controlling Excel via ActiveX automation