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