VBA for Office 2000 Unleashed Table of Contents Introduction Part I—Unleashing the VBA Programming Language Chapter 1—Introducing VBA What Is a Macro? How Does VBA Fit In? The Three Types of Procedures Recording a VBA Macro Viewing the Resulting Module Editing a Recorded Macro Touring the Visual Basic Editor The Project Explorer The Properties Window The Work Area Writing Your Own Command Macro Creating a New Module Writing a Command Macro Running a VBA Macro Creating User-Defined Functions with VBA Understanding User-Defined Functions Writing User-Defined Functions Employing User-Defined Functions Working with Procedures The Structure of a Procedure Calling a Procedure Calling a Procedure Name in the Same Project Calling a Procedure in Another Project Public Versus Private Procedures Another Method For Inserting a Procedure Visual Basic Editor Techniques for Easier Coding Taking Advantage of IntelliSense Working with Comment Blocks Finding Code Using the Find Feature Using the Replace Feature Navigating Modules with Bookmarks Adding Bookmarks Bookmark Navigation Methods Deleting Bookmarks Working with Modules Renaming a Module Exporting a Module Importing a Module Removing a Module Setting Visual Basic Editor Options The Editor Tab The Editor Format Tab The General Tab The Docking Tab Protecting Your Code Chapter 2—Understanding Variables Declaring Variables Understanding Variable Scope Procedure-Level Scope Module-Level Scope Public Scope Passing Variables as Arguments Passing Variables by Reference Passing Variables by Value Working with Static Variables Avoiding Variable Errors Variable Data Types Changing the Default Data Type Creating User-Defined Data Types Using Array Variables Dynamic Arrays Multidimensional Arrays Working with Constants Using Built-In Constants Creating User-Defined Constants Chapter 3—Building VBA Expressions Understanding Expression Structure VBA Operators Arithmetic Operators The Concatenation Operator Comparison Operators Using Comparison Operators with String Values Using the Like Operator Logical Operators Understanding Operator Precedence The Order of Precedence Controlling the Order of Precedence Working with Numeric Expressions A Closer Look at the Arithmetic Operators The Addition Operator (+) The Subtraction and Negation Operators ([nd]) The Multiplication Operator (*) The Division Operator (/) The Integer Division Operator (\) The Exponentiation Operator (^) The Mod Operator Numeric Data Type Conversion Functions VBA's Math Functions VBA's Financial Functions Working with String Expressions Working with Logical Expressions The And Operator The Or Operator The Xor Operator The Eqv Operator The Imp Operator The Not Operator Working with Date Expressions Formatting Numeric and Date Expressions The Format Function VBA's Predefined Formats Predefined Numeric Formats Predefined Date and Time Formats User-Defined Numeric Formats User-Defined Date and Time Formats New Functions in VBA 6.0 Chapter 4—Working with Objects What Is an Object? The Object Hierarchy Working with Object Properties Setting the Value of a Property Returning the Value of a Property Working with Object Methods Handling Object Events Working with Object Collections The Object Browser Object Browser Features Working with the Object Browser Referencing Additional Object Libraries Assigning an Object to a Variable The Is Operator Working with Multiple Properties or Methods The Application Object Properties of the Application Object Methods of the Application Object The Window Object Specifying a Window Object Opening a New Window Window Object Properties Window Object Methods Chapter 5—Controlling Your VBA Code Code That Makes Decisions Using If...Then to Make True/False Decisions Using If...Then...Else to Handle a False Result Making Multiple Decisions Using the And and Or Operators Using Multiple If...Then...Else Statements Using the Select Case Statement A Select Case Example: Converting Test Scores to Letter Grades Another Example: Taming the RGB Function Functions That Make Decisions The IIf Function The Choose Function The Switch Function Code That Loops Using Do...Loop Structures Using For...Next Loops Using For Each...Next Loops Using Exit For or Exit Do to Exit a Loop Part II—Unleashing Microsoft Office Objects Chapter 6—Word 2000 VBA Programming The Transition from WordBasic to VBA Reading and Setting Word's Program Options via VBA The View Tab The General Tab The Edit Tab Word's Application Object Properties of the Application Object Methods of the Application Object Events of the Application Object Working with Document Objects Specifying a Document Object Opening a Document Creating and Opening a Word Workspace Creating a New Document The RecentFiles Object Document Object Properties Document Object Methods Document Object Events Objects That Represent Text in Word The Range Object The Range Method The Range Property Range Object Properties Range Object Methods The Selection Object Selection Object Properties Selection Object Methods The Characters Object The Words Object The Sentences Object The Paragraph Object Paragraph Object Properties Paragraph Object Methods Checking Spelling and Grammar with VBA Spell-Checking a Document or Range Spell-Checking a Word Checking Grammar Word's Spelling and Grammar Options New Objects in Word 2000 Chapter 7—Manipulating Excel 2000 with VBA Excel's Macro Options Assigning a Shortcut Key Recording with Relative References Using VBA to Read and Set Excel's Program Options The View Tab The Calculation Tab The Edit Tab The General Tab Excel's Application Object Accessing Worksheet Functions Other Properties of the Application Object Methods of Excel's Application Object Some Event-Like Methods Running a Procedure When the User Presses a Key Running a Procedure at a Specific Time Running a Procedure When the User Selects Repeat or Undo Manipulating Workbook Objects Specifying a Workbook Object Opening a Workbook Creating a New Workbook Workbook Object Properties Workbook Object Methods Workbook Object Events Dealing with Worksheet Objects Specifying a Worksheet Object Creating a New Worksheet Properties of the Worksheet Object Methods of the Worksheet Object Worksheet Object Events Working with Range Objects Returning a Range Object Using the Range Method Using the Cells Method Returning a Row Returning a Column Using the Offset Method Selecting a Cell or Range Defining a Range Name More Range Object Properties More Range Object Methods Working with Add-In Applications Creating an Add-In Application Controlling Add-Ins with VBA New Objects in Excel 2000 Chapter 8—Programming PowerPoint 2000 Presentations PowerPoint's Application Object PowerPoint's Presentation Object Specifying a Presentation Object Opening a Presentation Creating a New Presentation Presentation Object Properties Presentation Object Methods The Juggling Application Working with PowerPoint Slide Objects Specifying a Slide Creating a New Slide Inserting Slides from a File Slide Object Properties The Juggling Application: Creating the Slides Slide Object Methods Dealing with Shape Objects Specifying a Shape Adding Shapes to a Slide Some Shape Object Properties The Juggling Application: Creating the Title Page Some Shape Object Methods The Juggling Application: Creating the Instructions Operating a Slide Show Slide Show Transitions Slide Show Settings Running the Slide Show New Objects in PowerPoint 2000 Chapter 9—VBA and Access 2000 What's New in Access 2000 VBA The Access Object Hierarchy Controlling Access Options More About the Access Application Object Some Application Object Properties Some Application Object Methods The Access Form Object Opening a Form Creating a Form Closing a Form Form Object Properties Form Object Methods The Access Report Object Opening a Report Creating a Report Closing a Report Report Grouping and Sorting The GroupLevel Object Creating Group Levels Properties of the GroupLevel Object The Control Object Using the Handy SysCmd Function Returning the Current State of an Object Returning Information About Access Creating Access Event Handlers Working with Access Modules Adding Code to the Form Module Adding Code to a Module Object Protecting Access Projects Part III—Unleashing VBA User Interface Design Chapter 10—Interacting with the User Programming Sounds Beeping the Speaker Programming PowerPoint Sound Effects Displaying Information to the User Changing the Mouse Pointer in Access Displaying a Message in the Status Bar Status Bar Messages in Word and Excel Programming the Status Bar in Access Displaying a Message Using MsgBox Setting the Style of the Message Getting Return Values from the Message Dialog Box Getting Input from the User Prompting the User for Input Accessing an Application's Built-In Dialog Boxes Chapter 11—Working with Microsoft Forms Understanding Custom Forms and Dialog Boxes Forms and Dialog Boxes in VBA 5.0 Adding a Form to Your Project Sharing Forms Between VBA Applications Importing and Exporting Form Files Dragging Form Objects Between Applications Changing the Form's Design-Time Properties The Appearance Category The Behavior Category The Font Category The Misc Category The Picture Category The Position Category The Scrolling Category Working with Controls Inserting Controls on a Form Selecting Controls Sizing Controls Sizing to the Grid Sizing to the Caption Making Controls the Same Size Moving Controls Aligning to the Grid Aligning Control Edges Centering Controls Arranging Command Buttons Adjusting the Spacing Between Controls Copying Controls Deleting Controls Grouping Controls Ordering Overlapped Controls Setting Control Properties Common Control Properties Setting the Tab Order Adding Controls to the Toolbox Creating User-Defined Controls Adding a Page to the Toolbox Adding User-Defined Controls to the New Page Customizing User-Defined Controls Handling Form Events Types of Form Controls Command Buttons Labels Text Boxes Frames Option Buttons Check Boxes Toggle Buttons List Boxes The ListBox Object The ComboBox Object List Box Techniques Scroll Bars Spin Buttons Tab Strips and MultiPage Controls The TabStrip Control The MultiPage Control Using a Form in a Procedure Displaying the Form Handling Events While the Form Is Displayed Unloading the Form Processing the Form Results Chapter 12—Creating Custom Menus and Toolbars Assigning Macros to Menu Commands First, a Game Plan Creating a New Menu Creating a New Cascade Menu Adding Menu Commands and Assigning Macros Deleting Menus and Menu Commands Creating Custom Toolbars for Your Macros Creating a New Toolbar Adding a Toolbar Button and Assigning a Macro Menus, Toolbars, and VBA 5.0 Understanding Command Bars Specifying a Command Bar Properties of the CommandBars Object Working with Command Bars Creating a New Command Bar Command Bar Properties Deleting a Custom Command Bar Resetting a Built-In Command Bar Working with Command Bar Controls Specifying a Control Adding a Control to a Command Bar The Command Bar Info Utility Control Properties Control Methods Part IV—Unleashing Application Integration Chapter 13—Working with Other Applications Starting Another Application Activating a Running Application Sending Keystrokes to an Application Using Dynamic Data Exchange DDE: The Basics Initiating a Link Between VBA and a Server Application Controlling the Server Application Exchanging Data with the Server Application Receiving Data from the Server Sending Data to the Server Chapter 14—Programming OLE and ActiveX Objects First, Some OLE Fundamentals Compound Documents The Component Object Model Automation ActiveX Controls OLE and the Registry Programmatic Identifier Keys CLSID Keys Programming Linked and Embedded Objects Inserting OLE Objects Understanding the AddOLEObject Method Looking Up an Object's Class Type Inserting a New Embedded Object Inserting an Existing File as an Embedded Object Inserting an Existing File as a Linked Object OLE Objects and Word's InlineShape Object Inserting ActiveX Controls in Word Working with Linked and Embedded Objects Some OLE Object Properties Some OLE Object Methods The Update Method The Verb Method OLE Verbs in Word VBA Chapter 15—Controlling Applications Via Automation Understanding Automation Referencing Object Libraries Late Binding Versus Early Binding Accessing Automation Objects The New Keyword The CreateObject Function The GetObject Function Handling Automation Errors Chapter 16—Rolling Your Own Objects with Class Modules Partway There: Custom Collections Declaring a Custom Collection Adding Objects to a Custom Collection Working with Objects in a Custom Collection Removing Objects from a Custom Collection Understanding Class Modules Setting Up a Class Module Creating the Class Module Defining the Data Members Implementing Class Properties The Property Get Procedure The Property Let Procedure Setting Up the Class Methods Defining the Class Events The Initialize Event The Terminate Event Creating Class Collections Setting Up a Class Module for a Collection Providing Wrappers for Each Member Implementing Class Properties for Objects Using Events with the Office Application Objects Excel Application Object Events Word Application Object Events PowerPoint Application Object Events Part V—Unleashing VBA Database Programming Chapter 17—Using VBA to Work with Excel Lists What Is a List? Working with List Data Setting Up the Application Entering Data Entering Data Using a Custom Form Handling Data Entry with VBA Preparing the Data Entry Form Writing Data to the Database Editing Data Editing Data Using the Custom Form Handling Data Editing with VBA Code Filtering Data Filtering the Customer Database Filtering Data with VBA AutoFiltering a Worksheet Getting a Count of the Database Records Deleting Records Deleting a Customer Database Record Handling Record Deletion with VBA Sorting Data Subtotaling Data Chapter 18—Programming Data Access Objects About Data Access Objects Accessing Data in an External Database Creating a Workspace Connecting to a Jet Database Connecting to a Non-Jet Database Connecting to an ODBC Data Source Working with Recordsets Understanding the Recordset Types Getting Field Information Recordset Object Properties Recordset Object Methods Querying a Recordset Opening a Recordset Using a SQL Expression Opening a Recordset from a QueryDef Object Retrieving Data into Excel Retrieving an Individual Field Value Retrieving One or More Entire Rows Retrieving an Entire Recordset Chapter 19—Advanced Database Programming Maintaining a Jet Database Creating a New Database Compacting a Database Working with Transaction Processing Using the ODBCDirect Workspace Creating an ODBCDirect Workspace Connecting to a Database The OpenDatabase Method for ODBCDirect Workspaces Creating a Connection Object Working with ODBCDirect Recordsets Returning Multiple Recordset Objects Programming Jet Database Security Creating a Secure Database Step 1: Create a New Workgroup File Step 2: Assign a Password to the Admin User Name Step 3: Create an Administrator Account Step 4: Remove the Admin User from the Admins Group Step 5: Convert to a Secure Database Opening a Secure Database Working with the User Object Adding a User Deleting a User User Object Properties and Methods Working with the Group Object Adding a Group Adding a User to a Group Deleting a Group Group Object Properties and Methods Setting Object Permissions A Peek at the Future: ActiveX Data Objects (ADO) Understanding Universal Data Access The Solution: OLE DB and ADO Programming ADO The Connection Object The Recordset Object Part VI—Unleashing VBA Internet and Intranet Programming Chapter 20—Internet and Intranet Programming Topics Using VBA to Read and Set the Office 2000 Web Options Programming Word's E-mail Options Publishing to HTML PublishObject in Excel PublishObject in PowerPoint Controlling Office 2000 Hyperlinks Working with the Hyperlink Object Adding New Hyperlink Objects Hyperlink Object Properties Hyperlink Object Methods The FollowHyperlink Method Building a Custom Web Browser Referencing the WebBrowser Control Displaying Web Pages with the WebBrowser Control Application: The Word Wide Web Browser Navigating with the WebBrowser Control Navigating in the Word Wide Web Browser Controlling Internet Explorer Via Automation Setting References and Object Variables Internet Explorer Properties Chapter 21—HTML Programming: VBScript and the Script Editor The Differences Between VBA and VBScript Attaching Scripts to a Page The <SCRIPT> Tag A Note About VBScript Event Procedures Using the LANGUAGE Attribute with a Control The Scripting Object Hierarchy The Window Object Window Object Properties Window Object Methods Window Object Events The Document Object Document Object Properties Document Object Methods How VBScript Interacts with Forms The Form Object Form Object Properties Submitting a Form Dealing with Form Controls Control Properties Control Methods Control Events Example: A Mortgage Calculator Working with the Script Editor Starting the Script Editor A Tour of the Script Editor Interface Inserting Scripts Chapter 22—E-Mail Programming with Outlook Understanding the NameSpace Object Using the Explorer Object Explorer Object Properties Explorer Object Methods The Inspector Object Inspector Object Properties Inspector Object Methods Working with MAPIFolder Objects Referencing Default Folders Using the Namespace Object's Folders Property Prompting the User for a Folder Some MAPIFolder Properties Some MAPIFolder Methods Understanding Items Types of Items Creating an Item with a Closer Look at MailItem Objects MailItem Object Properties Determining the Sender's E-Mail Address MailItem Object Methods Working with Recipients Working with Attachments Handling Object Events in Outlook VBA Setting Up an Object Event Handler Application Object Events Automation and the Outlook Object Model Getting the NameSpace Object Logging On to a MAPI Session Logging Off a MAPI Session Part VII—Unleashing Advanced VBA Programming Chapter 23—Trapping Program Errors A Basic Error-Trapping Strategy Setting the Trap Coding the Error Handler Resuming Program Execution Disabling the Trap Working with the Err Object Err Object Properties Err Object Methods Trappable VBA Errors Chapter 24—Debugging VBA Procedures A Basic Strategy for Debugging Syntax Errors Compile Errors Runtime Errors Logic Errors Pausing a Procedure Entering Break Mode Entering Break Mode from an Error Dialog Box Entering Break Mode at the Beginning of a Procedure Entering Break Mode by Pressing the Esc Key Setting a Breakpoint Entering Break Mode Using a Stop Statement Exiting Break Mode Stepping Through a Procedure Stepping Into a Procedure Stepping Over a Procedure Stepping Out of a Procedure Monitoring Procedure Values Using the Locals Window Adding a Watch Expression Editing a Watch Expression Deleting a Watch Expression Displaying Data Values Quickly Using the Immediate Window Printing Data in the Immediate Window Running the Print Method from a Procedure Running the Print Method in the Immediate Window Executing Statements in the Immediate Window Debugging Tips Indent Your Code for Readability Turn on Syntax Checking Require Variable Declarations Break Down Complex Procedures Enter VBA Keywords in Lowercase What to Do When a Procedure Refuses to Run Comment Out Problem Statements Break Up Long Statements Use Excel's Range Names Whenever Possible Take Advantage of User-Defined Constants Chapter 25—Programming the Windows API Understanding the Win32 API Declaring Win32 API Procedures Using Constants with API Procedures Using Type Definitions with API Procedures Some Cautionary Notes Win32 API Examples Beeping the Speaker Returning Windows' Folders Returning Disk Drive Information Determining the Number of Bytes Free Getting the Drive Type Getting System Metrics Exiting Windows from VBA A Better Way to Handle DoEvents The Registry and the Win32 API Opening a Registry Key Closing a Registry Key Querying a Registry Value Setting a Registry Value Chapter 26—VBA Tips and Techniques Conditional Compilation in VBA Setting Up Your Application for Conditional Compilation Conditional Compilation for Debugging Conditional Compilation for Win32 and Win16 Digitally Signing a VBA Project Saving Application Settings in the Registry Storing Settings in the Registry Reading Settings from the Registry Deleting Settings from the Registry An Example: Tracking File Usage Reading All the Section Settings Accessing the File System Through VBA Returning File and Folder Information The CurDir Function The Dir Function The FileDateTime Function The FileLen Function The GetAttr Function Manipulating Files and Folders The ChDir Statement The ChDrive Statement The FileCopy Statement The Kill Statement The MkDir Statement The Name Statement The RmDir Statement The SetAttr Statement Low-Level File I/O Opening a Disk File The File Mode The Access Permissions The Lock Permissions The File Handle The Record Length Understanding Sequential File Access Reading Sequential File Data Writing Sequential File Data The Print # Statement The Write # Statement Understanding Random File Access Reading Random Access Records Writing Random Access Records Working with Record Numbers Locking and Unlocking Records Understanding Binary File Access Tips for Faster Procedures Turn Off Screen Updating Hide Your Documents Don't Select Data Unless You Have To In Excel, Don't Recalculate Until You Have To Optimize Your Loops Part VIII—Unleashing VBA Applications Chapter 27—An Excel Check Book Application Using Check Book Entering Account Information Entering Basic Account Data Adding Check Book Categories Deleting Check Book Categories Entering a Starting Balance Recording Checks Recording Withdrawals Recording Deposits Balancing the Check Book How the Check Book Application Works Understanding the Account Info Worksheet The Account Data Area The Reconciliation Data Area The Income Categories Area The Expense Categories Area The Transaction Procedures A Tour of the Transaction Form Recording a Check Recording a Withdrawal Recording a Deposit The Activate Event Handler for the Transaction Form The Form's Event Handlers The Event Handler for Adding a Transaction The Account Information Procedures Examining the Account Information Form Displaying the Account Information Dialog Box The Event Handlers for the Option Buttons Adding a Category Deleting a Category Writing the Bank and Account Data The Reconciliation Procedures The Reconciliation Form Displaying the Reconciliation Dialog Box Setting Up the Reconciliation Screen Recording the Reconciliation Ending the Reconciliation Chapter 28—Making Backups As You Work Using the Backup Application Selecting the Files to Back Up Selecting the Backup Destination Setting the Backup Mode Understanding the Backup Application Backup and the Registry A Tour of the Backup Form Initializing the Form The Source Tab The All Open Documents Option The Unsaved Open Documents Only Option The Active Document Only Option The Selected Open Documents Only Option The Documents List Box The Ignore New, Unsaved Check Box The Destination Tab Back Up To: The BeforeUpdate Event Back Up To: The Click Event The Mode Tab The Manual Option Button The Reminder Option Button The Hour(s) Text Box The Minutes(s) Text Box The Second(s) Text Box The Command Buttons The Back Up Button The Save Settings Button The Cancel Button Backup's Support Procedures The ParseSelectedFiles Procedure The BuildDocList Procedure The DisplaySourceData Procedure The DisplayDestinationData Procedure The CheckBackupStatus Procedure The BackUpSelectedFiles Procedure The ProcessBackupMode Procedure The SaveRegistrySettings Procedure Chapter 29—Access and Outlook: E-Mail Merge What E-Mail Merge Does Creating a Mailing Step 1: Create the Data Source The Contacts Table Creating Queries Working with Individual Recipients Step 2: Create the Message Step 3: Create the Mailing Step 4: Send the Mailing A Tour of the E-Mail Merge Database The Tables The Contacts Table The Mailings Table The Messages Table The Salutations Table The Switchboard Items Table The Queries The Forms The Contacts Form The Messages Form The Select E-Mail Field Dialog Form The Mailings Form The Switchboard Form Under the Hood: The Code The Code in the Messages Form The Load Event The Current Event The Data Source Procedures The Click Events for the Command Buttons The UpdateSubstitutionList Procedure The Select E-Mail Field Dialog Form The Code in the Mailings Form The Load Event The Current Event The Click Event for the Subject Combo Box The UpdateMailingData Procedure The Click Event for the Edit Message Command Button The Click Event for the Send Mailing Command Button The ReplaceFields Function Appendix A—VBA Statements Appendix B—VBA Functions Appendix C—Programming the Windows Scripting Host Understanding the Windows Scripting Host How the Windows Scripting Host Works with Scripts Using WScript to Run Scripts from Windows Using CScript to Run Scripts from DOS Setting Script Properties Creating Scripts The Windows Scripting Host Object Model The WScript Object WScript Object Properties WScript Object Methods Using VBScript to Work with the File System The FileSystemObject Working with Disk Drives Working with Folders Working with Files Reading and Writing Files Using the TextStream Object The WshArguments Collection The WshShell Object Referencing the WshShell Object Displaying Information to the User Running Applications Working with Shortcuts Creating a Shortcut The WshShortcut Object Working with Registry Entries Reading Settings from the Registry Storing Settings in the Registry Deleting Settings from the Registry Working with Environment Variables The WshNetwork Object Referencing the WshNetwork Object WshNetwork Object Properties WshNetwork Object Methods Appendix D—The Windows ANSI Character Set Appendix E—HTML Primer Understanding HTML Tags The Basic Structure of Web Pages Adding a Title Text and Paragraphs Adding Formatting and Headings Setting Up Lists Working with Hyperlinks Inserting Images Setting Up Tables Working with HTML Forms Defining the Form The Submit Button Using a Submit Image Starting Over: The Reset Button Using Text Boxes for Single-Line Text Using Text Areas for Multiline Text Toggling an Option On and Off with Check Boxes Multiple Choice: Option Buttons Selecting from Lists Hidden Controls Return to the VBA for Office 2000 Unleashed home page Copyright © 1995-2008 Paul McFedries and Logophilia Limited