Contents

Introduction xvii

What Is a Macro? xviii

What Does VBA Have to Do with Macros? xix

What You Should Know Before Reading This Book xix

What's in the Book xx

This Book's Special Features xx

I GETTING STARTED WITH VBA

1 Creating and Running Recorded Macros 3

Recording a VBA Macro 5

Recording a Word Macro 5

Recording an Excel Macro 7

Running a Recorded Macro 8

Using the Macro Name List 8

Assigning Shortcut Keys to Recorded Word Macros 9

Assigning Shortcut Keys to Recorded Excel Macros 10

Creating a Quick Access Toolbar Button for a Recorded Macro 11

From Here 13

2 Writing Your Own Macros 15

Activating the Ribbon's Developer Tab 16

Displaying the Visual Basic Editor 16

Touring the Visual Basic Editor 17

Creating a New Module 17

Opening an Existing Module 18

Understanding VBA Procedures 19

Creating a Command Macro 19

Writing a Command Macro 20

Running a Command Macro 21

Entering VBA Statements 22

Creating a User-Defined Function 23

Understanding User-Defined Functions 24

Writing a User-Defined Function 25

Using a Function 26

Taking Advantage of IntelliSense 27

List Properties/Methods 27

List Constants 28

Parameter Info 29

Complete Word 30

Shutting Down the Visual Basic Editor 30

From Here 30

3 Understanding Program Variables 33

Declaring Variables 33

Avoiding Variable Errors 35

Variable Data Types 36

Changing the Default Data Type 39

Creating User-Defined Data Types 39

Using Array Variables 40

Dynamic Arrays 41

Multidimensional Arrays 44

Working with Constants 44

Using Built-In Constants 44

Creating User-Defined Constants 45

Storing User Input in a Variable 45

Getting Input Using MsgBox 45

Getting Input Using inputBox 50

From Here 51

4 Building VBA Expressions 53

Understanding Expressions 53

Working with VBA Operators 54

Arithmetic Operators 55

The Concatenation Operator 56

Comparison Operators 56

Logical Operators 57

Understanding Operator Precedence 57

The Order of Precedence 58

Controlling the Order of Precedence 58

Working with Numeric Expressions 60

VBA's Math Functions 60

VBA's Financial Functions 62

Working with String Expressions 63

Working with Logical Expressions 66

The And Operator 67

The Or Operator 67

The Xor Operator 67

The Not Operator 67

Working with Date Expressions 68

From Here 70

5 Working with Objects 71

What Is an Object? 71

The Object Hierarchy 73

Working with Object Properties 73

Setting the Value of a Property 75

Returning the Value of a Property 75

Working with Object Methods 75

Handling Object Events 77

Working with Object Collections 78

Assigning an Object to a Variable 79

The Is Operator 80

Working with Multiple Properties or Methods 80

Example: The Application Object 81

Displaying a Message in the Status Bar 82

Changing the Title Bar Caption 82

Working with the Application Window 82

Accessing an Application's Built-In Dialog Boxes 83

Checking Spelling 87

Example: The Window Object 88

Specifying a Window Object 88

Opening a New Window 88

Activating a Window 88

From Here 89

6 Controlling Your VBA Code 91

Code That Makes Decisions 91

Using If...Then to Make True/False Decisions 92

Using If...Then...Else to Handle a False Result 93

Making Multiple Decisions 94

Using the And and Or Operators 95

Using Multiple If...Then...Else Statements 95

Using the Select Case Statement 97

Functions That Make Decisions 101

The IIf Function 101

The Choose Function 102

The Switch Function 103

Code That Loops 104

Using Do...Loop Structures 105

Using For...Next Loops 106

Using For Each...Next Loops 109

Using Exit For or Exit Do to Exit a Loop 110

Indenting for Readability 111

From Here 111

II PUTTING VBA TO WORK

7 Programming Word 115

Working with Documents 115

Specifying a Document Object 115

Opening a Document 116

The RecentFiles Object 116

Creating a New Document 118

Saving a Document 118

Closing a Document 121

Closing All Open Documents 121

Example: Making Document Backups 122

Working with Text 124

Working with the Range Object 125

The Range Method 125

The Range Property 125

Reading and Changing Range Text 126

Formatting Text 126

Inserting Text 127

Deleting Text 128

Using the Selection Object 129

Checking the Selection Type 129

Moving the Insertion Point 130

Extending the Selection 131

Collapsing the Selection 132

Using the Words Object 132

Working with the Sentences Object 133

Displaying Sentence Word Counts 134

Programming the Paragraph Object 136

From Here 138

8 Programming Excel 139

Excel's Application Object 139

Accessing Worksheet Functions 139

Recalculating Workbooks 140

Converting a String into an Object 140

Pausing a Running Macro 141

Some Event-Like Methods 141

Manipulating Workbook Objects 146

Specifying a Workbook Object 146

Opening a Workbook 146

Creating a New Workbook 147

Specifying the Number of Sheets in a New Workbook 147

Saving Every Open Workbook 148

Closing a Workbook 150

Dealing with Worksheet Objects 150

Specifying a Worksheet Object 150

Creating a New Worksheet 151

Properties of the Worksheet Object 151

Methods of the Worksheet Object 152

Working with Range Objects 153

Returning a Range Object 153

Selecting a Cell or Range 158

Defining a Range Name 162

Inserting Data into a Range 162

Returning Data About a Range 163

Resizing a Range 163

From Here 164

9 Programming PowerPoint 165

PowerPoint's Presentation Object 165

Specifying a Presentation Object 165

Opening a Presentation 166

Creating a New Presentation 166

Presentation Object Properties 166

Presentation Object Methods 167

The Juggling Application 168

Working with PowerPoint Slide Objects 170

Specifying a Slide 170

Creating a New Slide 171

Inserting Slides from a File 172

Slide Object Properties 172

The Juggling Application: Creating the Slides 173

Slide Object Methods 174

Dealing with Shape Objects 174

Specifying a Shape 174

Adding Shapes to a Slide 175

Some Shape Object Properties 178

The Juggling Application: Creating the Title Slide 180

Some Shape Object Methods 181

The Juggling Application: Creating the Instructions 183

Operating a Slide Show 186

Slide Show Transitions 186

Slide Show Settings 187

Running the Slide Show 187

From Here 188

10 Programming Access Databases 189

Getting Ready: Two Steps Before You Begin 190

Step One: Create a Reference 190

Step Two: Create a Data Source 191

Working with Database Records: Opening a Recordset 192

Opening a Recordset Using a Table 193

Opening a Recordset: the Full Open Method Syntax 194

Opening a Recordset Using a select String 196

Working with a Recordset 198

Getting at the Recordset Data 198

Navigating Records 199

Finding a Record 202

Editing a Record 203

Adding a New Record 205

Deleting a Record 207

Retrieving Data into Excel 208

Retrieving an Individual Field Value 208

Retrieving One or More Entire Rows 208

Retrieving an Entire Recordset 210

From Here 212

11 Programming Outlook Email 213

Getting Started 213

Working with Outlook Folders 214

Referencing Default Folders 214

Using the Folders Property 214

Prompting the User for a Folder 216

Some MAPIFolder Methods 217

Handling Incoming and Outgoing Messages 217

Incoming: Handling the ItemAdd Event 218

Outgoing: Handling the ItemSend Event 219

Working with Email Messages 220

MailItem Object Properties 220

MailItem Object Methods 221

Example: Creating Advanced Rules for Incoming Messages 223

Example: Canning Spam 224

Sending a Message 225

Creating a New Message 225

Creating a Reply or Forward 225

Specifying the Message Recipients 226

Sending the Message 226

Example: Supplementing a Reminder with an Email Message 227

Working with Attachments 229

Example: Removing Attachments from a Forwarded Message 229

Attaching a File to a Message 230

Programming Outlook from Other Applications 231

Setting Up a Reference to Outlook 231

Getting the NameSpace Object 232

Logging On to an Outlook Session 232

Logging Off an Outlook Session 233

From Here 234

III GETTING THE MOST OUT OF VBA 12 Creating Custom VBA Dialog Boxes 237

Adding a Form to Your Project 238

Changing the Form's Design-Time Properties 239

The Appearance Category 239

The Behavior Category 239

The Font Category 240

The Misc Category 240

The Picture Category 241

The Position Category 241

The Scrolling Category 241

Working with Controls 242

Inserting Controls on a Form 242

Selecting Controls 243

Sizing Controls 244

Moving Controls 244

Copying Controls 244

Deleting Controls 245

Grouping Controls 245

Setting Control Properties 246

Common Control Properties 246

Setting the Tab Order 247

Handling Form Events 248

Types of Form Controls 248

Command Buttons 249

Labels 249

Text Boxes 249

Frames 250

Option Buttons 250

Check Boxes 251

Toggle Buttons 251

List Boxes 251

Scrollbars 253

Spin Buttons 253

Tab Strips and MultiPage Controls 254

Using a Form in a Procedure 258

Displaying the Form 258

Unloading the Form 258

Processing the Form Results 259

From Here 262

13 Customizing the Office 2007 Ribbon 263

Understanding Ribbon Extensibility 263

Extending the Ribbon: An Example 265

Step 1: Create a Macro-Enabled Office Document or Template 265

Step 2: Create a Text File and Add the Custom XML Markup 267

Step 3: Copy the Custom XML Markup File to the Document Package 268

Step 4: Rename and Open the Document 269

More Complexity Means More Power 270

Hiding the Built-In Ribbon 270

Creating Custom Tabs 271

Creating a New Tab 271

Customizing an Existing Tab 272

Creating Custom Groups 273

Creating a New Group 274

Customizing an Existing Group 274

Creating Custom Controls 275

Common Control Attributes 276

Creating a Button 277

Creating a Menu 278

Creating a Split Button 280

Creating a Check Box 281

Creating a Toggle Button 282

Creating a Drop-Down List 284

Creating a Gallery 285

Creating a Combo Box 288

Creating a Dialog Launcher 289

Working with Ribbon Commands at Runtime 289

From Here 297

14 VBA Tips and Techniques 299

Working with Modules 299

Renaming a Module 299

Exporting a Module 300

Importing a Module 300

Removing a Module 301

Configuring Macro Security Settings 301

Setting Up a Trusted Location 301

Setting the Macro Security Level 302

Digitally Signing a VBA Project 304

Saving Application Settings in the Registry 305

Storing Settings in the Registry 306

Reading Settings from the Registry 306

Deleting Settings from the Registry 307

Tracking File Usage 307

Reading All the Section Settings 309

Accessing the File System Through VBA 309

Returning File and Folder Information 310

Manipulating Files and Folders 314

Tips for Faster Procedures 319

Turn Off Screen Updating 319

Hide Your Documents 319

Don't Select Data Unless You Have To 319

In Excel, Don't Recalculate Until You Have To 319

Optimize Your Loops 320

From Here 321

15 Trapping Program Errors 323

A Basic Error-Trapping Strategy 324

Setting the Trap 324

Coding the Error Handler 325

Resuming Program Execution 327

Disabling the Trap 330

Working with the Err Object 330

Err Object Properties 330

Err Object Methods 332

Trappable VBA Errors 333

From Here 336

16 Debugging VBA Procedures 337

A Basic Strategy for Debugging 338

Syntax Errors 338

Compile Errors 338

Runtime Errors 338

Logic Errors 339

Pausing a Procedure 339

Entering Break Mode 340

Exiting Break Mode 342

Stepping Through a Procedure 342

Stepping into a Procedure 343

Stepping Over a Procedure 343

Stepping Out of a Procedure 343

Stepping to the Cursor 343

Monitoring Procedure Values 344

Using the Locals Window 344

Adding a Watch Expression 344

Editing a Watch Expression 346

Deleting a Watch Expression 346

Displaying Data Values Quickly 347

Using the Immediate Window 348

Printing Data in the Immediate Window 348

Executing Statements in the Immediate Window 350

Debugging Tips 350

Indent Your Code for Readability 350

Turn on Syntax Checking 351

Require Variable Declarations 351

Break Down Complex Procedures 351

Enter VBA Keywords in Lowercase 351

Comment Out Problem Statements 351

Break Up Long Statements 351

Use Excel's Range Names Whenever Possible 352

Take Advantage of User-Defined Constants 352

From Here 352

IV APPENDIXES

A VBA Statements 355

B VBA Functions 361

Index 371

0 0

Post a comment