Excel 2016 Power Programming with VBA

Michael Alexander is a Microsoft Certified Application Developer and author of several books on Microsoft Access and Excel. He runs a free tutorial site at datapigtechnologies.com.

John Walkenbach is arguably the foremost authority on Excel. He has written more than 30 books and maintains the popular Spreadsheet Page at http://spreadsheetpage.com

Dick Kusleika has been helping users get the most out of Microsoft Office products for more than fifteen years through online forums, blogging, books, and conferences.

ntroduction xxvii

Part I: Introduction to Excel VBA

Chapter 1: Essentials of Spreadsheet Application Development 3

What Is a Spreadsheet Application? 3

Steps for Application Development 4

Determining User Needs 5

Planning an Application That Meets User Needs 6

Determining the Most Appropriate User Interface 8

Customizing the Ribbon 8

Customizing shortcut menus 8

Creating shortcut keys 9

Creating custom dialog boxes 9

Using ActiveX controls on a worksheet 10

Executing the development effort 11

Concerning Yourself with the End User 12

Testing the application 12

Making the application bulletproof 13

Making the application aesthetically appealing and intuitive 15

Creating a user Help system 16

Documenting the development effort 16

Distributing the application to the user 16

Updating the application when necessary 17

Other Development Issues 17

The user’s installed version of Excel 17

Language issues 17

System speed 18

Video modes 18

Chapter 2: Introducing Visual Basic for Applications 19

Getting a Head Start with the Macro Recorder 19

Creating your first macro 20

Comparing absolute and relative macro recording 23

Other macro recording concepts 27

Working with the Visual Basic Editor 32

Understanding VBE components 32

Working with the Project window 33

Working with a Code window 36

Customizing the VBA environment 38

The Editor Format tab 40

The General tab 41

The Docking tab 41

VBA Fundamentals 42

Understanding objects 43

Understanding collections 43

Understanding properties 44

Deep Dive: Working with Range Objects 47

Finding the properties of the Range object 48

The Range property 48

The Cells property 49

The Offset property 52

Essential Concepts to Remember 53

Don’t Panic—You Are Not Alone 55

Read the rest of the book 55

Let Excel help write your macro 55

Use the Help system 55

Use the Object Browser 56

Pilfer code from the Internet 57

Leverage user forums 57

Visit expert blogs 58

Mine YouTube for video training 58

Learn from the Microsoft Office Dev Center 59

Dissect the other Excel files in your organization 59

Ask your local Excel genius 59

Chapter 3: VBA Programming Fundamentals 61

VBA Language Elements: An Overview 61

Comments 63

Variables, Data Types, and Constants 64

Defining data types 65

Declaring variables 67

Scoping variables 69

Working with constants 72

Working with strings 73

Working with dates 74

Assignment Statements 75

Arrays 77

Declaring arrays 77

Declaring multidimensional arrays 78

Declaring dynamic arrays 79

Object Variables 79

User-Defined Data Types 80

Built-In Functions 81

Manipulating Objects and Collections 84

With-End With constructs 84

For Each-Next constructs 85

Controlling Code Execution 87

GoTo statements 88

If-Then constructs 88

Select Case constructs 93

Looping blocks of instructions 96

Chapter 4: Working with VBA Sub Procedures 105

About Procedures 105

Declaring a Sub procedure 106

Scoping a procedure 107

Executing Sub Procedures 108

Executing a procedure with the Run Sub/UserForm command 109

Executing a procedure from the Macro dialog box 109

Executing a procedure with a Ctrl+shortcut key combination 110

Executing a procedure from the Ribbon 111

Executing a procedure from a customized shortcut menu 111

Executing a procedure from another procedure 112

Executing a procedure by clicking an object 116

Executing a procedure when an event occurs 118

Executing a procedure from the Immediate window 118

Passing Arguments to Procedures 119

Error-Handling Techniques 123

Trapping errors 123

Error-handling examples 124

A Realistic Example That Uses Sub Procedures 127

The goal 128

Project requirements 128

What you know 128

The approach 129

Some preliminary recording 129

Initial setup 131

Code writing 132

Writing the Sort procedure 133

More testing 137

Fixing the problems 138

Utility availability 141

Evaluating the project 142

Chapter 5: Creating Function Procedures 143

Sub Procedures versus Function Procedures 143

Why Create Custom Functions? 144

An Introductory Function Example 144

Using the function in a worksheet 145

Using the function in a VBA procedure 146

Analyzing the custom function 146

Function Procedures 148

A function’s scope 150

Executing function procedures 150

Function Arguments 153

Function Examples 153

Functions with no argument 153

A function with one argument 156

A function with two arguments 159

A function with an array argument 159

A function with optional arguments 160

A function that returns a VBA array 162

A function that returns an error value 165

A function with an indefinite number of arguments 166

Emulating Excel’s SUM Function 168

Extended Date Functions 171

Debugging Functions 173

Dealing with the Insert Function Dialog Box 174

Using the MacroOptions method 174

Specifying a function category 176

Adding a function description manually 177

Using Add-Ins to Store Custom Functions 178

Using the Windows API 179

Windows API examples 179

Determining the Windows directory 180

Detecting the Shift key 181

Learning more about API functions 182

Chapter 6: Understanding Excel’s Events 183

What You Should Know about Events 183

Understanding event sequences 184

Where to put event-handler procedures 184

Disabling events 186

Entering event-handler code 187

Event-handler procedures that use arguments 188

Getting Acquainted with Workbook-Level Events 190

The Open event 191

The Activate event 192

The SheetActivate event 192

The NewSheet event 193

The BeforeSave event 193

The Deactivate event 193

The BeforePrint event 194

The BeforeClose event 195

Examining Worksheet Events 197

The Change event 198

Monitoring a specific range for changes 199

The SelectionChange event 203

The BeforeDoubleClick event 204

The BeforeRightClick event 205

Monitoring with Application Events 206

Enabling Application-level events 207

Determining when a workbook is opened 208

Monitoring Application-level events 209

Accessing Events Not Associated with an Object 210

The OnTime event 210

The OnKey event 212

Chapter 7: VBA Programming Examples and Techniques 217

Learning by Example 217

Working with Ranges 218

Copying a range 218

Moving a range 219

Copying a variably sized range 220

Selecting or otherwise identifying various types of ranges 221

Resizing a range 223

Prompting for a cell value 224

Entering a value in the next empty cell 225

Pausing a macro to get a user-selected range 226

Counting selected cells 228

Determining the type of selected range 229

Looping through a selected range efficiently 231

Deleting all empty rows 233

Duplicating rows a variable number of times 234

Determining whether a range is contained in another range 236

Determining a cell’s data type 237

Reading and writing ranges 238

A better way to write to a range 240

Transferring one-dimensional arrays 242

Transferring a range to a variant array 242

Selecting cells by value 243

Copying a noncontiguous range 244

Working with Workbooks and Sheets 246

Saving all workbooks 246

Saving and closing all workbooks 247

Hiding all but the selection 247

Creating a hyperlink table of contents 249

Synchronizing worksheets 250

VBA Techniques 251

Toggling a Boolean property 251

Displaying the date and time 251

Displaying friendly time 253

Getting a list of fonts 254

Sorting an array 256

Processing a series of files 257

Some Useful Functions for Use in Your Code 259

The FileExists function 259

The FileNameOnly function 259

The PathExists function 260

The RangeNameExists function 260

The SheetExists function 261

The WorkbookIsOpen function 261

Retrieving a value from a closed workbook 262

Some Useful Worksheet Functions 264

Returning cell formatting information 264

A talking worksheet 266

Displaying the date when a file was saved or printed 266

Understanding object parents 267

Counting cells between two values 268

Determining the last nonempty cell in a column or row 269

Does a string match a pattern? 270

Extracting the nth element from a string 272

Spelling out a number 272

A multifunctional function 273

The SHEETOFFSET function 274

Returning the maximum value across all worksheets 275

Returning an array of nonduplicated random integers 276

Randomizing a range 278

Sorting a range 279

Windows API Calls 280

Understanding API Declarations 280

Determining file associations 281

Determining default printer information 282

Determining video display information 283

Reading from and writing to the Registry 284

Part II: Advanced VBA Techniques

Chapter 8: Working with Pivot Tables 291

An Introductory Pivot Table Example 291

Creating a pivot table 292

Examining the recorded code for the pivot table 294

Cleaning up the recorded pivot table code 294

Creating a More Complex Pivot Table 297

The code that created the pivot table 298

How the more complex pivot table works 299

Creating Multiple Pivot Tables 301

Creating a Reverse Pivot Table304

Chapter 9: Working with Charts 307

Getting the Inside Scoop on Charts 307

Chart locations 307

The macro recorder and charts 308

The Chart object model 308

Creating an Embedded Chart 310

Creating a Chart on a Chart Sheet 311

Modifying Charts 312

Using VBA to Activate a Chart 313

Moving a Chart 314

Using VBA to Deactivate a Chart 315

Determining Whether a Chart Is Activated 316

Deleting from the ChartObjects or Charts Collection 316

Looping through All Charts 318

Sizing and Aligning ChartObjects 320

Creating Lots of Charts 321

Exporting a Chart 324

Exporting all graphics 325

Changing the Data Used in a Chart 326

Changing chart data based on the active cell 327

Using VBA to determine the ranges used in a chart 329

Using VBA to Display Arbitrary Data Labels on a Chart 331

Displaying a Chart in a UserForm 335

Understanding Chart Events 337

An example of using Chart events 338

Enabling events for an embedded chart 340

Example: Using Chart events with an embedded chart 342

Discovering VBA Charting Tricks 344

Printing embedded charts on a full page 344

Creating unlinked charts 344

Displaying text with the MouseOver event 346

Scrolling a chart 349

Working with Sparkline Charts 351

Chapter 10: Interacting with Other Applications 355

Understanding Microsoft Office Automation 355

Understanding the concept of binding 356

A simple automation example 358

Automating Access from Excel 359

Running an Access Query from Excel 359

Running an Access Macro from Excel 360

Automating Word from Excel 361

Sending Excel data to a Word document 361

Simulating mail merge with a Word document 362

Automating PowerPoint from Excel 364

Sending Excel data to a PowerPoint presentation 365

Sending all Excel charts to a PowerPoint presentation 366

Convert a workbook into a PowerPoint presentation 367

Automating Outlook from Excel 369

Mailing the Active Workbook as an Attachment 369

Mailing a Specific Range as an Attachment 370

Mailing a Single Sheet as an Attachment 371

Mailing All Email Addresses in Your Contact List 372

Starting Other Applications from Excel 373

Using the VBA Shell function 373

Using the Windows ShellExecute API function 376

Using AppActivate 377

Running Control Panel dialog boxes 378

Chapter 11: Working with External Data and Files 379

Working with External Data Connections 379

Manually creating a connection 379

Manually editing data connections 383

Using VBA to create dynamic connections 384

Iterating through all connections in a workbook 386

Using ADO and VBA to Pull External Data 387

The connection string 388

Declaring a Recordset 389

Referencing the ADO object library 390

Putting it all together in code 391

Using ADO with the active workbook 392

Working with Text Files 394

Opening a text file 395

Reading a text file 396

Writing a text file 396

Getting a file number 396

Determining or setting the file position 397

Statements for reading and writing 397

Text File Manipulation Examples 398

Importing data in a text file 398

Exporting a range to a text file 398

Importing a text file to a range 399

Logging Excel usage 400

Filtering a text file 401

Performing Common File Operations 402

Using VBA file-related statements 402

Using the FileSystemObject object 407

Zipping and Unzipping Files 410

Zipping files 410

Unzipping a file 411

Part III: Working with UserForms

Chapter 12: Leveraging Custom Dialog Boxes 415

Before You Create That UserForm 415

Using an Input Box 415

The VBA InputBox function 416

The Application.InputBox method 418

The VBA MsgBox Function 421

The Excel GetOpenFilename Method 426

The Excel GetSaveAsFilename Method 429

Prompting for a Directory 430

Displaying Excel’s Built-In Dialog Boxes 430

Displaying a Data Form 433

Making the data form accessible 434

Displaying a data form by using VBA 434

Chapter 13: Introducing UserForms 435

How Excel Handles Custom Dialog Boxes 435

Inserting a New UserForm 436

Adding Controls to a UserForm 437

Toolbox Controls 437

CheckBox 438

ComboBox 438

CommandButton 438

Frame 439

Image 439

Label 439

ListBox 439

MultiPage 439

OptionButton 439

RefEdit 440

ScrollBar 440

SpinButton 440

TabStrip 440

TextBox 440

ToggleButton 440

Adjusting UserForm Controls 442

Adjusting a Control’s Properties 443

Using the Properties window 443

Common properties 445

Accommodating keyboard users 447

Displaying a UserForm 449

Adjusting the display position 449

Displaying a modeless UserForm 450

Displaying a UserForm based on a variable 450

Loading a UserForm 450

About event-handler procedures 451

Closing a UserForm 451

Creating a UserForm: An Example 453

Creating the UserForm 453

Writing code to display the dialog box 455

Testing the dialog box 456

Adding event-handler procedures 457

The finished dialog box 459

Understanding UserForm Events 459

Learning about events 459

UserForm events 460

SpinButton events 461

Pairing a SpinButton with a TextBox 462

Referencing UserForm Controls 465

Customizing the Toolbox 466

Adding new pages to the Toolbox 467

Customizing or combining controls 467

Adding new ActiveX controls 468

Creating UserForm Templates 469

A UserForm Checklist 469

Chapter 14: UserForm Examples 471

Creating a UserForm “Menu”471

Using CommandButtons in a UserForm 471

Using a ListBox in a UserForm 472

Selecting Ranges from a UserForm 474

Creating a Splash Screen 476

Disabling a UserForm's Close Button 478

Changing a UserForm's Size 479

Zooming and Scrolling a Sheet from a UserForm 480

ListBox Techniques 482

Adding items to a ListBox control 483

Determining the selected item in a ListBox 487

Determining multiple selections in a ListBox 488

Multiple lists in a single ListBox 489

ListBox item transfer 490

Moving items in a ListBox 492

Working with multicolumn ListBox controls 494

Using a ListBox to select worksheet rows 496

Using a ListBox to activate a sheet 498

Filtering a ListBox from a TextBox 501

Using the MultiPage Control in a UserForm 503

Using an External Control 504

Animating a Label 506

Chapter 15: Advanced UserForm Techniques 511

A Modeless Dialog Box 512

Displaying a Progress Indicator 516

Creating a stand-alone progress indicator 517

Showing a progress indicator that’s integrated into a UserForm 520

Creating a non-graphical progress indicator 524

Creating Wizards 527

Setting up the MultiPage control for the wizard 528

Adding the buttons to the wizard’s UserForm 528

Programming the wizard’s buttons 528

Programming dependencies in a wizard 530

Performing the task with the wizard 532

Emulating the MsgBox Function 533

MsgBox emulation: MyMsgBox code 533

How the MyMsgBox function works 535

Using the MyMsgBox function 537

A UserForm with Movable Controls 537

A UserForm with No Title Bar 538

Simulating a Toolbar with a UserForm 540

Emulating a Task Pane with a UserForm 542

A Resizable UserForm 543

Handling Multiple UserForm Controls with One Event Handler 547

Selecting a Color in a UserForm 550

Displaying a Chart in a UserForm 552

Saving a chart as a GIF file 553

Changing the Image control’s Picture property 554

Making a UserForm Semitransparent 554

A Puzzle on a UserForm 556

Video Poker on a UserForm 557

Part IV: Developing Excel Applications

Chapter 16: Creating and Using Add-Ins 561

What Is an Add-In? 561

Comparing an add-in with a standard workbook 561

Why create add-ins? 562

Understanding Excel’s Add-In Manager 564

Creating an Add-in 566

An Add-In Example 567

Adding descriptive information for the example add-in 568

Creating an add-in 568

Installing an add-in 570

Testing the add-in 571

Distributing an add-in 571

Modifying an add-in 571

Comparing XLAM and XLSM Files 572

XLAM file VBA collection membership 573

Visibility of XLSM and XLAM files 573

Worksheets and chart sheets in XLSM and XLAM files 574

Accessing VBA procedures in an add-in 575

Manipulating Add-Ins with VBA 578

Adding an item to the AddIns collection 579

Removing an item from the AddIns collection 580

AddIn object properties 580

Accessing an add-in as a workbook 583

AddIn object events 584

Optimizing the Performance of Add-Ins 584

Special Problems with Add-Ins 585

Ensuring that an add-in is installed 585

Referencing other files from an add-in 587

Detecting the proper Excel version for your add-in 588

Chapter 17: Working with the Ribbon 589

Ribbon Basics 589

Customizing the Ribbon 591

Adding a button to the Ribbon 591

Adding a button to the Quick Access Toolbar 594

Understanding the limitations of Ribbon customization 595

Creating a Custom Ribbon 596

Adding a button to an existing tab 596

Adding a check box to an existing tab 602

Ribbon controls demo 605

A dynamicMenu control example 613

More on Ribbon customization 616

Using VBA with the Ribbon 617

Accessing a Ribbon control 617

Working with the Ribbon 619

Activating a tab 621

Creating an Old-Style Toolbar 621

Limitations of old-style toolbars in Excel 2007 and later 622

Code to create a toolbar 622

Chapter 18: Working with Shortcut Menus 625

CommandBar Overview 625

CommandBar types 626

Listing shortcut menus 626

Referring to CommandBars 627

Referring to controls in a CommandBar 628

Properties of CommandBar controls 630

Displaying all shortcut menu items 630

Using VBA to Customize Shortcut Menus 632

Shortcut menu and the single-document interface 633

Resetting a shortcut menu634

Disabling a shortcut menu636

Disabling shortcut menu items 636

Adding a new item to the Cell shortcut menu 637

Adding a submenu to a shortcut menu 639

Limiting a shortcut menu to a single workbook642

Shortcut Menus and Events 642

Adding and deleting menus automatically 642

Disabling or hiding shortcut menu items 643

Creating a context-sensitive shortcut menu 643

Chapter 19: Providing Help for Your Applications 647

Help for Your Excel Applications 647

Help Systems That Use Excel Components 649

Using cell comments for help 649

Using a text box for help650

Using a worksheet to display help text 652

Displaying help in a UserForm 653

Displaying Help in a Web Browser 657

Using HTML files 657

Using an MHTML file 658

Using the HTML Help System 659

Using the Help method to display HTML Help 662

Associating a help file with your application 663

Associating a help topic with a VBA function 663

Chapter 20: Leveraging Class Modules 665

What Is a Class Module? 665

Built-in class modules666

Custom class modules 666

Creating a NumLock Class 667

Inserting a class module 668

Adding VBA code to the class module 668

Using the CNumLock class 670

Coding Properties, Methods, and Events 671

Programming properties of objects 671

Programming methods for objects 673

Class module events 673

Exposing a QueryTable Event 674

Creating a Class to Hold Classes 677

Creating the CSalesRep and CSalesReps classes 677

Creating the CInvoice and CInvoices classes 679

Filling the parent classes with objects 680

Calculating the commissons 682

Chapter 21: Understanding Compatibility Issues 685

What Is Compatibility? 685

Types of Compatibility Problems 686

Avoid Using New Features 687

But Will It Work on a Mac? 689

Dealing with 64-Bit Excel 690

Creating an International Application 691

Multilanguage applications 693

VBA language considerations 694

Using local properties 694

Identifying system settings 695

Date and time settings 697

Part V: Appendix

Appendix A: VBA Statements and Function Reference 701

Index 709