Excel 2013 VBA and Macros

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