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