About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store



Click here to order the book!


  "In this day and age of 'too much information and not enough
time,' the ability to get to the bottom line quickly and in a
concise method is what excels companies to the top of their
industry. The techniques in this book will allow you to do things"
you only dreamt of."

         -Jerry Kohl, president of Brighton Collectibles

VBA and Macros for Microsoft Excel 2007

"Develop your Excel macro programming skills using VBA instantly with proven techniques and STOP producing manual reports!"

As the macro language for Microsoft Excel, Visual Basic for Applications enables you to achieve tremendous efficiencies in your day-to-day use of Excel. Stop producing those manual reports! The solution is to automate those manual processes in Excel using Visual Basic for Applications (VBA). Every copy of Excel shipped since 1993 includes VBA lurking behind the cells of the worksheet.

As MrExcel Consulting, Jelen and Syrstad have written code for hundreds of clients around the world. With 200 million users of Microsoft Excel worldwide, there are too many potential clients and not enough consultants to go around. This book describes everything you could conceivably need to know to automate reports and design applications in Excel VBA. Whether you want to write macros for yourself, automate reports for your office or design full-blown applications for others, this book is for you.

"You are an Expert at Excel, but the Macro Recorder doesn't work and you can't make heads or tails out of the recorded code!"

If this is you, buy this book! Macros that you record today might work today but not tomorrow. Recorded macros might handle a dataset with 14 records but not one with 12 or 16 records. These are all common problems with the macro recorder that unfortunately cause too many Excel gurus to turn away from writing macros.

Click here to order the book!

"Learn why the Macro Recorder does not work AND the steps needed to convert recorded code into code that will work! "

There are two barriers to entry in learning Excel VBA. First, the macro recorder does a lousy job with about 10% of the code that it records. Something might work today but not tomorrow. Learn the steps needed to convert recorded code into code that will work every day with every dataset. Second, although "Visual Basic" sounds like "BASIC", they are not at all similar. If you've ever taken a class in BASIC or any other procedural language, this is actually a hindrance to figuring out VBA.

In this book, Jelen and Syrstad reveal exactly why the macro recorder fails. They will teach you how to understand recorded code so that you can quickly edit and improve the 10% of recorded code that is preventing your applications from running flawlessly every day.

"Learn how to customize the ribbon using VBA and RibbonX!"

Excel 2007 introduces a new ribbon interface. All of your old code to add items to the Excel 2003 menu will cause ugly buttons to be added to the Add-Ins tab. Now…learn the simple steps to create new custom groups on an existing tab or how to add your own ribbon tab! See how to add custom images to a button, and more!

About the Authors

Bill Jelen, Excel MVP and MrExcel, has been using spreadsheets since 1985, and he launched the website in 1998! Bill has made more than 50 guest appearances on Call for Help with Leo Laporte and has produced more than 250 episodes of his daily video podcast, Learn Excel from MrExcel. He is the host of Total Training’s Excel 2007 Advanced DVD. He also enjoys taking his show on the road, doing a one- to four-hour power Excel seminar anywhere that a room full of accountants or Excellers will show up. Before founding, Jelen spent 12 years in the trenches–working as a financial analyst for finance, marketing, accounting, and operations departments of a $500 million public company. He lives near Akron, Ohio, with his wife, Mary Ellen, and sons, Josh and Zeke.

Tracy Syrstad remembers the painful trek up the VBA learning curve while developing applications for herself and co-workers at a former job. Now, as the project manager for the MrExcel consulting team, she enjoys helping clients develop custom solutions for their unique situations, observing the myriad ways people use Excel and other Microsoft Office applications.

Click here to order the book!

"Learn how to do things you never dreamt were possible."

Detailed Table of Contents

  • Getting Results with VBA
  • What Is in This Book
  • The Future of VBA and Windows Versions of Excel
  • Special Elements and Typographical Conventions
  • Code Files
  • Next Steps

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, Sorting, 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 ISEPMTY 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
  • Custom Functions - Example and Explanation
  • Sharing UDF's
  • Useful Custom Excel Functions
  • Next Steps
5. Looping and Flow Control
  • For...Next Loops
  • Do Loops
  • The VBA Loop: For Each
  • Flow Control: Using If...Then...Else...End If
  • Either/Or Decisions: If...Then...Else...End If
  • Next Steps
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
7. Migrating Your Excel 2003 Apps to 2007 (a.k.a.What’s New in Excel 2007 and What Won’t Work Anymore!)
  • If It's Changed in the Front End, It's Changed in VBA
  • The Macro Recorder Won't Record Actions That It Did Record in Earlier Excel Versions
  • Learning the new Objects and Methods
  • Compatibility Mode
  • Next Steps
8. Create and Manipulate Names in VBA
  • Excel Names
  • Global Versus Local Names
  • Adding Names
  • Deleting Names
  • Adding Comments
  • Types of Names
  • Hiding Names
  • Checking for the Existence of a Name
  • Next Steps
9. Event Programming
  • Levels of Events
  • Using Events
  • Workbook Events
  • Worksheet Events
  • Quickly Entering Military Time into a Cell
  • Chart Sheet Events
  • Application-Level Events
  • Next Steps
10. Userforms—An Introduction
  • User Interaction Methods
  • Creating a Userform
  • Calling and Hiding a Userform
  • Programming the Userform
  • Programming Controls Using Basic Form Controls
  • Verifying Field Entry
  • Illegal Window Closing
  • Getting a Filename
II Automating Excel Power in VBA
11. 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
  • Using the Watch Window to Learn Rotation 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
  • File Operations
  • Combining and Separating Workbooks
  • Working with Cell Comments
  • Utilities to Wow Your Clients
  • Techniques for VBA Pros
  • Cool Applications
  • Next Steps
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
  • Next Steps
16. Reading from and Writing to the Web
  • Getting Data from the Web
  • Using Streaming Data
  • Using Application.OnTime to Periodically Analyze Data
  • Publishing Data to a Web Page
  • Trusting Web Content
  • Next Steps
17. XML in Excel 2007 Professional
  • What is XML?
  • Simple XML Rules
  • Universal File Format
  • XML as the New Universal File Format
  • The Alphabet Soup of XML
  • Microsoft's Use of XML as a File Type
  • Using XML Data from
  • Next Steps
18. Automating Word
  • Early Binding
  • Late Binding
  • Creating and Referencing Objects
  • Using Constant Values
  • Understanding Word's Objects
  • Controlling Word's Form Fields
  • Next Steps
19. Arrays
  • Declare an Array
  • Fill an Array
  • Empty an Array
  • Arrays Can Make It Easier to Manipulate Data, But Is That All?
  • Dynamic Arrays
  • Passing an Array
  • Next Steps
20. Text File Processing
  • Importing from Text Files
  • Writing Text Files
  • Next Steps
21. Using Access as a Back End to Enhance Multi-User Access to Data
  • ADO Versus DAO
  • The Tools of ADO
  • Adding a Record to the Database
  • Retrieving Records from the Database
  • Updating an Existing Record
  • Deleting Records via ADO
  • Summarizing Records via ADO
  • Other Utilities via ADO
  • Next Steps
22. Creating Classes, Records, and Collections
  • Inserting a Class Module
  • Trapping Application and Embedded Chart Events
  • Creating a Custom Object
  • Using a Custom Object
  • Using Property Let and Property Get to Control How Users Utilize Custom Objects
  • Collections
  • User-Defined Types (UDTs)
  • Next Steps
23. Advanced Userform Techniques
  • Using the UserForm Toolbar in the Design of Controls on Userforms
  • More Userform Controls
  • Controls and Collections
  • Modeless Userforms
  • Using Hyperlinks in Userforms
  • Adding Controls at Runtime
  • Adding Help to the Userform
  • Multicolumn List Boxes
  • Transparent Forms
  • Next Steps
24. Windows Application Programming Interface (API)
  • What Is the Windows API?
  • Understanding an API Declaration
  • Using an API Declaration
  • API Examples
  • Finding More API Declarations
  • Next Steps
25. Handling Errors
  • What Happens When an Error Occurs
  • Basic Error Handling with the On Error GoTo Syntax
  • Train Your Clients
  • Errors While Developing Versus Errors Months Later
  • The Ills of Protecting Code
  • Password Cracking
  • More Problems with Passwords
  • Errors Caused by Different Versions
  • Next Steps
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
  • Converting an Excel 2003 Custom Toolbar to Excel 2007
  • Troublshooting Error Messages
  • Other Ways to Run a Macro
  • Next Steps
27. Add-Ins
  • Characteristics of Standard Add-Ins
  • Converting an Excel Workbook to an Add-in
  • Having Your Client Install the Add-In
  • Using a Hidden Workbook as an Alternative to an Add-In
  • Using a Hidden Code Workbook to Hold All Macros and Forms
  • Next Steps

Click here to order the book!

"If you want to save hours per week in Microsoft Excel --- You Need This Book!"

Product Details
Paperback: 576 Pages ; Dimensions (in inches): 7x9x1.25
Publisher: QUE; ISBN 978-0-7897-3682-6 (Aug 2007)
Price: $39.99

Click here to order the book!


Excel is a registered trademark of the Microsoft® Corporation.
MrExcel is a Registered Trademark of Tickling Keys, Inc.

All contents Copyright 1998-2008 by MrExcel Consulting.