Developing Solutions with Office 2000 Components and VBA

Peter G. Aitken has written more than 25 books on computer-related topics with a special focus on programming. His consulting firm delivers custom Office and Visual Basic solutions to businesses, government, and universities. He is also a contributing editor at Visual Developer magazine, where he writes a popular column on Visual Basic development.

(NOTE: Each chapter concludes with Summing Up.) I. INTRODUCTION TO OFFICE DEVELOPMENT.

1. Why Develop with Office and VBA.


Advantages of Office Development. Office Is a Familiar Environment. Office Development Decreases Development Time and Expense. Office Provides an Integrated Solution. Office Development Is Extensible. Software Components. Objects and Components. Objects in Office. Properties. Methods. Events. The Office 2000 Suite. Word. Excel. Access. PowerPoint. Outlook. FrontPage. Shared Components. Data Access. Web Technologies. What's New in Office 2000. Web Integration. Virus Protection. Add-In Architecture. More Comprehensive Event Model. More Objects. New VBA Language Elements. Improved Data Access. Custom Help Files.

2. The Basics of Office Development.


Objects and Automation. The Component Object Model. How Automation Works. The Office Object Model. Collections. Designing Your Custom Application. A Simple Office Application.

3. Visual Basic for Applications-the Fundamentals.


Parts of the VBA Editor. Editing Window. Project Explorer. Properties Window. Working with Code. Using Modules. Writing Good Code. Adding References. Classes and Type Libraries. Securing Your Code. Preventing Unauthorized Access.

4. Working with Office Objects.


Objects and References. Object Variables. Creating New Objects. Referencing Existing Objects. Destroying Objects. Collections. Accessing Collection Members. Adding and Removing Collection Members. Finding Objects in the Object Hierarchy. Early Versus Late Binding. The Object Browser. Objects and Events.

II. THE OFFICE COMPONENTS.

5. Using Word Components.


The Word Object Model. The Document Object. Opening and Creating Documents. Saving and Closing Documents. Stories in a Document. Manipulating Document Content. Other Document Properties and Methods. The Application Object. Setting Word Options. Word Dialog Boxes. Windows and Panes. Events in Word. Document-Level Events. Application-Level Events. An Application-Level Event Example.

6. Using Excel Components.


Excel Overview. The Excel Object Model. The Workbook Object. Opening and Creating Workbooks. Saving and Closing Workbooks. Printing Workbook Contents. E-mailing a Workbook. Other Workbook Properties and Methods. The Worksheet Object. Adding and Deleting Worksheets. Copying and Moving Worksheets. The Range Object. Manipulating Worksheet Contents. Finding and Replacing Data. Sorting Data. Working with Charts. Chart Sheets Versus Embedded Charts. The Chart Object. The ChartWizard Method. Controlling Chart Appearance. The Application Object. Object References. Excel Options and Settings. Using the WorksheetFunction Object. Events in Excel.

7. Using Data Access Components.


Access Fundamentals. Data Access Technologies. The Access Object Model. Opening and Closing Access Applications. The AccessObject Object. Opening Reports, Forms, and Data Access Pages. Referring to Open Objects. The CurrentProject Object. The CurrentData Object. The Screen Object. The DoCmd Object. Access Options. Startup Properties. Working with Forms and Reports. Using Controls. Working with Data Access Pages. Creating Data Access Pages. Using Data Access Pages. Securing Databases. Startup Options. Database Passwords. Hiding VBA Source Code.

8. Using PowerPoint Components.


The Presentation Object. Creating and Opening Presentations. Saving and Closing Presentations. Presentation Templates. Slide Shows. The Slide Object. Shapes on Slides. Slide and Shape Ranges. The PowerPoint Application Object.

9. Using FrontPage Components.


FrontPage Overview. The Web Object. Working with Folders. Working with Web Pages. Creating, Opening, Moving, and Deleting Web Pages. Working with PageWindows. Working with Web Page Content. Dynamic HTML-Should You Use It?

10. Using Outlook Components.


Outlook Overview. The Application and NameSpace Objects. Manipulating Folders and Items. Working with Mail Messages. Using Received Messages. Creating and Sending Messages. Working with Message Recipients. Working with Address Books. Working with Attachments. VBA in Outlook. Events in Outlook.

11. Using the Office Shared Components.


Shared Components Overview. The Office Assistant. Displaying and Animating the Office Assistant. Using Balloons. The FileSearch Object. Command Bars. Document Properties.

III. THE VBA LANGUAGE.

12. Data Storage and Operators.


Basic VBA Syntax. The Line Continuation Character. Comments. Formatting Source Code. VBA Editor Options. Storing Data. Naming and Declaring Variables. Numbers. Text. Constants. Objects. True/False Values. Dates. The Variant Type. Arrays. User Defined Types. Enumerations. Variable Scope. Operators. The Assignment Operator. Mathematical Operators. String Operators. Comparison Operators. Logical Operators. Operator Precedence and Parentheses.

13. Conditional and Loop Statements.


Conditional Statements. If Then Else. Select Case. Loop Statements. For Next. For Each Next. Do Loop. The GoTo Statement.

14. Writing Procedures.


Types of Procedures. Defining a Procedure. Passing Arguments. Optional and ParamArray Arguments. Passing Arguments ByVal and ByRef. Variables in Procedures. Calling a Procedure. Named Arguments. Procedure Scope. Planning and Storing Procedures.

15. Working with Strings.


String Processing Summary. Asc. Chr. InputBox. InStr, InStrRev. InStrRev. LCase, UCase. Left, Mid (function), Right. Len. LTrim, RTrim, Trim. Mid (function). Mid (statement). MsgBox. Option Compare. Right. RTrim. Space. Str. String. StrComp. StrConv. Trim. UCase. Val.

16. Working with Dates and Times.


The Date Data Type. Creating Dates and Times. Adding and Subtracting Dates and Times. Getting Date and Time Information. Formatting Dates and Times.

17. Working with Files.


Overview. File Access. Opening Files. File Numbers. Closing Files. Using Sequential Files. Using Random Files. Using Binary Files. Working with Text Files. Object-Oriented Text File Manipulation. Traditional Text File Manipulation. File Management. Object-Oriented File Management. Traditional File Management.

18. Creating Custom Classes and Objects.


Why Create Classes? Class Module Fundamentals. Creating Class Properties. Read-Only Properties. Variant Properties. Multiple Argument Properties. Data Validation with Property Procedures. Creating Class Methods. Class Events. A Class Demonstration.

19. Creating User Forms.


Creating a User Form. User Form Properties. Adding Controls to a User Form. Working with Controls. User Form Code and Event Procedures. Change. Click. DblClick. Enter, Exit. KeyDown, KeyUp. KeyPress. MouseDown, MouseUp, MouseMove. Displaying, Using, and Hiding User Forms. A User Form Demonstration.

IV. OTHER OFFICE DEVELOPMENT TOOLS.

20. VBA Error Handling.


Writing Solid Code. Runtime Errors. Preventing Errors from Happening. Trapping Errors. On Error Resume Next. ADO Errors. Raising Errors. Class Module Errors. Returning Errors from Functions. Testing Your Error Handling Code. Error Logging. Dealing with Unanticipated Errors.

21. Debugging, Deploying, and Supporting Your Application.


Debugging Your Applications. Working with Breakpoints. Using Watches. The Locals Window. Controlling Program Execution. Using the Call Stack. Using the Immediate Window. Deploying Your Application. Supporting an Application with Online Help. Microsoft HTML Help. WinHelp 4.0.

22. Using the Windows API and Registry.


The Windows API. Declaring API Procedures. The API Text Viewer. Calling API Procedures. Strings and API Procedures. Using Callbacks. The Windows Registry. VBA Registry Functions. API Registry Functions.

23. Office Technologies for the Web.


Web Components Overview. Using the Web Components. Excel. Access. FrontPage. Other Web Page Editors. Using the Spreadsheet Control. Using the PivotTable Control. Using the Chart Control. Using the DataSource Control.

V. PUTTING IT ALL TO WORK.

24. Web Publishing of a Multi-Author Compound Document.


The Scenario. Planning. Step 1: Keeping the Required Information. Step 2: Checking for Updates. Step 3: Assembling and Publishing the Document. Step 4: Possible Errors and Bugs. The Excel Spreadsheet. Writing Pseudocode. The SubDocument Object. The Main Application.

25. Numeric Processing and Graphing.


The Scenario. Planning. Creating the Workbook. Planning the Code. Potential Errors and Bugs. The Main Procedure. The Secondary Procedures.

26. Custom Report Generation.


The Scenario. Report Generation Basics. Structure of a Report. Adding Report Elements. A Demonstration.

Appendix: Keycode Constants and Values.
Index.