- Copyright
- About the Authors
- Acknowledgments
- We Want to Hear from You!
- Introduction
- 1. Unleash the Power of Excel with VBA
- The Power of Excel
- Barriers to Entry
- The Macro Recorder Doesn’t Work!
- Knowing Your Tools—The Developer Ribbon
- Macro Security
- Overview of Recording, Storing, and Running a Macro
- Running a Macro
- Using New File Types in Excel 2007
- Understanding the Visual Basic Editor
- Understanding Shortcomings of the Macro Recorder
- Next Steps: Learning VBA Is the Solution
- 2. This Sounds Like BASIC, So Why Doesn’t It Look Familiar?
- I Can’t Understand This Code
- Understanding the Parts of VBA “Speech”
- Is VBA Really This Hard? No!
- Examining Recorded Macro Code—Using the VB Editor and Help
- Using Debugging Tools to Figure Out Recorded Code
- The Ultimate Reference to All Objects, Methods, Properties
- Five Easy Tips for Cleaning Up Recorded Code
- Putting It All Together—Fixing the Recorded Code
- Next Steps
- 3. Referring to Ranges
- The Range Object
- Using the Upper-Left and Lower-Right Corners of a Selection to Specify a Range
- Named Ranges
- Shortcut for Referencing Ranges
- Referencing Ranges in Other Sheets
- Referencing a Range Relative to Another Range
- Using the Cells Property to Select a Range
- Using the Offset Property to Refer to a Range
- Using the Resize Property to Change the Size of a Range
- Using the Columns and Rows Properties to Specify a Range
- Using the Union Method to Join Multiple Ranges
- Using the Intersect Method to Create a New Range from Overlapping Ranges
- Using the ISEMPTY Function to Check Whether a Cell Is Empty
- Using the CurrentRegion Property to Quickly Select a Data Range
- Using the Areas Collection to Return a Noncontiguous Range
- Referencing Tables
- Next Steps
- 4. User-Defined Functions
- Creating User-Defined Functions
- Sharing UDFs
- Useful Custom Excel Functions
- Set the Current Workbook’s Name in a Cell
- Set the Current Workbook’s Name and File Path in a Cell
- Check Whether a Workbook Is Open
- Check Whether a Sheet in an Open Workbook Exists
- Count the Number of Workbooks in a Directory
- Retrieve USERID
- Retrieve Date and Time of Last Save
- Retrieve Permanent Date and Time
- Validate an Email Address
- Sum Cells Based on the Interior Color
- Count Unique Values
- Remove Duplicates from a Range
- Find the First Non-Zero-Length Cell in a Range
- Substitute Multiple Characters
- Retrieve Numbers from Mixed Text
- Convert Week Number into Date
- Separate Delimited String
- Sort and Concatenate
- Sort Numeric and Alpha Characters
- Search for a String within Text
- Reverse the Contents of a Cell
- Multiple Max
- Return Hyperlink Address
- Return the Column Letter of a Cell Address
- Static Random
- Using Select Case on a Worksheet
- Next Steps
- 5. Looping and Flow Control
- 6. R1C1-Style Formulas
- Referring to Cells: A1 Versus R1C1 References
- Switching Excel to Display R1C1 Style References
- The Miracle of Excel Formulas
- Explanation of R1C1 Reference Style
- Conditional Formatting—R1C1 Required
- Array Formulas Require R1C1 Formulas
- Next Steps
- 7. What’s New in Excel 2007 and What’s Changed
- 8. Create and Manipulate Names in VBA
- 9. Event Programming
- Levels of Events
- Using Events
- Workbook Events
- Workbook_Activate()
- Workbook_Deactivate()
- Workbook_Open()
- Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
- Workbook_BeforePrint(Cancel As Boolean)
- Workbook_BeforeClose(Cancel As Boolean)
- Workbook_NewSheet(ByVal Sh As Object)
- Workbook_WindowResize(ByVal Wn As Window)
- Workbook_WindowActivate(ByVal Wn As Window)
- Workbook_WindowDeactivate(ByVal Wn As Window)
- Workbook_AddInInstall()
- Workbook_AddInUninstall
- Workbook_SheetActivate(ByVal Sh As Object)
- Workbook_SheetBeforeDoubleClick (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
- Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
- Workbook_SheetCalculate(ByVal Sh As Object)
- Workbook_SheetChange (ByVal Sh As Object, ByVal Target As Range)
- Workbook_Sync(ByVal SyncEventType As Office.MsoSyncEventType)
- Workbook_SheetDeactivate (ByVal Sh As Object)
- Workbook_SheetFollowHyperlink (ByVal Sh As Object, ByVal Target As Hyperlink)
- Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
- Workbook_PivotTableCloseConnection(ByVal Target As PivotTable)
- Workbook_PivotTableOpenConnection(ByVal Target As PivotTable)
- Workbook_RowsetComplete(ByVal Description As String, ByVal Sheet As String, ByVal Success As Boolean)
- Worksheet Events
- Worksheet_Activate()
- Worksheet_Deactivate()
- Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
- Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
- Worksheet_Calculate()
- Worksheet_Change(ByVal Target As Range)
- Worksheet_SelectionChange(ByVal Target As Range)
- Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
- Chart Sheet Events
- Embedded Charts
- Chart_Activate()
- Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
- Chart_BeforeRightClick(Cancel As Boolean)
- Chart_Calculate()
- Chart_Deactivate()
- Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
- Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
- Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
- Chart_Resize()
- Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
- Chart_SeriesChange(ByVal SeriesIndex As Long, ByVal PointIndex As Long)
- Chart_DragOver()
- Chart_DragPlot()
- Application-Level Events
- AppEvent_AfterCalculate()
- AppEvent_NewWorkbook(ByVal Wb As Workbook)
- AppEvent_SheetActivate (ByVal Sh As Object)
- AppEvent_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
- AppEvent_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
- AppEvent_SheetCalculate(ByVal Sh As Object)
- AppEvent_SheetChange(ByVal Sh As Object, ByVal Target As Range)
- AppEvent_SheetDeactivate(ByVal Sh As Object)
- AppEvent_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
- AppEvent_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
- AppEvent_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
- AppEvent_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window)
- AppEvent_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window)
- AppEvent_WorkbookActivate(ByVal Wb As Workbook)
- AppEvent_WorkbookAddinInstall(ByVal Wb As Workbook)
- AppEvent_WorkbookAddinUninstall(ByVal Wb As Workbook)
- AppEvent_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
- AppEvent_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
- AppEvent_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
- AppEvent_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)
- AppEvent_WorkbookOpen(ByVal Wb As Workbook)
- AppEvent_WorkbookPivotTableCloseConnection(ByVal Wb As Workbook, ByVal Target As PivotTable)
- AppEvent_WorkbookPivotTableOpenConnection(ByVal Wb As Workbook, ByVal Target As PivotTable)
- AppEvent_WorkbookRowsetComplete(ByVal Wb As Workbook, ByVal Description As String, ByVal Sheet As String, ByVal Success As Boolean)
- AppEvent_WorkbookSync(ByVal Wb As Workbook, ByVal SyncEventType As Office.MsoSyncEventType)
- Next Steps
- 10. Userforms—An Introduction
- 11. Creating Charts
- Charting in Excel 2007
- Coding for New Charting Features in Excel 2007
- Referencing Charts and Chart Objects in VBA Code
- Creating a Chart
- Recording Commands from the Layout or Design Ribbons
- Using SetElement to Emulate Changes on the Layout Ribbon
- Changing a Chart Title Using VBA
- Emulating Changes on the Format Ribbon
- Using the Watch Window to Discover Object Settings
- Creating Advanced Charts
- Exporting a Chart as a Graphic
- Creating Pivot Charts
- Next Steps
- 12. Data Mining with Advanced Filter
- Advanced Filter Is Easier in VBA Than in Excel
- Using Advanced Filter to Extract a Unique List of Values
- Using Advanced Filter with Criteria Ranges
- Using Filter in Place in Advanced Filter
- The Real Workhorse: xlFilterCopy with All Records Rather than Unique Records Only
- Using AutoFilter
- Next Steps
- 13. Using VBA to Create Pivot Tables
- Introducing Pivot Tables
- Understanding Versions
- Creating a Vanilla Pivot Table in the Excel Interface
- Building a Pivot Table in Excel VBA
- Creating a Report Showing Revenue by Product
- Handling Additional Annoyances When Creating Your Final Report
- Addressing Issues with Two or More Data Fields
- Summarizing Date Fields with Grouping
- Using Advanced Pivot Table Techniques
- Controlling the Sort Order Manually
- Using Sum, Average, Count, Min, Max, and More
- Creating Report Percentages
- Using New Pivot Table Features in Excel 2007
- Next Steps
- 14. Excel Power
- 15. Data Visualizations and Conditional Formatting
- Introduction to Data Visualizations
- New VBA Methods and Properties for Data Visualizations
- Adding Data Bars to a Range
- Adding Color Scales to a Range
- Adding Icon Sets to a Range
- Using Visualization Tricks
- Using Other Conditional Formatting Methods
- Formatting Cells That Are Above or Below Average
- Formatting Cells in the Top 10 or Bottom 5
- Formatting Unique or Duplicate Cells
- Formatting Cells Based on Their Value
- Formatting Cells That Contain Text
- Formatting Cells That Contain Dates
- Formatting Cells That Contain Blanks or Errors
- Using a Formula to Determine Which Cells to Format
- Using the New NumberFormat Property
- Next Steps
- 16. Reading from and Writing to the Web
- 17. XML in Excel 2007
- 18. Automating Word
- 19. Arrays
- 20. Text File Processing
- 21. Using Access as a Back End to Enhance Multi-User Access to Data
- 22. Creating Classes, Records, and Collections
- 23. Advanced Userform Techniques
- 24. Windows Application Programming Interface (API)
- 25. Handling Errors
- 26. Customizing the Ribbon to Run Macros
- Out with the Old, In with the New
- Where to Add Your Code: customui Folder and File
- Creating the Tab and Group
- Adding a Control to Your Ribbon
- Accessing the File Structure
- Understanding the RELS File
- Renaming the Excel File and Opening the Workbook
- Using Images on Buttons
- Troubleshooting Error Messages
- The Attribute “Attribute Name” on the Element “customui Ribbon” Is Not Defined in the DTD/Schema
- Illegal Qualified Name Character
- Element “customui Tag Name” Is Unexpected According to Content Model of Parent Element “customui Tag Name”
- Excel Found Unreadable Content
- Wrong Number of Arguments or Invalid Property Assignment
- Nothing Happens
- Other Ways to Run a Macro
- Next Steps
- 27. Creating Add-Ins
Thanks to Tracy Syrstad for being a great co-author and for doing a great job of managing all the consulting projects at MrExcel. Mala Singh is the smartest charting wizard I know and vetted the chapter on charting with VBA. Jerry Kohl provided inspiration for many of the ideas in this book.
Thanks to everyone who ever asked a question at the Power Macros class at the University of Akron. You helped improve this second edition. Thanks to all the MrExcel.com readers and MVPs and clients. Thanks to Chad Rothschiller at Microsoft for teaching me everything there is to know about Excel XML. Thanks to Dave Gainer, Steve Zaske, Eric Patterson, and Joe Chirilov at Microsoft. Thanks to Pam Gensel for macro lesson number 1 and to Robert F. Jelen for being my first programming fan. Robert K. Jelen, Emil T. Hoffman, and Khalil F. Matta were early influences who shaped my decision to start programming.
Thanks to Leo Laporte, everyone at The Lab with Leo, and Craig Crossman for being great tech journalists.
Thanks to Dan Bricklin and Bob Frankston for inventing the computer spreadsheet. Thanks to Mitch Kapor for Lotus 1-2-3. Like everyone else who uses computers to make a living, I owe a debt of gratitude to these three pioneers.
Thanks to Barb Jelen for keeping MrExcel running while I wrote. Lora White is our office manager and was forced into riding shotgun and shooting screen shots for this book on long car trips. As always, thanks to the hundreds of people answering 30,000 Excel questions a year at the MrExcel message board. Thanks to Duane Aubin, Wei Jiang, Suat Ozgur, Nate Oliver, and Jake Hildebrand for their programming expertise.
At Pearson, Loretta Yates is an awesome acquisitions editor. Thanks to Judi Taylor, Greg Wiegand, Betsy Harris, Laura Norman, Christian A. Kenyeres, and Keith Cline. Thanks to William Brown and Waterside.
Thanks to Lavan Alexander, Steve Aronson, Oliver Berghaus, Von Brinkley, Wendy Cooke, Chris Crockett, Christy Dare, Joe Degraauw, Todd Feltner, Sylvia Gomez, Louis Grajeda, Jennie Heskin, Michael Himes, Mary Hulshouser, Steve Hunt, Edward Hyden, Richard Irwin, Therese Klassen, Shirley Miller, George Moore, Michael Moreno, Ali Mozaffari, Kiesha Neil, Jay Nelapudi, Ky Nguyen, Victor Omiyale, Andrea Patzkowsky, Earl Pearce, Courtney Robinson, Juan Rubio, Kim Sabatini, Chris Simmons, Michael Snowden, Marlin Snyder, Laree Snyder, Todd Thornbrough, Derek Truman, and Yan Yang for suggestions on the content of Chapter 6 during the first Data Analyst Boot Camp in Frisco, TX.
Finally, thanks to Josh Jelen, Zeke Jelen, and Mary Ellen Jelen.
—Bill
Thanks to Juan Pablo González Ruiz for offering his expertise, especially with the functions found in Chapter 4. Thanks to Daniel Klann, Dennis Wallentin, Ivan F. Moala, Juan Pablo González Ruiz, Masaru Kaji, Nathan P. Oliver, Richie Sills, Russell Hauf, Suat Mehmet Ozgur, Tom Urtis, Tommy Miles, and Wei Jiang for their contributions to Chapter 14.
Thanks to the MrExcel.com MVPs for helping me understand the intricacies of the Excel world we share. Thanks to Jacob Hilderbrand for his expertise on automating Word.
Thanks to the developers of DOOM 3—it kept John occupied as I worked late into the night. Thanks to Dani Jaacks for forcing me to take a break when I needed one.
And last, but not least, thanks to Bill Jelen. His site, MrExcel.com, is a place where thousands come for help. It’s also a place where I, and others like me, have an opportunity to assist others.
—Tracy
-
No Comment