Home > Store

Excel 2013 VBA and Macros

Register your product to gain access to bonus material or receive a coupon.

Excel 2013 VBA and Macros

eBook (Watermarked)

  • Sorry, this book is no longer in print.
  • Includes EPUB and PDF
  • About eBook Formats
  • This eBook includes the following formats, accessible from your Account page after purchase:

    ePub EPUB The open industry format known for its reflowable content and usability on supported mobile devices.

    Adobe Reader PDF The popular standard, used most often with the free Adobe® Reader® software.

    This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.

Not for Sale

Description

  • Copyright 2013
  • Edition: 1st
  • eBook (Watermarked)
  • ISBN-10: 0-13-325942-0
  • ISBN-13: 978-0-13-325942-1

SAVE TIME AND SUPERCHARGE EXCEL 2013 WITH VBA AND MACROS!

Use Excel® 2013 VBA and Macros to automate virtually any routine task, and save yourself hours, days, maybe
even weeks. Then, learn how to make Excel do things you thought were simply impossible! You’ll discover macro
techniques you won’t find anywhere else and learn how to create automated reports that are amazingly powerful
and useful. Bill Jelen and Tracy Syrstad show how to instantly visualize information, so you and your colleagues
can understand and act on it…how to capture data from anywhere, and use it anywhere…how to automate
Excel 2013’s most valuable new features. Mastering advanced Excel macros has never been easier. You’ll find
simple, step-by-step instructions, real-world examples and case studies, and 50 workbooks packed with bonus
examples, macros, and solutions–straight from MrExcel.

•   Get started fast with Excel 2013 macro development

•   Write macros that use Excel 2013 enhancements, including Timelines and the latest pivot table models

•   Work efficiently with ranges, cells, and R1C1-style formulas

•   Build super-fast applications with arrays

•   Write Excel 2013 VBA code that works on older versions of Excel

•   Create custom dialog boxes to collect information from your users

•   Use error handling to make your macros more resilient

•   Use web queries and new web service functions to integrate data from anywhere

•   Master advanced techniques such as classes, collections, and custom functions

•   Build sophisticated data mining and business analysis applications

•   Read and write to both Access and SQL Server databases

•   Control other Office programs–and even control Windows itself

•   Start writing Excel Apps similar to those in the Excel App Store

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

Sample Content

Table of Contents

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

9780789748616, 1/14/2031, TOC

Updates

Submit Errata

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020