Absolute Beginner's Guide to Vba

by McFedries, Paul

• ISBN13: 9780789730763
• ISBN10: 0789730766
• Edition: 1st
• Format: Paperback
• Copyright: 2004-03-09
• Publisher: Que Publishing

Summary

Absolute Beginner's Guide to VBA shows you how to take full advantage of the Microsoft Office Suite by automating routine Office tasks. No matter which Office application you're using, there are some tasks you perform dozens of times, such as typing and formatting a section of text, running a series of menu commands, or editing a document in a particular way. This book shows you how to accomplish the same results with the click of a button. Even if you've never programmed before, the simple step-by-step approach of Absolute Beginner's Guide to VBA will have you up to speed with VBA in no time! Book jacket.

Author Biography

Paul McFedries is well-known as a teacher of Office, Windows, and VBA. He is the president of Logophilia Limited and has been programming computers for more than 25 years

Table of Contents

Introduction	p. 1
Getting Started with VBA	p. 5
Recording Your First Macro	p. 7
What Is a Macro?	p. 8
What Does VBA Have to Do with Macros?	p. 8
Understanding VBA Procedures	p. 9
Recording a VBA Macro	p. 10
Viewing the Resulting Module	p. 12
Editing a Recorded Macro	p. 13
Writing Your Own Macros	p. 15
Displaying the Visual Basic Editor	p. 16
Touring the Visual Basic Editor	p. 17
Opening an Existing Module	p. 17
Creating a New Module	p. 18
Writing Your Own Command Macro	p. 18
Running a VBA Macro	p. 20
Creating User-Defined Functions with VBA	p. 21
Understanding User-Defined Functions	p. 21
Writing User-Defined Functions	p. 22
Employing User-Defined Functions	p. 23
Working with Procedures	p. 23
The Structure of a Procedure	p. 23
Calling a Procedure	p. 24
Taking Advantage of IntelliSense	p. 26
Working with Comment Blocks	p. 30
Working with Modules	p. 31
Renaming a Module	p. 31
Exporting a Module	p. 31
Importing a Module	p. 32
Removing a Module	p. 32
Shutting Down the Visual Basic Editor	p. 32
Understanding Program Variables	p. 35
Declaring Variables	p. 36
Avoiding Variable Errors	p. 37
Variable Data Types	p. 38
Using Array Variables	p. 40
Working with Constants	p. 41
Using Built-In Constants	p. 41
Creating User-Defined Constants	p. 41
Building VBA Expressions	p. 43
Working with VBA Operators	p. 45
Arithmetic Operators	p. 46
The Concatenation Operator	p. 46
Comparison Operators	p. 47
Logical Operators	p. 47
Understanding Operator Precedence	p. 48
The Order of Precedence	p. 48
Controlling the Order of Precedence	p. 49
Working with Numeric Expressions	p. 51
VBA's Math Functions	p. 51
VBA's Financial Functions	p. 53
Working with String Expressions	p. 54
Working with Logical Expressions	p. 57
The And Operator	p. 58
The Or Operator	p. 58
The Xor Operator	p. 58
The Not Operator	p. 58
Working with Date Expressions	p. 58
Working with Objects	p. 63
The Object Hierarchy	p. 65
Working with Object Properties	p. 65
Setting the Value of a Property	p. 67
Returning the Value of a Property	p. 67
Working with Object Methods	p. 68
Handling Object Events	p. 70
Working with Object Collections	p. 71
Assigning an Object to a Variable	p. 72
The Is Operator	p. 72
Working with Multiple Properties or Methods	p. 73
Example: The Application Object	p. 75
Properties of the Application Object	p. 75
Methods of the Application Object	p. 77
Example: The Window Object	p. 78
Specifying a Window Object	p. 79
Opening a New Window	p. 79
Window Object Properties	p. 79
Window Object Methods	p. 80
Controlling Your VBA Code	p. 83
Code That Makes Decisions	p. 84
Using If...Then to Make True/False Decisions	p. 84
Using If...Then...Else to Handle a False Result	p. 86
Making Multiple Decisions	p. 87
Using the And and Or Operators	p. 87
Using Multiple If...Then...Else Statements	p. 88
Using the Select Case Statement	p. 90
Functions That Make Decisions	p. 95
The IIf Function	p. 95
The Choose Function	p. 96
The Switch Function	p. 97
Code That Loops	p. 99
Using Do...Loop Structures	p. 99
Using For...Next Loops	p. 101
Using For Each...Next Loops	p. 103
Using Exit For or Exit Do to Exit a Loop	p. 105
Putting VBA to Work	p. 109
Programming Word	p. 111
Working with Documents	p. 112
Specifying a Document Object	p. 112
Opening a Document	p. 112
The RecentFiles Object	p. 113
Creating a New Document	p. 114
Saving a Document	p. 115
Closing a Document	p. 117
Example: Making Document Backups	p. 118
More Useful Document Object Methods	p. 121
Working with Text	p. 123
The Range Object	p. 123
The Range Method	p. 124
The Range Property	p. 124
Reading and Changing Range Text	p. 124
Formatting Text	p. 125
Some Useful Range Object Methods	p. 126
The Selection Object	p. 127
Checking the Selection Type	p. 127
Selection Object Methods	p. 127
The Characters Object	p. 131
The Words Object	p. 132
The Sentences Object	p. 134
The Paragraph Object	p. 134
Some Useful Paragraph Object Properties	p. 134
Some Useful Paragraph Object Methods	p. 135
Programming Excel	p. 137
Excel's Application Object	p. 138
Accessing Worksheet Functions	p. 138
Methods of Excel's Application Object	p. 138
Some Event-Like Methods	p. 140
Manipulating Workbook Objects	p. 145
Specifying a Workbook Object	p. 145
Opening a Workbook	p. 146
Creating a New Workbook	p. 146
Workbook Object Properties	p. 147
Workbook Object Methods	p. 147
Dealing with Worksheet Objects	p. 149
Specifying a Worksheet Object	p. 149
Creating a New Worksheet	p. 149
Properties of the Worksheet Object	p. 150
Methods of the Worksheet Object	p. 150
Working with Range Objects	p. 151
Returning a Range Object	p. 152
Using the Offset Method	p. 155
Selecting a Cell or Range	p. 157
Defining a Range Name	p. 157
More Range Object Properties	p. 158
More Range Object Methods	p. 158
Programming PowerPoint	p. 163
PowerPoint's Application Object	p. 164
PowerPoint's Presentation Object	p. 164
Specifying a Presentation Object	p. 164
Opening a Presentation	p. 165
Creating a New Presentation	p. 165
Presentation Object Properties	p. 166
Presentation Object Methods	p. 166
The Juggling Application	p. 167
Working with PowerPoint Slide Objects	p. 170
Specifying a Slide	p. 170
Creating a New Slide	p. 171
Inserting Slides from a File	p. 171
Slide Object Properties	p. 172
The Juggling Application: Creating the Slides	p. 173
Slide Object Methods	p. 174
Dealing with Shape Objects	p. 174
Specifying a Shape	p. 175
Adding Shapes to a Slide	p. 175
Some Shape Object Properties	p. 179
The Juggling Application: Creating the Title Page	p. 181
Some Shape Object Methods	p. 183
The Juggling Application: Creating the Instructions	p. 184
Operating a Slide Show	p. 187
Slide Show Transitions	p. 188
Slide Show Settings	p. 188
Running the Slide Show	p. 189
Programming Access Databases	p. 191
Getting Ready: Two Steps Before You Begin	p. 192
Create a Reference	p. 192
Create a Data Source	p. 193
Working with Database Records: Opening a Recordset	p. 194
Opening a Recordset Using a Table	p. 196
Opening a Recordset Using a SELECT String	p. 198
Working with a Recordset	p. 200
Getting at the Recordset Data	p. 200
Navigating Records	p. 202
Finding a Record	p. 204
Editing a Record	p. 206
Adding a New Record	p. 208
Deleting a Record	p. 210
Retrieving Data into Excel	p. 211
Retrieving an Individual Field Value	p. 212
Retrieving One or More Entire Rows	p. 212
Retrieving an Entire Recordset	p. 214
Programming Outlook Email	p. 219
Working with Outlook Folders	p. 220
Referencing Default Folders	p. 220
Using the Folders Property	p. 221
Prompting the User for a Folder	p. 222
Some MAPIFolder Methods	p. 224
Working with Email Messages	p. 224
MailItem Object Properties	p. 224
MailItem Object Methods	p. 226
Sending a Message	p. 228
Creating a New Message	p. 228
Creating a Reply or Forward	p. 228
Specifying the Message Recipients	p. 229
Sending the Message	p. 230
Working with Attachments	p. 231
Programming Outlook from Other Applications	p. 233
Setting Up a Reference to Outlook	p. 233
Getting the NameSpace Object	p. 234
Logging On to an Outlook Session	p. 234
Logging Off an Outlook Session	p. 235
Getting the Most Out of VBA	p. 239
Interacting with the User	p. 241
Programming Sounds	p. 242
Beeping the Speaker	p. 242
Programming PowerPoint Sound Effects	p. 243
Displaying Information to the User	p. 243
Displaying a Message in the Status Bar	p. 244
Displaying a Message Using MsgBox	p. 250
Getting Input from the User	p. 254
Prompting the User for Input	p. 255
Accessing an Application's Built-In Dialog Boxes	p. 257
Creating Custom VBA Dialog Boxes	p. 263
Adding a Form to Your Project	p. 264
Changing the Form's Design-Time Properties	p. 265
The Appearance Category	p. 266
The Behavior Category	p. 266
The Font Category	p. 266
The Misc Category	p. 267
The Picture Category	p. 267
The Position Category	p. 267
The Scrolling Category	p. 267
Working with Controls	p. 268
Inserting Controls on a Form	p. 268
Selecting Controls	p. 269
Sizing Controls	p. 270
Moving Controls	p. 270
Copying Controls	p. 271
Deleting Controls	p. 271
Grouping Controls	p. 271
Setting Control Properties	p. 272
Common Control Properties	p. 272
Setting the Tab Order	p. 273
Handling Form Events	p. 274
Types of Form Controls	p. 275
Command Buttons	p. 275
Labels	p. 275
Text Boxes	p. 276
Frames	p. 277
Option Buttons	p. 277
Check Boxes	p. 278
Toggle Buttons	p. 278
List Boxes	p. 278
Scrollbars	p. 280
Spin Buttons	p. 280
Tab Strips and MultiPage Controls	p. 281
Using a Form in a Procedure	p. 285
Displaying the Form	p. 285
Unloading the Form	p. 286
Processing the Form Results	p. 287
Creating Custom Menus and Toolbars	p. 291
Assigning Macros to Menu Commands	p. 292
First, a Game Plan	p. 292
Creating a New Menu	p. 293
Creating a New Submenu	p. 294
Adding Menu Commands and Assigning Macros	p. 295
Deleting Menus and Menu Commands	p. 296
Creating Custom Toolbars for Your Macros	p. 296
Creating a New Toolbar	p. 296
Adding a Toolbar Button and Assigning a Macro	p. 297
Menus, Toolbars, and VBA	p. 297
Understanding Command Bars	p. 298
Specifying a Command Bar	p. 299
Creating a New Command Bar	p. 301
Command Bar Properties	p. 302
Deleting a Custom Command Bar	p. 305
Resetting a Built-In Command Bar	p. 305
Working with Command Bar Controls	p. 306
Specifying a Control	p. 306
Adding a Control to a Command Bar	p. 308
The Command Bar Info Utility	p. 309
Control Properties	p. 309
Control Methods	p. 313
Debugging VBA Procedures	p. 315
Trapping Program Errors	p. 316
A Basic Error-Trapping Strategy	p. 316
Working with the Err Object	p. 324
Err Object Properties	p. 325
A Basic Strategy for Debugging	p. 327
Syntax Errors	p. 327
Compile Errors	p. 328
Runtime Errors	p. 328
Logic Errors	p. 328
Pausing a Procedure	p. 329
Entering Break Mode	p. 329
Exiting Break Mode	p. 331
Stepping Through a Procedure	p. 332
Stepping into a Procedure	p. 333
Stepping Over a Procedure	p. 333
Stepping Out of a Procedure	p. 333
Stepping to the Cursor	p. 334
Monitoring Procedure Values	p. 334
Using the Locals Window	p. 334
Adding a Watch Expression	p. 334
Editing a Watch Expression	p. 336
Deleting a Watch Expression	p. 337
Displaying Data Values Quickly	p. 337
Using the Immediate Window	p. 338
Printing Data in the Immediate Window	p. 338
Executing Statements in the Immediate Window	p. 340
Debugging Tips	p. 341
Indent Your Code for Readability	p. 341
Turn on Syntax Checking	p. 341
Require Variable Declarations	p. 341
Break Down Complex Procedures	p. 342
Enter VBA Keywords in Lowercase	p. 342
Comment Out Problem Statements	p. 342
Break Up Long Statements	p. 342
Use Excel's Range Names Whenever Possible	p. 342
Take Advantage of User-Defined Constants	p. 342
Appendixes	p. 345
VBA Statements	p. 347
VBA Functions	p. 353
Table of Contents provided by Ingram. All Rights Reserved.