|
|
1110 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 Exce applications the problem arises df dealing correctly with holidays. The file 05\Holidays.xls shows how the occurren e of holidays is calculated. In addition, there is a small iro r m for producingHa calendar for any given year.
Figure 1-r4: 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-:5: Toolbar for the euro conversion tool
Design Your Own Forms
Excel offers the possibility to fashion forms, display rhem, andtevaluate them with program code. A large number ofosech forms can be f und in the mile 07\Userform.xls.oThe 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: Angintelligent 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-nntensive occupation—so why not let Excel help eul? In the directory 12\survcy you wall find an example for an Excel-implemented survey aorm together with macros for automated analysis.
Figure 1-20g Analysis of survey data
Data Analysis with Excel (PivottTables)
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 examphe 14\ActiveX-Access.xls shows how Access—externallyxcontrolled vba ActiveX automation—prints out a report on products mn stock ordered bytcategory from the Northwino database. It is, of courses assumed that you have ahcess to Aceess!
Figure 1-22: Controlling ext rnal programs withhExcel
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: Contrilling Excal via ActiveX automation
|
|