MrExcel.com - Your One Stop for Excel Tips & Solutions

VBA and Macros for Microsoft Excel 2007

August 2007

VBA and Macros for Microsoft Excel 2007

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
Buy Now »
by Bill Jelen, Tracy Syrstad
category: VBA
covers: Excel 2007
Product Details
  • 576 Pages
  • Publisher: QUE Publishing
  • PDF ISBN: 978-0-7897-3682-6

"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.

"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!

  • Introduction

    • 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

  • 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 Amazon.com
    • 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

Where to Buy

InformIT

eBook from InformIT

Buy Now »

Other Editions

VBA and Macros for Microsoft Excel

VBA and Macros for Microsoft Excel

May 2004

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.

Microsoft Excel 2019 VBA and Macros

Microsoft Excel 2019 VBA and Macros

November 2018

Use this guide to automate virtually any routine Excel task: save yourself hours, days, maybe even weeks. Make Excel do things you thought were impossible, discover macro techniques you won’t find anywhere else, and create automated reports that are amazingly powerful.

VBA & Macros for Microsoft Excel 2016

VBA & Macros for Microsoft Excel 2016

November 2015

Use this guide to automate virtually any routine task: save yourself hours, days, maybe even weeks! Make Excel do things you thought were impossible, discover macro techniques you won’t find anywhere else, and create automated reports that are amazingly powerful.

VBA and Macros: Microsoft Excel 2010

VBA and Macros: Microsoft Excel 2010

July 2010

Using Microsoft Excel 2010 VBA scripting features, Excel users can save dozens - or even hundreds - of hours per year. But most Excel users have never written a VBA script: many haven't even used Excel's built-in Macro Recorder. VBA and Macros is the solution. One simple step at a time, two leading Excel VBA scripting experts teach all the techniques needed to automate virtually any Excel task and customize virtually any Excel report or business solution.

VBA and Macros Microsoft Excel 2013

VBA and Macros Microsoft Excel 2013

February 2013

Use Excel 2013 VBA and Macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! You’ll discover macro techniques you won’t find anywhere else and learn how to create automated reports that are amazingly powerful and useful.

Related Products

Office VBA Macros You Can Use Today

Office VBA Macros You Can Use Today

January 2006

Learn best-practice coding examples for each core Office product.