SAVE TIME AND SUPERCHARGE EXCEL 2013 WITH VBA AND MACROS!   Use Excel® 2013 VBA and Macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! You’ll discover macro techniques you won’t find anywhere else and learn how to create automated reports that are amazingly powerful and useful. Bill Jelen and Tracy Syrstad show how to instantly visualize information, so you and your colleagues can understand and act on it…how to capture data from anywhere, and use it anywhere…how to automate Excel 2013’s most valuable new features. Mastering advanced Excel macros has never been easier. You’ll find simple, step-by-step instructions, real-world examples and case studies, and 50 workbooks packed with bonus examples, macros, and solutions–straight from MrExcel.   •   Get started fast with Excel 2013 macro development •   Write macros that use Excel 2013 enhancements, including Timelines and the latest pivot table models •   Work efficiently with ranges, cells, and R1C1-style formulas •   Build super-fast applications with arrays •   Write Excel 2013 VBA code that works on older versions of Excel •   Create custom dialog boxes to collect information from your users •   Use error handling to make your macros more resilient •   Use web queries and new web service functions to integrate data from anywhere •   Master advanced techniques such as classes, collections, and custom functions •   Build sophisticated data mining and business analysis applications •   Read and write to both Access and SQL Server databases •   Control other Office programs–and even control Windows itself •   Start writing Excel Apps similar to those in the Excel App Store   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  

Table of Contents:
Introduction ........................................................ 1     Getting Results with VBA ...................................................................1     What Is in This Book? .................................................................1         Reduce 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     Special Elements and Typographical Conventions .........................................4     Code Files .........................................................................................................5     Next Steps..........................................................................5 1 Unleash the Power of Excel with VBA ......................................... 7     The Power of Excel .....................................................................7     Barriers to Entry .......................................................................7         The Macro Recorder Doesn’t Work! ...................................................7         Visual Basic Is Not Like BASIC ..............................................................8         Good News: Climbing the Learning Curve Is Easy ....................................8         Great News: Excel with VBA Is Worth the Effort ..................................8     Knowing Your Tools: The Developer Tab .............................................................9     Understanding Which File Types Allow Macros .........................................10     Macro Security ...........................................................................................11         Adding a Trusted Location ........................................................................12         Using Macro Settings to Enable Macros in Workbooks Outside of Trusted Locations ........................13         Using Disable All Macros with Notification ...............................................13     Overview of Recording, Storing, and Running a Macro ......................................14         Filling Out the Record Macro Dialog ..................................................................14     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         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 the AutoSum or Quick Analysis While Recording a Macro .....................30         Three Tips When Using the Macro Recorder ..............................................31     Next Steps..................................................................................31 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 ....................................37         Using Help Topics ........................................................................38     Examining Recorded Macro Code: Using the VB Editor and Help .............................39         Optional Parameters ..............................................40         Defined Constants ..........................................................41         Properties Can Return Objects ...................................45     Using Debugging Tools to Figure Out Recorded Code .........................46         Stepping Through Code .................................................................46         More Debugging Options: Breakpoints .......................................49         Backing Up or Moving Forward in Code .................................49         Not Stepping Through Each Line of Code.....................................50         Querying Anything While Stepping Through Code ..........................50         Using a Watch to Set a Breakpoint .........................................53         Using a Watch on an Object ...........................54     Object Browser: The Ultimate Reference ....................................55     Seven Tips for Cleaning Up Recorded Code ................................56         Tip 1: Don’t Select Anything .............................56         Tip 2: Cells(2,5) Is More Convenient Than Range(“E2”) ..........................57         Tip 3: Use More Reliable Ways to Find the Last Row .......................................58         Tip 4: Use Variables to Avoid Hard-Coding Rows and Formulas ......................59         Tip 5: R1C1 Formulas That Make Your Life Easier .............................59         Tip 6: Learn to Copy and Paste in a Single Statement................................59         Tip 7: Use With...End With to Perform Multiple Actions .................60     Next Steps............................................................................................63     3 Referring to Ranges ......................................................65     The Range Object ........................................................................65     Syntax to Specify a Range ................................................................66     Named Ranges ........................................................................................66     Shortcut for Referencing Ranges .................................................................66     Referencing Ranges in Other Sheets ..................................................67     Referencing a Range Relative to Another Range ............................67     Use the Cells Property to Select a Range .................................................68     Use the Offset Property to Refer to a Range ..............................................69     Use the Resize Property to Change the Size of a Range ........................71     Use the Columns and Rows Properties to Specify a Range ......................72     Use the Union Method to Join Multiple Ranges ................................................72     Use the Intersect Method to Create a New Range from Overlapping Ranges..........................72     Use the ISEMPTY Function to Check Whether a Cell Is Empty ............................................73     Use the CurrentRegion Property to Select a Data Range .....................................73     Use the Areas Collection to Return a Noncontiguous Range .........................................76     Referencing Tables ............................................................................77     Next Steps..................................................................................................77 4 Looping and Flow Control ................................................................79     For...Next Loops ............................................................................................79         Using Variables in the For Statement ..............................................................82         Variations on the For...Next Loop .................................................................82         Exiting a Loop Early After a Condition Is Met ....................................................83         Nesting One Loop Inside Another Loop .........................................................84     Do Loops .....................................................................................................85         Using the While or Until Clause in Do Loops ........................................87         While...Wend Loops .............................................................................88     The VBA Loop: For Each ............................................................................89         Object Variables .........................................................................................89     Flow Control: Using If...Then...Else and Select Case ...........................................92         Basic Flow Control: If...Then...Else ................................................92         Conditions ..............................................................................................92         If...Then...End If ....................................................................................93         Either/Or Decisions: If...Then...Else...End If .............................................93         Using If...ElseIf...End If for Multiple Conditions .....................................93         Using Select Case...End Select for Multiple Conditions ...............................94         Complex Expressions in Case Statements ........................................................95         Nesting If Statements .....................................................................95     Next Steps..............................................................................................................97 5 R1C1-Style Formulas ................................................................99     Referring to Cells: A1 Versus R1C1 References ...........................99     Toggling to R1C1-Style References ................................................100     The Miracle of Excel Formulas ...............................................................101         Enter a Formula Once and Copy 1,000 Times ....................................101         The Secret: It’s Not That Amazing ............................................................102     Explanation of R1C1 Reference Style ..............................................................103         Using R1C1 with Relative References....................................................104         Using R1C1 with Absolute References ......................................................104         Using R1C1 with Mixed References...............................................................105         Referring to Entire Columns or Rows with R1C1 Style .....................................................105         Replacing Many A1 Formulas with a Single R1C1 Formula ...................................106         Remembering Column Numbers Associated with Column Letters ................................107     Array Formulas Require R1C1 Formulas .................................................................108     Next Steps...................................................................................................................109 6 Create and Manipulate Names in VBA ....................................................111     Excel Names .....................................................................................111     Global Versus Local Names .............................................................111     Adding Names ...................................................................................112     Deleting Names ................................................................................113     Adding Comments .......................................................................114     Types of Names .................................................................114         Formulas ...........................................................................114         Strings .........................................................................................115         Numbers ........................................................................................116         Tables ................................................................................................117         Using Arrays in Names .........................................................................117         Reserved Names .....................................................................................118     Hiding Names ................................................................................................119     Checking for the Existence of a Name ...............................................................119     Next Steps............................................................................................121 7 Event Programming ..............................................................123     Levels of Events ...................................................................123     Using Events ..........................................................................124         Event Parameters ...................................................................124         Enabling Events .................................................................125     Workbook Events ..............................................................................125         Workbook Level Sheet and Chart Events ...................................................129     Worksheet Events ...................................................................132     Chart Sheet Events .................................................................................137         Embedded Charts ...........................................................................137     Application-Level Events ................................................................................140     Next Steps........................................................................................................................148 8 Arrays ....................................................................................149     Declare an Array ................................................................149     Declare a Multidimensional Array ................................150     Fill an Array......................................................................151     Retrieve Data from an Array .............................................................152     Use Arrays to Speed Up Code ........................................................153     Use Dynamic Arrays ..................................................................................155     Passing an Array .................................................................................156     Next Steps................................................................................................................................157 9 Creating Classes, Records, and Collections ......................................................159     Inserting a Class Module ...............................................................................159     Trapping Application and Embedded Chart Events ..........................................159         Application Events ...........................................................................................160         Embedded Chart Events ..................................................................................161     Creating a Custom Object .....................................................................163     Using a Custom Object .............................................................................163     Using Property Let and Property Get to Control How Users Utilize Custom Objects .......................................165     Using Collections to Hold Multiple Records ...............................................................................167         Creating a Collection in a Standard Module ..................................................167         Creating a Collection in a Class Module .........................................................168     Using User-Defined Types to Create Custom Properties .............................................172     Next Steps.......................................................................................................................174 10 Userforms: An Introduction ...........................................................175     User Interaction Methods ..........................................................................175         Input Boxes..............................................................................................175         Message Boxes .......................................................................................176     Creating a Userform ......................................................................... 176     Calling and Hiding a Userform ................................................177     Programming the Userform ................................................................178         Userform Events .............................................................................178     Programming Controls .....................................................................................180     Using Basic Form Controls...................................................................................181         Using Labels, Text Boxes, and Command Buttons .....................................................181         Deciding Whether to Use List Boxes or Combo Boxes in Forms ...............................183         Adding Option Buttons to a Userform ...........................................................186         Adding Graphics to a Userform ....................................................................187         Using a Spin Button on a Userform ...........................................188         Using the MultiPage Control to Combine Forms .........................................190     Verifying Field Entry ....................................................................................................192     Illegal Window Closing ............................................................................192     Getting a Filename ..............................................................................................193     Next Steps..........................................................................................................................195 11 Data Mining with Advanced Filter .....................................................197     Replacing a Loop with AutoFilter ............................................................................197         Using New AutoFilter Techniques ............................................................200         Selecting Visible Cells Only ..........................................................203     Advanced Filter Is Easier in VBA Than in Excel ......................................................204         Using the Excel Interface to Build an Advanced Filter ....................................205     Using Advanced Filter to Extract a Unique List of Values ..............................................206         Extracting a Unique List of Values with the User Interface ........................206         Extracting a Unique List of Values with VBA Code ..................................................207         Getting Unique Combinations of Two or More Fields ..............................................211     Using Advanced Filter with Criteria Ranges .................................................................212         Joining Multiple Criteria with a Logical OR ................................................................213         Joining Two Criteria with a Logical AND ..............................................214         Other Slightly Complex Criteria Ranges .....................................214         The Most Complex Criteria: Replacing the List of Values with a Condition Created as the Result of a Formula ...214     Using Filter in Place in Advanced Filter ............................................221         Catching No Records When Using Filter in Place..................222         Showing All Records After Filter in Place .............................. 222     The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only ...............222         Copying All Columns .............................................................223         Copying a Subset of Columns and Reordering ............................224         Excel in Practice: Turning Off a Few Drop-Downs in the AutoFilter .......................229     Next Steps............................................................................................................230 12 Using VBA to Create Pivot Tables...................................231     Introducing Pivot Tables .............................................................231     Understanding Versions ...................................................................231     Building a Pivot Table in Excel VBA ..........................................................232         Defining the Pivot Cache ..........................................................................232         Creating and Configuring the Pivot Table ...............................................233         Adding Fields to the Data Area ....................................................................234         Learning Why You Cannot Move or Change Part of a Pivot Report ...............................237         Determining the Size of a Finished Pivot Table to Convert the Pivot Table to Values .................238     Using Advanced Pivot Table Features .............................................240         Using Multiple Value Fields ........................................................240         Grouping Daily Dates to Months, Quarters, or Years ......................241         Changing the Calculation to Show Percentages ..........................243         Eliminating Blank Cells in the Values Area...................................246         Controlling the Sort Order with AutoSort .........................................246         Replicating the Report for Every Product ...................................246     Filtering a Dataset ...........................................249         Manually Filtering Two or More Items in a Pivot Field ....................249         Using the Conceptual Filters .......................................250         Using the Search Filter ........................................................254         Setting Up Slicers to Filter a Pivot Table .......................................................257         Setting Up a Timeline to Filter an Excel 2013 Pivot Table ...............................259     Using the Data Model in Excel 2013 ................................................262         Adding Both Tables to the Data Model .........................................262         Creating a Relationship Between the Two Tables ...........................263         Defining the PivotCache and Building the Pivot Table ...............................263         Adding Model Fields to the Pivot Table ..................................................264         Adding Numeric Fields to the Values Area ............................264         Putting It All Together ................................................265     Using Other Pivot Table Features ................................267         Calculated Data Fields...........................................267         Calculated Items .............................................................268         Using ShowDetail to Filter a Recordset ...............................268         Changing the Layout from the Design Tab ..............................268         Settings for the Report Layout ................................. 269         Suppressing Subtotals for Multiple Row Fields .................... 269     Next Steps......................................................................... 270 13 Excel Power ......................................................271     File Operations ..........................................................271         List Files in a Directory ................................................271         Import CSV ................................................................273         Read Entire TXT to Memory and Parse .....................................274     Combining and Separating Workbooks ..................................................275         Separate Worksheets into Workbooks .........................................275         Combine Workbooks .........................................................................276         Filter and Copy Data to Separate Worksheets ....................................277         Export Data to Word ...................................................................278     Working with Cell Comments ........................................................279         List Comments .....................................................................279         Resize Comments ...........................................................................281         Place a Chart in a Comment .......................................................................282     Utilities to Wow Your Clients ............................................................283         Using Conditional Formatting to Highlight Selected Cell ...................................283         Highlight Selected Cell Without Using Conditional Formatting ...............................285         Custom Transpose Data ...............................286         Select/Deselect Noncontiguous Cells ..................288     Techniques for VBA Pros ..........................290         Excel State Class Module .....................290         Pivot Table Drill-Down ...............................292         Custom Sort Order ...................................................293         Cell Progress Indicator ...........................................294         Protected Password Box ..................................295         Change Case ..........................................................297         Selecting with SpecialCells ................................................298         ActiveX Right-Click Menu ...........................................299     Cool Applications ....................................................................................300         Historical Stock/Fund Quotes ................................................................301         Using VBA Extensibility to Add Code to New Workbooks ..........................302     Next Steps....................................................................................... 303 14 Sample User-Defined Functions ............................................305     Creating User-Defined Functions ....................... 305     Sharing UDFs .........................................................307     Useful Custom Excel Functions ..............................308         Set the Current Workbook’s Name in a Cell ..........................308         Set the Current Workbook’s Name and File Path in a Cell .................308         Check Whether a Workbook Is Open ..............................309         Check Whether a Sheet in an Open Workbook Exists ..................309         Count the Number of Workbooks in a Directory ..................310         Retrieve USERID .......................................................311         Retrieve Date and Time of Last Save ..............................312         Retrieve Permanent Date and Time .......................................312         Validate an Email Address .........................................................313         Sum Cells Based on Interior Color .................................................315         Count Unique Values .................................................................316         Remove Duplicates from a Range ..........................................316         Find the First Nonzero-Length Cell in a Range ..................318         Substitute Multiple Characters ................................318         Retrieve Numbers from Mixed Text ..................................320         Convert Week Number into Date ..........................................320         Separate Delimited String .........................................321         Sort and Concatenate .........................................................321         Sort Numeric and Alpha Characters .......................................323         Search for a String Within Text .....................................................324         Reverse the Contents of a Cell ....................................................325         Multiple Max ..................................................................................326         Return Hyperlink Address ..........................................................326         Return the Column Letter of a Cell Address .................................327         Static Random ........................................................................327         Using Select Case on a Worksheet .........................................................328     Next Steps....................................................................................................329 15 Creating Charts .........................................................................331     Charting in Excel 2013 ...............................................................331         Considering Backward Compatibility ..............................332         Referencing the Chart Container ............................................332     Understanding the Global Settings ..............................333         Specifying a Built-in Chart Type ........................................333         Specifying Location and Size of the Chart ....................336         Referring to a Specific Chart ...........................................337     Creating a Chart in Various Excel Versions ..................................338         Using .AddChart2 Method in Excel 2013 ..................................338         Creating Charts in Excel 2007–2013 ......................................340         Creating Charts in Excel 2003–2013 ..........................................341     Customizing a Chart ....................................................................................342         Specifying a Chart Title .........................................................................342         Quickly Formatting a Chart Using New Excel 2013 Features ........................343         Using SetElement to Emulate Changes from the Plus Icon .............................350         Using the Format Method to Micromanage Formatting Options ................................355     Creating a Combo Chart ....................... 359     Creating Advanced Charts .................................... 363         Creating True Open-High-Low-Close Stock Charts .......................................364         Creating Bins for a Frequency Chart .........................................................365         Creating a Stacked Area Chart ...............................................................368     Exporting a Chart as a Graphic .....................................................372     Creating Pivot Charts .........................................................................373     Next Steps....................................................... 375 16 Data Visualizations and Conditional Formatting ...........................377     Introduction to Data Visualizations ............................................................377     VBA Methods and Properties for Data Visualizations ...........................................378     Adding Data Bars to a Range ................................................................380     Adding Color Scales to a Range ..................................................................384     Adding Icon Sets to a Range ..................................................................385         Specifying an Icon Set.......................................................................386         Specifying Ranges for Each Icon ............................................................388     Using Visualization Tricks ............................................................................388         Creating an Icon Set for a Subset of a Range .....................................................388         Using Two Colors of Data Bars in a Range ............................................390     Using Other Conditional Formatting Methods ................................................392         Formatting Cells That Are Above or Below Average ....................................392         Formatting Cells in the Top 10 or Bottom 5 ...............................................393         Formatting Unique or Duplicate Cells .................................................393         Formatting Cells Based on Their Value ...........................................395         Formatting Cells That Contain Text...............................................................395         Formatting Cells That Contain Dates ........................................................396         Formatting Cells That Contain Blanks or Errors .........................................396         Using a Formula to Determine Which Cells to Format .............................396         Using the New NumberFormat Property ........................................398     Next Steps...........................................................................................................................398 17 Dashboarding with Sparklines in Excel 2013 ...............................399     Creating Sparklines ..............................................................................399     Scaling Sparklines ..................................................................401     Formatting Sparklines ....................................................................405         Using Theme Colors .......................................................................405         Using RGB Colors .................................................................................408         Formatting Sparkline Elements ................................................................410         Formatting Win/Loss Charts .............................................................412     Creating a Dashboard .........................................................413         Observations About Sparklines ..........................................................................414         Creating Hundreds of Individual Sparklines in a Dashboard .....................................414     Next Steps...............................................................................................................418 18 Reading from and Writing to the Web .....................................419     Getting Data from the Web ..............................................................................419         Manually Creating a Web Query and Refreshing with VBA ............................420         Using VBA to Update an Existing Web Query .....................................423         Building Many Web Queries with VBA ..............................................424     Using Application.OnTime to Periodically Analyze Data ................................427         Scheduled Procedures Require Ready Mode ......................................428         Specifying a Window of Time for an Update .............................................428         Canceling a Previously Scheduled Macro ...............................................429         Closing Excel Cancels All Pending Scheduled Macros ...............................429         Scheduling a Macro to Run x Minutes in the Future ................................429         Scheduling a Verbal Reminder .........................................................430         Scheduling a Macro to Run Every Two Minutes .......................................431     Publishing Data to a Web Page ...................................................................432         Using VBA to Create Custom Web Pages .......................................................434         Using Excel as a Content Management System ..............................................434         Bonus: FTP from Excel.............................................................................437     Next Steps...................................................................................................438 19 Text File Processing ...........................................................................439     Importing from Text Files .............................................................................................439         Importing Text Files with Fewer Than 1,048,576 Rows .............................................439         Reading Text Files One Row at a Time ...........................................................................445     Writing Text Files ...........................................................................................449     Next Steps..........................................................................................449 20 Automating Word ..............................................................................451     Using Early Binding to Reference the Word Object ...................................................451     Using Late Binding to Reference the Word Object ................................................................453     Using the New Keyword to Reference the Word Application ......................................454     Using the CreateObject Function to Create a New Instance of an Object .............................454     Using the GetObject Function to Reference an Existing Instance of Word ........................455     Using Constant Values ...................................................................................................456         Using the Watch Window to Retrieve the Real Value of a Constant .......................456         Using the Object Browser to Retrieve the Real Value of a Constant ..........................457     Understanding Word’s Objects ........................................................................................458         Document Object .................................................................................458         Selection Object ...................................................................................460         Range Object ..................................................................................461         Bookmarks .........................................................................................464     Controlling Form Fields in Word .................................................................465     Next Steps......................................................................................................................467 21 Using Access as a Back End to Enhance Multiuser Access to Data ...............469     ADO Versus DAO ...............................................................................................470     The Tools of ADO .................................................................................................472     Adding a Record to the Database........................................................................473     Retrieving Records from the Database ............................................................475     Updating an Existing Record .............................................................................476     Deleting Records via ADO............................................................................478     Summarizing Records via ADO .......................................................................479     Other Utilities via ADO .........................................................................................480         Checking for the Existence of Tables ..................................................................480         Checking for the Existence of a Field ....................................................................481         Adding a Table On the Fly .....................................................................................482         Adding a Field On the Fly .......................................................................................482     SQL Server Examples .................................................................................................483     Next Steps.........................................................................................................484 22 Advanced Userform Techniques .............................................................485     Using the UserForm Toolbar in the Design of Controls on Userforms ..........................485     More Userform Controls .............................................................................485         Check Boxes .............................................................................................485         Tab Strips ......................................................................................487         RefEdit ...................................................................................................489         Toggle Buttons ................................................................................491         Using a Scrollbar As a Slider to Select Values ...............................................491     Controls and Collections.....................................................................................493     Modeless Userforms ......................................................................................495     Using Hyperlinks in Userforms .............................................................................495     Adding Controls at Runtime ..................................................................................496         Resizing the Userform On the Fly.................................................................498         Adding a Control On the Fly ...............................................................................498         Sizing On the Fly .........................................................................................498         Adding Other Controls ................................................................................499         Adding an Image On the Fly ...................................................................................499         Putting It All Together .................................................................................500     Adding Help to the Userform ........................................................................502         Showing Accelerator Keys .......................................................................502         Adding Control Tip Text .................................................................................503         Creating the Tab Order ..............................................................................503         Coloring the Active Control ..................................................................................503     Creating Transparent Forms .............................................................506     Next Steps.............................................................................................................507 23 Windows API ............................................................................................509     What Is the Windows API? ...........................................................................509     Understanding an API Declaration ..........................................................509     Using an API Declaration .........................................................................510     Making 32-Bit and 64-Bit Compatible API Declarations ................................511     API Examples .................................................................................................512         Retrieving the Computer Name ....................................................................512         Checking Whether an Excel File Is Open on a Network ..............................................513         Retrieving Display-Resolution Information .........................................................513         Customizing the About Dialog .......................................................................514         Disabling the X for Closing a Userform ............................................................515         Running Timer ....................................................................................516         Playing Sounds .................................................................................517     Next Steps.............................................................................................................................517 24 Handling Errors ..............................................................................................519     What Happens When an Error Occurs? ..............................................................519         Debug Error Inside Userform Code Is Misleading ...............................................520     Basic Error Handling with the On Error GoTo Syntax .....................................522     Generic Error Handlers ....................................................................................................524         Handling Errors by Choosing to Ignore Them .........................................................................524         Suppressing Excel Warnings ...................................................................................................526         Encountering Errors on Purpose ........................................................................................526     Train Your Clients .............................................................................................526     Errors While Developing Versus Errors Months Later ....................................527         Runtime Error 9: Subscript Out of Range ........................................................527         Runtime Error 1004: Method Range of Object Global Failed .....................................528     The Ills of Protecting Code .................................................................................529     More Problems with Passwords .....................................................................530     Errors Caused by Different Versions .....................................................................530     Next Steps.............................................................................................................531 25 Customizing the Ribbon to Run Macros .............................................................533     Out with the Old, In with the New ...........................................................................533     Where to Add Your Code: customui Folder and File ......................................534     Creating the Tab and Group .......................................................................................535     Adding a Control to Your Ribbon ...................................................................................536     Accessing the File Structure .......................................................................................542     Understanding the RELS File ...............................................................................542     Renaming the Excel File and Opening the Workbook ..................................543     Using Images on Buttons .............................................................................................543         Using Microsoft Office Icons on Your Ribbon ..................................................544         Adding Custom Icon Images to Your Ribbon ..........................................................545     Troubleshooting Error Messages ...................................................................................548         The Attribute “Attribute Name” on the Element “customui Ribbon” Is Not Defined in the DTD/Schema ........548         Illegal Qualified Name Character ........................................................548         Element “customui Tag Name” Is Unexpected According to Content Model of Parent Element “customui Tag Name”.............................549         Excel Found a Problem with Some Content ........................549         Wrong Number of Arguments or Invalid Property Assignment ...................550         Invalid File Format or File Extension ...............................550         Nothing Happens ..............................................................551     Other Ways to Run a Macro ..........................................551         Using a Keyboard Shortcut to Run a Macro.........................551         Attaching a Macro to a Command Button ............................552         Attaching a Macro to a Shape ..............................................552         Attaching a Macro to an ActiveX Control ...............................553         Running a Macro from a Hyperlink ...........................................554     Next Steps.............................................................................554 26 Creating Add-Ins ..................................................................555     Characteristics of Standard Add-Ins ...........................................555     Converting an Excel Workbook to an Add-In ...................................................556         Using Save As to Convert a File to an Add-In .................................................557         Using the VB Editor to Convert a File to an Add-In .............................558     Having Your Client Install the Add-In ..........................................................558     Closing Add-Ins ...................................................................................560     Removing Add-Ins .............................................................................................560     Using a Hidden Workbook as an Alternative to an Add-In ..........................................561     Next Steps................................................................................................................562 27 An Introduction to Creating Apps for Office .............................563     Creating Your First App—Hello World ..........................................563     Adding Interactivity to Your App .................................................................568     A Basic Introduction to HTML..........................................................................570         Tags ..................................................................................................570         Buttons ......................................................................................................................570         CSS...................................................................................................................571     Using XML to Define Your App ....................................................................................571     Using JavaScript to Add Interactivity to Your App....................................................572         The Structure of a Function .................................................................................572         Variables ...........................................................................................................573         Strings .............................................................................................................................574         Arrays ................................................................................................................................574         JS for Loops .........................................................................................................575         How to Do an if Statement in JS .........................................................................576         How to Do a Select..Case Statement in JS ....................................................................576         How to Do a For each..next Statement in JS .........................................................................577         Mathematical, Logical, and Assignment Operators .....................................................578         Math Functions in JS ...........................................................................................579         Writing to the Content or Task Pane..................................................581         JavaScript Changes for Working in the Office App ..........................581     Napa Office 365 Development Tools ..............................................................582     Next Steps...............................................................................................................582 28 What Is New in Excel 2013 and What Has Changed ...............................583     If It Has Changed in the Front End, It Has Changed in VBA ....................583         The Ribbon .............................................................................583         Single Document Interface (SDI)........................................583         Quick Analysis Tool ..................................................................585         Charts .....................................................................................585         PivotTables .......................................................................................585         Slicers ................................................................................................586         SmartArt ............................................................................................586     Learning the New Objects and Methods .................................................587     Compatibility Mode .....................................................................................587         Version .....................................................................................................587         Excel8CompatibilityMode .............................................................588     Next Steps........................................................................................588 9780789748616, 1/14/2031, TOC