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
