Excel 2013 VBA and Macros

Bill Jelen , Excel MVP and the host of MrExcel.com, has been using spreadsheets since 1985, and he launched the MrExcel.com website in 1998. Bill was a regular guest on Call for Help with Leo Laporte and has produced more than 1,500 episodes of his daily video podcast, Learn Excel from MrExcel. He is the author of 39 books about Microsoft Excel and writes the monthly Excel column for Strategic Finance magazine. His Excel tips appear regularly in CFO Excel Pro Newsletter and CFO Magazine . Before founding MrExcel.com, Bill Jelen spent 12 years in the trenches–working as a financial analyst for finance, marketing, accounting, and operations departments of a $500 million public company. He lives near Akron, Ohio, with his wife, Mary Ellen.   Tracy Syrstad is the project manager for the MrExcel consulting team. She was introduced to Excel VBA by a co-worker who encouraged her to learn VBA by recording steps, and then modifying the code as needed. Her first macro was a simple lookup and highlight for a part index, although it hardly seemed simple when she did it. She was encouraged by her success with that macro and others that followed. She’ll never forget the day when it all clicked. She hopes this book will bring that click to its readers sooner and with less frustration. She lives near Sioux Falls, South Dakota, with her husband, John.

Introduction ........................................................ 1

Getting Results with VBA ...................................................................1

What Is in This Book? .................................................................1

  Reduce the Learning Curve .............................................1

  Excel VBA Power .................................................................2

  Techie Stuff Needed to Produce Applications ................................2

  Does This Book Teach Excel? ............................................................2

The Future of VBA and Windows Versions of Excel ..............................4

  Versions of Excel ......................................................................................4

Special Elements and Typographical Conventions .........................................4

Code Files .........................................................................................................5

Next Steps..........................................................................5

1 Unleash the Power of Excel with VBA ......................................... 7

The Power of Excel .....................................................................7

Barriers to Entry .......................................................................7

  The Macro Recorder Doesn’t Work! ...................................................7

  Visual Basic Is Not Like BASIC ..............................................................8

  Good News: Climbing the Learning Curve Is Easy ....................................8

  Great News: Excel with VBA Is Worth the Effort ..................................8

Knowing Your Tools: The Developer Tab .............................................................9

Understanding Which File Types Allow Macros .........................................10

Macro Security ...........................................................................................11

  Adding a Trusted Location ........................................................................12

  Using Macro Settings to Enable Macros in Workbooks Outside of Trusted Locations ........................13

  Using Disable All Macros with Notification ...............................................13

Overview of Recording, Storing, and Running a Macro ......................................14

  Filling Out the Record Macro Dialog ..................................................................14

Running a Macro..............................................................................................16

  Creating a Macro Button on the Ribbon .........................................................16

  Creating a Macro Button on the Quick Access Toolbar ............................17

  Assigning a Macro to a Form Control, Text Box, or Shape .......................................18

Understanding the VB Editor ...............................................19

  VB Editor Settings ........................................................................20

  The Project Explorer ...........................................................20

  The Properties Window .......................................................................21

Understanding Shortcomings of the Macro Recorder ..................................21

  Examining Code in the Programming Window .....................................23

  Running the Macro on Another Day Produces Undesired Results ...................25

  Possible Solution: Use Relative References When Recording ..............................26

  Never Use the AutoSum or Quick Analysis While Recording a Macro .....................30

  Three Tips When Using the Macro Recorder ..............................................31

Next Steps..................................................................................31

2 This Sounds Like BASIC, So Why Doesn’t It Look Familiar? ........................33

I Can’t Understand This Code ................................................................33

Understanding the Parts of VBA “Speech” .............................34

VBA Is Not Really Hard .......................................................37

  VBA Help Files: Using F1 to Find Anything ....................................37

  Using Help Topics ........................................................................38

Examining Recorded Macro Code: Using the VB Editor and Help .............................39

  Optional Parameters ..............................................40

  Defined Constants ..........................................................41

  Properties Can Return Objects ...................................45

Using Debugging Tools to Figure Out Recorded Code .........................46

  Stepping Through Code .................................................................46

  More Debugging Options: Breakpoints .......................................49

  Backing Up or Moving Forward in Code .................................49

  Not Stepping Through Each Line of Code.....................................50

  Querying Anything While Stepping Through Code ..........................50

  Using a Watch to Set a Breakpoint .........................................53

  Using a Watch on an Object ...........................54

Object Browser: The Ultimate Reference ....................................55

Seven Tips for Cleaning Up Recorded Code ................................56

  Tip 1: Don’t Select Anything .............................56

  Tip 2: Cells(2,5) Is More Convenient Than Range(“E2”) ..........................57

  Tip 3: Use More Reliable Ways to Find the Last Row .......................................58

  Tip 4: Use Variables to Avoid Hard-Coding Rows and Formulas ......................59

  Tip 5: R1C1 Formulas That Make Your Life Easier .............................59

  Tip 6: Learn to Copy and Paste in a Single Statement................................59

  Tip 7: Use With...End With to Perform Multiple Actions .................60

Next Steps............................................................................................63

3 Referring to Ranges ......................................................65

The Range Object ........................................................................65

Syntax to Specify a Range ................................................................66

Named Ranges ........................................................................................66

Shortcut for Referencing Ranges .................................................................66

Referencing Ranges in Other Sheets ..................................................67

Referencing a Range Relative to Another Range ............................67

Use the Cells Property to Select a Range .................................................68

Use the Offset Property to Refer to a Range ..............................................69

Use the Resize Property to Change the Size of a Range ........................71

Use the Columns and Rows Properties to Specify a Range ......................72

Use the Union Method to Join Multiple Ranges ................................................72

Use the Intersect Method to Create a New Range from Overlapping Ranges..........................72

Use the ISEMPTY Function to Check Whether a Cell Is Empty ............................................73

Use the CurrentRegion Property to Select a Data Range .....................................73

Use the Areas Collection to Return a Noncontiguous Range .........................................76

Referencing Tables ............................................................................77

Next Steps..................................................................................................77

4 Looping and Flow Control ................................................................79

For...Next Loops ............................................................................................79

  Using Variables in the For Statement ..............................................................82

  Variations on the For...Next Loop .................................................................82

  Exiting a Loop Early After a Condition Is Met ....................................................83

  Nesting One Loop Inside Another Loop .........................................................84

Do Loops .....................................................................................................85

  Using the While or Until Clause in Do Loops ........................................87

  While...Wend Loops .............................................................................88

The VBA Loop: For Each ............................................................................89

  Object Variables .........................................................................................89

Flow Control: Using If...Then...Else and Select Case ...........................................92

  Basic Flow Control: If...Then...Else ................................................92

  Conditions ..............................................................................................92

  If...Then...End If ....................................................................................93

  Either/Or Decisions: If...Then...Else...End If .............................................93

  Using If...ElseIf...End If for Multiple Conditions .....................................93

  Using Select Case...End Select for Multiple Conditions ...............................94

  Complex Expressions in Case Statements ........................................................95

  Nesting If Statements .....................................................................95

Next Steps..............................................................................................................97

5 R1C1-Style Formulas ................................................................99

Referring to Cells: A1 Versus R1C1 References ...........................99

Toggling to R1C1-Style References ................................................100

The Miracle of Excel Formulas ...............................................................101

  Enter a Formula Once and Copy 1,000 Times ....................................101

  The Secret: It’s Not That Amazing ............................................................102

Explanation of R1C1 Reference Style ..............................................................103

  Using R1C1 with Relative References....................................................104

  Using R1C1 with Absolute References ......................................................104

  Using R1C1 with Mixed References...............................................................105

  Referring to Entire Columns or Rows with R1C1 Style .....................................................105

  Replacing Many A1 Formulas with a Single R1C1 Formula ...................................106

  Remembering Column Numbers Associated with Column Letters ................................107

Array Formulas Require R1C1 Formulas .................................................................108

Next Steps...................................................................................................................109

6 Create and Manipulate Names in VBA ....................................................111

Excel Names .....................................................................................111

Global Versus Local Names .............................................................111

Adding Names ...................................................................................112

Deleting Names ................................................................................113

Adding Comments .......................................................................114

Types of Names .................................................................114

  Formulas ...........................................................................114

  Strings .........................................................................................115

  Numbers ........................................................................................116

  Tables ................................................................................................117

  Using Arrays in Names .........................................................................117

  Reserved Names .....................................................................................118

Hiding Names ................................................................................................119

Checking for the Existence of a Name ...............................................................119

Next Steps............................................................................................121

7 Event Programming ..............................................................123

Levels of Events ...................................................................123

Using Events ..........................................................................124

  Event Parameters ...................................................................124

  Enabling Events .................................................................125

Workbook Events ..............................................................................125

  Workbook Level Sheet and Chart Events ...................................................129

Worksheet Events ...................................................................132

Chart Sheet Events .................................................................................137

  Embedded Charts ...........................................................................137

Application-Level Events ................................................................................140

Next Steps........................................................................................................................148

8 Arrays ....................................................................................149

Declare an Array ................................................................149

Declare a Multidimensional Array ................................150

Fill an Array......................................................................151

Retrieve Data from an Array .............................................................152

Use Arrays to Speed Up Code ........................................................153

Use Dynamic Arrays ..................................................................................155

Passing an Array .................................................................................156

Next Steps................................................................................................................................157

9 Creating Classes, Records, and Collections ......................................................159

Inserting a Class Module ...............................................................................159

Trapping Application and Embedded Chart Events ..........................................159

  Application Events ...........................................................................................160

  Embedded Chart Events ..................................................................................161

    Creating a Custom Object .....................................................................163

Using a Custom Object .............................................................................163

Using Property Let and Property Get to Control How Users Utilize Custom Objects .......................................165

Using Collections to Hold Multiple Records ...............................................................................167

  Creating a Collection in a Standard Module ..................................................167

  Creating a Collection in a Class Module .........................................................168

Using User-Defined Types to Create Custom Properties .............................................172

Next Steps.......................................................................................................................174

10 Userforms: An Introduction ...........................................................175

User Interaction Methods ..........................................................................175

  Input Boxes..............................................................................................175

  Message Boxes .......................................................................................176

Creating a Userform ......................................................................... 176

Calling and Hiding a Userform ................................................177

Programming the Userform ................................................................178

  Userform Events .............................................................................178

Programming Controls .....................................................................................180

Using Basic Form Controls...................................................................................181

  Using Labels, Text Boxes, and Command Buttons .....................................................181

  Deciding Whether to Use List Boxes or Combo Boxes in Forms ...............................183

  Adding Option Buttons to a Userform ...........................................................186

  Adding Graphics to a Userform ....................................................................187

  Using a Spin Button on a Userform ...........................................188

  Using the MultiPage Control to Combine Forms .........................................190

Verifying Field Entry ....................................................................................................192

Illegal Window Closing ............................................................................192

Getting a Filename ..............................................................................................193

Next Steps..........................................................................................................................195

11 Data Mining with Advanced Filter .....................................................197

Replacing a Loop with AutoFilter ............................................................................197

  Using New AutoFilter Techniques ............................................................200

  Selecting Visible Cells Only ..........................................................203

Advanced Filter Is Easier in VBA Than in Excel ......................................................204

  Using the Excel Interface to Build an Advanced Filter ....................................205

Using Advanced Filter to Extract a Unique List of Values ..............................................206

  Extracting a Unique List of Values with the User Interface ........................206

  Extracting a Unique List of Values with VBA Code ..................................................207

  Getting Unique Combinations of Two or More Fields ..............................................211

Using Advanced Filter with Criteria Ranges .................................................................212

  Joining Multiple Criteria with a Logical OR ................................................................213

  Joining Two Criteria with a Logical AND ..............................................214

  Other Slightly Complex Criteria Ranges .....................................214

  The Most Complex Criteria: Replacing the List of Values with a Condition Created as the Result of a Formula ...214

Using Filter in Place in Advanced Filter ............................................221

  Catching No Records When Using Filter in Place..................222

  Showing All Records After Filter in Place .............................. 222

The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only ...............222

  Copying All Columns .............................................................223

  Copying a Subset of Columns and Reordering ............................224

  Excel in Practice: Turning Off a Few Drop-Downs in the AutoFilter .......................229

Next Steps............................................................................................................230

12 Using VBA to Create Pivot Tables...................................231

Introducing Pivot Tables .............................................................231

Understanding Versions ...................................................................231

Building a Pivot Table in Excel VBA ..........................................................232

  Defining the Pivot Cache ..........................................................................232

  Creating and Configuring the Pivot Table ...............................................233

  Adding Fields to the Data Area ....................................................................234

  Learning Why You Cannot Move or Change Part of a Pivot Report ...............................237

  Determining the Size of a Finished Pivot Table to Convert the Pivot Table to Values .................238

Using Advanced Pivot Table Features .............................................240

  Using Multiple Value Fields ........................................................240

  Grouping Daily Dates to Months, Quarters, or Years ......................241

  Changing the Calculation to Show Percentages ..........................243

  Eliminating Blank Cells in the Values Area...................................246

  Controlling the Sort Order with AutoSort .........................................246

  Replicating the Report for Every Product ...................................246

Filtering a Dataset ...........................................249

  Manually Filtering Two or More Items in a Pivot Field ....................249

  Using the Conceptual Filters .......................................250

  Using the Search Filter ........................................................254

  Setting Up Slicers to Filter a Pivot Table .......................................................257

  Setting Up a Timeline to Filter an Excel 2013 Pivot Table ...............................259

Using the Data Model in Excel 2013 ................................................262

  Adding Both Tables to the Data Model .........................................262

  Creating a Relationship Between the Two Tables ...........................263

  Defining the PivotCache and Building the Pivot Table ...............................263

  Adding Model Fields to the Pivot Table ..................................................264

  Adding Numeric Fields to the Values Area ............................264

  Putting It All Together ................................................265

Using Other Pivot Table Features ................................267

  Calculated Data Fields...........................................267

  Calculated Items .............................................................268

  Using ShowDetail to Filter a Recordset ...............................268

  Changing the Layout from the Design Tab ..............................268

  Settings for the Report Layout ................................. 269

  Suppressing Subtotals for Multiple Row Fields .................... 269

Next Steps......................................................................... 270

13 Excel Power ......................................................271

File Operations ..........................................................271

  List Files in a Directory ................................................271

  Import CSV ................................................................273

  Read Entire TXT to Memory and Parse .....................................274

Combining and Separating Workbooks ..................................................275

  Separate Worksheets into Workbooks .........................................275

  Combine Workbooks .........................................................................276

  Filter and Copy Data to Separate Worksheets ....................................277

  Export Data to Word ...................................................................278

Working with Cell Comments ........................................................279

  List Comments .....................................................................279

  Resize Comments ...........................................................................281

  Place a Chart in a Comment .......................................................................282

Utilities to Wow Your Clients ............................................................283

  Using Conditional Formatting to Highlight Selected Cell ...................................283

  Highlight Selected Cell Without Using Conditional Formatting ...............................285

  Custom Transpose Data ...............................286

  Select/Deselect Noncontiguous Cells ..................288

Techniques for VBA Pros ..........................290

  Excel State Class Module .....................290

  Pivot Table Drill-Down ...............................292

  Custom Sort Order ...................................................293

  Cell Progress Indicator ...........................................294

  Protected Password Box ..................................295

  Change Case ..........................................................297

  Selecting with SpecialCells ................................................298

  ActiveX Right-Click Menu ...........................................299

Cool Applications ....................................................................................300

  Historical Stock/Fund Quotes ................................................................301

  Using VBA Extensibility to Add Code to New Workbooks ..........................302

Next Steps....................................................................................... 303

14 Sample User-Defined Functions ............................................305

Creating User-Defined Functions ....................... 305

Sharing UDFs .........................................................307

Useful Custom Excel Functions ..............................308

  Set the Current Workbook’s Name in a Cell ..........................308

  Set the Current Workbook’s Name and File Path in a Cell .................308

  Check Whether a Workbook Is Open ..............................309

  Check Whether a Sheet in an Open Workbook Exists ..................309

  Count the Number of Workbooks in a Directory ..................310

  Retrieve USERID .......................................................311

  Retrieve Date and Time of Last Save ..............................312

  Retrieve Permanent Date and Time .......................................312

  Validate an Email Address .........................................................313

  Sum Cells Based on Interior Color .................................................315

  Count Unique Values .................................................................316

  Remove Duplicates from a Range ..........................................316

  Find the First Nonzero-Length Cell in a Range ..................318

  Substitute Multiple Characters ................................318

  Retrieve Numbers from Mixed Text ..................................320

  Convert Week Number into Date ..........................................320

  Separate Delimited String .........................................321

  Sort and Concatenate .........................................................321

  Sort Numeric and Alpha Characters .......................................323

  Search for a String Within Text .....................................................324

  Reverse the Contents of a Cell ....................................................325

  Multiple Max ..................................................................................326

  Return Hyperlink Address ..........................................................326

  Return the Column Letter of a Cell Address .................................327

  Static Random ........................................................................327

  Using Select Case on a Worksheet .........................................................328

Next Steps....................................................................................................329

15 Creating Charts .........................................................................331

Charting in Excel 2013 ...............................................................331

  Considering Backward Compatibility ..............................332

  Referencing the Chart Container ............................................332

Understanding the Global Settings ..............................333

  Specifying a Built-in Chart Type ........................................333

  Specifying Location and Size of the Chart ....................336

  Referring to a Specific Chart ...........................................337

Creating a Chart in Various Excel Versions ..................................338

  Using .AddChart2 Method in Excel 2013 ..................................338

     Creating Charts in Excel 2007–2013 ......................................340

  Creating Charts in Excel 2003–2013 ..........................................341

Customizing a Chart ....................................................................................342

  Specifying a Chart Title .........................................................................342

  Quickly Formatting a Chart Using New Excel 2013 Features ........................343

  Using SetElement to Emulate Changes from the Plus Icon .............................350

  Using the Format Method to Micromanage Formatting Options ................................355

Creating a Combo Chart ....................... 359

Creating Advanced Charts .................................... 363

  Creating True Open-High-Low-Close Stock Charts .......................................364

  Creating Bins for a Frequency Chart .........................................................365

  Creating a Stacked Area Chart ...............................................................368

Exporting a Chart as a Graphic .....................................................372

Creating Pivot Charts .........................................................................373

Next Steps....................................................... 375

16 Data Visualizations and Conditional Formatting ...........................377

Introduction to Data Visualizations ............................................................377

VBA Methods and Properties for Data Visualizations ...........................................378

Adding Data Bars to a Range ................................................................380

Adding Color Scales to a Range ..................................................................384

Adding Icon Sets to a Range ..................................................................385

  Specifying an Icon Set.......................................................................386

  Specifying Ranges for Each Icon ............................................................388

Using Visualization Tricks ............................................................................388

 Creating an Icon Set for a Subset of a Range .....................................................388

  Using Two Colors of Data Bars in a Range ............................................390

Using Other Conditional Formatting Methods ................................................392

  Formatting Cells That Are Above or Below Average ....................................392

  Formatting Cells in the Top 10 or Bottom 5 ...............................................393

  Formatting Unique or Duplicate Cells .................................................393

  Formatting Cells Based on Their Value ...........................................395

  Formatting Cells That Contain Text...............................................................395

  Formatting Cells That Contain Dates ........................................................396

  Formatting Cells That Contain Blanks or Errors .........................................396

  Using a Formula to Determine Which Cells to Format .............................396

  Using the New NumberFormat Property ........................................398

Next Steps...........................................................................................................................398

17 Dashboarding with Sparklines in Excel 2013 ...............................399

Creating Sparklines ..............................................................................399

Scaling Sparklines ..................................................................401

Formatting Sparklines ....................................................................405

  Using Theme Colors .......................................................................405

  Using RGB Colors .................................................................................408

  Formatting Sparkline Elements ................................................................410

  Formatting Win/Loss Charts .............................................................412

Creating a Dashboard .........................................................413

  Observations About Sparklines ..........................................................................414

  Creating Hundreds of Individual Sparklines in a Dashboard .....................................414

Next Steps...............................................................................................................418

18 Reading from and Writing to the Web .....................................419

Getting Data from the Web ..............................................................................419

  Manually Creating a Web Query and Refreshing with VBA ............................420

  Using VBA to Update an Existing Web Query .....................................423

  Building Many Web Queries with VBA ..............................................424

Using Application.OnTime to Periodically Analyze Data ................................427

  Scheduled Procedures Require Ready Mode ......................................428

  Specifying a Window of Time for an Update .............................................428

  Canceling a Previously Scheduled Macro ...............................................429

  Closing Excel Cancels All Pending Scheduled Macros ...............................429

  Scheduling a Macro to Run x Minutes in the Future ................................429

  Scheduling a Verbal Reminder .........................................................430

  Scheduling a Macro to Run Every Two Minutes .......................................431

Publishing Data to a Web Page ...................................................................432

  Using VBA to Create Custom Web Pages .......................................................434

  Using Excel as a Content Management System ..............................................434

  Bonus: FTP from Excel.............................................................................437

Next Steps...................................................................................................438

19 Text File Processing ...........................................................................439

Importing from Text Files .............................................................................................439

  Importing Text Files with Fewer Than 1,048,576 Rows .............................................439

  Reading Text Files One Row at a Time ...........................................................................445

Writing Text Files ...........................................................................................449

Next Steps..........................................................................................449

20 Automating Word ..............................................................................451

Using Early Binding to Reference the Word Object ...................................................451

Using Late Binding to Reference the Word Object ................................................................453

Using the New Keyword to Reference the Word Application ......................................454

Using the CreateObject Function to Create a New Instance of an Object .............................454

Using the GetObject Function to Reference an Existing Instance of Word ........................455

Using Constant Values ...................................................................................................456

  Using the Watch Window to Retrieve the Real Value of a Constant .......................456

  Using the Object Browser to Retrieve the Real Value of a Constant ..........................457

Understanding Word’s Objects ........................................................................................458

  Document Object .................................................................................458

  Selection Object ...................................................................................460

  Range Object ..................................................................................461

  Bookmarks .........................................................................................464

Controlling Form Fields in Word .................................................................465

Next Steps......................................................................................................................467

21 Using Access as a Back End to Enhance Multiuser Access to Data ...............469

ADO Versus DAO ...............................................................................................470

The Tools of ADO .................................................................................................472

Adding a Record to the Database........................................................................473

Retrieving Records from the Database ............................................................475

Updating an Existing Record .............................................................................476

Deleting Records via ADO............................................................................478

Summarizing Records via ADO .......................................................................479

Other Utilities via ADO .........................................................................................480

  Checking for the Existence of Tables ..................................................................480

  Checking for the Existence of a Field ....................................................................481

  Adding a Table On the Fly .....................................................................................482

  Adding a Field On the Fly .......................................................................................482

SQL Server Examples .................................................................................................483

Next Steps.........................................................................................................484

22 Advanced Userform Techniques .............................................................485

Using the UserForm Toolbar in the Design of Controls on Userforms ..........................485

More Userform Controls .............................................................................485

  Check Boxes .............................................................................................485

  Tab Strips ......................................................................................487

  RefEdit ...................................................................................................489

  Toggle Buttons ................................................................................491

  Using a Scrollbar As a Slider to Select Values ...............................................491

Controls and Collections.....................................................................................493

Modeless Userforms ......................................................................................495

Using Hyperlinks in Userforms .............................................................................495

Adding Controls at Runtime ..................................................................................496

  Resizing the Userform On the Fly.................................................................498

  Adding a Control On the Fly ...............................................................................498

  Sizing On the Fly .........................................................................................498

  Adding Other Controls ................................................................................499

  Adding an Image On the Fly ...................................................................................499

  Putting It All Together .................................................................................500

Adding Help to the Userform ........................................................................502

  Showing Accelerator Keys .......................................................................502

  Adding Control Tip Text .................................................................................503

  Creating the Tab Order ..............................................................................503

  Coloring the Active Control ..................................................................................503

Creating Transparent Forms .............................................................506

Next Steps.............................................................................................................507

23 Windows API ............................................................................................509

What Is the Windows API? ...........................................................................509

Understanding an API Declaration ..........................................................509

Using an API Declaration .........................................................................510

Making 32-Bit and 64-Bit Compatible API Declarations ................................511

API Examples .................................................................................................512

  Retrieving the Computer Name ....................................................................512

  Checking Whether an Excel File Is Open on a Network ..............................................513

  Retrieving Display-Resolution Information .........................................................513

  Customizing the About Dialog .......................................................................514

  Disabling the X for Closing a Userform ............................................................515

  Running Timer ....................................................................................516

  Playing Sounds .................................................................................517

Next Steps.............................................................................................................................517

24 Handling Errors ..............................................................................................519

What Happens When an Error Occurs? ..............................................................519

  Debug Error Inside Userform Code Is Misleading ...............................................520

Basic Error Handling with the On Error GoTo Syntax .....................................522

Generic Error Handlers ....................................................................................................524

  Handling Errors by Choosing to Ignore Them .........................................................................524

  Suppressing Excel Warnings ...................................................................................................526

  Encountering Errors on Purpose ........................................................................................526

Train Your Clients .............................................................................................526

Errors While Developing Versus Errors Months Later ....................................527

  Runtime Error 9: Subscript Out of Range ........................................................527

  Runtime Error 1004: Method Range of Object Global Failed .....................................528

The Ills of Protecting Code .................................................................................529

More Problems with Passwords .....................................................................530

Errors Caused by Different Versions .....................................................................530

Next Steps.............................................................................................................531

25 Customizing the Ribbon to Run Macros .............................................................533

Out with the Old, In with the New ...........................................................................533

Where to Add Your Code: customui Folder and File ......................................534

Creating the Tab and Group .......................................................................................535

Adding a Control to Your Ribbon ...................................................................................536

Accessing the File Structure .......................................................................................542

Understanding the RELS File ...............................................................................542

Renaming the Excel File and Opening the Workbook ..................................543

Using Images on Buttons .............................................................................................543

  Using Microsoft Office Icons on Your Ribbon ..................................................544

  Adding Custom Icon Images to Your Ribbon ..........................................................545

Troubleshooting Error Messages ...................................................................................548

  The Attribute “Attribute Name” on the Element “customui Ribbon” Is Not Defined in the DTD/Schema ........548

  Illegal Qualified Name Character ........................................................548

  Element “customui Tag Name” Is Unexpected According to Content Model of Parent Element “customui Tag Name”.............................549

  Excel Found a Problem with Some Content ........................549

  Wrong Number of Arguments or Invalid Property Assignment ...................550

  Invalid File Format or File Extension ...............................550

  Nothing Happens ..............................................................551

Other Ways to Run a Macro ..........................................551

  Using a Keyboard Shortcut to Run a Macro.........................551

  Attaching a Macro to a Command Button ............................552

  Attaching a Macro to a Shape ..............................................552

  Attaching a Macro to an ActiveX Control ...............................553

  Running a Macro from a Hyperlink ...........................................554

Next Steps.............................................................................554

26 Creating Add-Ins ..................................................................555

Characteristics of Standard Add-Ins ...........................................555

Converting an Excel Workbook to an Add-In ...................................................556

  Using Save As to Convert a File to an Add-In .................................................557

  Using the VB Editor to Convert a File to an Add-In .............................558

Having Your Client Install the Add-In ..........................................................558

Closing Add-Ins ...................................................................................560

Removing Add-Ins .............................................................................................560

Using a Hidden Workbook as an Alternative to an Add-In ..........................................561

Next Steps................................................................................................................562

27 An Introduction to Creating Apps for Office .............................563

Creating Your First App—Hello World ..........................................563

Adding Interactivity to Your App .................................................................568

A Basic Introduction to HTML..........................................................................570

  Tags ..................................................................................................570

  Buttons ......................................................................................................................570

  CSS...................................................................................................................571

Using XML to Define Your App ....................................................................................571

Using JavaScript to Add Interactivity to Your App....................................................572

  The Structure of a Function .................................................................................572

  Variables ...........................................................................................................573

  Strings .............................................................................................................................574

  Arrays ................................................................................................................................574

  JS for Loops .........................................................................................................575

  How to Do an if Statement in JS .........................................................................576

  How to Do a Select..Case Statement in JS ....................................................................576

  How to Do a For each..next Statement in JS .........................................................................577

  Mathematical, Logical, and Assignment Operators .....................................................578

  Math Functions in JS ...........................................................................................579

  Writing to the Content or Task Pane..................................................581

  JavaScript Changes for Working in the Office App ..........................581

Napa Office 365 Development Tools ..............................................................582

Next Steps...............................................................................................................582

28 What Is New in Excel 2013 and What Has Changed ...............................583

If It Has Changed in the Front End, It Has Changed in VBA ....................583

  The Ribbon .............................................................................583

  Single Document Interface (SDI)........................................583

  Quick Analysis Tool ..................................................................585

  Charts .....................................................................................585

  PivotTables .......................................................................................585

  Slicers ................................................................................................586

  SmartArt ............................................................................................586

Learning the New Objects and Methods .................................................587

Compatibility Mode .....................................................................................587

  Version .....................................................................................................587

  Excel8CompatibilityMode .............................................................588

Next Steps........................................................................................588

9780789748616, 1/14/2031, TOC