WILEY

Wiley Publishing, In

Power ProgrammingExcel® 2010

w th VBA

by John Walkenbach

Contents at a GlanceIntroduction ................................................................................................................................................................ 1

Part I: Some Essential BackgroundChapter 1: Excel 2010: Where lt Came From ..................................................................................................... 11Chapter 2: Excel in a Nutshell ............................................................................................................................ 23Chapter 3: Formula Tricks and Techniques ..................................................................................................... 53Chapter 4: Understanding Excel Files ............................................................................................................... 77

Part II: Excel Application DevelopmentChapter 5: What Is a Spreadsheet Application? ............................................................................................. 1 01Chapter 6: Essentials of Spreadsheet Application Development ............................................................. 111

Part III: Understanding Visual Basic for ApplicationsChapter 7: Introducing Visual Basic for Applications .................................................................................... 135Chapter 8: VBA Programming Fundamentals .................................................................................................193Chapter 9: Working with VBA Sub Procedures ............................................................................................ 241Chapter 10: Creating Function Procedures ..................................................................................................... 281Chapter 11: VBA Programming Examples and Techniques ....................................................................... 325

Part IV: Working with UserFormsChapter 12: Custom Dialog Box Alternatives ................................................................................................. 399Chapter 13: Introducing UserForms ................................................................................................................. 419Chapter 14: UserForm Examples ...................................................................................................................... 455Chapter 15: Advanced UserForm Techniques .............................................................................................. 493

Part V: Advanced Programming TechniquesChapter 16: Developing Excel Utilities with VBA ........................................................................................ 543Chapter 17: Working with Pivot Tables ......................................................................................................... 565Chapter 18: Working with Charts ..................................................................................................................... 583Chapter 19: Understanding Excel's Events ..................................................................................................... 639Chapter 20: Interacting with Other Applications ........................................................................................ 677Chapter 21: Creating and Using Add-Ins ....................................................................................................... 703

Part VI: Developing ApplicationsChapter 22: Working with the Ribbon ........................................................................................................... 733Chapter 23: Working with Shortcut Menus ................................................................................................... 769Chapter 24: Providing Help for Your Applications ...................................................................................... 789Chapter 25: Developing User-Oriented Applications .................................................................................. 809

Part VII: Other TopicsChapter 26: Compatibility Issues .......................................................................................................... 825Chapter 27: Manipulating Files with VBA ............................................................................................ 839Chapter 28: Manipulating Visual Basic Components ........................................................................... 871Chapter 29: Understanding Class Modules ......................................................................................... 895Chapter 30: Working with Colors .......................................................................................................... 911Chapter 31: Frequently Asked Questions about Excel Programming ............................................... 937

Part VIII: AppendixesAppendix A: Excel Resources Online .................................................................................................. 969Appendix B: VBA Statements and Functions Reference .................................................................... 977Appendix C: VBA Error Codes ............................................................................................................. 985Appendix D: What's an the CD-ROM ................................................................................................. 989

Index .................................................................................................................................................... 1007

End-User License Agreement ........................................................................................................... 1053

Table of ContentsIntroduction ............................................................................................................................................. 1

Part 1: Some Essential BackgroundChapter 1: Excel 2010: Where lt Came From ................................................................................ 11

A Brief History of Spreadsheets ...................................................................................................................... 11lt all started with VisiCalc ...................................................................................................................... 11Lotus 1-2-3 ............................................................................................................................................... 12Quattro Pro ............................................................................................................................................... 14Microsoft Excel ......................................................................................................................................... 15Current Competition ............................................................................................................................ 20

Why Excel ls Great for Developers ............................................................................................................. 20Excel's Role in Microsoft's Strategy ............................................................................................................. 22

Chapter 2: Excel in a Nutshell ........................................................................................................ 23Thinking in Terms of Objects ........................................................................................................................ 23Workbooks ....................................................................................................................................................... 24

Worksheets ............................................................................................................................................. 24Chart sheets ........................................................................................................................................... 26XLM macro sheets ................................................................................................................................ 26Excel 5/95 dialog sheets .................................................................................................................... 28

Excel's User Interface ...................................................................................................................................... 28About the Ribbon ................................................................................................................................ 28Shortcut menus and the Mini Toolbar ............................................................................................ 34Dialog boxes ........................................................................................................................................... 35Keyboard shortcuts .............................................................................................................................. 36Smart Tags ............................................................................................................................................. 36Task pane ............................................................................................................................................... 36

Customizing the Display ................................................................................................................................ 37Data Entry ......................................................................................................................................................... 38Formulas, Functions, and Names ................................................................................................................. 38Selecting Objects ............................................................................................................................................. 40Formatting .......................................................................................................................................................... 40Protection Options ........................................................................................................................................... 42

Protecting formulas from being overwritten ................................................................................ 42Protecting a workbook's structure ................................................................................................... 43Applying password protection to a workbook .............................................................................. 43Protecting VBA code with a password .......................................................................................... 43

Charts .................................................................................................................................................................. 44Shapes and SmartArt ....................................................................................................................................... 45Database Access ............................................................................................................................................. 46

Worksheet databases .......................................................................................................................... 46External databases .............................................................................................................................. 47

Internet Features ............................................................................................................................................. 47Analysis Tools ................................................................................................................................................... 48Add-Ins ................................................................................................................................................................ 50Macros and Programming .............................................................................................................................. 50File Format ......................................................................................................................................................... 50Excel's Help System ........................................................................................................................................... 51

Chapter 3: Formula Tricks and Techniques ................................................................. 53About Formulas ............................................................................................................................................... 53Calculating Formulas ...................................................................................................................................... 54Cell and Range References ............................................................................................................................ 55

Why use references that aren't relative? ........................................................................................ 55About R1C1 notation ............................................................................................................................ 56Referencing other sheets or workbooks ........................................................................................ 57

Using Names ..................................................................................................................................................... 58Naming cells and ranges ................................................................................................................... 59Applying names to existing references .......................................................................................... 60Intersecting names ................................................................................................................................ 61Naming columns and rows ................................................................................................................. 61Scoping names ........................................................................................................................................ 61Naming constants ............................................................................................................................... 62Naming formulas ................................................................................................................................. 63Naming objects ................................................................................................................................... 65

Formula Errors ................................................................................................................................................ 65Array Formulas ................................................................................................................................................ 66

An array formula example ................................................................................................................. 66An array formula calendar ................................................................................................................. 67Array formula pros and cons ............................................................................................................. 68

Counting and Summing Techniques ........................................................................................................... 69Counting formula examples ............................................................................................................... 70Summing formula examples ............................................................................................................. 70Other counting tools .............................................................................................................................. 71

Working with Dates and Times ..................................................................................................................... 71Entering dates and times ................................................................................................................... 72Using pre-1900 dates .......................................................................................................................... 73

Creating Megaformulas .................................................................................................................................. 74

Chapter 4: Understanding Excel Files.......................................................................... 77Starting Excel ................................................................................................................................................... 77File Types ........................................................................................................................................................... 80

Excel file formats .................................................................................................................................. 80Text file formats ....................................................................................................................................... 81Database file formats ............................................................................................................................ 81Other file formats ................................................................................................................................ 82

Working with Template Files ........................................................................................................................ 83Viewing templates ................................................................................................................................ 83Creating templates ............................................................................................................................... 84Creating workbook templates ............................................................................................................ 86

Inside an Excel File .......................................................................................................................................... 87Dissecting a file .................................................................................................................................... 87Why is the file format important? ..................................................................................................... 91

The OfficeUl File ................................................................................................................................................. 91The XLB File ....................................................................................................................................................... 92Add-In Files ....................................................................................................................................................... 93Excel Settings in the Registry ........................................................................................................................ 94

About the Registry .............................................................................................................................. 94Excel's settings .................................................................................................................................... 95

Part II: Excel Application DevelopmentChapter 5: What Is a Spreadsheet Application? ....................................................................... 101

Spreadsheet Applications .............................................................................................................................. 101The Developer and the End-User ............................................................................................................... 102

Who are developers? What do they do? ...................................................................................... 102Classifying spreadsheet users ......................................................................................................... 104The audience for spreadsheet applications ................................................................................ 104

Solving Problems with Excel ...................................................................................................................... 105Basic Spreadsheet Types ............................................................................................................................ 106

Quick-and-dirty spreadsheets ......................................................................................................... 106For-your-eyes-only spreadsheets ................................................................................................... 107Single-user applications .................................................................................................................... 107Spaghetti applications ...................................................................................................................... 107Utility applications .............................................................................................................................. 108Add-ins that contain worksheet functions .................................................................................. 108Single-block budgets ........................................................................................................................ 109What-if models .................................................................................................................................. 109Data storage and access spreadsheets ........................................................................................ 109Database front ends ............................................................................................................................ 110Turnkey applications ............................................................................................................................ 110

Chapter 6: Essentials of Spreadsheet Application Development ........................................ 111Steps for Application Development ........................................................................................................... 111Determining User Needs ................................................................................................................................ 112Planning an Application That Meets User Needs .................................................................................... 113Determining the Most Appropriate User Interface .................................................................................. 115

Customizing the Ribbon ...................................................................................................................... 118Customizing shortcut menus ............................................................................................................. 118Creating shortcut keys ........................................................................................................................ 119Creating custom dialog boxes ......................................................................................................... 119Using ActiveX controls an a worksheet ........................................................................................ 120Executing the development effort ................................................................................................... 122

Concerning Yourself with the End User ..................................................................................................... 122Testing the application ........................................................................................................................ 122Making the application bulletproof .................................................................................................124Making the application aesthetically appealing and intuitive ...................................................126

Creating a user Help system .............................................................................................................. 127

Documenting the development effort ............................................................................................. 128

Distributing the application to the user ........................................................................................... 128Updating the application when necessary .................................................................................... 129

Other Development Issues .............................................................................................................................129The user's installed version of Excel ............................................................................................... 130Language issues ................................................................................................................................ 130System speed ...................................................................................................................................... 130Video modes ......................................................................................................................................... 131

Part UI: Understanding Visual Basic for ApplicationsChapter 7: Introducing Visual Basic for Applications .............................................................. 135

Getting Some BASIC Background ............................................................................................................... 135Delving in to VBA ............................................................................................................................................ 136

Object models ...................................................................................................................................... 136VBA versus XLM ..................................................................................................................................136

Covering the Basics of VBA ......................................................................................................................... 137Introducing the Visual Basic Editor .......................................................................................................... 140

Displaying Excel's Developer tab .................................................................................................... 141Activating the VBE ............................................................................................................................. 141The VBE windows ............................................................................................................................... 142

Working with the Project Explorer ............................................................................................................ 143Adding a new VBA module ............................................................................................................ 144Removing a VBA module .................................................................................................................. 145Exporting and importing objects .................................................................................................... 145

Working with Code Windows ...................................................................................................................... 145Minimizing and maximizing windows ......................................................................................... 146Storing VBA code ............................................................................................................................. 146Entering VBA code ............................................................................................................................. 147

Customizing the VBE Environment ............................................................................................................ 153Using the Editor tab ........................................................................................................................... 154Using the Editor Format tab ............................................................................................................ 156Using the General tab ......................................................................................................................... 157Using the Docking tab ....................................................................................................................... 158

The Macro Recorder ....................................................................................................................................... 159What the macro recorder actually records .................................................................................. 160Relative or absolute recording? ....................................................................................................... 161Recording options ............................................................................................................................. 164Cleaning up recorded macros ...........................................................................................................165

About Objects and Collections ..................................................................................................................... 167The object hierarchy ........................................................................................................................... 167About collections ..................................................................................................................................168Referring to objects ............................................................................................................................168

Properties and Methods ............................................................................................................................. 169Object properties ................................................................................................................................ 169Object methods .................................................................................................................................. 170

The Comment Object: A Case Study ........................................................................................................... 172Viewing Help for the Comment object .......................................................................................... 173Properties of a Comment object ....................................................................................................... 174Methods of a Comment object ......................................................................................................... 175The Comments collection ................................................................................................................. 175About the Comment property ......................................................................................................... 176Objects within a Comment object .................................................................................................. 177Determining whether a cell has a comment .................................................................................. 178Adding a new Comment object ....................................................................................................... 179

Some Useful Application Properties ......................................................................................................... 180Working with Range Objects ........................................................................................................................ 182

The Range property ............................................................................................................................ 182The Cells property .............................................................................................................................. 184The Offset property ............................................................................................................................ 187

Things to Know about Objects ..................................................................................................................... 188Essential concepts to remember ..................................................................................................... 188Learning more about objects and properties ................................................................................189

Chapter 8: VBA Programming Fundamentals ............................................................. 193VBA Language Elements: An Overview ..................................................................................................... 193Comments ......................................................................................................................................................... 195Variables, Data Types, and Constants ......................................................................................................... 197

Defining data types .............................................................................................................................. 198Declaring variables ............................................................................................................................ 201Scoping variables .............................................................................................................................. 203Working with constants ................................................................................................................... 206Working with strings .......................................................................................................................... 209Working with dates ............................................................................................................................ 209

Assignment Statements .............................................................................................................................. 210Arrays .................................................................................................................................................................. 213

Declaring arrays .................................................................................................................................... 213Declaring multidimensional arrays .................................................................................................. 214Declaring dynamic arrays ................................................................................................................... 214

Object Variables ............................................................................................................................................... 215User-Defined Data Types .............................................................................................................................. 216Built-in Functions ............................................................................................................................................ 217Manipulating Objects and Collections ....................................................................................................... 220

With-End With constructs ............................................................................................................... 220For Each-Next constructs ................................................................................................................. 221

Controlling Code Execution ........................................................................................................................ 223GoTo statements ................................................................................................................................ 224If-Then constructs .............................................................................................................................. 224Select Case constructs ...................................................................................................................... 229Looping blocks of instructions ....................................................................................................... 232

Chapter 9: Working with VBA Sub Procedures ........................................................... 241About Procedures .............................................................................................................................................241

Declaring a Sub procedure ............................................................................................................. 242Scoping a procedure .......................... '2A-Z

Executing Sub Procedures ........................................................................................................................... 244Executing a procedure with the Run Sub/UserForm command ............................................. 245Executing a procedure from the Macro dialog box .................................................................... 245Executing a procedure with a Ctrl+shortcut key combination ............................................... 246Executing a procedure from the Ribbon ...................................................................................... 247Executing a procedure from a customized shortcut menu ..................................................... 247Executing a procedure from another procedure ........................................................................ 248Executing a procedure by clicking an object .............................................................................. 253Executing a procedure when an event occurs ............................................................................ 254Executing a procedure from the Immediate window ............................................................... 254

Passing Arguments to Procedures ........................................................................................................... 255Error-Handling Techniques .......................................................................................................................... 259

Trapping errors .................................................................................................................................. 259Error-handling examples ....................................................................................................................261

A Realistic Example That Uses Sub Procedures .................................................................................... 264The goal ............................................................................................................................................... 264Project requirements ........................................................................................................................ 264What you know .................................................................................................................................. 265The approach ...................................................................................................................................... 265What you need to know .................................................................................................................... 266Some preliminary recording ........................................................................................................... 266Initial setup .......................................................................................................................................... 268Code writing ........................................................................................................................................ 269Writing the Sort procedure ............................................................................................................. 270More testing ........................................................................................................................................ 274Fixing the problems .......................................................................................................................... 275Utility availability ................................................................................................................................ 279Evaluating the project ..................................................................................................................... 279

Chapter 10: Creating Function Procedures ................................................................ 281Sub Procedures versus Function Procedures .......................................................................................... 281Why Create Custom Functions? ............................................................................................................... 282An Introductory Function Example .......................................................................................................... 282

Using the function in a worksheet ................................................................................................ 283Using the function in a VBA procedure ........................................................................................ 284Analyzing the custom function ...................................................................................................... 285

Function Procedures .................................................................................................................................... 287A function's scope .............................................................................................................................. 288Executing function procedures ....................................................................................................... 288

Function Arguments .................................................................................................................................... 292Function Examples ........................................................................................................................................ 293

Functions with no argument ........................................................................................................... 293A function with one argument ....................................................................................................... 295A function with two arguments ..................................................................................................... 298A function with an array argument ............................................................................................... 299A function with optional arguments ............................................................................................. 300A function that returns a VBA array ............................................................................................... 302

A function that returns an error value .......................................................................................... 305A function with an indefinite number of arguments ................................................................. 307

Emulating Excel's SUM function ................................................................................................................. 308Extended Date Functions .............................................................................................................................. 311Debugging Functions ...................................................................................................................................... 313Dealing with the Insert Function Dialog Box ............................................................................................ 314

Using the MacroOptions method ..................................................................................................... 315Specifying a function category ......................................................................................................... 317Adding a function description manually ........................................................................................ 318

Using Add-ins to Store Custom Functions .................................................................................................319Using the Windows API ................................................................................................................................ 320

Windows API examples ...................................................................................................................... 321Determining the Windows directory .............................................................................................. 321Detecting the Shift key ...................................................................................................................... 322Learning more about API functions .............................................................................................. 323

Chapter 11: VBA Programming Examples and Techniques ......................................... 325Learning by Example .................................................................................................................................... 325Working with Ranges .................................................................................................................................... 326

Copying a range ................................................................................................................................ 326Moving a range .................................................................................................................................. 328Copying a variably sized range ....................................................................................................... 328Selecting or otherwise identifying various types of ranges ..................................................... 330Prompting for a cell value ............................................................................................................... 332Entering a value in the next empty cell ........................................................................................ 333Pausing a macro to get a user-selected range ........................................................................... 334Counting selected cells ...................................................................................................................... 336Determining the type of selected range ...................................................................................... 337Looping through a selected range efficiently .............................................................................. 339Deleting all empty rows ................................................................................................................... 342Duplicating rows a variable number of times .............................................................................. 342Determining whether a range is contained in another range ................................................ 344Determining a cell's data type ......................................................................................................... 345Reading and writing ranges ............................................................................................................. 346A better way to write to a range ................................................................................................... 347Transferring one-dimensional arrays ............................................................................................ 349Transferring a range to a variant array ........................................................................................ 349Selecting cells by value .................................................................................................................... 350Copying a noncontiguous range ..................................................................................................... 352

Working with Workbooks and Sheets ..................................................................................................... 353Saving all workbooks ........................................................................................................................ 354Saving and ciosing all workbooks ................................................................................................. 354Hiding all but the selection ............................................................................................................. 354Synchronizing worksheets ............................................................................................................... 356

VBA Techniques ............................................................................................................................................. 357Toggling a Boolean property ......................................................................................................... 357Determining the number of printed pages .................................................................................. 358

Displaying the date and time .......................................................................................................... 358

Getting a list of fonts ........................................................................................................................ 360

Sorting an array ................................................................................................................................... 362

Processing a series of Files ................................................................................................................ 363

Some Useful Functions for Use in Your Code ......................................................................................... 365

The FileExists function ...................................................................................................................... 365

The FileNameOnly function ............................................................................................................. 365

The PathExists function .................................................................................................................... 366The RangeNameExists function ..................................................................................................... 366The SheetExists function .................................................................................................................. 368The Workbookls0pen function ....................................................................................................... 368Retrieving a value from a closed workbook ................................................................................ 368

Some Useful Worksheet Functions ........................................................................................................... 370Returning cell formatting information .......................................................................................... 370A talking worksheet .......................................................................................................................... 372Displaying the date when a file was saved or printed ............................................................. 372Understanding object parents ......................................................................................................... 373Counting cells between two values .............................................................................................. 374Determining the last non-empty cell in a column or row ......................................................... 375Does a string match a pattern 2 377

Extracting the nth element from a string .................................................................................... 378Spelling out a number ..................................................................................................................... 379A multifunctional function ............................................................................................................... 380The SheetOffset function ................................................................................................................... 381Returning the maximum value across all worksheets ................................................................. 381Returning an array of nonduplicated random integers ........................................................... 383Randomizing a range ....................................................................................................................... 384

Windows API Calls ........................................................................................................................................ 386Determining file associations ......................................................................................................... 386Determining disk drive information .............................................................................................. 387Determining default printer information ...................................................................................... 388Determining video display information ........................................................................................ 389Adding sound to your applications .............................................................................................. 390Reading from and writing to the Registry .................................................................................. 392

Part IV: Working with UserFormsChapter 12: Custom Dialog Box Alternatives ............................................................. 399

Before You Create That UserForm ............................................................................................................ 399Using an Input Box ......................................................................................................................................... 399

The VBA InputBox function .............................................................................................................. 400The Excel InputBox method ............................................................................................................ 402

The VBA MsgBox Function .......................................................................................................................... 404The Excel GetOpenFilename Method ........................................................................................................ 409The Excel GetSaveAsFilename Method ......................................................................................................412Prompting for a Directory ...............................................................................................................................413Displaying Excel's Bunt-1n Dialog Boxes ....................................................................................................413

Displaying a Data Form ................................................................................................................................ 416Making the data form accessible .................................................................................................. 416Displaying a data form by using VBA .......................................................................................... 418

Chapter 13: Introducing UserForms ........................................................................... 419How Excel Handles Custom Dialog Boxes .............................................................................................. 419Inserting a New UserForm .......................................................................................................................... 420Adding Controls to a UserForm ................................................................................................................... 421Toolbox Controls .......................................................................................................................................... 422

CheckBox ............................................................................................................................................. 423ComboBox ........................................................................................................................................... 423CommandButton ................................................................................................................................ 423Frame ................................................................................................................................................... 423Image ..................................................................................................................................................... 423Label ..................................................................................................................................................... 423ListBox ................................................................................................................................................... 424Multipage ............................................................................................................................................ 424OptionButton ...................................................................................................................................... 424RefEdit ................................................................................................................................................... 424ScrollBar ............................................................................................................................................... 424SpinButton .......................................................................................................................................... 424TabStrip ................................................................................................................................................. 425TextBox ................................................................................................................................................. 425ToggleButton ...................................................................................................................................... 426

Adjusting UserForm Controls ..................................................................................................................... 426Adjusting a Control's Properties ............................................................................................................... 426

Using the Properties window ......................................................................................................... 428Common properties .......................................................................................................................... 429Accommodating keyboard users ................................................................................................... 430

Displaying a UserForm .................................................................................................................................. 432Displaying a modeless UserForm ................................................................................................... 433Displaying a UserForm based an a variable ................................................................................ 433Loading a UserForm .......................................................................................................................... 433About event-handler procedures ................................................................................................... 433

Closing a UserForm ...................................................................................................................................... 434Creating a UserForm: An Example ........................................................................................................... 435

Creating the UserForm ..................................................................................................................... 436Writing code to display the dialog box ........................................................................................ 438Testing the dialog box ...................................................................................................................... 439Adding event-handler procedures ................................................................................................. 440Validating the data ............................................................................................................................ 441The finished dialog box ................................................................................................................... 442

Understanding UserForm Events ............................................................................................................... 442Learning about events ..................................................................................................................... 442UserForm events ................................................................................................................................ 443SpinButton events .............................................................................................................................. 444Pairing a SpinButton with a TextBox ............................................................................................ 446

Referencing UserForm Controls ................................................................................................................. 448

Customizing the Toolbox ............................................................................................................................ 450

Adding new pages to the Toolbox ................................................................................................. 450

Customizing or combining controls ............................................................................................... 450

Adding new ActiveX controls ........................................................................................................... 451

Creating UserForm Templates .................................................................................................................... 452

A UserForm Checklist .................................................................................................................................. 453

Chapter 14: UserForm Examples ............................................................................... 455Creating a UserForm "Menu" ..................................................................................................................... 455

Using CommandButtons in a UserForm ...................................................................................... 455

Using a ListBox in a UserForm ....................................................................................................... 456

Selecting Ranges from a UserForm ........................................................................................................... 457

Creating a Splash Screen ............................................................................................................................. 459

Disabling a UserForm's Close Button ...................................................................................................... 461

Changing a UserForm's Size ....................................................................................................................... 462Zooming and Scrolling a Sheet from a UserForm .................................................................................. 464

ListBox Techniques ..................................................................................................................................... 466Adding items to a ListBox control ................................................................................................ 467Determining the selected item in a ListBox ............................................................................... 472Determining multiple selections in a ListBox ............................................................................. 472Multiple lists in a single ListBox .................................................................................................... 474ListBox item transfer ......................................................................................................................... 474Moving items in a ListBox ............................................................................................................... 476Working with multicolumn ListBox controls ............................................................................. 478Using a ListBox to select worksheet rows ................................................................................. 480Using a ListBox to activate a sheet .............................................................................................. 482

Using the MultiPage Control in a UserForm ............................................................................................ 485Using an External Control ........................................................................................................................... 486Animating a Label ....................................................................................................................................... 489

Chapter 15: Advanced UserForm Techniques ........................................................... 493A Modeless Dialog Box ............................................................................................................................... 493Displaying a Progress Indicator ................................................................................................................. 497

Creating a stand-alone progress indicator .................................................................................. 498Showing a progress indicator by using a MultiPage control .................................................. 502Showing a progress indicator without using a MultiPage control ........................................ 505

Creating Wizards .......................................................................................................................................... 507Setting up the MultiPage control for the wizard ....................................................................... 508Adding the buttons to the wizard's UserForm ............................................................................ 508Programming the wizard's buttons .............................................................................................. 508Programming dependencies in a wizard ...................................................................................... 510Performing the task with the wizard .............................................................................................. 512

Emulating the MsgBox Function ................................................................................................................. 513MsgBox emulation: MyMsgBox code ............................................................................................... 514How the MyMsgBox function works ................................................................................................. 515Using the MyMsgBox function ........................................................................................................... 516

A UserForm with Movable Controls ........................................................................................................... 517A UserForm with No Title Bar ...................................................................................................................... 518

Simulating a Toolbar with a UserForm ....................................................................................................... 519A Resizable UserForm .................................................................................................................................... 521Handling Multiple UserForm Controls with One Event Handler ......................................................... 526Selecting a Color in a UserForm ................................................................................................................. 529Displaying a Chart in a UserForm ............................................................................................................... 531

Saving a chart as a GIF file ............................................................................................................... 532Changing the Image control Picture property ............................................................................ 532

Making a UserForm Semitransparent ....................................................................................................... 532An Enhanced Data Form .............................................................................................................................. 534

About the Enhanced Data Form ..................................................................................................... 536Installing the Enhanced Data Form add-in .................................................................................. 537

A Puzzle on a UserForm .............................................................................................................................. 537Video Poker on a UserForm ........................................................................................................................ 538

Part V: Advanced Programming TechniquesChapter 16: Developing Excel Utilities with VBA .................................................................. 543

About Excel Utilities ....................................................................................................................................... 543Using VBA to Develop Utilities ................................................................................................................... 544What Makes a Good Utility? ........................................................................................................................ 545Text Tools: The Anatomy of a Utility ......................................................................................................... 545

Background for Text Tools ............................................................................................................. 546Project goals for Text Tools ............................................................................................................. 547The Text Tools workbook ................................................................................................................. 547How the Text Tools utility works ................................................................................................... 548The UserForm for the Text Tools utility ........................................................................................ 548The Modulel VBA module ............................................................................................................... 550The UserForml code module ........................................................................................................... 552Making the Text Tools utility efficient .......................................................................................... 554Saving the Text Tools utility settings ............................................................................................. 555Implementing Undo .......................................................................................................................... 557Displaying the Help file ...................................................................................................................... 559Adding the RibbonX code ............................................................................................................... 560Post-mortem of the project ............................................................................................................. 562Understand the Text Tools utility ................................................................................................... 562

More about Excel Utilities ............................................................................................................................ 563

Chapter 17: Working with Pivot Tables .................................................................................... 565An lntroductory Pivot Table Example ....................................................................................................... 565

Creating a pivot table ........................................................................................................................ 566Examining the recorded code for the pivot table ..................................................................... 568Cleaning up the recorded pivot table code ................................................................................ 568

Creating a More Complex Pivot Table ....................................................................................................... 571The code that created the pivot table .......................................................................................... 573How the more complex pivot table works .................................................................................. 574

Creating Multiple Pivot Tables .................................................................................................................... 576Creating a Reverse Pivot Table ................................................................................................................. 579

Chapter 18: Working with Charts .............................................................................. 583Getting the Inside Scoop on Charts ............................................................................................................ 583

Chart locations ..................................................................................................................................... 584

The macro recorder and charts ....................................................................................................... 584

The Chart object model .................................................................................................................... 585

Creating an Embedded Chart ...................................................................................................................... 586

Creating a Chart on a Chart Sheet ............................................................................................................. 588

Using VBA to Activate a Chart .................................................................................................................... 589

Moving a Chart ............................................................................................................................................... 590

Using VBA to Deactivate a Chart ................................................................................................................. 591

Determining Whether a Chart Is Activated ............................................................................................ 592

Deleting from the ChartObjects or Charts Collection ............................................................................ 593

Looping through All Charts ....................................................................................................................... 594

Sizing and Aligning ChartObjects ............................................................................................................. 596

Exporting a Chart .......................................................................................................................................... 598

Exporting all graphics ....................................................................................................................... 599Changing the Data Used in a Chart ........................................................................................................... 600

Changing chart data based on the active cell ............................................................................ 601Using VBA to determine the ranges used in a chart ................................................................. 603

Using VBA to Display Arbitrary Data Labels on a Chart ..................................................................... 606Displaying a Chart in a UserForm ............................................................................................................. 609Understanding Chart Events ......................................................................................................................... 611

An example of using Chart events .................................................................................................. 612Enabling events for an embedded chart ........................................................................................ 615Example: Using Chart events with an embedded chart ........................................................... 616

Discovering VBA Charting Tricks ................................................................................................................. 618Printing embedded charts on a full page .................................................................................... 619Hiding series by hiding columns .................................................................................................... 619Creating unlinked charts ................................................................................................................... 621Displaying text with the MouseOver event .................................................................................. 622

Animating Charts .......................................................................................................................................... 625Scrolling a chart .................................................................................................................................. 626Creating a hypocycloid chart ......................................................................................................... 628Creating a "clock" chart ................................................................................................................... 629

Creating an Interactive Chart without VBA .............................................................................................. 631Getting the data to create an interactive chart .......................................................................... 632Creating the Option Button controls for an interactive chart ................................................ 632Creating the City lists for the interactive chart ............................................................................ 632Creating the interactive chart data range .................................................................................... 633Creating the interactive chart ......................................................................................................... 634

Working with Sparkline Charts ................................................................................................................... 635

Chapter 19: Understanding Excel's Events ................................................................. 639What You Should Know about Events ..................................................................................................... 639

Understanding event sequences ..................................................................................................... 640Where to put event-handler procedures ...................................................................................... 640Disabling events ................................................................................................................................ 642

Entering event-handler code ........................................................................................................... 643Event-handler procedures that use arguments ......................................................................... 644

Getting Acquainted with Workbook-Level Events ................................................................................ 646The Open event .................................................................................................................................. 647The Activate event ............................................................................................................................ 648The SheetActivate event ................................................................................................................. 648The NewSheet event .......................................................................................................................... 649The BeforeSave event ........................................................................................................................ 649The Deactivate event ........................................................................................................................ 650The BeforePrint event ........................................................................................................................ 650The BeforeClose event ...................................................................................................................... 652

Examining Worksheet Events ...................................................................................................................... 654The Change event .............................................................................................................................. 654Monitoring a specific range for changes ...................................................................................... 655The SelectionChange event ............................................................................................................. 660The BeforeDoubleClick event ......................................................................................................... 661The BeforeRightClick event ............................................................................................................. 662

Checking Out Chart Events .......................................................................................................................... 662Monitoring with Application Events ......................................................................................................... 664

Enabling Application-level events ................................................................................................. 666Determining when a workbook is opened .................................................................................. 666Monitoring Application-level events ............................................................................................ 668

Using UserForm Events ................................................................................................................................ 669Accessing Events Not Associated with an Object ................................................................................ 670

The OnTime event .............................................................................................................................. 670The OnKey event ................................................................................................................................ 672

Chapter 20: Interacting with Other Applications ...................................................... 677Starting an Application from Excel ........................................................................................................... 677

Using the VBA Shell function ......................................................................................................... 677Using the Windows ShellExecute API function ......................................................................... 680

Activating an Application with Excel ......................................................................................................... 681Using AppActivate .............................................................................................................................. 681Activating a Microsoft Office application .................................................................................... 682

Running Control Panel Dialog Boxes ....................................................................................................... 683Using Automation in Excel .......................................................................................................................... 684

Working with foreign objects using automation ....................................................................... 685Early versus late binding ................................................................................................................. 685A simple example of late binding ................................................................................................... 688Controlling Word from Excel ........................................................................................................... 689Controlling Excel from another application ................................................................................ 692

Sending Personalized E-Mail via Outlook ................................................................................................. 695Sending E-Mail Attachments from Excel ................................................................................................. 698Using SendKeys ............................................................................................................................................. 701

Chapter 21: Creating and Using Add- lns .................................................................. 703What Is an Add-In? 703

Comparing an add-in with a standard workbook ..................................................................... 703Why create add-ins? .......................................................................................................................... 704

XX

Understanding Excel's Add-ln Manager ................................................................................................... 706

Creating an Add-in ........................................................................................................................................ 707

An Add-In Example ...................................................................................................................................... 708Adding descriptive information for the example add-in ......................................................... 709

Creating an add-in .............................................................................................................................. 710

Installing an add-in ............................................................................................................................ 710

Testing the add-in ................................................................................................................................ 712Distributing an add-in ......................................................................................................................... 712Modifying an add-in ........................................................................................................................... 713

Comparing XLAM and XLSM Files ...............................................................................................................714XLAM file VBA collection membership .......................................................................................... 714Visibility of XLSM and XLAM files .................................................................................................... 715Worksheets and chart sheets in XLSM and XLAM files ............................................................... 716Accessing VBA procedures in an add-in ........................................................................................ 717

Manipulating Add-Ins with VBA ................................................................................................................... 721Addln object properties ................................................................................................................... 722Accessing an add-in as a workbook .............................................................................................. 725Addln object events ......................................................................................................................... 726

Optimizing the Performance of Add-ins .................................................................................................. 726Special Problems with Add-lns ................................................................................................................. 727

Ensuring that an add-in is installed .............................................................................................. 727Referencing other files from an add-in ........................................................................................ 729Detecting the proper Excel version for your add-in ................................................................. 730

Part VI: Developing ApplicationsChapter 22: Working with the Ribbon ..................................................................... 733

Ribbon Basics ................................................................................................................................................ 733Using VBA with the Ribbon ....................................................................................................................... 737

Accessing a Ribbon control ............................................................................................................. 738Working with the Ribbon ................................................................................................................. 740Activating a tab ................................................................................................................................. 742

Customizing the Ribbon ............................................................................................................................. 743A simple RibbonX example ............................................................................................................. 743A simple Ribbon example, take 2 ................................................................................................... 746Another RibbonX example ................................................................................................................. 751Ribbon controls demo ..................................................................................................................... 754A DynamicMenu Control Example ................................................................................................... 761More an Ribbon customization ....................................................................................................... 763

Creating an Old-Style Toolbar ................................................................................................................... 764Limitations of old-style toolbars in Excel 2010 .......................................................................... 765Code to create a toolbar .................................................................................................................. 765

Chapter 23: Working with Shortcut Menus ............................................................... 769CommandBar Overview .............................................................................................................................. 769

CommandBar types .......................................................................................................................... 770Listing shortcut menus ...................................................................................................................... 770

Referring to CommandBars ............................................................................................................... 771Referring to controls in a CommandBar ...................................................................................... 772Properties of CommandBar controls ............................................................................................ 773Displaying all shortcut menu items .............................................................................................. 774

Using VBA to Customize Shortcut Menus .............................................................................................. 777Resetting a shortcut menu ............................................................................................................... 777Disabling a Shortcut Menu ............................................................................................................... 778Disabling shortcut menu items ....................................................................................................... 778Adding a new item to the Cell shortcut menu ........................................................................... 779Adding a submenu to a shortcut menu .......................................................................................... 781

Shortcut Menus and Events ........................................................................................................................ 783Adding and deleting menus automatically .................................................................................. 784Disabling or hiding shortcut menu items .................................................................................... 785Creating a context-sensitive shortcut menu ................................................................................ 785

Chapter 24: Providing Help for Your Applications ................................................................ 789Help for Your Excel Applications ............................................................................................................... 789Help Systems That Use Excel Components ............................................................................................ 790

Using cell comments for help ......................................................................................................... 792Using a text box for help ................................................................................................................. 793Using a worksheet to display help text ........................................................................................ 794Displaying help in a UserForm ....................................................................................................... 795

Displaying Help in a Web Browser ........................................................................................................... 799Using HTML fites ................................................................................................................................ 799Using an MHTML file .......................................................................................................................... 800

Using the HTML Heip System ...................................................................................................................... 801Using the Heip method to display HTML Help ........................................................................... 804

Associating a Help File with Your Application ...................................................................................... 805Associating a Help topic with a VBA function ............................................................................ 805

Chapter 25: Developing User -Oriented Applications ............................................................ 809What is a User-Oriented Application? ..................................................................................................... 809the Loan Amortization Wizard ................................................................................................................... 809

Using the Loan Amortization Wizard ............................................................................................ 810The Loan Amortization Wizard workbook structure ................................................................... 812How the Loan Amortization Wizard works .................................................................................... 813Potential enhancements for the Loan Amortization Wizard .................................................. 820

Application Development Concepts ......................................................................................................... 820

Part VII: Other TopiaChapter 26: Compatibility Issues ............................................................................................... 825

What Is Compatibility, ................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................... 825Types of Compatibility Problems ............................................................................................................... 826Avoid Using New Features .......................................................................................................................... 827But Will lt Work an a Mac?........................................................................................................................... 828Dealing with 64-bit Excel ............................................................................................................................ 830

Creating an International Application .......................................................................................................... 831

Multilanguage applications .............................................................................................................. 832

VBA language considerations .......................................................................................................... 834

Using local properties ........................................................................................................................834

Identifying system settings .............................................................................................................. 834

Date and time settings ...................................................................................................................... 837

Chapter 27: Manipulating Files with VBA .................................................................................. 839

Performing Common File Operations ....................................................................................................... 839

Using VBA file-related statements ................................................................................................. 840

Using the FileSystemObject object ............................................................................................... 845

Displaying Extended File Information ....................................................................................................... 848

Working with Text Files ................................................................................................................................ 850

Opening a text file ................................................................................................................................ 851

Reading a text file .............................................................................................................................. 852

Writing a text file ................................................................................................................................ 852

Getting a file number ........................................................................................................................ 852

Determining or setting the file position ........................................................................................ 853

Statements for reading and writing .............................................................................................. 853

Text File Manipulation Examples ............................................................................................................... 854

Importing data in a text file ............................................................................................................. 854

Exporting a range to a text file ...................................................................................................... 856

I mporting a text file to a range ...................................................................................................... 857

Logging Excel usage ......................................................................................................................... 858

Filtering a text file ............................................................................................................................. 859

Exporting a range to HTML format .............................................................................................. 859

Exporting a range to an XML file .................................................................................................. 863

Zipping and Unzipping Files ....................................................................................................................... 865Zipping files ........................................................................................................................................ 865Unzipping a File ................................................................................................................................. 867

Working with ADO ........................................................................................................................................ 868

Chapter 28: Manipulating Visual Basic Components .............................................................. 871Introducing the IDE ........................................................................................................................................ 871The IDE Object Model .................................................................................................................................. 873

The VBProjects collection ............................................................................................................... 874Displaying All Components in a VBA Project ........................................................................................ 876Listing All VBA Procedures in a Workbook ............................................................................................. 877Replacing a Module with an Updated Version ...................................................................................... 879Using VBA to Write VBA Code ....................................................................................................................881Adding Controls to a UserForm at Design Time .................................................................................... 883

Design-time versus runtime UserForm manipulations ............................................................. 884Adding 100 CommandButtons at design time ............................................................................ 885

Creating UserForms Programmatically ..................................................................................................... 886A simple runtime UserForm example ........................................................................................... 887A useful (but not so simple) dynamic UserForm example ....................................................... 888

Chapter 29: Understanding Class Modules ............................................................... 895What is a Class Module? .............................................................................................................................. 895Example: Creating a NumLock Class ......................................................................................................... 896

Inserting a dass module ................................................................................................................... 897Adding VBA code to the dass module ........................................................................................ 897Using the NumLockClass dass ....................................................................................................... 900

More about Class Modules .......................................................................................................................... 901Programming properties of objects .............................................................................................. 901Programming methods for objects .............................................................................................. 903Class module events .......................................................................................................................... 904

Example: A CSV File Class .......................................................................................................................... 904Class module-level variables for the CSVFileClass ................................................................... 905Property procedures for the CSVFileClass .................................................................................. 905Method procedures for the CSVFileClass .................................................................................... 905Using the CSVFileClass object ......................................................................................................... 907

Chapter 30: Working with Colors ............................................................................... 911Specifying Colors ............................................................................................................................................. 911

The RGB color system ........................................................................................................................ 912The HSL color system .......................................................................................................................... 913Converting colors ................................................................................................................................ 913

Understanding Grayscale ............................................................................................................................ 916Converting colors to gray ............................................................................................................... 916Viewing charts as grayscale ............................................................................................................. 918

Experimenting with Colors .......................................................................................................................... 919Understanding Document Themes ............................................................................................................. 921

About document themes ................................................................................................................... 921Understanding document theme colors ........................................................................................ 921Displaying all theme colors ............................................................................................................. 925

Working with Shape Objects ..................................................................................................................... 927A shape's background color ........................................................................................................... 928Shapes and theme colors ................................................................................................................. 930Shape examples .................................................................................................................................. 932

Modifying Chart Colors ................................................................................................................................ 933

Chapter 31: Frequently Asked Questions about Excel Programming ......................... 937Getting the Scoop an FAQs ........................................................................................................................ 937General Excel Questions .............................................................................................................................. 938The Visual Basic Editor ................................................................................................................................ 942Procedures ....................................................................................................................................................... 944Functions ......................................................................................................................................................... 948Objects, Properties, Methods, and Events .................................................................................................951UserForms ....................................................................................................................................................... 959Add-Ins ............................................................................................................................................................. 962User Interface ................................................................................................................................................. 964

xXiittäWie

Part VIII. AppendixesAppendix A: Excel Resources Online .......................................................................................... 969

The Excel Help System ................................................................................................................................. 969

Microsoft Technical Support ......................................................................................................................... 969

Support options ................................................................................................................................... 970

Microsoft Knowledge Base .............................................................................................................. 970

Microsoft Excel home page .............................................................................................................. 970Microsoft Office home page ............................................................................................................ 970

Internet Newsgroups ....................................................................................................................................... 971Accessing newsgroups by using a newsreader ............................................................................ 971Accessing newsgroups by using a Web browser ........................................................................ 971Searching newsgroups ...................................................................................................................... 972

Internet Web sites ........................................................................................................................................ 973The Spreadsheet Page ..................................................................................................................... 973Daily Dose of Excel ........................................................................................................................... 974Jon Peltier's Excel Page ................................................................................................................... 974Pearson Software Consulting ......................................................................................................... 974Contextures ........................................................................................................................................ 974Pointy Haired Dilbert ....................................................................................................................... 974David McRitchie's Excel Pages ....................................................................................................... 975Mr. Excel .............................................................................................................................................. 975

Appendix B: VBA Statements and Functions Reference ............................................................ 977Invoking Excel functions in VBA instructions ........................................................................................ 980

Appendix C: VBA Error Codes ..................................................................................................... 985

Appendix D: What's on the CD-ROM ......................................................................................... 989System Requirements ............................................................... 989Using the CD ....................................................................... 989Files and Software on the CD .........................................................990

Applications. . . . . . . . .......................................................... 990

eBook version of Excel 2010 Power Programming with VBA ......................................... 990Sample files for Excel 2010 Power Programming with VBA ........................................... 990

Troubleshooting ................................................................... 1005

Index1007

End-User License Agreement ................................................................................................... 1053