Excel 2016 VBA and Macros

Name: Excel 2016 VBA and Macros
Brand: Addison Wesley
Price: 23.99 EUR
Availability: OnlineOnly

Bill Jelen Tracy Syrstad(Autor*in)

Addison Wesley (Verlag)

1. Auflage

Erschienen am 3. November 2015

640 Seiten

E-Book

ePUB mit Adobe-DRM

Systemvoraussetzungen

978-0-13-438603-4 (ISBN)

23,99 €inkl. 7% MwSt.

Systemvoraussetzungen

für ePUB mit Adobe-DRM

E-Book Einzellizenz

Als Download verfügbar

Beschreibung

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.

Weitere Details

Weitere Ausgaben

Personen

Inhalt

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

Systemvoraussetzungen

Als PDF speichern Als Link merken