Excel 2016 VBA and Macros (includes Content Update Program)

Reihe
Que
Autor
Bill Jelen / Tracy Syrstad  
Verlag
QUE Publishing
Einband
Softcover
Auflage
1
Sprache
Englisch
Seiten
640
Erschienen
November 2015
ISBN13
9780789755858
ISBN
0789755858
Related Titles


Produktdetail

Artikel Preis SFr Verfügbar  
9780789755858
Excel 2016 VBA and Macros (includes Content Update Program)
45.10 ca. 7-9 Tage

Description

Students will learn how to make Excel do things they thought were impossible, discover macro techniques not found anywhere else, and create automated reports that are amazingly powerful. Bill Jelen and Tracy Syrstad help readers instantly visualize information. Readers will find simple, step-by-step instructions, real-world case studies, and 50 workbooks packed with bonus examples, macros, and solutions–straight from MrExcel!

Features

  • The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve
  • Shows how to automate powerful new Excel 2016 features for analysis, formatting, sharing, and presentation
  • Includes the best practical explanation of object-oriented programming for non-programmers ever written
  • Co-authored by Excel legend and Microsoft MVP Bill Jelen ("MrExcel") as part of the popular MrExcel's Library series

New to this Edition

New screen shots throughout, improvements/updates to chapters 7, 8, 9, 13, 14, 27
Approximately what percentage of content will change?  40%
All 2016 features
Polishing the chapters where we are smarter now.

Table of Contents

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

 

Author

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,900 episodes of his daily video podcast, Learn Excel from MrExcel. He is the author of 44 books about Microsoft Excel and writes the monthly Excel column for Strategic Finance 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 in Merritt Island, Florida, with his wife, Mary Ellen.

 

Tracy Syrstad is a Microsoft Excel developer and author of eight Excel books. She has been helping people with Microsoft Office issues since 1997, when she discovered free online forums where anyone could ask and answer questions. Tracy found out she enjoyed teaching others new skills, and when she began working as a developer, she was able to integrate the fun of teaching with one-on-one online desktop sharing sessions. Tracy lives on acreage in eastern South Dakota with her husband, one dog, two cats, one horse (two, hopefully soon), and a variety of wild foxes, squirrels, and rabbits.