Table of Contents

Introduction 1

Is This the Right Book? 1

So You Want to Be a Programmer 2

Why Bother? 3

What I Assume about You 3

Obligatory Typographical Conventions Section 4

Check Your Security Settings 5

How This Book Is Organized 5

Part I: Introducing VBA 6

Part II: How VBA Works with Excel 6

Part III: Programming Concepts 6

Part IV: Developing Custom Dialog Boxes 6

Part V: Creating Custom Toolbars and Menus 6

Part VI: Putting It All Together 6

Part VII: 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 text string 13

Automating a task you perform frequently 13

Automating repetitive operations 13

Creating a custom command 13

Creating a custom toolbar button 13

Creating a custom menu command 14

Creating a simplified front end 14

Developing new worksheet functions 14

Creating complete, macro-driven applications 14

Creating custom add-ins for Excel 14

Advantages and Disadvantages of VBA 15

VBA advantages 15

VBA disadvantages 15

VBA in a Nutshell 16

An Excursion into Versions 18

Chapter 2: Jumping Right In 21

What You'll Be Doing 21

Taking the First Steps 22

Recording the Macro 23

Testing the Macro 24

Examining the Macro 25

Modifying the Macro 28

More about the ConvertFormulas Macro 29

Part II: How VBA Works with Excel 31

Chapter 3: Introducing the Visual Basic Editor 33

What Is the Visual Basic Editor? 33

Activating the VBE 33

Understanding VBE components 34

Working with the Project Explorer 36

Adding a new VBA module 36

Removing a VBA module 37

Exporting and importing objects 37

Working with a Code Window 38

Minimizing and maximizing windows 38

Creating a module 39

Getting VBA code into a module 39

Entering code directly 40

Using the macro recorder 42

Copying VBA code 44

Customizing the VBA Environment 44

Using the Editor tab 45

Using the Editor Format tab 47

Using the General tab 48

Using the Docking tab 48

Chapter 4: Introducing the Excel Object Model 51

Excel Is an Object? 52

Climbing 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 60

Finding Out More 60

Using VBA's Help system 60

Using the Object Browser 61

Chapter 5: VBA Sub and Function Procedures 63

Subs versus Functions 63

Looking at Sub procedures 64

Looking at Function procedures 64

Naming Subs and Functions 65

Executing Sub Procedures 65

Executing the Sub procedure directly 67

Executing the procedure from the Macro dialog box 68

Executing a macro using a shortcut key 68

Executing the procedure from a button or shape 70

Executing the procedure from another procedure 71

Executing Function Procedures 72

Calling the function from a Sub procedure 72

Calling a function from a worksheet formula 73

Chapter 6: Using the Excel Macro Recorder 75

Is It Live or Is It VBA? 75

Recording Basics 76

Preparing to Record 78

Relative or Absolute? 78

Recording in absolute mode 78

Recording in relative mode 79

What Gets Recorded? 81

Recording Options 82

Macro name 83

Shortcut key 83

Store Macro In 83

Description 83

Is This Thing Efficient? 84

Part HI: Programming Concepts 87

Chapter 7: Essential VBA Language Elements 89

Using Comments in Your VBA Code 89

Using Variables, Constants, and Data Types 91

Understanding variables 91

What are VBA's data types? 92

Declaring and scoping variables 93

Working with constants 98

Working with strings 100

Working with dates 100

Using Assignment Statements 101

Assignment statement examples 102

About that equal sign 102

Other operators 102

Working with Arrays 104

Declaring arrays 104

Multidimensional arrays 105

Dynamic Arrays 105

Using Labels 106

Chapter 8: Working with Range Objects 107

A Quick Review 107

Other Ways to Refer to a Range 108

The Cells property 109

The Offset property 110

Referring to entire columns and rows 110

Some Useful Range Object Properties 111

The Value property 111

The Text property 112

The Count property 112

The Column and Row properties 112

The Address property 113

The HasFormula property 113

The Font property 114

The Interior property 114

The Formula property 115

The NumberFormat property 115

Some Useful Range Object Methods 116

The Select method 116

The Copy and Paste methods 116

The Clear method 117

The Delete method 117

Chapter 9: Using VBA and Worksheet Functions 119

What Is a Function? 119

Using VBA Functions 120

VBA function examples 120

VBA functions that do more than return a value 122

Discovering VBA functions 123

Using Worksheet Functions in VBA 126

Worksheet function examples 127

Entering worksheet functions 129

More about Using Worksheet Functions 130

Using Custom Functions 131

Chapter 10: Controlling Program Flow and Making Decisions 133

Going with the Flow, Dude 133

The GoTo Statement 134

Decisions, decisions 135

The If-Then structure 135

The Select Case structure 140

Knocking Your Code for a Loop 143

For-Next loops 144

Do-While loop 147

Do-Until loop 148

Looping through a Collection 149

Chapter 11: Automatic Procedures and Events 151

Preparing for the Big Event 151

Are events useful? 154

Programming event-handler procedures 154

Where Does the VBA Code Go? 155

Writing an Event-Handler Procedure 156

Introductory Examples 157

The Open event for a workbook 157

The BeforeClose event for a workbook 159

The BeforeSave event for a workbook 160

Examples of Activation Events 161

Activate and Deactivate events in a sheet 161

Activate and Deactivate events in a workbook 161

Workbook activation events 162

Other Worksheet-Related Events 163

The BeforeDoubleClick event 163

The BeforeRightClick event 163

The Change event 164

Events Not Associated with Objects 166

The OnTime event 167

Keypress events 168

Chapter 12: Error-Handling Techniques 171

Types of Errors 171

An Erroneous Example 172

The macro's not quite perfect 172

The macro is still not perfect 174

Is the macro perfect yet? 174

Giving up on perfection 176

Handling Errors Another Way 176

Revisiting the EnterSquareRoot procedure 176

About the On Error statement 177

Handling Errors: The Details 178

Resuming after an error 178

Error handling in a nutshell 180

Knowing when to ignore errors 180

Identifying specific errors 181

An Intentional Error 182

Chapter 13: Bug Extermination Techniques 185

Species of Bugs 185

Identifying Bugs 186

Debugging Techniques 187

Examining your code 187

Using the MsgBox function 187

Inserting Debug.Print statements 189

Using the VBA debugger 189

About the Debugger 189

Setting breakpoints in your code 189

Using the Watch window 192

Bug Reduction Tips 194

Chapter 14: VBA Programming Examples 195

Working with Ranges 195

Copying a range 196

Copying a variable-sized range 197

Selecting to the end of a row or column 198

Selecting a row or column 199

Moving a range 199

Looping through a range efficiently 200

Prompting for a cell value 201

Determining the selection type 202

Identifying a multiple selection 203

Changing Excel Settings 203

Changing Boolean settings 204

Changing non-Boolean settings 204

Working with Charts 205

Modifying the chart type 205

Looping through the ChartObjects collection 206

Modifying properties 206

Applying chart formatting 207

VBA Speed Tips 207

Turning off screen updating 208

Turning off automatic calculation 208

Eliminating those pesky alert messages 209

Simplifying object references 209

Declaring variable types 210

Using the With-End With structure 211

Part IV: Developing Custom Dialog Boxes 213

Chapter 15: Custom Dialog Box Alternatives 215

Why Create Dialog Boxes? 215

The MsgBox Function 216

Displaying a simple message box 216

Getting a response from a message box 217

Customizing message boxes 218

The InputBox Function 221

InputBox syntax 221

An InputBox example 221

The GetOpenFilename Method 223

The syntax 223

A GetOpenFilename example 224

Selecting multiple files 226

The GetSaveAsFilename Method 227

Displaying Excel's Built-in Dialog Boxes 228

Chapter 16: Custom Dialog Box Basics 231

Knowing When to Use a Custom Dialog Box

(Also Known as UserForm) 231

Creating Custom Dialog Boxes: An Overview 232

Working with UserForms 233

Inserting a new UserForm 233

Adding controls to a UserForm 234

Changing properties for a UserForm control 235

Viewing the UserForm Code window 236

Displaying a custom dialog box 237

Using information from a custom dialog box 237

A Custom Dialog Box Example 238

Creating the custom dialog box 238

Adding the CommandButtons 238

Adding the OptionButtons 239

Adding event-handler procedures 241

Creating a macro to display the dialog box 243

Making the macro available 243

Testing the macro 244

Chapter 17: Using Dialog Box Controls 247

Getting Started with Dialog Box Controls 247

Adding controls 247

Introducing control properties 248

Dialog Box Controls — the Details 250

CheckBox control 251

ComboBox control 252

CommandButton control 253

Frame control 253

Image control 254

Label control 254

ListBox control 255

MultiPage control 256

OptionButton control 256

RefEdit control 257

ScrollBar control 258

SpinButton control 258

TabStrip control 259

TextBox control 259

ToggleButton control 260

Working with Dialog Box Controls 260

Moving and resizing controls 261

Aligning and spacing controls 261

Accommodating keyboard users 262

Testing a UserForm 263

Dialog Box Aesthetics 264

Chapter 18: UserForm Techniques and Tricks 265

Using Dialog Boxes 265

A UserForm Example 265

Creating the dialog box 266

Writing code to display the dialog box 268

Making the macro available 268

Trying out your dialog box 269

Adding event-handler procedures 269

Validating the data 271

Now the dialog box works 271

More UserForm Examples 272

A ListBox example 272

Selecting a range 276

Using multiple sets of OptionButtons 278

Using a SpinButton and a TextBox 278

Using a UserForm as a progress indicator 280

Creating a tabbed dialog box 283

Displaying a chart in a dialog box 284

A Dialog Box Checklist 286

Part V: Creating Custom Toolbars and Menus 287

Chapter 19: Customizing the Excel Toolbars 289

Introducing CommandBars 289

Customizing Toolbars 289

Working with Toolbars 291

The Toolbars tab 292

The Commands tab 294

The Options tab 294

Adding and Removing Toolbar Controls 295

Moving and copying controls 295

Inserting a new control 295

Using other toolbar button operations 296

Distributing Toolbars 297

Using VBA to Manipulate Toolbars 298

Commanding the CommandBars collection 299

Listing all CommandBar objects 299

Referring to CommandBars 300

Referring to controls in a CommandBar 300

Properties of CommandBar controls 301

VBA Examples 302

Resetting all built-in toolbars 302

Displaying a toolbar when a worksheet is activated 302

Ensuring that an attached toolbar is displayed 303

Hiding and restoring toolbars 304

Chapter 20: When the Normal Excel Menus Aren't Good Enough . . .307

Defining Menu Lingo 307

How Excel Handles Menus 308

Customizing Menus Directly 309

Looking Out for the CommandBar Object 310

Referring to CommandBars 310

Referring to Controls in a CommandBar 310

Properties of CommandBar Controls 312

Placing your menu code 313

Would You Like to See Our Menu Examples? 313

Creating a menu 313

Adding a menu item 315

Deleting a menu 316

Deleting a menu item 316

Changing menu captions 317

Adding a menu item to the Tools menu 318

Working with Shortcut Menus 320

Adding menu items to a shortcut menu 321

Deleting menu items from a shortcut menu 321

Disabling shortcut menus 322

Finding Out More 322

Part VI: Putting It All Together 323

Chapter 21: Creating Worksheet Functions —

and Living to Tell about It 325

Why Create Custom Functions? 325

Understanding VBA Function Basics 326

Writing Functions 327

Working with Function Arguments 327

Function Examples 328

A function with no argument 328

A function with one argument 328

A function with two arguments 330

A function with a range argument 331

A function with an optional argument 332

A function with an indefinite number of arguments 334

Using the Insert Function Dialog Box 335

Displaying the function's description 335

Function categories 336

Argument descriptions 337

Chapter 22: Creating Excel Add-Ins 339

Why Create Add-Ins? 340

Working with Add-Ins 341

Add-in Basics 341

An Add-in Example 343

Setting up the workbook 343

Testing the workbook 346

Adding descriptive information 346

Creating the add-in 347

Opening the add-in 348

Distributing the add-in 349

Modifying the add-in 349

Chapter 23: Interacting with Other Office Applications 351

Starting Another Application from Excel 351

Using the VBA Shell function 351

Activating a Microsoft Office application 352

Using Automation in Excel 352

Getting Word's version number 354

Controlling Word from Excel 355

Controlling Excel from Word 355

Sending Personalized E-mail Using Outlook 358

Working with ADO 360

Part VU: The Part of Tens 363

Chapter 24: Ten VBA Questions (And Answers) 365

The Top Ten Questions about VBA 365

Chapter 25: (Almost) Ten Excel Resources 369

The VBA Help System 369

Microsoft Product Support 369

Internet Newsgroups 370

Internet Web Sites 370

Excel Blogs 371

Google 371

Local User Groups 371

My Other Book 371

Index 373

0 0

Post a comment