Excel 2016 VBA and Macros

MASTER CORE EXCEL 2016 TOOLS FOR BUILDING POWERFUL, RELIABLE SPREADSHEETS!   Use this guide to automate virtually any routine task: save yourself hours, days, maybe even weeks! Make Excel do things you thought were impossible, discover macro techniques you won’t find anywhere else, and create automated reports that are amazingly powerful. Bill Jelen and Tracy Syrstad help you  instantly visualize information, so you can act on it… capture data from anywhere, and use it anywhere… automate Excel 2016’s best new features. You’ll find simple, step-by-step instructions, real-world case studies, and 50 workbooks packed with bonus examples, macros, and solutions–straight from MrExcel!   Get started fast with Excel macro development Work efficiently with ranges, cells, and formulas Build super-fast applications with arrays Automate Excel’s new pivot table enhancements Collect user data with custom dialogs Make your macros more reliable and resilient Pull data from the Internet with web queries Use advanced classes, collections, and custom functions Build sophisticated business analysis solutions Read and write to Access or SQL Server databases Control other Office programs, and Windows itself Write code that also works on older Excel versions Start writing Office Store-style Excel Apps   About MrExcel Library   Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will   Dramatically increase your productivity–saving you 50 hours a year or more Present proven, creative strategies for solving real-world problems Show you how to get great results, no matter how much data you have Help you avoid critical mistakes that even experienced users make This book is part of Que’s Content Update Program. As Microsoft updates features of Excel, sections of this book will be updated or new sections will be added to match the updates to the software. See inside for details.                

Table of Contents:
Introduction ....................................................................................1     What Is in This Book? ................................................................1         Reducing the Learning Curve ......................................1         Excel VBA Power .................................................................2         Techie Stuff Needed to Produce Applications ......................................................................................2         Does This Book Teach Excel? ........................................2     The Future of VBA and Windows Versions of Excel .....................................................................................4         Versions of Excel .................................................................4         Differences for Mac Users ..............................................4     Special Elements and Typographical Conventions ......................................................................................5     Code Files .........................................................................................5     Next Steps ........................................................................................5 1 Unleashing the Power of Excel with VBA .................................................................................7     The Power of Excel ......................................................................7     Barriers to Entry ...........................................................................7         The Macro Recorder Doesn’t Work! .........................7         No One on the Excel Team Is Focused on the Macro Recorder ..................................................8         Visual Basic Is Not Like BASIC......................................8         Good News: Climbing the Learning Curve Is Easy .............................................................................9         Great News: Excel with VBA Is Worth the Effort .................................................................................9     Knowing Your Tools: The Developer Tab .........................9     Understanding Which File Types Allow Macros .........................................................................................10     Macro Security ...........................................................................12         Adding a Trusted Location .........................................12         Using Macro Settings to Enable Macros in Workbooks Outside Trusted Locations ..................................................13         Using Disable All Macros with Notification.........................................................................................14     Overview of Recording, Storing, and Running a Macro ........................................................................14         Filling Out the Record Macro Dialog ....................15     Running a Macro ......................................................................16         Creating a Macro Button on the Ribbon ...............................................................................................16         Creating a Macro Button on the Quick Access Toolbar ................................................................17         Assigning a Macro to a Form Control, Text Box, or Shape ........................................................18     Understanding the VB Editor.............................................19         VB Editor Settings ...........................................................20         The Project Explorer .......................................................20         The Properties Window ...............................................21     Understanding Shortcomings of the Macro Recorder ...........................................................................21         Recording the Macro .....................................................23         Examining Code in the Programming Window ...............................................................................23         Running the Macro on Another Day Produces Undesired Results ........................................25         Possible Solution: Use Relative References When Recording ..................................................26         Never Use AutoSum or Quick Analysis While Recording a Macro .........................................30     Four Tips for Using the Macro Recorder................................................................................................31     Next Steps ............................................................................32 2 This Sounds Like BASIC, So Why Doesn’t It Look Familiar?.................................33     I Can’t Understand This Code .............................................33     Understanding the Parts of VBA “Speech” ...................................................................................................34     VBA Is Not Really Hard ..........................................................37         VBA Help Files: Using F1 to Find Anything .........................................................................................38         Using Help Topics ............................................................38     Examining Recorded Macro Code: Using the VB Editor and Help ...................................................39         Optional Parameters .....................................................39         Defined Constants ...........................................................40         Properties Can Return Objects .................................43     Using Debugging Tools to Figure Out Recorded Code ...........................................................................43         Stepping Through Code ...............................................43         More Debugging Options: Breakpoints .................................................................................................45         Backing Up or Moving Forward in Code ...............................................................................................45         Not Stepping Through Each Line of Code ............................................................................................46         Querying Anything While Stepping Through Code .......................................................................46         Using a Watch to Set a Breakpoint .......................49         Using a Watch on an Object ......................................49     Object Browser: The Ultimate Reference ....................50     Seven Tips for Cleaning Up Recorded Code..................................................................................................51         Tip 1: Don’t Select Anything .....................................51         Tip 2: Use Cells(2,5) Because It’s More Convenient Than Range("E2") ..............................................................52         Tip 3: Use More Reliable Ways to Find the Last Row ....................................................................52         Tip 4: Use Variables to Avoid Hard-Coding Rows and Formulas............................................53         Tip 5: Use R1C1 Formulas That Make Your Life Easier .................................................................54         Tip 6: Copy and Paste in a Single Statement .....................................................................................54         Tip 7: Use With...End With to Perform Multiple Actions ....................................54     Next Steps .....................................................................................57 3 Referring to Ranges....................................................59     The Range Object ................................................................59     Syntax for Specifying a Range ..........................................60     Named Ranges ...........................................................................60     Shortcut for Referencing Ranges ....................................60     Referencing Ranges in Other Sheets ............................61     Referencing a Range Relative to Another Range .....................................................................................61     Using the Cells Property to Select a Range ........................................................................................62     Using the Offset Property to Refer to a Range ................................................................................63     Using the Resize Property to Change the Size of a Range ........................................................65     Using the Columns and Rows Properties to Specify a Range ...............................................66     Using the Union Method to Join Multiple Ranges ............................................................................66     Using the Intersect Method to Create a New Range from Overlapping Ranges ...........................................................67     Using the IsEmpty Function to Check Whether a Cell Is Empty .............................................67     Using the CurrentRegion Property to Select a Data Range ............................................68     Using the Areas Collection to Return a Noncontiguous Range ................................................70     Referencing Tables ...................................................................71     Next Steps .....................................................................................72 4 Looping and Flow Control ....................................73     For...Next Loops .......................................................73         Using Variables in the For Statement ...............................................................................................75         Variations on the For...Next Loop ...........................................................................................76         Exiting a Loop Early After a Condition Is Met ....................................................................................77         Nesting One Loop Inside Another Loop ................................................................................................78     Do Loops ......................................................................................78         Using the While or Until Clause in Do Loops ....................................................................81     The VBA Loop: For Each ...........................................82         Object Variables................................................................83     Flow Control: Using If...Then...Else and Select Case ............................86         Basic Flow Control: If...Then...Else ...........................................................................86         Using Select Case...End Select for Multiple Conditions ....................88     Next Steps .....................................................................................91 5 R1C1-Style Formulas ..................................................93     Referring to Cells: A1 Versus R1C1 References ...........................................................................................93     Toggling to R1C1-Style References ................................94     Witnessing the Miracle of Excel Formulas ....................................................................................................95         Entering a Formula Once and Copying 1,000 Times .....................................................................95         The Secret: It’s Not That Amazing .........................96     Understanding the R1C1 Reference Style.....................................................................................................97         Using R1C1 with Relative References.....................................................................................................97         Using R1C1 with Absolute References ...................................................................................................98         Using R1C1 with Mixed References ......................98         Referring to Entire Columns or Rows with R1C1 Style.................................................................99         Replacing Many A1 Formulas with a Single R1C1 Formula ......................................................99         Remembering Column Numbers Associated with Column Letters ...................................101     Using R1C1 Formulas with Array Formulas ..............................................................................................101     Next Steps ..................................................................................102 6 Creating and Manipulating Names in VBA .......................................................................103     Global Versus Local Names ..............................................103     Adding Names .........................................................................104     Deleting Names ......................................................................105     Adding Comments ................................................................106     Types of Names .......................................................................106         Formulas ............................................................................106         Strings .................................................................................107         Numbers ............................................................................108         Tables...................................................................................109         Using Arrays in Names ..............................................109         Reserved Names ............................................................110     Hiding Names ..........................................................................111     Checking for the Existence of a Name ......................111     Next Steps ..................................................................................114 7 Event Programming ...............................................115     Levels of Events ......................................................................115     Using Events .............................................................................116         Event Parameters .........................................................116         Enabling Events .............................................................117     Workbook Events ...................................................................117         Workbook-Level Sheet and Chart Events .........................................................................................119     Worksheet Events ..................................................................120     Chart Events ..............................................................................123         Embedded Charts .........................................................123         Embedded Chart and Chart Sheet Events .........................................................................................124     Application-Level Events...................................................125     Next Steps ..................................................................................130 8 Arrays ......................................................................................131     Declaring an Array ................................................................131     Declaring a Multidimensional Array ..........................132     Filling an Array ........................................................................133     Retrieving Data from an Array.......................................134     Using Arrays to Speed Up Code .....................................135     Using Dynamic Arrays .........................................................136     Passing an Array .....................................................................137     Next Steps ..................................................................................138 9 Creating Classes and Collections..................................................................................................139     Inserting a Class Module ...................................................139     Trapping Application and Embedded Chart Events .............................................................................140         Application Events .......................................................140         Embedded Chart Events ...........................................141     Creating a Custom Object .................................................143     Using a Custom Object .......................................................145     Using Collections ...................................................................145         Creating a Collection ..................................................146         Creating a Collection in a Standard Module....................................................................................146         Creating a Collection in a Class Module .............................................................................................148     Using Dictionaries .................................................................150     Using User-Defined Types to Create Custom Properties ....................................................................153     Next Steps ..................................................................................156 10 Userforms: An Introduction ...........................157     Input Boxes ...............................................................................157     Message Boxes ........................................................................158     Creating a Userform .............................................................158     Calling and Hiding a Userform ......................................159     Programming Userforms ..................................................160         Userform Events ............................................................160     Programming Controls .......................................................162     Using Basic Form Controls ................................................163         Using Labels, Text Boxes, and Command Buttons ......................................................................163         Deciding Whether to Use List Boxes or Combo Boxes in Forms ..........................................165         Adding Option Buttons to a Userform ................................................................................................167         Adding Graphics to a Userform ............................169         Using a Spin Button on a Userform ......................................................................................................170         Using the MultiPage Control to Combine Forms .............................................................171     Verifying Field Entry ............................................................174     Illegal Window Closing ......................................................174     Getting a Filename ...............................................................175     Next Steps ..................................................................................176 11 Data Mining with Advanced Filter.............................................................................................177     Replacing a Loop with AutoFilter ................................177         Using AutoFilter Techniques ..................................180         Selecting Visible Cells Only .....................................183     Advanced Filter—Easier in VBA Than in Excel .......................................................................................184         Using the Excel Interface to Build an Advanced Filter ..............................................................185     Using Advanced Filter to Extract a Unique List of Values .................................................................186         Extracting a Unique List of Values with the User Interface ...................................................186         Extracting a Unique List of Values with VBA Code ......................................................................187         Getting Unique Combinations of Two or More Fields ...............................................................191     Using Advanced Filter with Criteria Ranges .............................................................................................192         Joining Multiple Criteria with a Logical OR .....................................................................................193         Joining Two Criteria with a Logical AND ...........................................................................................194         Other Slightly Complex Criteria Ranges .............................................................................................194         The Most Complex Criteria: Replacing the List of Values with a Condition Created as the Result of a Formula ......................194     Using Filter in Place in Advanced Filter ....................201         Catching No Records When Using a Filter in Place .....................................................................202         Showing All Records After Running a Filter in Place .................................................................202     The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only ..............................................203         Copying All Columns ...................................................203         Copying a Subset of Columns and Reordering ..............................................................................204         Excel in Practice: Turning Off a Few Drop-downs in the AutoFilter ..................................209     Next Steps ..................................................................................210 12 Using VBA to Create Pivot Tables ...............................................................................................211     Understanding How Pivot Tables Evolved Over Various Excel Versions ....................................211     While Building a Pivot Table in Excel VBA .................................................................................................212         Defining the Pivot Cache .........................................212         Creating and Configuring the Pivot Table ........................................................................................213         Adding Fields to the Data Area ............................214         Learning Why You Cannot Move or Change Part of a Pivot Report..................................216         Determining the Size of a Finished Pivot Table to Convert the Pivot Table to Values .............................................217     Using Advanced Pivot Table Features ........................219         Using Multiple Value Fields ....................................220         Grouping Daily Dates to Months, Quarters, or Years .................................................................221         Changing the Calculation to Show Percentages ..........................................................................222         Eliminating Blank Cells in the Values Area ......................................................................................225         Controlling the Sort Order with AutoSort .........................................................................................225         Replicating the Report for Every Product .........................................................................................225     Filtering a Data Set ...............................................................228         Manually Filtering Two or More Items in a Pivot Field ............................................................228         Using the Conceptual Filters ..................................229         Using the Search Filter ..............................................233         Setting Up Slicers to Filter a Pivot Table ...........................................................................................235         Setting Up a Timeline to Filter an Excel 2016 Pivot Table ......................................................239     Using the Data Model in Excel 2016 ..........................242         Adding Both Tables to the Data Model ..............................................................................................242         Creating a Relationship Between the Two Tables .......................................................................243         Defining the PivotCache and Building the Pivot Table.............................................................243         Adding Model Fields to the Pivot Table .............................................................................................244         Adding Numeric Fields to the Values Area ......................................................................................244         Putting It All Together...............................................245     Using Other Pivot Table Features .................................247         Calculated Data Fields ...............................................247         Calculated Items ...........................................................247         Using ShowDetail to Filter a Record Set ..............................................................................248         Changing the Layout from the Design Tab ......................................................................................248         Settings for the Report Layout .............................248         Suppressing Subtotals for Multiple Row Fields.............................................................................249     Next Steps ..................................................................................250 13 Excel Power ......................................................................251     File Operations ........................................................................251         Listing Files in a Directory .......................................251         Importing and Deleting a CSV File .....................254         Reading a Text File into Memory and Parsing ...............................................................................254     Combining and Separating Workbooks ......................................................................................................255         Separating Worksheets into Workbooks...........................................................................................255         Combining Workbooks..............................................256         Filtering and Copying Data to Separate Worksheets .................................................................257         Copying Data to Separate Worksheets Without Using Filter ................................................258         Exporting Data to an XML File ..............................259     Working with Cell Comments ........................................260         Resizing Comments.....................................................260         Placing a Chart in a Comment ..............................261     Selecting Cells .........................................................................263         Using Conditional Formatting to Highlight the Selected Cell ..............................................263         Highlighting the Selected Cell Without Using Conditional Formatting .........................264         Selecting/Deselecting Noncontiguous Cells ....................................................................................265         Creating a Hidden Log File......................................267     Techniques for VBA Pros ....................................................268         Creating an Excel State Class Module .................................................................................................268         Drilling-Down a Pivot Table ...................................270         Filtering an OLAP Pivot Table by a List of Items ...........................................................................271         Creating a Custom Sort Order ...............................273         Creating a Cell Progress Indicator ......................274         Using a Protected Password Box ........................275         Changing Case ................................................................277         Selecting with SpecialCells .....................................279         Resetting a Table’s Format .....................................279     Cool Applications ...................................................................280         Getting Historical Stock/Fund Quotes .................................................................................................280         Using VBA Extensibility to Add Code to New Workbooks .......................................................281     Next Steps ..................................................................................282 14 Sample User-Defined Functions ...................................................................................................283     Creating User-Defined Functions .................................283     Sharing UDFs ............................................................................286     Useful Custom Excel Functions ......................................286         Setting the Current Workbook’s Name in a Cell ...........................................................................286         Setting the Current Workbook’s Name and File Path in a Cell ............................................287         Checking Whether a Workbook Is Open ............................................................................................287         Checking Whether a Sheet in an Open Workbook Exists ........................................................287         Counting the Number of Workbooks in a Directory ...................................................................288         Retrieving the User ID................................................289         Retrieving Date and Time of Last Save ..............................................................................................291         Retrieving Permanent Date and Time ................................................................................................291         Validating an Email Address ..................................292         Summing Cells Based on Interior Color ..............................................................................................293         Counting Unique Values ...........................................294         Removing Duplicates from a Range .....................................................................................................295         Finding the First Nonzero-Length Cell in a Range ......................................................................296         Substituting Multiple Characters ........................297         Retrieving Numbers from Mixed Text .................................................................................................298         Converting Week Number into Date ...................................................................................................299         Extracting a Single Element from a Delimited String ...............................................................300         Sorting and Concatenating ....................................300         Sorting Numeric and Alpha Characters ..............................................................................................302         Searching for a String Within Text.....................303         Reversing the Contents of a Cell .........................304         Returning the Addresses of Duplicate Max Values .....................................................................304         Returning a Hyperlink Address ............................305         Returning the Column Letter of a Cell Address .............................................................................306         Using Static Random ..................................................306         Using Select Case on a Worksheet .....................................................................................307     Next Steps ..................................................................................308 15 Creating Charts ............................................................309     Contrasting the Good and Bad VBA to Create Charts..........................................................................309     Planning for More Charts to Break .............................310     Using .AddChart2 to Create a Chart ................................................................................................311     Understanding Chart Styles ............................................312     Formatting a Chart ...............................................................315         Referring to a Specific Chart ..................................315         Specifying a Chart Title .............................................316         Applying a Chart Color ..............................................317         Filtering a Chart .............................................................318         Using SetElement to Emulate Changes from the Plus Icon .....................................319         Using the Format Method to Micromanage Formatting Options ..............................324         Changing an Object’s Fill .........................................325         Formatting Line Settings .........................................327     Creating a Combo Chart ....................................................327     Exporting a Chart as a Graphic ......................................330     Considering Backward Compatibility ........................331     Next Steps ..................................................................................331 16 Data Visualizations and Conditional Formatting .....................................................333     VBA Methods and Properties for Data Visualizations .........................................................................334     Adding Data Bars to a Range .........................................335     Adding Color Scales to a Range ....................................339     Adding Icon Sets to a Range ...........................................341         Specifying an Icon Set ...............................................341         Specifying Ranges for Each Icon ..........................343     Using Visualization Tricks ..................................................343         Creating an Icon Set for a Subset of a Range .................................................................................344         Using Two Colors of Data Bars in a Range ........................................................................................345     Using Other Conditional Formatting Methods .......................................................................................347         Formatting Cells That Are Above or Below Average ..................................................................348         Formatting Cells in the Top 10 or Bottom 5 ....................................................................................348         Formatting Unique or Duplicate Cells .................................................................................................349         Formatting Cells Based on Their Value ..............................................................................................350         Formatting Cells That Contain Text ......................................................................................................351         Formatting Cells That Contain Dates ...................................................................................................351         Formatting Cells That Contain Blanks or Errors ............................................................................351         Using a Formula to Determine Which Cells to Format .............................................................352         Using the New NumberFormat Property .............................................................................353     Next Steps ..................................................................................354 17 Dashboarding with Sparklines in Excel 2016 ................................................................355     Creating Sparklines ..............................................................356     Scaling Sparklines .................................................................357     Formatting Sparklines ........................................................361         Using Theme Colors ....................................................361         Using RGB Colors ...........................................................364         Formatting Sparkline Elements ...........................365         Formatting Win/Loss Charts ..................................368     Creating a Dashboard .........................................................369         Observations About Sparklines ............................369         Creating Hundreds of Individual Sparklines in a Dashboard.................................................370     Next Steps ..................................................................................374 18 Reading from and Writing to the Web .................................................................................375     Getting Data from the Web .............................................375         Building Multiple Queries with VBA ....................................................................................................377         Finding Results from Retrieved Data ...................................................................................................378         Putting It All Together...............................................379         Examples of Scraping Websites Using Web Queries ..................................................................380     Using Application.OnTime to Periodically Analyze Data ...............................................381         Using Ready Mode for Scheduled Procedures ...............................................................................381         Specifying a Window of Time for an Update ..................................................................................382         Canceling a Previously Scheduled Macro ..........................................................................................382         Closing Excel Cancels All Pending Scheduled Macros ................................................................383         Scheduling a Macro to Run x Minutes in the Future .................................................................383         Scheduling a Verbal Reminder .............................383         Scheduling a Macro to Run Every Two Minutes............................................................................384     Publishing Data to a Web Page .....................................385         Using VBA to Create Custom Web Pages ..........................................................................................386         Using Excel as a Content Management System ............................................................................387         Bonus: FTP from Excel ...............................................389     Next Steps ..................................................................................390 19 Text File Processing ................................................391     Importing from Text Files .................................................391         Importing Text Files with Fewer Than 1,048,576 Rows ..........................................................391         Dealing with Text Files with More Than 1,048,576 Rows ......................................................398     Writing Text Files ...................................................................402     Next Steps ..................................................................................403 20 Automating Word .....................................................405     Using Early Binding to Reference a Word Object ...................................................................................406     Using Late Binding to Reference a Word Object ....................................................................................408     Using the New Keyword to Reference a Word Application ............................................................409     Using the CreateObject Function to Create a New Instance of an Object ............409     Using the GetObject Function to Reference an Existing Instance of Word ...............410     Using Constant Values ........................................................411         Using the Watches Window to Retrieve the Real Value of a Constant...........................411         Using the Object Browser to Retrieve the Real Value of a Constant ................................412     Understanding Word’s Objects ......................................413         The Document Object ........................................413         The Selection Object ....................................415         The Range Object ....................................................416         Bookmarks ........................................................................419     Controlling Form Fields in Word ...................................420     Next Steps ..................................................................................422 21 Using Access as a Back End to Enhance Multiuser Access to Data ...........423     ADO Versus DAOs ...................................................................424     The Tools of ADO ....................................................................426     Adding a Record to a Database .....................................427     Retrieving Records from a Database ..........................429     Updating an Existing Record ..........................................431     Deleting Records via ADO .................................................433     Summarizing Records via ADO ......................................433     Other Utilities via ADO........................................................434         Checking for the Existence of Tables ...................................................................................................434         Checking for the Existence of a Field ...................................................................................................435         Adding a Table On the Fly .......................................436         Adding a Field On the Fly ........................................436     SQL Server Examples ...........................................................437     Next Steps ..................................................................................438 22 Advanced Userform Techniques ...................................................................................................439     Using the UserForm Toolbar in the Design of Controls on Userforms .......................................439     More Userform Controls ....................................................440         Checkbox Controls .............................................440     Controls and Collections ....................................................447     Modeless Userforms ............................................................449     Using Hyperlinks in Userforms ......................................449     Adding Controls at Runtime............................................450         Resizing the Userform On the Fly .......................452         Adding a Control On the Fly ...................................452         Sizing On the Fly ...........................................................452         Adding Other Controls ...............................................453         Adding an Image On the Fly ..................................453         Putting It All Together...............................................454     Adding Help to a Userform ..............................................456         Showing Accelerator Keys .......................................456         Adding Control Tip Text ............................................457         Creating the Tab Order ..............................................457         Coloring the Active Control ....................................457     Creating Transparent Forms ............................................460     Next Steps ..................................................................................461 23 The Windows Application Programming Interface (API) .................................463     Understanding an API Declaration .............................464     Using an API Declaration ..................................................465     Making 32-Bit- and 64-Bit-Compatible API Declarations ...............................................................465     API Function Examples ......................................................467         Retrieving the Computer Name ...........................467         Checking Whether an Excel File Is Open on a Network ............................................................467         Retrieving Display-Resolution Information .....................................................................................468         Customizing the About Dialog .............................469         Disabling the X for Closing a Userform ..............................................................................................470         Creating a Running Timer .......................................471         Playing Sounds ..............................................................472     Next Steps ..................................................................................472 24 Handling Errors ............................................................473     What Happens When an Error Occurs? .....................473         A Misleading Debug Error in Userform Code ..................................................................................475     Basic Error Handling with the On Error GoTo Syntax .....................................................477     Generic Error Handlers .......................................................478         Handling Errors by Choosing to Ignore Them ................................................................................479         Suppressing Excel Warnings ..................................481         Encountering Errors on Purpose ..........................481     Training Your Clients ............................................................481     Errors While Developing Versus Errors Months Later ..........................................................................482         Runtime Error 9: Subscript Out of Range ..........................................................................................482         Runtime Error 1004: Method Range of Object Global Failed ................................................483     The Ills of Protecting Code ...............................................484     More Problems with Passwords ...................................485     Errors Caused by Different Versions ............................486     Next Steps ..................................................................................486 25 Customizing the Ribbon to Run Macros ..............................................................................487     Where to Add Code: The customui Folder and File ...............................................................................488     Creating a Tab and a Group .............................................489     Adding a Control to a Ribbon .........................................490     Accessing the File Structure ............................................496     Understanding the RELS File ..........................................496     Renaming an Excel File and Opening a Workbook ...............................................................................497     Using Images on Buttons .................................................497         Using Microsoft Office Icons on a Ribbon .........................................................................................498         Adding Custom Icon Images to a Ribbon .........................................................................................499     Troubleshooting Error Messages ..................................500         The Attribute “Attribute Name” on the Element “customui Ribbon” Is Not Defined in the DTD/Schema ............................500         Illegal Qualified Name Character ........................501         Element “customui Tag Name” Is Unexpected According to Content Model of Parent Element “customui Tag Name”...............501         Found a Problem with Some Content ................................................................................................502         Wrong Number of Arguments or Invalid Property Assignment .........................................503         Invalid File Format or File Extension ...................................................................................................503         Nothing Happens .........................................................503     Other Ways to Run a Macro .............................................504         Using a Keyboard Shortcut to Run a Macro .....................................................................................504         Attaching a Macro to a Command Button .......................................................................................504         Attaching a Macro to a Shape ..............................505         Attaching a Macro to an ActiveX Control ..........................................................................................506         Running a Macro from a Hyperlink ......................................................................................................507     Next Steps ..................................................................................508 26 Creating Add-ins .........................................................509     Characteristics of Standard Add-ins ...........................509     Converting an Excel Workbook to an Add-in ...........................................................................................510         Using Save As to Convert a File to an Add-in .................................................................................511         Using the VB Editor to Convert a File to an Add-in .....................................................................512     Having a Client Install an Add-in .................................512     Closing Add-ins .......................................................................514     Removing Add-ins ................................................................514     Using a Hidden Workbook as an Alternative to an Add-in ..............................................................515     Next Steps ..................................................................................516 27 An Introduction to Creating Office Add-ins .....................................................................517     Creating Your First Office Add-in—Hello World ....................................................................................517     Adding Interactivity to an Office Add-in ....................................................................................................521     A Basic Introduction to HTML ........................................524         Using Tags.........................................................................524         Adding Buttons ..............................................................524         Using CSS Files ...............................................................525     Using XML to Define an Office Add-in ......................525     Using JavaScript to Add Interactivity to an Office Add-in ................................................................526         The Structure of a Function ....................................526         Variables ............................................................................527         Strings .................................................................................528         Arrays ...................................................................................528         JavaScript for Loops ..............................................529         How to Do an if Statement in JavaScript .....................................................................................530         How to Do a Select..Case Statement in JavaScript ................................................530         How to Do a For each..next Statement in JavaScript ........................................532         Mathematical, Logical, and Assignment Operators ...................................................................532         Math Functions in JavaScript ................................534         Writing to the Content Pane or Task Pane ......................................................................................535         JavaScript Changes for Working in an Office Add-in .................................................................535     Napa Office 365 Development Tools ..........................536     Next Steps ..................................................................................537 28 What’s New in Excel 2016 and What’s Changed .........................................................539     If It Has Changed in the Front End, It Has Changed in VBA .............................................................539         The Ribbon .......................................................................539         Single Document Interface (SDI).........................540         Quick Analysis Tool ......................................................541         Charts ...................................................................................541         Pivot Tables ......................................................................541         Slicers ...................................................................................541         SmartArt ............................................................................542     Learning the New Objects and Methods ....................................................................................................542     Compatibility Mode .............................................................542         Using the Version Property .........................543         Using the Excel8CompatibilityMode Property .............................................543     Next Steps ..................................................................................544 TOC, 9780789755858, 10/19/2015