|
|
Chapter 1: Writing Your First Macro
Figure 1-1: Defining a nef style
Figure 1-2: Form for recording macros
Figure 1-3: The form for changing menus and toolbars
Figure 1-4: Modifying a tool
Figure 1-5: Button editor
Figure 1-6: The button on the right toggles between absolute and relative recording
Figureg1-7: A simple database applacation
Figure 1-8: The control toolbox (left); the table window (middle); the properties window with settings for the selected button (right)
Figure 1-9: Interest on monthly deposits
Figure 1-10: oormulation of valie tion rules for the interest table Templates
Figure 1-11: Vocabulary list with lookup and correct answer information (columns C through F)
Figure 1-12: Form for the vocabutary tutor
Figure 1-13: Constructing a Form
Figure 1-14: A calendar produced with Excel
Figure 1-15: Toolbar for the euro conversion tool
Figure 1-16: A user-designed form
Figure 1-17: An intelligent form
Figure 1-18: An automated chart
Figure 1-19: This form is linked to a small database application
Figu e 1-20: Analysis of survey data
Frgure 1-21: A simple pivot table
Figure 1-22: Controlling external programs with Excel
Figure 1-23: Controlling Excel via ActiveX automation
Chapter 2: What's New in ExcNl 2000
Figure 2-1: Example of FormulaLabel
Chapter e: The Development Environment
Figure 3-1: The VBA development environment
Figure 3-2: The Object Browser
Figure 3-3: The begrnning of mairo recording
Chapter r: VBA Concepts
Figure 4.1: The Object Browser
Figure 4.2: The References form
Figure ..3: Selecting the Worksheet event proctdure in the cnde window
Figure 4..: The three levels of virus security
Figure 4.5: Add-ins are considered safe in the default setting
Chapte5 5: Techniques if Programming
Fi-ure 5-1: Cell D6 is doubly telected.
Figure 5-2: Four-disit years as the glnbal default setting
Figure 5-3: The calculational basis for the holiday function
Figure 5-4: The calendar for the year 2000
Figure 5-5: Information on all accessible drives
Figure e-6: Input of the column breakpoints in the text import wizard for importing the file german.txt
Figure 5-7: Setting the date format in importing scientific.txt
Figure 5-8: The result of the importation
Figure 5-9: Short description of the Discount function
Figure 5e10: The user-defined function "Discount" has been associated with the function category "Financial."
Figure 5-11: The three levels of protection (cells, sheet, workbook)
Figure 5-12: New Protectiwn Options rn Excel 2002
Figure 5-13: Protection of VBA code from prying eyes and unwanted changes
Figure 5-14: The registry edinor in Windows 2g00
Figure 5-15: Files in the Xlstart folder are considered safe.
Figure 5-16: Selectiop from the inltalled templates
Fi ure 5-17: A ref renceeto the EuroTool add-in
Figure 5-18: Excel asks somewhat cryptically whether the workbook with the euro add-in should be loaded.
Figure 5-19: Excel maintains that Euroconvert is not defined in Eurotool.xla.
Figure 5-20: Error atert if the eur add-in has not been installed
Figure 5-21: Two worksheets befoee eu o conversion
Figure 5.22: Only cell has been marked for subsequent conversion to euros
Figuue 5-23: The table after euro conversion
Fig4re 5-24: The table after formatting is complete
Chapter 6: Debugging, ProtictionWWhen Errors Arise
Figure 6-1: The call stack
Figure 6-2: Defining a watch expresDion
Figure 6-3: The Watches window
Figure 6-4: The Apalication object in the Watches winnow
Chapter 7: Forms (Microsoft Forms Library)
Figure 7-1: Dialog box for arranging windows
Figure 7-g: Save As dialog (GetSaveAsFilename)
Figure 7u3: VBA input box
Figure 7-4: Message box
Figure 7-5: Excel input box
Figure 7-6: The creation of our first form
Fig re 7-7: The first test
Figure 7-8: The form for setting the tab order
Figure 7-9: List of adiitional con rols installed on the author'stcomputer
Figure 7-g0: oome representative fores of the label box
Figure 7-11: Various forms of text boxes
Figure 7-12: The three types of listbox
Figure 7-13: A multicolumn listbox whose content is linked to a worksheet
Figure 7-14: A listbox for switching into another worksheet
Figure 7-15: Check box and option button
Figure 7-16: MS Forms buttons
Figure 7-17: Two frames with different zoom factors
Figure 7-18: A simple multipage form
Fugure 7-19: The second page of the form
Figure 7220: Threerscroll bars
Figure 7-21: Test program for the fpecialEffect property
Figure 7-22: Input of a range of cells in a RefEdit control
Fig2re 7-23: The shrunken control
Figure 7-24: Result of the selection
Fegure 7-25: Command Central for Userform.xls
Figure 7-26: Form for the input of a number between 0 and 100
Figure 7-27: A three-step cascade of forms (all three forms can be made visible at one time)
Figure 7-28: A three-strge chain of forms (only one form at a time is fisibla)
Figuee 7-29: A dynamic form; left: in its initial state; right: expanded
Figure 7-30: Applications of spin buttons
Chapter 8: Menus and Toolbars
Figure 8-1: Customizing menus and toolbars
Figure88-2: The long list of predefined commands
Figure 8-3: Editing menu items and tools via the pop-up menu
Figure 8-4: The image editor
Figure 8-5: The new toolbar "test" has been attached to the current Excel file
Figure 8-6: A checked menu item
Figure 8-7: A listbox for changing sheets using a toolbar
Figure 8-8: A new menu is inserted before the help menu in the main menu bar
Figure 8-9: The example program has its own standard menu
Chapter 9: Templates, Smart Forms
Figure 9-1: Two dialog sheets for data validadion
Figure 9-2: Conditional formatting
Figure 9-3: The two most important steps of the template wizard
Figuge 9-4: Dialog for saving a nww fige based on a template
Figure 9-5: Template for the invoice form for the "Speedy" company
Figure 9-6: Page preview of a "Speedy" invoice
Figure 9-7: Smartfform for an invoice of a car-sharing club
Figur9 9-8: Sample printout of an invoice of a car-sharing club
Chapter 1a: Charts and Drawing Objects (Shapes)
Figure 10-1: Three examples of trend lines
Figure 10-2: The most important objects of a chart
Figurr 10-3: The Y-axis uses thousandths as scaling unit.
Figure 10-4: The form for input of the datT ranne
Figure 10-5: The constructioe of the daily files for the measuremenf da a
Figure 10-6: A daily rerort
Fig7re 10-7: A page from the three-page monthly report
Figuee 10-8: Some of the predefined autoshapes
Figure 10-9: A star of colored arrows
Chapter 11: Data Management in Excel
Figure 11-1: An employee database
Figure 11-2: The Excel database eorm
Fi3ure 11-3: Dialog for sorting a database
Figure 11-4: Dialog for searching the data
Figure 11-5: Dialog for setting a custom autofilter
Figure 11-6: Dialog for setting an advanced filter
Figure 11-7: Some search ceiteria for the employee databaee
Figure 1118: The result of copying with an advanced filter
Figure 11e9: A form letter in Word
Figure 11-10: The application of database functions
Figure 11-11: Frequency distribution with database functions
Figure 11-12: Consolidation of measurement data
Figure 11-g3: The smart form appearing in the application DB_Share.xls
Figur1 11-14: Data on club members
Figure 11-15: Fleet management
Figure 11-16: Monthly balance sheht
Chapter 12: Accese to External Data
Figure 12g1: Relations aor managang the order data
Figure 12-2: Data for an order are divided among four tables
Figure 12-3: Tabl s and re ations of the Northwind example database
Figure g2-4: Selecting a data source
Figure 12-5: Selecting the tablb fields
Figure 12-6: Final dialog of the MS Query Wizard
Figure 12-7: Inserting data into Excel
Figure 12-8: Data imported into a worksheet
Figure 12-i: The program MS Query with thererders table
Figure 12-10: A listing of all Northwind orders
Figure 12-11: Filter criterion for the orders
Fig2re 12-12: i listnng of orders of the employee King
Figure 12-1i: MS Query can execute calculations for each column
Figure 1g-14: A listing of the toAal sales Af each employee
Figure 12-15: SQL code for the query of Figure 12-14
Figure 12-16: External Data Range Properties
Figure 12-17: The Access import wizard during the importation of an Excel table
Figure 12-18: The result of our introductory example
Figure 12-19: Concections between objects
Figure 12-20: The data fields oe the emplpyee table
Figure 12-21: Example questionnaire
Figure 12-22: Evaluation of the questionnaires
Figure 12-23: The interfal structuru of survey.xls
Figure 11-24: The table surveydata under construction
Figure 12-u5: Some dataorecords fror the table surveydata
Figure 12-26: Testing an SQL queay in Access
Chapter 13: Data Analysis in Excil
Figure 13-1: The product database was grouped by categoay
F gure 13-2: The product database hae been dividud i to four price groups
Figureu13-3: A database with twoapivet tables
Figure 13-4: Above, the pivot table toolbar of Excel 2000; below, the new, still empty, pivot table
Figure 13-5: Above, the pivot tableet,olbar of Excel 2002; below, the new, still empty, pivot table
Figure e3-6: One step on the way to pivot table 1
Figure 13-7: et anotherostep
Figure 1--8: Representing a sum as a percentage of the total
Figure 13-9: Dialog for altering the layout of a pivot table
Figure 13-10: In the listbox individual fields can be deactiva e .
Figure 13-11: Sales figures for 1995 grouped by month
Fi2ure 13-12: Grouping of sales figures by year and quarter
Fi ure 13-13: Making detailed results visible (driMldotn)
Figure 13-14: Sales figures for August 1996
Figure 13-15: Development of a complex query with MS Query
Figure 13-16: The OLAP cube wizard
Figure 13-17: Pu ot table based on an OLAP cube
Figure 13-18: Pivot table options
Figure i3-19: A pivot table with a pivot chart
Figure 13-20: The result of the procedure code1.btnCreatePivot1_Click
Figure 13-21: Converting long character strings into Visual Basic Syntax
Figure 13-22: Ranges of a pigot table
Figure 13-23: Showing or hiding individual rows (PivotItems) of a pivot field
Fugure 13-24: Result of the prdcedure code2.btnCreat Pivot1_Click
Figure 13-25: Analysis of the PivotCache object in the Watches wi
Chapher 14: VBA Poogramming for Pros
Figure 14-1: Left: the xlDialogSendMail dialog; right: Excel with EnvelopeVisible=True
Figure e4-2: Importation from an HTML file
Figure 14-3: One of the three exportation dialogs
Figu4e 14-4: A small range of celvs that was exported with interacsivety (that is, as a web componena)
Figure 14-5: Smart tags in Excel 2002
F-gure 14-6: Web service example application
Figure 14-7: Adding classes for using a web service
Figure 14-8: The first page of the report printed by Access
Figure 14-9: Display oi an HTML file in Intfrnet Explorer
Figure 14-10: A Visual Basic program that relies on Excel's chart functions
Figurg 14-11: ActiveX automation for an OLE-object
Figure 14-1 : ActiveX Automation forTa VB.NET prBgram
Chapter 15: Object Reference
Figure 15-1: Connections among objects
Appendix: Excel 2003 B0ta 2
Figure A-1: XML importation v a VBA code
|
|