Table of Contents

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

Part I: Introducing VBA 6

Part II: How VBA Works with Excel 6

Part III: Programming Concepts 6

Part IV: Communicating with Your Users 6

Part V: Putting It All Together 7

Part VI: The Part of Tens 7

Marginal Icons 7

Get the Sample Files 8

Now What? 8

Part 1: Introducing VBA 9

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

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

Part II: How VBA Works with Excel 33

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

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

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

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

Part HI: 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 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

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

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

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

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

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

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

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

Part IV: Communicating with Your Users 221

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

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

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

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

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

Part V: Putting It All Together 315

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

Chapter 21: Creating Excel Add-Ins 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

Part VI: The Part of Tens 345

Chapter 22: Ten VBA Questions (And Answers) 347

The Top Ten Questions about VBA 347

Chapter 23: (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

0 0

Post a comment