Excel 2016 VBA and Macros

Description

• Copyright 2016
• Dimensions: 7" x 9-1/8"
• Pages: 640
• Edition: 1st

• eBook
• ISBN-10: 0-13-438600-0
• ISBN-13: 978-0-13-438600-3

MASTER CORE EXCEL 2016 TOOLS FOR BUILDING POWERFUL, RELIABLE SPREADSHEETS!

Use this guide to automate virtually any routine task: save yourself hours, days, maybe even weeks! Make Excel do things you thought were impossible, discover macro techniques you won’t find anywhere else, and create automated reports that are amazingly powerful. Bill Jelen and Tracy Syrstad help you  instantly visualize information, so you can act on it… capture data from anywhere, and use it anywhere… automate Excel 2016’s best new features. You’ll find simple, step-by-step instructions, real-world case studies, and 50 workbooks packed with bonus examples, macros, and solutions–straight from MrExcel!

• Get started fast with Excel macro development
• Work efficiently with ranges, cells, and formulas
• Build super-fast applications with arrays
• Automate Excel’s new pivot table enhancements
• Collect user data with custom dialogs
• Make your macros more reliable and resilient
• Pull data from the Internet with web queries
• Use advanced classes, collections, and custom functions
• Build sophisticated business analysis solutions
• Read and write to Access or SQL Server databases
• Control other Office programs, and Windows itself
• Write code that also works on older Excel versions
• Start writing Office Store-style Excel Apps

About MrExcel Library

Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will

• Dramatically increase your productivity–saving you 50 hours a year or more
• Present proven, creative strategies for solving real-world problems
• Show you how to get great results, no matter how much data you have
• Help you avoid critical mistakes that even experienced users make

This book is part of Que’s Content Update Program. As Microsoft updates features of Excel, sections of this book will be updated or new sections will be added to match the updates to the software. See inside for details.



Sample Content

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



Updates

Submit Errata

