Excel VBA Programming For Dummies

Short of changing the tires on your car, Microsoft Excel can do pretty much anything. And the possibilities are even more endless when you learn to program with Excel Visual Basic for Applications (VBA). Regardless of your familiarity with Excel VBA, Excel VBA Programming For Dummies can enhance your experience with the popular spreadsheet software.

Pretty soon, you'll be doing things you didn't think were possible in Excel, from automating processes to writing your own worksheet functions. You'll learn how to:

• Understand the basic tools and operations of Visual Basic for Applications
• Create custom spreadsheet functions that make life easier for you and the people maintaining your spreadsheets
• Deal with errors and exceptions and eliminate the bugs in your code

Perfect for anyone who's never even heard of Excel VBA, Excel VBA Programming For Dummies is also a fantastic resource for intermediate and advanced Excel users looking for a heads-up on the latest features and newest functionality of this simple yet powerful scripting language.

Dick Kusleika has over 25 years’ experience helping Office users get the most out of Microsoft’s bestselling software. From online forums to blogs, books, and conferences, he delivers sound and straightforward advice to readers of all skill levels.

Introduction 1

About This Book 1

Typographical conventions 2

Macro security 3

Foolish Assumptions 5

Icons Used in This Book 5

Beyond the Book 6

Where to Go from Here 6

Part 1: Starting Excel VBA Programming 7

Chapter 1: Getting to Know VBA 9

Understanding VBA Basics 9

Knowing What VBA Can Do 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

Getting the Most from VBA 12

Knowing what VBA does best 12

Recognizing the disadvantages of using VBA 13

Understanding VBA Concepts 14

Ensuring Excel Compatibility 16

Chapter 2: Building Simple Macros 17

Displaying the Developer Tab 17

Creating a Macro 18

Preparing the Environment 18

Recording a Macro 19

Running the Macro 21

Viewing a Macro in the Visual Basic Editor 22

Modifying the Macro 24

Saving Workbooks That Contain Macros 25

Understanding Macro Security 25

Part 2: Employing VBA with Excel 29

Chapter 3: Working in the Visual Basic Editor 31

Getting to Know the Visual Basic Editor 31

Activating the VBE 32

Exploring VBE components 32

Working with the Project Explorer 34

Adding a new VBA module 35

Working with a Code Pane 36

Minimizing and maximizing windows 36

Looking at the parts of a module 37

Getting VBA code into a module 38

Entering code directly 38

Using the macro recorder 41

Copying VBA code 44

Customizing the VBE 44

Using the Editor tab 44

Using the Editor Format tab 47

Using the General tab 48

Using the Docking tab 48

Chapter 4: Introducing the Excel Object Model 51

Working with the Excel Object Model 51

Understanding the object hierarchy 52

Referring to objects 53

Diving into Object Properties and Methods 57

Setting object properties 58

Taking action with object methods 60

Triggering actions with object events 61

Finding Out More from VBA Resources 62

Using VBA’s Help system 62

Using the Object Browser 63

Automatically listing properties and methods 64

Chapter 5: VBA Sub and Function Procedures 67

Understanding Subs versus Functions 67

Looking at Sub procedures 68

Looking at Function procedures 68

Naming Subs and Functions 69

Executing Sub procedures 70

Executing the Sub procedure directly 72

Executing the procedure from the Macro dialog box 73

Executing a macro by using a shortcut key 74

Executing the procedure from a button or shape 75

Executing the procedure from another procedure 77

Executing the procedure from the Immediate window 77

Executing Function Procedures 78

Calling the function from a Sub procedure 78

Calling a function from the Immediate window 79

Calling a function from a worksheet formula 80

Chapter 6: Using the Excel Macro Recorder 83

Recording Basics 84

Preparing to Record 85

Choosing Between Relative and Absolute Mode 86

Recording in absolute mode 86

Recording in relative mode 87

Watching the Macro Recorder in Action 89

Specifying Recording Options for Your Macro 91

Streamlining Code Generated by the Macro Recorder 92

Part 3: Programming Concepts 97

Chapter 7: Essential VBA Language Elements 99

Using Comments in Your VBA Code 99

Using Variables, Constants, and Data Types 101

Understanding variables 101

What are VBA’s data types? 103

Declaring and scoping variables 103

Working with constants 110

Using premade constants 110

Working with strings 111

Working with dates 112

Using Assignment Statements 113

Assignment statement examples 113

About that equal sign 114

Smooth operators 114

Working with Arrays 116

Declaring arrays 116

Multidimensional arrays 117

Dynamic arrays 117

Using Labels 118

Chapter 8: Working with Range Objects 119

Referring to Range Objects 119

Referring to a Range Using Properties 121

The Cells property 122

The Offset property 122

The Resize property 123

Working with Range Object Properties 124

The Value property 124

The Text property 125

The Count property 126

The Column and Row properties 126

The Address property 126

The HasFormula property 127

The Font property 128

The Interior property 128

The Formula property 130

The NumberFormat property 131

Taking Action with Range Object Methods 131

The Select method 132

The Copy and Paste methods 132

The Clear method 133

The Delete method 133

Chapter 9: Using VBA and Worksheet Functions 135

Understanding Functions 135

Using Built-In VBA Functions 136

Working with dates and times 136

Manipulating strings 138

Identifying objects and data 139

VBA functions that do more than return a value 140

Discovering VBA functions 141

Using Worksheet Functions in VBA 144

Worksheet function examples 144

Entering worksheet functions 147

More about using worksheet functions 148

Using Custom Functions 148

Chapter 10: Controlling Program Flow and Making Decisions 151

Going with the Flow, Dude 151

The GoTo Statement 152

Decisions, Decisions 154

The If-Then structure 154

The Select Case structure 158

Knocking Your Code for a Loop 162

For-Next loops 162

Do-While loop 167

Do-Until loop 168

Using For Each-Next Loops with Collections 168

Chapter 11: Automatic Procedures and Events 171

Preparing for the Big Event 171

Learning when to use event procedures 173

Programming event-handler procedures 174

Knowing Where to Put the Event Code 174

Writing an Event-Handler Procedure 175

Triggering Workbook Events 177

The Open event for a workbook 177

The BeforeClose event for a workbook 179

The BeforeSave event for a workbook 180

Using Activation Events 181

Activate and deactivate events in a sheet 181

Activate and deactivate events in a workbook 182

Workbook activation events 184

Programming Worksheet-Related Events 185

The BeforeDoubleClick event 185

The BeforeRightClick event 185

The Change event 186

Understanding Events Not Associated with Objects 188

The OnTime event 188

Keypress events 190

Chapter 12: Error-Handling Techniques 193

Types of Errors 193

An Erroneous Macro Example 194

Not-quite-perfect macros 195

Perfecting the macro 196

Giving up on perfection 198

Alternate Ways of Handling Errors 198

Revisiting the EnterSquareRoot procedure 198

Trapping errors with the On Error statement 200

Handling Errors: The Details 200

Resuming after an error 200

Error handling in a nutshell 202

An Intentional Error 203

Chapter 13: Bug Extermination Techniques 205

Species of Bugs 205

Identifying Bugs 207

Debugging Techniques 208

Examining your code 208

Using the MsgBox function 208

Inserting Debug.Print statements 210

Using the VBA debugger 210

Using the Debugger’s Tools 211

Setting breakpoints in your code 211

Using the Watches window 214

Using the Locals window 216

Bug Reduction Tips 217

Chapter 14: VBA Programming Examples 219

Working with Ranges 220

Copying a range 220

Copying a variable-size range 221

Selecting to the end of a row or column 223

Selecting a row or column 224

Moving a range 224

Looping through a range efficiently 225

Looping through a range efficiently (Part II) 227

Prompting for a cell value 227

Determining the selection type 228

Identifying a multiple selection 229

Changing Excel Settings 230

Changing Boolean settings 230

Changing non-Boolean settings 231

Working with Charts 231

AddChart versus AddChart2 232

Modifying the chart type 234

Looping through the ChartObjects collection 234

Modifying chart properties 235

Applying chart formatting 235

VBA Speed Tips 237

Turning off screen updating 237

Turning off automatic calculation 238

Eliminating those pesky alert messages 239

Simplifying object references 240

Declaring variable types 241

Using the With-End With structure 242

Part 4: Communicating with Your Users 243

Chapter 15: Simple Dialog Boxes 245

Interacting with the User in VBA 246

Displaying Messages with the MsgBox Function 247

Displaying a simple message box 247

Getting a response from a message box 248

Customizing message boxes 250

Getting Data with an Input Box 252

Understanding the InputBox syntax 253

Using the InputBox function 253

Using the InputBox method 255

Allowing the User to Select a File or Folder 256

Constructing a GetOpenFilename statement 256

Selecting a file with GetOpenFilename 256

Picking a file with GetSaveAsFilename 259

Getting a folder name 259

Displaying Excel’s Built-In Dialog Boxes 260

Chapter 16: UserForm Basics 263

Knowing When to Use a UserForm 263

Creating UserForms: An Overview 265

Working with UserForms 266

Inserting a new UserForm 266

Adding controls to a UserForm 267

Changing properties for a UserForm control 268

Viewing the UserForm Code pane 269

Displaying a UserForm 269

Using information from a UserForm 270

A UserForm Example 271

Creating the UserForm 271

Adding the CommandButtons 272

Adding the OptionButtons 272

Adding event-handler procedures 275

Creating a macro to display the dialog box 277

Making the macro available 277

Testing the macro 278

Chapter 17: Using UserForm Controls 281

Getting Started with Dialog Box Controls 281

Adding controls 282

Introducing control properties 283

Learning Dialog Box Controls Details 285

CheckBox control 285

ComboBox control 286

CommandButton control 287

Frame control 288

Image control 288

Label control 289

ListBox control 289

MultiPage control 291

OptionButton control 292

RefEdit control 292

ScrollBar control 293

SpinButton control 294

TabStrip control 295

TextBox control 295

ToggleButton control 296

Working with Dialog Box Controls 296

Moving and resizing controls 297

Aligning and spacing controls 297

Accommodating keyboard users 297

Testing a UserForm 300

Dialog Box Aesthetics 300

Chapter 18: UserForm Techniques and Tricks 301

Using Dialog Boxes 302

A UserForm Example 302

Creating the dialog box 302

Writing code to display the dialog box 305

Making the macro available 305

Trying out your dialog box 306

Adding event-handler procedures 307

Validating the data 309

Now the dialog box works 309

A ListBox Control Example 309

Filling a ListBox Control 310

Determining the selected item 312

Determining multiple selections 313

Selecting a Range 314

Using Multiple Sets of Option Buttons 316

Using a Spin Button and a Text Box 317

Using a UserForm as a Progress Indicator 319

Creating the progress-indicator dialog box 320

The procedures 321

How this example works 322

Creating a Modeless Tabbed Dialog Box 323

Displaying a Chart in a UserForm 325

A Dialog Box Checklist 326

Chapter 19: Accessing Your Macros through the User Interface 329

Customizing the Ribbon 329

Customizing the Ribbon manually 330

Adding a macro to the Ribbon 332

Customizing the Ribbon with XML 333

Customizing the Excel UI with VBA 337

Adding commands to the Add-ins Ribbon tab 338

Adding a new item to the Cell shortcut menu 339

Adding customizations automatically 340

Understanding shortcut menus and the single document interface 341

Part 5: Putting It All Together 343

Chapter 20: Creating Worksheet Functions 345

Create Custom Functions to Simplify Your Work 345

Understanding VBA Function Basics 347

Writing Functions 347

Working with Function Arguments 348

A function with no argument 349

A function with one argument 349

A function with two arguments 351

A function with a range argument 352

A function with an optional argument 354

Introducing Wrapper Functions 356

The NumberFormat function 356

The ExtractElement function 357

The SayIt function 358

The IsLike function 358

Working with Functions That Return an Array 359

Returning an array of month names 359

Returning a sorted list 360

Using the Insert Function Dialog Box 362

Displaying the function’s description 362

Adding argument descriptions 364

Chapter 21: Creating Excel Add-Ins 365

Add-Ins Defined 365

Reasons to Create Add-Ins 366

Working with Add-Ins 367

Understanding Add-In Basics 368

Looking at an Add-In Example 369

Setting up the workbook 369

Testing the workbook 372

Adding descriptive information 372

Protecting the VBA code 373

Creating the add-in 374

Opening the add-in 374

Distributing the add-in 375

Modifying the add-in 375

Part 6: The Part of Tens 377

Chapter 22: Ten Handy Visual Basic Editor Tips 379

Applying Block Comments 380

Copying Multiple Lines of Code at Once 381

Jumping between Modules and Procedures 382

Teleporting to Your Functions 382

Staying in the Right Procedure 383

Stepping through Your Code 383

Stepping to a Specific Line in Your Code 384

Stopping Your Code at a Predefined Point 385

Seeing the Beginning and End of Variable Values 386

Turning Off Auto Syntax Check 387

Chapter 23: Resources for VBA Help 389

Letting Excel Write Code for You 390

Referencing the Help System 390

Pilfering Code from the Internet 390

Leveraging User Forums 391

Visiting Expert Blogs 392

Mining YouTube for Video Training 393

Attending Live and Online Training Classes 393

Learning from the Microsoft Office Dev Center 393

Dissecting the Other Excel Files in Your Organization 394

Asking Your Local Excel Guru 394

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

Do Declare All Variables 395

Don’t Confuse Passwords with Security 396

Do Clean Up Your Code 396

Don’t Put Everything in One Procedure 397

Do Consider Other Software 397

Don’t Assume That Everyone Enables Macros 397

Do Get in the Habit of Experimenting 398

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

Do Keep Your Users in Mind 399

Don’t Forget about Backups 399

Index 401

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.