Excel 2016 power programming with VBA

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

Maximize your Excel experience with VBA ; Excel 2016 Power Programming with VBA is fully updated to cover all the latest tools and tricks of Excel 2016. Encompassing an analysis of Excel application development and a complete introduction to Visual Basic for Applications (VBA), this comprehensive book presents all of the techniques you need to develop both large and small Excel applications. Over 800 pages of tips, tricks, and best practices shed light on key topics, such as the Excel interface, file formats, enhanced interactivity with other Office applications, and improved collaboration features. In addition to the procedures, tips, and ideas that will expand your capabilities, this resource provides you with access to over 100 online example Excel workbooks and the Power Utility Pak, found on the Mr. Spreadsheet website. ; Understanding how to leverage VBA to improve your Excel programming skills can enhance the quality of deliverables that you produce-and can help you take your career to the next level. ; Explore fully updated content that offers comprehensive coverage through over 900 pages of tips, tricks, and techniques ; Leverage templates and worksheets that put your new knowledge in action, and reinforce the skills introduced in the text ; Access online resources, including the Power Utility Pak, that supplement the content ; Improve your capabilities regarding Excel programming with VBA, unlocking more of your potential in the office ; Excel 2016 Power Programming with VBA is a fundamental resource for intermediate to advanced users who want to polish their skills regarding spreadsheet applications using VBA.