VBA and Macros: Microsoft Excel 2010 (MrExcel Library)

Contents......Page 4
Reduce the Learning Curve......Page 24
Does This Book Teach Excel?......Page 25
Versions of Excel......Page 27
Special Elements and Typographical Conventions......Page 28
Next Steps......Page 29
The Macro Recorder Doesn’t Work!......Page 30
Great News: Excel with VBA Is Worth the Effort......Page 31
Knowing Your Tools: The Developer Tab......Page 32
Adding a Trusted Location......Page 33
Using Macro Settings to Enable Macros in Workbooks Outside of Trusted Locations......Page 34
Overview of Recording, Storing, and Running a Macro......Page 35
Filling Out the Record Macro Dialog......Page 36
Creating a Macro Button on the Ribbon......Page 37
Creating a Macro Button on the Quick Access Toolbar......Page 38
Assigning a Macro to a Form Control, Text Box, or Shape......Page 39
Using New File Types in Excel 2010......Page 41
VB Editor Settings......Page 42
The Project Explorer......Page 43
Understanding Shortcomings of the Macro Recorder......Page 44
Examining Code in the Programming Window......Page 46
Running the Macro on Another Day Produces Undesired Results......Page 48
Possible Solution: Use Relative References When Recording......Page 49
Never Use the AutoSum Button While Recording a Macro......Page 53
Three Tips When Using the Macro Recorder......Page 54
Next Steps......Page 55
I Can’t Understand This Code......Page 56
Understanding the Parts of VBA “Speech”......Page 57
VBA Help Files: Using F1 to Find Anything......Page 60
Examining Recorded Macro Code: Using the VB Editor and Help......Page 62
Defined Constants......Page 64
Stepping Through Code......Page 69
Backing Up or Moving Forward in Code......Page 72
Querying Anything While Stepping Through Code......Page 73
Using a Watch on an Object......Page 78
Object Browser: The Ultimate Reference......Page 79
Tip 1: Don’t Select Anything......Page 81
Tip 3: Ride the Range from the Bottom to Find Last Row......Page 82
Tip 4: Use Variables to Avoid Hard-Coding Rows and Formulas......Page 83
Tip 7: Use With...End With to Perform Multiple Actions......Page 84
Next Steps......Page 87
The Range Object......Page 88
Shortcut for Referencing Ranges......Page 89
Referencing Ranges in Other Sheets......Page 90
Use the Cells Property to Select a Range......Page 91
Use the Offset Property to Refer to a Range......Page 92
Use the Resize Property to Change the Size of a Range......Page 94
Use the Union Method to Join Multiple Ranges......Page 95
Use the ISEMPTY Function to Check Whether a Cell Is Empty......Page 96
Use the CurrentRegion Property to Select a Data Range......Page 97
Referencing Tables......Page 100
Next Steps......Page 101
Creating User-Defined Functions......Page 102
Sharing UDFs......Page 104
Set the Current Workbook’s Name and File Path in a Cell......Page 105
Check Whether a Sheet in an Open Workbook Exists......Page 106
Count the Number of Workbooks in a Directory......Page 107
Retrieve USERID......Page 108
Retrieve Date and Time of Last Save......Page 109
Retrieve Permanent Date and Time......Page 110
Validate an E-mail Address......Page 111
Sum Cells Based on Interior Color......Page 112
Count Unique Values......Page 113
Remove Duplicates from a Range......Page 114
Find the First Nonzero-Length Cell in a Range......Page 116
Substitute Multiple Characters......Page 117
Retrieve Numbers from Mixed Text......Page 118
Separate Delimited String......Page 119
Sort and Concatenate......Page 120
Sort Numeric and Alpha Characters......Page 122
Search for a String Within Text......Page 123
Multiple Max......Page 124
Return Hyperlink Address......Page 125
Static Random......Page 126
Using Select Case on a Worksheet......Page 127
Next Steps......Page 128
For…Next Loops......Page 130
Variations on the For...Next Loop......Page 133
Exiting a Loop Early After a Condition Is Met......Page 134
Nesting One Loop Inside Another Loop......Page 135
Do Loops......Page 136
Using the While or Until Clause in Do Loops......Page 138
Object Variables......Page 140
Flow Control: Using If...Then...Else and Select Case......Page 143
If...Then...End If......Page 144
Using If...Else If...End If for Multiple Conditions......Page 145
Using Select Case...End Select for Multiple Conditions......Page 146
Nesting If Statements......Page 147
Next Steps......Page 149
Referring to Cells: A1 Versus R1C1 References......Page 150
Switching Excel to Display R1C1-Style References......Page 151
Enter a Formula Once and Copy 1,000 Times......Page 152
The Secret: It’s Not That Amazing......Page 153
Using R1C1 with Relative Reference......Page 155
Using R1C1 with Mixed References......Page 156
Replacing Many A1 Formulas with a Single R1C1 Formula......Page 157
Remembering Column Numbers Associated with Column Letters......Page 159
Array Formulas Require R1C1 Formulas......Page 160
Next Steps......Page 161
Charts......Page 162
Conditional Formatting......Page 163
Sorting......Page 164
SmartArt......Page 165
Learning the New Objects and Methods......Page 166
Version......Page 167
Excel8CompatibilityMode......Page 168
Next Steps......Page 169
Global Versus Local Names......Page 170
Adding Names......Page 171
Deleting Names......Page 172
Types of Names......Page 173
Strings......Page 174
Numbers......Page 175
Using Arrays in Names......Page 176
Reserved Names......Page 177
Checking for the Existence of a Name......Page 178
Next Steps......Page 181
Levels of Events......Page 182
Event Parameters......Page 183
Workbook_Open()......Page 184
Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)......Page 185
Workbook_BeforeClose(Cancel As Boolean)......Page 186
Workbook_WindowResize(ByVal Wn As Window)......Page 187
Workbook_RowsetComplete(ByVal Description As String, ByVal Sheet As String, ByVal Success As Boolean)......Page 188
Workbook Level Sheet and Chart Events......Page 189
Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)......Page 191
Worksheet_Calculate()......Page 192
Worksheet_SelectionChange(ByVal Target As Range)......Page 193
Worksheet_FollowHyperlink(ByVal Target As Hyperlink)......Page 194
Embedded Charts......Page 195
Chart_Deactivate()......Page 196
Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)......Page 197
Chart_DragPlot()......Page 198
AppEvent_AfterCalculate()......Page 199
AppEvent_SheetActivate (ByVal Sh As Object)......Page 200
AppEvent_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)......Page 201
AppEvent_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)......Page 202
AppEvent_WorkbookPivotTableOpenConnection(ByVal Wb As Workbook, ByVal Target As PivotTable)......Page 203
AppEvent_WorkbookAfterXmlImport(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal IsRefresh As Boolean, ByVal Result As XlXmlImportResult)......Page 204
Next Steps......Page 205
Input Boxes......Page 206
Creating a Userform......Page 207
Userform Events......Page 209
Programming Controls......Page 211
Using Labels, Text Boxes, and Command Buttons......Page 212
Deciding Whether to Use List Boxes or Combo Boxes in Forms......Page 214
Adding Option Buttons to a Userform......Page 217
Adding Graphics to a Userform......Page 218
Using a Spin Button on a Userform......Page 219
Using the MultiPage Control to Combine Forms......Page 221
Illegal Window Closing......Page 223
Getting a Filename......Page 224
Next Steps......Page 225
Referencing Charts and Chart Objects in VBA Code......Page 226
Specifying the Size and Location of a Chart......Page 227
Later Referring to a Specific Chart......Page 229
Specifying a Built-in Chart Type......Page 231
Specifying a Template Chart Type......Page 233
Changing a Chart’s Layout or Style......Page 234
Using SetElement to Emulate Changes on the Layout Tab......Page 236
Using the Format Method to Access Formatting Options......Page 241
Creating Advanced Charts......Page 257
Creating True Open-High-Low-Close Stock Charts......Page 258
Creating Bins for a Frequency Chart......Page 259
Creating a Stacked Area Chart......Page 262
Creating a Dynamic Chart in a Userform......Page 267
Creating Pivot Charts......Page 269
Next Steps......Page 271
Replacing a Loop with AutoFilter......Page 272
Using New AutoFilter Techniques......Page 274
Selecting Visible Cells Only......Page 278
Advanced Filter Is Easier in VBA Than in Excel......Page 280
Using Advanced Filter to Extract a Unique List of Values......Page 281
Extracting a Unique List of Values with the User Interface......Page 282
Extracting a Unique List of Values with VBA Code......Page 283
Getting Unique Combinations of Two or More Fields......Page 286
Using Advanced Filter with Criteria Ranges......Page 288
Other Slightly Complex Criteria Ranges......Page 290
The Most Complex Criteria: Replacing the List of Values with a Condition Created as the Result of a Formula......Page 291
Using Filter in Place in Advanced Filter......Page 298
The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only......Page 299
Copying All Columns......Page 300
Copying a Subset of Columns and Reordering......Page 301
Using Filter in Place with Unique Records Only......Page 306
Next Steps......Page 308
Understanding Versions......Page 310
New Beginning with Excel 2007......Page 311
Creating a Vanilla Pivot Table in the Excel Interface......Page 313
Understanding Compact Layout......Page 316
Building a Pivot Table in Excel VBA......Page 317
Creating and Configuring the Pivot Table......Page 318
Adding Fields to the Data Area......Page 319
Determining Size of a Finished Pivot Table to Convert the Pivot Table to Values......Page 322
Using Multiple Value Fields......Page 325
Grouping Daily Dates to Months, Quarters, or Years......Page 326
Changing the Calculation to Show Percentages......Page 328
Controlling the Sort Order with AutoSort......Page 331
Replicating the Report for Every Product......Page 332
Manually Filtering Two or More Items in a Pivot Field......Page 335
Using the Conceptual Filters......Page 336
Using the Search Filter......Page 339
Setting Up Slicers to Filter a Pivot Table......Page 342
Filtering an OLAP Pivot Table Using Named Sets......Page 344
Calculated Data Fields......Page 347
Changing the Layout from the Design Tab......Page 348
Suppressing Subtotals for Multiple Row Fields......Page 349
Next Steps......Page 350
List Files in a Directory......Page 352
Import CSV......Page 354
Read Entire TXT to Memory and Parse......Page 355
Separate Worksheets into Workbooks......Page 356
Combine Workbooks......Page 357
Filter and Copy Data to Separate Worksheets......Page 358
Export Data to Word......Page 359
List Comments......Page 360
Resize Comments......Page 362
Resize Comments with Centering......Page 363
Place a Chart in a Comment......Page 364
Using Conditional Formatting to Highlight Selected Cell......Page 365
Highlight Selected Cell Without Using Conditional Formatting......Page 367
Custom Transpose Data......Page 368
Select/Deselect Noncontiguous Cells......Page 370
Pivot Table Drill-Down......Page 372
Speedy Page Setup......Page 373
Calculating Time to Execute Code......Page 376
Custom Sort Order......Page 377
Cell Progress Indicator......Page 378
Protected Password Box......Page 379
Change Case......Page 382
ActiveX Right-Click Menu......Page 383
Historical Stock/Fund Quotes......Page 385
Using VBA Extensibility to Add Code to New Workbooks......Page 386
Next Steps......Page 388
Introduction to Data Visualizations......Page 390
VBA Methods and Properties for Data Visualizations......Page 391
Adding Data Bars to a Range......Page 392
Adding Color Scales to a Range......Page 397
Adding Icon Sets to a Range......Page 398
Specifying an Icon Set......Page 399
Specifying Ranges for Each Icon......Page 400
Creating an Icon Set for a Subset of a Range......Page 401
Using Two Colors of Data Bars in a Range......Page 403
Using Other Conditional Formatting Methods......Page 405
Formatting Cells in the Top 10 or Bottom 5......Page 406
Formatting Unique or Duplicate Cells......Page 407
Formatting Cells Based on Their Value......Page 408
Formatting Cells That Contain Dates......Page 409
Using a Formula to Determine Which Cells to Format......Page 410
Using the New NumberFormat Property......Page 411
Next Steps......Page 412
Getting Data from the Web......Page 414
Manually Creating a Web Query and Refreshing with VBA......Page 415
Using VBA to Update an Existing Web Query......Page 418
Building Many Web Queries with VBA......Page 419
Using Application.OnTime to Periodically Analyze Data......Page 422
Canceling a Previously Scheduled Macro......Page 423
Scheduling a Macro to Run x Minutes in the Future......Page 424
Scheduling a Verbal Reminder......Page 425
Scheduling a Macro to Run Every 2 Minutes......Page 426
Publishing Data to a Web Page......Page 427
Using VBA to Create Custom Web Pages......Page 429
Using Excel as a Content Management System......Page 430
Bonus: FTP from Excel......Page 432
Next Steps......Page 433
17 Dashboarding with Sparklines in Excel 2010......Page 434
Creating Sparklines......Page 435
Scaling the Sparklines......Page 437
Using Theme Colors......Page 441
Using RGB Colors......Page 444
Formatting Sparkline Elements......Page 446
Formatting Win/Loss Charts......Page 449
Creating a Dashboard......Page 450
Creating 100’s of Individual Sparklines in a Dashboard......Page 451
Next Steps......Page 455
Early Binding......Page 456
Compile Error: Can’t Find Object or Library......Page 458
Late Binding......Page 459
The New Keyword......Page 460
GetObject Function......Page 461
Using Constant Values......Page 462
Using the Object Browser to Retrieve the Real Value of a Constant......Page 463
Understanding Word’s Objects......Page 464
Document Object......Page 465
Selection Object......Page 466
Range Object......Page 467
Bookmarks......Page 471
Controlling Form Fields in Word......Page 473
Next Steps......Page 475
Declare an Array......Page 476
Multidimensional Arrays......Page 477
Fill an Array......Page 478
Empty an Array......Page 479
Arrays Make It Easier to Manipulate Data, but Is That All?......Page 480
Dynamic Arrays......Page 482
Passing an Array......Page 483
Next Steps......Page 484
Importing Text Files with Fewer Than 1,048,576 Rows......Page 486
Reading Text Files with More Than 1,048,576 Rows......Page 493
Writing Text Files......Page 496
Next Steps......Page 497
21 Using Access as a Back End to Enhance Multiuser Access to Data......Page 498
ADO Versus DAO......Page 499
The Tools of ADO......Page 501
Adding a Record to the Database......Page 503
Retrieving Records from the Database......Page 504
Updating an Existing Record......Page 506
Summarizing Records via ADO......Page 508
Checking for the Existence of Tables......Page 510
Checking for the Existence of a Field......Page 511
Adding a Field On the Fly......Page 512
SQL Server Examples......Page 513
Next Steps......Page 514
Inserting a Class Module......Page 516
Application Events......Page 517
Embedded Chart Events......Page 518
Creating a Custom Object......Page 520
Using a Custom Object......Page 521
Using Property Let and Property Get to Control How Users Utilize Custom Objects......Page 522
Creating a Collection in a Standard Module......Page 524
Creating a Collection in a Class Module......Page 525
User-Defined Types......Page 529
Next Steps......Page 532
More Userform Controls......Page 534
Check Boxes......Page 535
Tab Strips......Page 536
RefEdit......Page 538
Using a Scrollbar As a Slider to Select Values......Page 540
Controls and Collections......Page 542
Modeless Userforms......Page 544
Using Hyperlinks in Userforms......Page 545
Adding Controls at Runtime......Page 546
Resizing the Userform On-the-fly......Page 547
Adding Other Controls......Page 548
Adding an Image On-the-fly......Page 549
Putting It All Together......Page 550
Showing Accelerator Keys......Page 552
Coloring the Active Control......Page 553
Transparent Forms......Page 556
Next Steps......Page 557
What Is the Windows API?......Page 558
Understanding an API Declaration......Page 559
API Examples......Page 560
Retrieve the Computer Name......Page 561
Check Whether an Excel File Is Open on a Network......Page 562
Retrieve Display-Resolution Information......Page 563
Disable the X for Closing a Userform......Page 564
Running Timer......Page 565
Retrieving a File Path......Page 566
Next Steps......Page 570
What Happens When an Error Occurs?......Page 572
Debug Error Inside Userform Code Is Misleading......Page 574
Basic Error Handling with the On Error GoTo Syntax......Page 575
Handling Errors by Choosing to Ignore Them......Page 577
Encountering Errors on Purpose......Page 579
Runtime Error 9: Subscript Out of Range......Page 580
RunTime Error 1004: Method Range of Object Global Failed......Page 581
The Ills of Protecting Code......Page 582
More Problems with Passwords......Page 583
Errors Caused by Different Versions......Page 584
Next Steps......Page 585
Out with the Old, In with the New......Page 586
Where to Add Your Code: customui Folder and File......Page 587
Creating the Tab and Group......Page 588
Adding a Control to Your Ribbon......Page 589
Understanding the RELS File......Page 594
Using Images on Buttons......Page 595
Microsoft Office Icons......Page 596
Custom Icon Images......Page 597
The Attribute “Attribute Name” on the Element “customui Ribbon” Is Not Defined in the DTD/Schema......Page 600
Element “customui Tag Name” Is Unexpected According to Content Model of Parent Element “customui Tag Name”......Page 601
Excel Found Unreadable Content......Page 602
Keyboard Shortcut......Page 603
Attach a Macro to a Command Button......Page 604
Attach a Macro to a Shape......Page 605
Attach a Macro to an ActiveX Control......Page 606
Running a Macro from a Hyperlink......Page 607
Next Steps......Page 608
Characteristics of Standard Add-Ins......Page 610
Converting an Excel Workbook to an Add-In......Page 611
Using Save As to Convert a File to an Add-In......Page 612
Using the VB Editor to Convert a File to an Add-In......Page 613
Having Your Client Install the Add-In......Page 614
Standard Add-Ins Are Not Secure......Page 615
Using a Hidden Workbook as an Alternative to an Add-In......Page 616
Next Steps......Page 618
A......Page 620
C......Page 624
D......Page 629
E......Page 631
F......Page 633
G......Page 635
I......Page 636
L......Page 637
M......Page 638
N......Page 639
O......Page 640
P......Page 641
R......Page 642
S......Page 645
T......Page 647
U......Page 648
V......Page 650
W......Page 651
X-Y-Z......Page 653