Excel 2007 VBA Programming For Dummies by Jan Karel Pieterse (Revised by), John Walkenbach

Introduction     1
Is This the Right Book?     1
So You Want to Be a Programmer     2
Why Bother?     2
What I Assume about You     3
Obligatory Typographical Conventions Section     4
Check Your Security Settings     4
How This Book Is Organized     6
Introducing VBA     6
How VBA Works with Excel     6
Programming Concepts     6
Communicating with Your Users     6
Putting It All Together     7
The Part of Tens     7
Marginal Icons     7
Get the Sample Files     8
Now What?     8
Introducing VBA     9
What Is VBA?     11
Okay, So What Is VBA?     11
What Can You Do with VBA?     12
Inserting a bunch of text     13
Automating a task you perform frequently     13
Automating repetitive operations     13
Creating a custom command     13
Creating a custom button     14
Developing new worksheet functions     14
Creating complete, macro-driven applications     14
Creating custom add-ins for Excel     14
Advantages and Disadvantages of VBA     14
VBA advantages     15
VBA disadvantages     15
VBA in a Nutshell     16
An Excursion into Versions     18
Jumping Right In     21
First Things First     21
What You'll Be Doing     22
Taking the First Steps     23
Recording the Macro     23
Testing the Macro     25
Examining the Macro     25
Modifying the Macro     28
Saving Workbooks that Contain Macros     29
Understanding Macro Security     29
More about the NameAndTime Macro     31
How VBA Works with Excel     33
Working In the Visual Basic Editor     35
What Is the Visual Basic Editor?     35
Activating the VBE     35
Understanding VBE components     36
Working with the Project Explorer     38
Adding a new VBA module     39
Removing a VBA module     39
Exporting and importing objects     40
Working with a Code Window     40
Minimizing and maximizing windows     40
Creating a module     42
Getting VBA code into a module      42
Entering code directly     43
Using the macro recorder     45
Copying VBA code     47
Customizing the VBA Environment     47
Using the Editor tab     48
Using the Editor Format tab     50
Using the General tab     52
Using the Docking tab     52
Introducing the Excel Object Model     53
Excel Is an Object?     54
Climbing the Object Hierarchy     54
Wrapping Your Mind around Collections     56
Referring to Objects     56
Navigating through the hierarchy     57
Simplifying object references     58
Diving into Object Properties and Methods     59
Object properties     59
Object methods     62
Object events     63
Finding Out More     63
Using VBA's Help system     63
Using the Object Browser     64
VBA Sub and Function Procedures     67
Subs versus Functions     67
Looking at Sub procedures     68
Looking at Function procedures     68
Naming Subs and Functions     69
Executing Sub procedures      69
Executing the Sub procedure directly     71
Executing the procedure from the Macro dialog box     72
Executing a macro by using a shortcut key     72
Executing the procedure from a button or shape     74
Executing the procedure from another procedure     76
Executing Function procedures     76
Calling the function from a Sub procedure     77
Calling a function from a worksheet formula     77
Using the Excel Macro Recorder     79
Is It Live or Is It VBA?     79
Recording Basics     80
Preparing to Record     82
Relative or Absolute?     82
Recording in absolute mode     82
Recording in relative mode     83
What Gets Recorded?     85
Recording Options     86
Macro name     87
Shortcut key     87
Store Macro In     87
Description     87
Is This Thing Efficient?     88
Programming Concepts     91
Essential VBA Language Elements     93
Using Comments in Your VBA Code     93
Using Variables, Constants, and Data Types     95
Understanding variables      95
What are VBA's data types?     97
Declaring and scoping variables     98
Working with constants     103
Working with strings     105
Working with dates     106
Using Assignment Statements     106
Assignment statement examples     107
About that equal sign     107
Other operators     108
Working with Arrays     109
Declaring arrays     109
Multidimensional arrays     110
Dynamic arrays     111
Using Labels     111
Working with Range Objects     113
A Quick Review     113
Other Ways to Refer to a Range     114
The Cells property     115
The Offset property     116
Referring to entire columns and rows     116
Some Useful Range Object Properties     117
The Value property     117
The Text property     118
The Count property     118
The Column and Row properties     118
The Address property     119
The HasFormula property     119
The Font property     120
The Interior property     120
The Formula property     121
The NumberFormat property     121
Some Useful Range Object Methods     122
The Select method     122
The Copy and Paste methods     123
The Clear method     123
The Delete method     124
Using VBA and Worksheet Functions     125
What Is a Function?     125
Using Built-in VBA Functions     126
VBA function examples     126
VBA functions that do more than return a value     128
Discovering VBA functions     129
Using Worksheet Functions in VBA     132
Worksheet function examples     133
Entering worksheet functions     136
More about Using Worksheet Functions     136
Using Custom Functions     137
Controlling Program Flow and Making Decisions     139
Going with the Flow, Dude     139
The GoTo Statement     140
Decisions, decisions     141
The If-Then structure     141
The Select Case structure     146
Knocking Your Code for a Loop     149
For-Next loops     150
Do-While loop     153
Do-Until loop     154
Looping through a Collection     155
Automatic Procedures and Events     157
Preparing for the Big Event     157
Are events useful?     159
Programming event-handler procedures     160
Where Does the VBA Code Go?     160
Writing an Event-Handler Procedure     161
Introductory Examples     163
The Open event for a workbook     163
The BeforeClose event for a workbook     165
The BeforeSave event for a workbook     165
Examples of Activation Events     166
Activate and deactivate events in a sheet     166
Activate and deactivate events in a workbook     167
Workbook activation events     168
Other Worksheet-Related Events     169
The BeforeDoubleClick event     169
The BeforeRightClick event     169
The Change event     170
Events Not Associated with Objects     172
The OnTime event     172
Keypress events     174
Error-Handling Techniques     177
Types of Errors     177
An Erroneous Example     178
The macro's not quite perfect     179
The macro is still not perfect      180
Is the macro perfect yet?     180
Giving up on perfection     181
Handling Errors Another Way     182
Revisiting the EnterSquareRoot procedure     182
About the On Error statement     183
Handling Errors: The Details     184
Resuming after an error     184
Error handling in a nutshell     186
Knowing when to ignore errors     186
Identifying specific errors     187
An Intentional Error     188
Bug Extermination Techniques     191
Species of Bugs     191
Identifying Bugs     192
Debugging Techniques     193
Examining your code     193
Using the MsgBox function     194
Inserting Debug.Print statements     195
Using the VBA debugger     196
About the Debugger     196
Setting breakpoints in your code     196
Using the Watch window     199
Using the Locals Window     201
Bug Reduction Tips     201
VBA Programming Examples     203
Working with Ranges     203
Copying a range     204
Copying a variable-sized range      205
Selecting to the end of a row or column     206
Selecting a row or column     207
Moving a range     207
Looping through a range efficiently     208
Prompting for a cell value     209
Determining the selection type     210
Identifying a multiple selection     211
Changing Excel Settings     211
Changing Boolean settings     212
Changing non-Boolean settings     212
Working with Charts     213
Modifying the chart type     214
Looping through the ChartObjects collection     214
Modifying chart properties     215
Applying chart formatting     215
VBA Speed Tips     216
Turning off screen updating     216
Turning off automatic calculation     217
Eliminating those pesky alert messages     218
Simplifying object references     219
Declaring variable types     219
Using the With-End With structure     220
Communicating with Your Users     221
Simple Dialog Boxes     223
Why Create UserForms?     223
The MsgBox Function     224
Displaying a simple message box      225
Getting a response from a message box     225
Customizing message boxes     226
The InputBox Function     229
InputBox syntax     229
An InputBox example     229
The GetOpenFilename Method     231
The syntax     232
A GetOpenFilename example     232
Selecting multiple files     234
The GetSaveAsFilename Method     235
Getting a Folder Name     236
Displaying Excel's Built-in Dialog Boxes     236
UserForm Basics     239
Knowing When to Use a UserForm     239
Creating UserForms: An Overview     240
Working with UserForms     241
Inserting a new UserForm     241
Adding controls to a UserForm     242
Changing properties for a UserForm control     243
Viewing the UserForm Code window     244
Displaying a UserForm     245
Using information from a UserForm     245
A UserForm Example     246
Creating the UserForm     246
Adding the CommandButtons     247
Adding the OptionButtons     248
Adding event-handler procedures     250
Creating a macro to display the dialog box     251
Making the macro available     252
Testing the macro     253
Using UserForm Controls     255
Getting Started with Dialog Box Controls     255
Adding controls     255
Introducing control properties     257
Dialog Box Controls: The Details     259
CheckBox control     259
ComboBox control     260
CommandButton control     261
Frame control     262
Image control     262
Label control     263
ListBox control     264
MultiPage control     265
OptionButton control     266
RefEdit control     267
ScrollBar control     267
SpinButton control     268
TabStrip control     269
TextBox control     269
ToggleButton control     270
Working with Dialog Box Controls     270
Moving and resizing controls     270
Aligning and spacing controls     271
Accommodating keyboard users     272
Testing a UserForm     273
Dialog Box Aesthetics     274
UserForm Techniques and Tricks      275
Using Dialog Boxes     275
A UserForm Example     275
Creating the dialog box     276
Writing code to display the dialog box     278
Making the macro available     279
Trying out your dialog box     279
Adding event-handler procedures     280
Validating the data     282
Now the dialog box works     282
More UserForm Examples     282
A ListBox example     282
Selecting a range     287
Using multiple sets of OptionButtons     288
Using a SpinButton and a TextBox     289
Using a UserForm as a progress indicator     291
Creating a tabbed dialog box     295
Displaying a chart in a dialog box     296
A Dialog Box Checklist     297
Accessing Your Macros Through the User Interface     299
CommandBars and Excel 2007     299
Excel 2007 Ribbon Customization     301
Working with CommandBars     304
Commanding the CommandBars collection     304
Listing all shortcut menus     304
Referring to CommandBars     305
Referring to controls in a CommandBar     306
Properties of CommandBar controls     307
VBA Shortcut Menu Examples     309
Resetting all built-in right-click menus     309
Adding a new item to the Cell shortcut menu     309
Disabling a shortcut menu     311
Creating a Custom Toolbar     312
Putting It Alt Together     315
Creating Worksheet Functions and Living to Tell about It     317
Why Create Custom Functions?     317
Understanding VBA Function Basics     318
Writing Functions     319
Working with Function Arguments     319
Function Examples     320
A function with no argument     320
A function with one argument     320
A function with two arguments     322
A function with a range argument     323
A function with an optional argument     324
A function with an indefinite number of arguments     326
Functions That Return an Array     327
Returning an array of month names     327
Returning a sorted list     328
Using the Insert Function Dialog Box     330
Displaying the function's description     330
Argument descriptions     331
Creating Excel Add-Ins      333
Okay...So What's an Add-In?     333
Why Create Add-Ins?     334
Working with Add-Ins     335
Add-in Basics     336
An Add-in Example     337
Setting up the workbook     337
Testing the workbook     339
Adding descriptive information     340
Protecting the VBA code     341
Creating the add-in     341
Opening the add-in     341
Distributing the add-in     342
Modifying the add-in     342
The Part of Tens     345
Ten VBA Questions (And Answers)     347
The Top Ten Questions about VBA     347
(Almost) Ten Excel Resources     351
The VBA Help System     351
Microsoft Product Support     351
Internet Newsgroups     352
Internet Web Sites     353
Excel Blogs     353
Google     353
Local User Groups     354
My Other Book     354
Index     355