Click here to order the book!
"This book is for everyone
who has ever recorded a macro,
only to find it did not work
the next day."
-Bill Jelen, MrExcel
VBA and Macros for Microsoft Excel
"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.
As corporate IT departments have found themselves with long backlogs of requests, Excel users have found that they can produce the reports needed to run their business themselves. This is both a good and bad thing. On the good side, without waiting for resources from IT, you’ve probably been able to figure out how to import data and produce reports in Excel. On the bad side, you are now stuck importing data and producing reports in Excel.
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.
"Learn why the Macro Recorder does not work"
There are two barriers to entry to 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. 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.
"See Proven Techniques for Automating Charts and Reports."
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. The authors provide detailed looks into the code that they regularly use to solve reporting problems. In one case, MrExcel Consulting replaced 40 hours of manual report-creating with a single button click and 4 minutes of processing time. You will see the code used to create this report. Whether you are interesting in writing macros for yourself or in becoming a professional Excel developer, this book is your training manual.
Click here to order the book!
About the Authors
Tracy Syrstad works as a project manager and consultant for the MrExcel Consulting team. She remembers the painful trek up the VBA learning curve while developing applications for co-workers at a former job. She is co-editor of Holy Macro! It’s 1,900 Excel VBA Examples CD and editor of Dreamboat On Word. She lives on an arboreous acreage in eastern South Dakota with her husband John and dog General.
Bill Jelen is an accomplished Excel author and the principal behind the leading Excel Web site, MrExcel.com. As an Excel consultant, he has written Excel VBA solutions for hundreds of clients around the English-speaking world. MrExcel.com hosts more than 12 million page views annually. Prior to founding MrExcel.com, 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.
Click here to order the book!
"Learn how to do things you never dreamt were possible."
Detailed Table of Contents
I. FIRST STEPS UP THE VBA LEARNING CURVE.
1. Unleash the Power of Excel with VBA!
Every intermediate Excel user has tried to record an Excel macro and it never works the second day. You will learn why this happens, how Microsoft failed in implementing the macro recorder, and how to bridge the gap from Macro Recorder to useful code. Covers the VBA environment, debugging, watch window.
- Getting Results with VBA.
- What Is in This Book.
- A Brief History of Spreadsheets and Macros.
- The Future of VBA and Excel.
2. This Sounds Like BASIC, So Why Doesn't It Look Familiar?
- The Power of Excel.
- Barriers to Entry.
- Knowing Your Tools-The Visual Basic Toolbar.
- Macro Security.
- Overview of Recording, Storing, and Running a Macro.
- Running a Macro.
- Understanding the Visual Basic Editor (VBE).
- Examining Code in the Programming Window.
- Running the Macro on Another Day Produces Undesired Results.
- Next Steps: Learning VBA Is the Solution.
The layman’s introduction to Object Oriented Programming. Why VBA code doesn’t look anything like the BASIC we learned in high school.
3. Referring to Ranges.
- 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.
Excel is cells, ranges, worksheets. You will learn the language of specifying cells
4. User-Defined Functions.
- The Range Object
- Using the Top-Left and Bottom-Right Corners of a Selection to Specify a Range
- 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 Non-contiguous Range
30 real-life example functions that you can put to productive use.
- Creating User-Defined Functions
- Useful Custom Excel Functions
Covers the usual “procedural” loops plus the powerful loops available in VBA. The macro recorder will NEVER record a loop, so this is a key stepping-stone from someone trying to record macros to being able to write their own useful programs.
6. R1C1 Style Formulas.
- For…Next Loops
- Do Loops
- The VBA Loop: For Each
Microsoft introduced R1C1 style of cell references, but they were beaten by the “A1” style made popular by Lotus. The macro recorder enters all formulas in R1C1 style and for a really good reason. We’ll get people up to speed on this arcane formula style and show why it can be your friend.
- 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 Conditional Formatting
Even advanced users may not understand that identical range names can exist on all worksheets. Discuss the difference between Workbook-level names and Worksheet-level names. Explain how these differences can trip up your code.
8. Event Programming
- Global Versus Local Names
- Adding Names
- Deleting Names
- Types of Names
- Hiding Names
- Checking for the Existence of a Name
How to run macros when the workbook opens, cells change, etc. There is incredible power in using worksheet-level event handlers and most people don’t realize or believe that it is in there.
9. UserForms-An Introduction.
- Levels of Events
- Using Events
- Workbook Events
- Worksheet Events
- Chart Sheet Events
- Application-Level Events
The basics of how to interact with the user. Again – this will never be learned from the macro recorder.
II. AUTOMATING EXCEL POWER IN VBA.
Part II is for the Excel power user. You can create charts, etc. with your eyes closed and the power in this book is learning how to automate this with VBA.
- User Interaction Methods
- Creating a Userform
- Calling and Hiding a Userform
- Using Basic Form Controls
- Using the Multipage Control to Combine Forms
Everything you need to know to create charts with VBA.
11. Data Mining with Advanced Filter.
- Embedded Charts Versus ChartSheets
- Creating a Chart with VBA
- Using Object Variables to Streamline Code
- The Anatomy of a Chart
- Table of Chart Types
- Details of Various Chart Types
- Interactive Charts
- Exporting Charts as Images
- Drawing with X-Y Charts
- Custom Charts with VBA
Again, this is an arcane command that is rarely used in the Excel user interface but is incredibly powerful when used in macros.
12. Pivot Tables.
- 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 Instead of Unique Records Only
Pivot tables are the most important feature in Excel. This chapter will cover how to create pivot tables using VBA.
13. Excel Power.
- Creating a Vanilla Pivot Table in Excel Interface
- Building a Pivot Table in Excel VBA
- Revenue by Customer for a Product Line Manager
- Handling Additional Annoyances
- Product Profitability-Issues with Two or More Data Fields
- Summarizing Date Fields with Grouping
- Advanced Pivot Table Techniques
- Sum, Average, Count, Min, Max, and More
- Reporting Percentages
30 more awesome code examples showing how to do common Excel tasks using VBA. Here, you will find code samples submitted by regulars from the MrExcel board. Read as these contributors share their favorite code: Russell Hauf, Richie Sills, Nate Oliver, Tom Urtis, Tommy Miles, Colo, Suat Ozgur, XLDennis, Ivan Moala, Juan Pablo Gonzalez, Wei Jiang and Daniel Klann.
14. Reading from and Writing to the Web.
- Using VBA to Extend Excel
- File Operations
- Combining and Separating Workbooks
- Working with Cell Comments
- Utilities to Wow Your Clients
- Techniques for VBA Pros
- Cool Application
Web Queries are new to Excel, but many a day-trader tries to use them to pull real-time stock quotes into Excel. We will show how to set up a web query in VBA and how to use Application.OnTime to poll new data from the web every second or minute.
15. XML in Excel 2003 Professional.
- Getting Data from the Web
- Using Streaming Data
- Using Application
- OnTime to Periodically Analyze Data
- Publishing Data to a Web Page
XML is the great promise for Office 2003. Data can be repurposed from one application to another. The problem is, no one has XML data or can figure out how to use XML. This chapter will offer a beginner’s guide to using XML and walk through three real-life examples.
16. Automating Word.
- What Is XML? Simple XML Rules
- Universal File Format
- XML as the New Universal File Format
- The Alphabet Soup of XML
- Using XML to Round-Trip a Workbook from Excel to HTML and Back
How to produce letters in Word using Excel data.
III. TECHIE STUFF YOU WILL NEED TO PRODUCE APPLICATIONS FOR THE ADMINISTRATOR TO RUN.
- Early Binding
- Late Binding
- Creating and Referencing Objects
- Word's Objects
if your goal is to produce an application that you will give to someone else to run, then these techie topics have to be addressed.
People run from arrays, but this chapter will show how you can make your code run twice as fast using Arrays.
18. Text File Processing.
- 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
Excel is limited to 65,536 rows and eventually someone has to read a file that is larger than that. This chapter shows how.
19. Using Access as a Back End to Enhance Multi-User Access to Data.
- Importing from Text Files
- Writing Text Files
As Excel apps gain hold, you get someone who needs to have two co-workers working on the same data at the same time. This is a common technique for a VB programmer, but very foreign to VBA programmers. For multi-user apps, it is a lifesaver.
20. Creating Classes, Records, and Collections.
- 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
Powerful techniques available with classes and collections.
21. Advanced UserForm Techniques.
- 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
- User-Defined Types (UDTs)
The details on Userforms that would have scared you away if they were in Chapter 9.
22. Windows Application Programming Interface (API).
- Using the UserForm Toolbar in the Design of Controls on UserForms
- Controls and Collections
- More UserForm Controls
- Modeless Userforms
- Hyperlinks in Userforms
- Adding Controls at Runtime
- Using a Scrollbar as a Slider to Select Values
- Adding Help Tips to Controls
- Tab Order
- Coloring the Active Control
- Transparent Forms
Introduction to API functions. How to use them. 10 Examples that you can use today and how to learn more.
23. Handling Errors.
- What Is the Windows API? Understanding an API Declaration
- Using an API Declaration
- API Examples
- Finding More API Declarations
The last thing you need is to hand an application off to an admin, leave for vacation, then have your boss call you back from Maui because of an unhandled error that's brought the company to a halt.
This chapter will teach you how to bulletproof the applications
24. Using Custom Menus to Run Macros.
- What Happens When an Error Occurs
- Basic Error Handling with the On Error GoTo Syntax
- Generic Error Handlers
- Train Your Clients
- Errors While Developing Versus Errors Months Later
- The Ills of Protecting Code
- More Problems with Passwords
- Errors Caused by Different Versions
How to run macros from custom menus.
- Creating a Custom Menu
- Creating a Custom Toolbar
- Other Ways to Run a Macro
How to package your application as an Add-In
26. Case Study: Designing an Excel Application.
- 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
Microsoft MVP Tushar Mehta reveals the steps in developing a real application.
Click here to order the book!
- About Tushar Mehta
- Using Excel for More Than Number Crunching
- The Solution
- Implementing the Solution in Excel and VBA
"If you want to save hours per week in Microsoft Excel --- You Need This Book!"
Paperback: 576 Pages ; Dimensions (in inches): 7-3/8 x 9-1/8
Publisher: QUE; ISBN 0789731290 (May 2004)
"Bonus when you buy the book!"
When you order from MrExcel, you will receive a link to download the project files from the book. These will contain all of the code, bonus code, additional notes from the authors, 25 bonus macros. This is a $25 value - yours free when you order. When you buy the book from our shopping cart, watch for the Download Center link after your transaction is completed. If you bought the book elsewhere, see page 7 for information on downloading the code. If you bought the book elsewhere and have a first printing without the URL on page 7, then contact consult @ MrExcel.com for information on the code files.
Click here to order the book!