Developing Solutions with Office 2000 Components and VBA

About

Features



Description

• Copyright 2000
• Dimensions: 7" x 9-1/4"
• Pages: 528
• Edition: 1st

• Book
• ISBN-10: 0-13-026305-2
• ISBN-13: 978-0-13-026305-6

Microsoft Office continues to "grow up" as a productivity and development platform, so that it is now one of the most powerful such platforms on the market for business applications. It consists of hundreds of programmable objects developers can use to create powerful custom applications -- far more quickly than building them from scratch. Peter Aitken begins by reviewing Microsoft Office 2000's applications, the components developers can use, and the basics of development with Office components. Next, he shows how to master the fundamentals of VBA, the programming language that provides the "glue" needed to build custom Office solutions. He reviews the components associated with Word, Excel, Access, PowerPoint, FrontPage, and Outlook -- and the Shared Components accessible to all of them. And, finally, he walks the developer through the construction of four real-world Office applications, with an emphasis on using the power of Office on the Web and corporate intranets.



Sample Content

Downloadable Sample Chapter

Click here for a sample chapter for this book: 0130263052.pdf

Table of Contents

(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.

Preface

Preface

When most people think of Microsoft Office, they think of a powerful set of office applications-Word for word processing, Excel for spreadsheets, Access for databases, and so on. There is, however, much more to Office than that. Hidden behind the application's programs is a powerful set of development tools that can be used to create sophisticated custom solutions to address specific needs. The foundation of these development capabilities is the Office object model, a rich set of programmable objects, sometimes referred to as software components. These components provide the functionality of the individual applications that make up Office, and are also available for customized programming. When combined with the powerful Visual Basic for Applications (VBA) programming language, also part of Office, the result is a powerful and flexible development tool that is often your best choice when faced with a Windows development project.

Is Office a replacement for traditional programming languages such as Visual Basic, C++, and Java? In a word, no. From the developer's perspective, Office is specialized for creating custom applications that perform the same general sorts of tasks that the Office applications themselves do-manipulate text, work with numbers, display graphs, send and receive e-mail, and so on. Since it is exactly this type of functionality that is often needed, there are many situations in which Office will be your best choice of development tools. Outside this area, however, Office is not a good choice. For example, an astronomer writing a program to analyze crater patterns on Mars would not turn to Office. There are so many situations in which Office is the best choice that any Windows developer really should have some familiarity with its capabilities. Office may be your only development tool, or it may be one of half a dozen that you use, but it cannot be ignored.

This book is aimed at individuals who are at the beginner and intermediate level and who want to use Office to develop custom solutions. No previous programming experience is required, although if you do have such experience, particularly with Visual Basic, you'll be able to work through some sections of the book more quickly, Part Three in particular. My approach is a combination of reference material and demonstrations. I am a strong believer in learning by doing, and I feel that the best approach to learning how to use a development tool is a mixture of presenting the raw information you need, and showing you how it is used in a real-world situation.

I make no pretense of covering all the details of Office development. There is no way a single book can include all the related information, and even if such a book were possible, no one would want to read it. My goal is to cover to most important fundamentals of Office-those tools and techniques that you will need most often. Once you understand these fundamentals, it is an easy matter to turn to the online reference materials for the details that could not be included in the book.



Updates

Submit Errata

