Hjem

Introduction xxxi
Part 1 Recording Macros and Getting Started with VBA 1
Chapter 1 Recording and Running Macros in the Office Applications 3 What Is VBA and What Can You Do with It? 3 Understanding Macro Basics 5 Recording a Macro 6 Running a Macro 23 Recording a Sample Word Macro 24 Recording a Sample Excel Macro 27 Specifying How to Trigger an Existing Macro 29 Deleting a Macro 29 The Bottom Line 31
Chapter 2 Getting Started with the Visual Basic Editor
.33 Opening the Visual Basic Editor 33 Using the Visual Basic Editor''s Main Windows 36 Setting Properties for a Project 48 Customizing the Visual Basic Editor 51 The Bottom Line 64
Chapter 3 Editing Recorded Macros
.67 Testing a Macro in the Visual Basic Editor 68 Editing a Word Macro 73 Editing the Excel Macro 76 Editing a PowerPoint Macro 80 The Bottom Line 87
Chapter 4 Creating Code from Scratch in the Visual Basic Editor
.89 Setting Up the Visual Basic Editor to Create Macros 89 Creating a Procedure for Word 91 Creating a Macro for Excel 96 Creating a Procedure for PowerPoint 101 Creating a Procedure for Access 106 The Bottom Line 107
Part 2 Learning How to Work with VBA 109
Chapter 5 Understanding the Essentials of VBA Syntax 111 Getting Ready 111 Procedures 112 Statements 114 Keywords 117 Expressions 118 Operators 118 Variables 118 Constants 120 Arguments 120 Objects 123 Collections 123 Properties 123 Methods 124 Events 124 The Bottom Line 126
Chapter 6 Working with Variables, Constants, and Enumerations 129 Working with Variables 130 Working with Constants 147 Working with Enumerations 149 The Bottom Line 150
Chapter 7 Using Array Variables 151 What Is an Array? 151 Declaring an Array 153 Storing Values in an Array 155 Multidimensional Arrays 156 Declaring a Dynamic Array 157 Redimensioning an Array 157 Returning Information from an Array 158 Erasing an Array 158 Determining Whether a Variable Is an Array 158 Finding the Bounds of an Array 158 Sorting an Array 159 Searching an Array 163 The Bottom Line 173
Chapter 8 Finding the Objects, Methods, and Properties You Need
.175 What Is an Object? 175 Working with Collections 180 Finding the Objects You Need 182 Using Object Variables to Represent Objects 194 Team Programming and OOP 197 The Bottom Line 199
Part 3 Making Decisions and Using Loops and Functions 201
Chapter 9 Using Built-In Functions 203 What Is a Function? 203 Using Functions 205 Using Functions to Convert Data 208 Using the Asc Function to Return a Character Code 210 Using the Val Function to Extract a Number from the Start of a String 210 Using the Format Function to Format an Expression 213 Using the Chr Function and Constants to Enter Special Characters in a String 218 Using Functions to Manipulate Strings 219 Using the Left, Right, and Mid Functions to Return
Part of a String 221 Using InStr and InStrRev to Find a String within another String 224 Using LTrim, RTrim, and Trim to Remove Spaces from a String 227 Using Len to Check the Length of a String 228 Using StrConv, LCase, and UCase to Change the Case of a String 229 Using the StrComp Function to Compare Apples to Apples 231 Using VBA''s Mathematical Functions 231 Using VBA''s Date and Time Functions 232 Using the DatePart Function to Parse Dates 234 Calculating Time Intervals Using the DateDiff Function 235 Using the DateAdd Function to Add or Subtract Time from a Date 236 Using File-Management Functions 236 Checking Whether a File Exists Using the Dir Function 236 The Bottom Line 238
Chapter 10 Creating Your Own Functions
.241 Components of a Function 242 Creating a Function 244 Examples of Functions for Any VBA-Enabled Office Application 247 Creating a Function for Word 251 Creating a Function for Excel 253 Creating a Function for PowerPoint 255 Creating a Function for Access 257 The Bottom Line 258
Chapter 11 Making Decisions in Your Code 261 How Do You Compare Things in VBA? 262 Testing Multiple Conditions by Using Logical Operators 263 Select Case Blocks 278 The Bottom Line 282
Chapter 12 Using Loops to Repeat Actions 283 When Should You Use a Loop? 283 Understanding the Basics of Loops 284 Using For... Loops for Fixed Repetitions 285 Using Do... Loops for Variable Numbers of Repetitions 295 While...Wend Loops 306 Nesting Loops 307 Avoiding Loops 310 The Bottom Line 310
Part 4 Using Message Boxes, Input Boxes, and Dialog Boxes 313
Chapter 13 Getting User Input with Message Boxes and Input Boxes 315 Opening a Macro 316 Displaying Status-Bar Messages in Word and Excel 317 Message Boxes 319 Input Boxes 331 Forms: When Message Boxes and Input Boxes The Bottom Line 334
Chapter 14 Creating Simple Custom Dialog Boxes 337 When Should You Use a Custom Dialog Box? 337 Creating a Custom Dialog Box 338 Working with Groups of Controls 372 Linking a Form to a Procedure 378 Retrieving the User''s Choices from a Dialog Box 380 Examples of Connecting Forms to Procedures 384 Using an Application''s Built-In Dialog Boxes from VBA 400 The Bottom Line 406
Chapter 15 Creating Complex Forms 409 Creating and Working with Complex Dialog Boxes 410 Using Events to Control Forms 430 The Bottom Line 457
Part 5 Creating Effective Code 459
Chapter 16 Building Modular Code and Using Classes 461 Creating Modular Code 461 Creating and Using Classes 478 The Bottom Line 489
Chapter 17 Debugging Your Code and Handling Errors 491 Principles of Debugging 491 The Different Types of Errors 493 VBA''s Debugging Tools 498 Dealing with Loops 508 Dealing with Runtime Errors 509 Suppressing Alerts 516 Handling User Interrupts in Word, Excel, and Project 517 Documenting Your Code 518 The Bottom Line 520
Chapter 18 Building Well-Behaved Code 521 What Is a Well-Behaved Procedure? 521 Retaining or Restoring the User Environment 522 Leaving the User in the Best Position to Continue Working 523 Keeping the User Informed During the Procedure 524 Making Sure a Procedure Is Running Under Suitable Conditions 532 Cleaning Up After a Procedure 532 The Bottom Line 534
Chapter 19 Exploring VBA''s Security Features 537 Understanding How VBA Implements Security 537 Signing Your Macro Projects with Digital Signatures 541 Choosing a Suitable Level of Security 551 Locking Your Code 556 The Bottom Line 557
Part 6 Programming the Office Applications 559
Chapter 20 Understanding the Word Object Model and Key Objects 561 Examining the Word Object Model 561 Working with the Documents Collection and the Document Object 564 Printing a Document 576 Working with the ActiveDocument Object 578 Working with the Selection Object 579 Creating and Using Ranges 589 Manipulating Options 591 The Bottom Line 594
Chapter 21 Working with Widely Used Objects in Word 597 Using Find and Replace via VBA 597 Working with Headers, Footers, and Page Numbers 603 Working with Sections, Page Setup, Windows, and Views 610 Working with Tables 615 The Bottom Line 628
Chapter 22 Understanding the Excel Object Model and Key Objects 631 Getting an Overview of the Excel Object Model 631 Understanding Excel''s Creatable Objects 632 Managing Workbooks 633 Working with Worksheets 642 Working with the Active Cell or Selection 647 Working with Ranges 650 Setting Options 654 The Bottom Line 656
Chapter 23 Working with Widely Used Objects in Excel 659 Working with Charts 659 Working with Windows Objects 666 Working with Find and Replace 669 Adding Shapes 672 The Bottom Line 672
Chapter 24 Understanding the PowerPoint Object Model and Key Objects 673 Getting an Overview of the PowerPoint Object Model 673 Understanding PowerPoint''s Creatable Objects 674 Working with Presentations 675 Working with Windows and Views 683 Working with Slides 686 Working with Masters 693 The Bottom Line 695
Chapter 25 Working with Shapes and Running Slide Shows 697 Working with Shapes 697 Working with Headers and Footers 711 Setting Up and Running a Slide Show 713 The Bottom Line 717
Chapter 26 Understanding the Outlook Object Model and Key Objects 719 Getting an Overview of the Outlook Object Model 719 Working with the Application Object 721 Understanding General Methods for Working with Outlook Objects 727 Working with Messages 731 Working with Calendar Items 734 Working with Tasks and Task Requests 735 Searching for Items 737 The Bottom