Excel VBA Programming For Dummies

Michael Alexander is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis with Microsoft Access and Microsoft Excel. He has been named a Microsoft MVP for his ongoing contributions to the Excel community. You can find Mike at www.datapigtechnologies.com.

Introduction 1

About This Book 1

Obligatory Typographical Conventions Section 2

Check Your Security Settings 3

Foolish Assumptions 4

Icons Used in This Book 5

Sample Files Online 5

Where to Go from Here 6

Part 1: Getting Started with Excel VBA Programming 7

Chapter 1: What Is VBA? 9

Okay, So What Is VBA? 9

What Can You Do with VBA? 10

Inserting a bunch of text 11

Automating a task you perform frequently 11

Automating repetitive operations 11

Creating a custom command 11

Creating a custom button 12

Developing new worksheet functions 12

Creating custom add-ins for Excel 12

Advantages and Disadvantages of VBA 12

VBA advantages 12

VBA disadvantages 13

VBA in a Nutshell 13

Excel Compatibility 16

Chapter 2: Jumping Right In 17

First Things First 17

What You’ll Be Doing 18

Taking the First Steps 18

Recording the Macro 19

Testing the Macro 21

Examining the Macro 21

Modifying the Macro 23

Saving Workbooks That Contain Macros 24

Understanding Macro Security 24

Revealing More about the NameAndTime Macro 26

Part 2: How VBA Works with Excel 29

Chapter 3: Working in the Visual Basic Editor 31

What Is the Visual Basic Editor? 31

Activating the VBE 32

Understanding VBE components 32

Working with the Project Window 34

Adding a new VBA module 35

Removing a VBA module 36

Exporting and importing objects 36

Working with a Code Window 37

Minimizing and maximizing windows 37

Creating a module 38

Getting VBA code into a module 39

Entering code directly 39

Using the macro recorder 42

Copying VBA code 45

Customizing the VBA Environment 45

Using the Editor tab 45

Using the Editor Format tab 48

Using the General tab 49

Using the Docking tab 50

Chapter 4: Introducing the Excel Object Model 51

Excel Is an Object? 52

Climbing Down the Object Hierarchy 52

Wrapping Your Mind around Collections 53

Referring to Objects 54

Navigating through the hierarchy 55

Simplifying object references 56

Diving into Object Properties and Methods 56

Object properties 58

Object methods 59

Object events 61

Finding Out More 61

Using VBA’s Help system 61

Using the Object Browser 62

Automatically listing properties and methods 63

Chapter 5: VBA Sub and Function Procedures 65

Understanding Subs versus Functions 65

Looking at Sub procedures 66

Looking at Function procedures 66

Naming Subs and Functions 67

Executing Sub procedures 68

Executing the Sub procedure directly 70

Executing the procedure from the Macro dialog box 70

Executing a macro by using a shortcut key 71

Executing the procedure from a button or shape 72

Executing the procedure from another procedure 74

Executing Function procedures 75

Calling the function from a Sub procedure 75

Calling a function from a worksheet formula 76

Chapter 6: Using the Excel Macro Recorder 79

Recording Basics 80

Preparing to Record 81

Relative or Absolute? 82

Recording in absolute mode 82

Recording in relative mode 83

What Gets Recorded? 84

Recording Options 86

Macro name 86

Shortcut key 87

Store Macro In option 87

Description 87

Is This Thing Efficient? 87

Part 3: Programming Concepts 91

Chapter 7: 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 104

Premade constants 105

Working with strings 106

Working with dates 107

Using Assignment Statements 108

Assignment statement examples 108

About that equal sign 108

Smooth operators 109

Working with Arrays 111

Declaring arrays 111

Multidimensional arrays 112

Dynamic arrays 112

Using Labels 113

Chapter 8: Working with Range Objects 115

A Quick Review 115

Other Ways to Refer to a Range 117

The Cells property 117

The Offset property 118

Some Useful Range Object Properties 119

The Value property 120

The Text property 121

The Count property 121

The Column and Row properties 121

The Address property 122

The HasFormula property 122

The Font property 123

The Interior property 124

The Formula property 125

The NumberFormat property 126

Some Useful Range Object Methods 127

The Select method 127

The Copy and Paste methods 127

The Clear method 128

The Delete method 128

Chapter 9: Using VBA and Worksheet Functions 131

What Is a Function? 131

Using Built-In VBA Functions 132

VBA function examples 132

VBA functions that do more than return a value 135

Discovering VBA functions 135

Using Worksheet Functions in VBA 138

Worksheet function examples 139

Entering worksheet functions 141

More about using worksheet functions 142

Using Custom Functions 142

Chapter 10: Controlling Program Flow and Making Decisions 145

Going with the Flow, Dude 145

The GoTo Statement 146

Decisions, Decisions 148

The If-Then structure 148

The Select Case structure 152

Knocking Your Code for a Loop 155

For-Next loops 156

Do-While loop 161

Do-Until loop 161

Using For Each-Next Loops with Collections 161

Chapter 11: Automatic Procedures and Events 165

Preparing for the Big Event 165

Are events useful? 167

Programming event-handler procedures 168

Where Does the VBA Code Go? 168

Writing an Event-Handler Procedure 169

Introductory Examples 171

The Open event for a workbook 171

The BeforeClose event for a workbook 173

The BeforeSave event for a workbook 174

Examples of Activation Events 175

Activate and deactivate events in a sheet 175

Activate and deactivate events in a workbook 176

Workbook activation events 178

Other Worksheet-Related Events 178

The BeforeDoubleClick event 178

The BeforeRightClick event 179

The Change event 179

Events Not Associated with Objects 181

The OnTime event 182

Keypress events 183

Chapter 12: Error-Handling Techniques 187

Types of Errors 187

An Erroneous Example 188

The macro’s not quite perfect 189

The macro is still not perfect 190

Is the macro perfect yet? 191

Giving up on perfection 192

Handling Errors Another Way 192

Revisiting the EnterSquareRoot procedure 192

About the On Error statement 194

Handling Errors: The Details 194

Resuming after an error 194

Error handling in a nutshell 196

Knowing when to ignore errors 197

Identifying specific errors 197

An Intentional Error 199

Chapter 13: Bug Extermination Techniques 201

Species of Bugs 201

Identifying Bugs 202

Debugging Techniques 203

Examining your code 204

Using the MsgBox function 204

Inserting Debug.Print statements 206

Using the VBA debugger 206

About the Debugger 207

Setting breakpoints in your code 207

Using the Watches window 210

Using the Locals window 211

Bug Reduction Tips 212

Chapter 14: VBA Programming Examples 213

Working with Ranges 214

Copying a range 214

Copying a variable-size range 215

Selecting to the end of a row or column 217

Selecting a row or column 218

Moving a range 218

Looping through a range efficiently 219

Looping through a range efficiently (Part II) 220

Prompting for a cell value 221

Determining the selection type 222

Identifying a multiple selection 223

Changing Excel Settings 223

Changing Boolean settings 224

Changing non-Boolean settings 225

Working with Charts 225

AddChart versus AddChart2 226

Modifying the chart type 228

Looping through the ChartObjects collection 228

Modifying chart properties 229

Applying chart formatting 229

VBA Speed Tips 231

Turning off screen updating 231

Turning off automatic calculation 232

Eliminating those pesky alert messages 233

Simplifying object references 233

Declaring variable types 234

Using the With-End With structure 235

Part 4: Communicating with Your Users 237

Chapter 15: Simple Dialog Boxes 239

UserForm Alternatives 239

The MsgBox Function 240

Displaying a simple message box 241

Getting a response from a message box 241

Customizing message boxes 243

The InputBox Function 246

InputBox syntax 246

An InputBox example 247

Another type of InputBox 248

The GetOpenFilename Method 249

The syntax for the GetOpenFilename method 250

A GetOpenFilename example 250

The GetSaveAsFilename Method 252

Getting a Folder Name 253

Displaying Excel’s Built-in Dialog Boxes 254

Chapter 16: UserForm Basics 257

Knowing When to Use a UserForm 257

Creating UserForms: An Overview 258

Working with UserForms 259

Inserting a new UserForm 259

Adding controls to a UserForm 260

Changing properties for a UserForm control 261

Viewing the UserForm Code window 262

Displaying a UserForm 263

Using information from a UserForm 263

A UserForm Example 264

Creating the UserForm 264

Adding the CommandButtons 265

Adding the OptionButtons 266

Adding event-handler procedures 268

Creating a macro to display the dialog box 270

Making the macro available 270

Testing the macro 272

Chapter 17: Using UserForm Controls 275

Getting Started with Dialog Box Controls 275

Adding controls 276

Introducing control properties 276

Dialog Box Controls: The Details 279

CheckBox control 279

ComboBox control 280

CommandButton control 280

Frame control 281

Image control 281

Label control 282

ListBox control 283

MultiPage control 284

OptionButton control 285

RefEdit control 286

ScrollBar control 286

SpinButton control 287

TabStrip control 288

TextBox control 288

ToggleButton control 289

Working with Dialog Box Controls 289

Moving and resizing controls 290

Aligning and spacing controls 290

Accommodating keyboard users 291

Testing a UserForm 292

Dialog Box Aesthetics 293

Chapter 18: UserForm Techniques and Tricks 295

Using Dialog Boxes 296

A UserForm Example 296

Creating the dialog box 296

Writing code to display the dialog box 299

Making the macro available 299

Trying out your dialog box 300

Adding event-handler procedures 300

Validating the data 302

Now the dialog box works 303

A ListBox Example 303

Filling a ListBox 304

Determining the selected item 305

Determining multiple selections 306

Selecting a Range 308

Using Multiple Sets of OptionButtons 310

Using a SpinButton and a TextBox 311

Using a UserForm as a Progress Indicator 312

Creating the progress-indicator dialog box 313

The procedures 314

How this example works 316

Creating a Modeless Tabbed Dialog Box 316

Displaying a Chart in a UserForm 318

A Dialog Box Checklist 319

Chapter 19: Accessing Your Macros through the User Interface 321

Customizing the Ribbon 321

Customizing the Ribbon manually 322

Adding a macro to the Ribbon 324

Customizing the Ribbon with XML 325

Customizing Shortcut Menus 329

Adding a new item to the Cell shortcut menu 330

What’s different since Excel 2007? 332

Part 5: Putting It All Together 333

Chapter 20: Creating Worksheet Functions — and Living to Tell about It 335

Why Create Custom Functions? 336

Understanding VBA Function Basics 337

Writing Functions 338

Working with Function Arguments 338

A function with no argument 339

A function with one argument 339

A function with two arguments 341

A function with a range argument 342

A function with an optional argument 344

Introducing Wrapper Functions 346

The NumberFormat function 346

The ExtractElement function 347

The SayIt function 348

The IsLike function 348

Working with Functions That Return an Array 348

Returning an array of month names 349

Returning a sorted list 349

Using the Insert Function Dialog Box 351

Displaying the function’s description 351

Adding argument descriptions 353

Chapter 21: Creating Excel Add-Ins 355

Okay So What’s an Add-In? 355

Why Create Add-Ins? 356

Working with Add-Ins 357

Understanding Add-In Basics 358

Looking at an Add-In Example 359

Setting up the workbook 359

Testing the workbook 362

Adding descriptive information 362

Protecting the VBA code 363

Creating the add-in 363

Opening the add-in 364

Distributing the add-in 364

Modifying the add-in 365

Part 6: The Part of Tens 367

Chapter 22: Ten Handy Visual Basic Editor Tips 369

Applying Block Comments 370

Copying Multiple Lines of Code at Once 371

Jumping between Modules and Procedures 371

Teleporting to Your Functions 371

Staying in the Right Procedure 372

Stepping Through Your Code 372

Stepping to a Specific Line in Your Code 373

Stopping Your Code at a Predefined Point 374

Seeing the Beginning and End of Variable Values 375

Turning Off Auto Syntax Check 375

Chapter 23: Resources for VBA Help 377

Letting Excel Write Code for You 378

Pilfering Code from the Internet 379

Leveraging User Forums 379

Visiting Expert Blogs 380

Mining YouTube for Video Training 381

Attending Live and Online Training Classes 381

Learning from the Microsoft Office Dev Center 382

Dissecting the Other Excel Files in Your Organization 382

Asking Your Local Excel Guru 382

Chapter 24: Ten VBA Do’s and Don’ts 383

Do Declare All Variables 383

Don’t Confuse Passwords with Security 384

Do Clean Up Your Code 384

Don’t Put Everything in One Procedure 385

Do Consider Other Software 385

Don’t Assume That Everyone Enables Macros 385

Do Get in the Habit of Experimenting 386

Don’t Assume That Your Code Will Work with Other Excel Versions 386

Do Keep Your Users in Mind 386

Don’t Forget About Backups 387

Index 389