Excel 2013 VBA and Macros

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