VBA and Macros for Microsoft Excel

Product Author Bios

Bill Jelen "Mr. Excel" is an accomplished Excel author and the principal behind the leading Excel website, MrExcel.com. As an Excel consultant, he has written Excel VBA solutions for hundreds of clients around the English-speaking world. His website hosts over 10 million page views annually. Prior to founding MrExcel.com, Jelen spent twelve years in the trenches, working as a financial analyst for finance, marketing, accounting and operations departments of a $500 million public company.

Tracy Syrstad works as an Excel and Access Consultant. As part of the Mr.Excel.com Consulting team, she has helped develop VBA solutions for clients around the world. 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 ֿ,600 Excel VBA Examples CD” and editor of Dreamboat On Word.

Everyone is looking for ways to save money these days. That can be hard to do for businesses that have complex needs, such as custom software applications. However, VBA and Macros for Microsoft Excel can teach you ways to customize pre-existing software to meet your specific needs. A variety of topics are covered that are sure to give you a solid knowledge of the VBA language. Event programming, user forms, carts, pivot tables, multi-dimensional arrays and Web queries are just a few of the areas you will learn about in this book. Written by the principal behind the leading Excel Web site, www.mrexcel.com, this book is sure to save you time and money!

Downloads

All the code developed for the book in one convenient file for download - 4,504 kb -- vbabook.exe

Customer Reviews

48 of 51 people found the following review helpful Excellent VBA Resource, and I've seen them all!, July 21, 2004 By  Anne M. Troy (Quakertown, PA) - See all my reviews    This review is from: VBA and Macros for Microsoft Excel (Paperback) I have never been able to grasp the concepts of VBA. I believe in VBA and its ability to supercharge Office applications, but could never seem to learn even Excel VBA, which is supposed to be the easiest. This is the first book I've read (yes, I've read the Romans and the Walkenbachs) that made any sense to me at all. Too many books approach VBA as if you have already learned how to code in other programs. I came to VBA from the Office user side of things. I don't have that *programming* brain required to pick up VBA easily. So this book has been a godsend. While I've only read about half the book so far, so many things that escaped me are finally falling into place. Thanks very much to Bill and Tracy for a terrific VBA resource! Help other customers find the most helpful reviews  Was this review helpful to you?  Report abuse | Permalink  Comment 18 of 19 people found the following review helpful Excellent for all, May 15, 2005 By  Jimmy Hai (Manchester, CT USA) - See all my reviews    This review is from: VBA and Macros for Microsoft Excel (Paperback) This book is an excellent resource for all levels. The book contains some minor typo and error in the code but you can download all the correct code files from the publisher's website. If you keep working along with book, you will soon be able to create macro (or modify the book's macro) that save you hours of time. I highly recommend reading Chapter 14: Reading from and Writing to the web. It contains code that you can extract data from the web, it is also the main reason why I bought this book. Help other customers find the most helpful reviews  Was this review helpful to you?  Report abuse | Permalink  Comment 17 of 19 people found the following review helpful Excellent Book for Mastering VBA in Excel, July 19, 2004 By  Jay (Rockville, MD United States) - See all my reviews This review is from: VBA and Macros for Microsoft Excel (Paperback) This book is extremely helpful! My knowledge and understanding of VBA and Excel have skyrocketed after reading. Sections like "5 easy tips for cleaning up recorded code" are fantastic to the beginner. It covers tons of material in a concise, effective manner so that you can start writing advance code immediately. The book is not only for beginners though. While some examples are easy to understand, others are very challenging. There are many advanced sections of the book that I'm totally not ready for now but will be in the future. I read one other Microsoft book on VBA in Excel that centered around specific examples on a CD and this book blows it away. This book gives you much more general, useful knowledge on how to write code, in all areas of Excel. If you are looking for a VBA reference book, this is a good one. It might not be the only book you will need (I also have Roman's book--and have found it useful to have 2 books for referencing), but it is well... Read more Help other customers find the most helpful reviews  Was this review helpful to you?  Report abuse | Permalink  Comment

Share your thoughts with other customers:

› See all 21 customer reviews...

Praise For VBA and Macros for Microsoft Excel

5 Star Reviews at Amazon.com....

"Excellent VBA Resource, and I've seen them all!"

"Awesomely Empowering and Revealing!"

"Excellent Book for Mastering VBA in Excel"

Online Sample Chapters

Referring to Excel Ranges

Referring to Ranges

Table of Contents

Introduction.

    Getting Results with VBA.

    What Is in This Book.

    A Brief History of Spreadsheets and Macros.

    The Future of VBA and Excel.

    Special Elements and Typographical Conventions.

    Next Steps.

I. FIRST STEPS UP THE VBA LEARNING CURVE.

1. Unleash the Power of Excel with VBA!

    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.

    Frustration.

    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 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. Next Steps.

4. User-Defined Functions.

Creating User-Defined Functions. Useful Custom Excel Functions. Next Steps.

5. Looping.

For...Next Loops. Do Loops. The VBA Loop: For Each. 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 Conditional

    Formatting. Next Steps.

7. Names.

    Global Versus Local Names.

    Adding Names.

    Deleting Names.

    Types of Names.

    Hiding Names.

    Checking for the Existence of a Name.

    Next Steps.

8. Event Programming.

    Levels of Events.

    Using Events.

    Workbook Events.

    Worksheet Events.

    Chart Sheet

    Events.

    Application-Level Events.

    Next Steps.

9. UserForms-An Introduction.

    User Interaction Methods.

    Creating a Userform.

    Calling and Hiding a Userform.

    Using Basic Form Controls.

    Using the Multipage Control to Combine Forms.

    Next Steps.

II. AUTOMATING EXCEL POWER IN VBA.

10. Charts.

    Overview.

     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.

    Next Steps.

11. 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 Instead of Unique Records Only.

    AutoFilters.

    Next Steps.

12. Pivot Tables.

    Versions.

    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.

    Next Steps.

13. Excel Power.

    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.

    Next Steps.

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

    Next Steps.

15. XML in Excel 2003 Professional.

    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.

    Next Steps.

16. Automating Word.

    Early Binding.

    Late Binding.

    Creating and Referencing Objects.

    Word's Objects.

    Next Steps.

III. TECHIE STUFF YOU WILL NEED TO PRODUCE APPLICATIONS FOR THE ADMINISTRATOR TO RUN.

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

18. Text File Processing.

    Importing from Text Files.

    Writing Text Files.

    Next Steps.

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

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

21. Advanced UserForm Techniques.

    Using the UserForm Toolbar in the Design of Controls on UserForms.

    Controls and Collections.

    More UserForm Controls.

    Tabstrip.

    RefEdit.

    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.

    Next Steps.

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

23. Handling Errors.

    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.

    Next Steps.

24. Using Custom Menus to Run Macros.

    Creating a Custom Menu.

    Creating a Custom Toolbar.

    Other Ways to Run a Macro.

    Next Steps.

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

    Next Steps.

26. Case Study: Designing an Excel Application.

    About Tushar Mehta.

    Using Excel for More Than Number Crunching.

    The Solution.

    Implementing the Solution in Excel and VBA.

    Summary.

    Next Steps.

Index.

Errata

Note: You will need the Free Adobe Acrobat Reader to view the errata for the book. If you do not already have Acrobat installed on your machine, click the "Get Acrobat Reader" button to download and install.

Click on the links below to display the PDF file in a new window. Right-click on the link and select Save As if you want to download it to your hard drive.

0789731290errata.pdf (124 KB)