Programming Books C Java PHP Python Learn more Browse Programming Books
Qty:1
  • List Price: $39.99
  • Save: $13.19 (33%)
FREE Shipping on orders over $35.
Only 13 left in stock (more on the way).
Ships from and sold by Amazon.com.
Gift-wrap available.
Add to Cart
FREE Shipping on orders over $35.
Used: Good | Details
Sold by FPQ Books
Condition: Used: Good
Comment: The cover is clean but does show some wear; bent cover. The spine is slightly warped. Text only, no supplement included. The pages show little wear and tear. Item ships secure with Fulfillment By Amazon, Prime customers get 2nd day at no charge!
Access codes and supplements are not guaranteed with used items.
Add to Cart
Trade in your item
Get a $2.00
Gift Card.
Have one to sell? Sell on Amazon
Flip to back Flip to front
Listen Playing... Paused   You're listening to a sample of the Audible audio edition.
Learn more
See this image

VBA and Macros for Microsoft Office Excel 2007 Paperback – August 19, 2007

ISBN-13: 978-0789736826 ISBN-10: 0789736829 Edition: 1st

Buy New
Price: $26.80
27 New from $19.95 33 Used from $4.99
Amazon Price New from Used from
Paperback
"Please retry"
$26.80
$19.95 $4.99
Free%20Two-Day%20Shipping%20for%20College%20Students%20with%20Amazon%20Student


Frequently Bought Together

VBA and Macros for Microsoft Office Excel 2007 + Pivot Table Data Crunching for Microsoft Office Excel 2007
Price for both: $50.16

Buy the selected items together

NO_CONTENT_IN_FEATURE

Shop the new tech.book(store)
New! Introducing the tech.book(store), a hub for Software Developers and Architects, Networking Administrators, TPMs, and other technology professionals to find highly-rated and highly-relevant career resources. Shop books on programming and big data, or read this week's blog posts by authors and thought-leaders in the tech industry. > Shop now

Product Details

  • Paperback: 624 pages
  • Publisher: Que Publishing; 1 edition (August 19, 2007)
  • Language: English
  • ISBN-10: 0789736829
  • ISBN-13: 978-0789736826
  • Product Dimensions: 9.1 x 7.1 x 1.2 inches
  • Shipping Weight: 2.2 pounds (View shipping rates and policies)
  • Average Customer Review: 4.1 out of 5 stars  See all reviews (11 customer reviews)
  • Amazon Best Sellers Rank: #591,323 in Books (See Top 100 in Books)

Editorial Reviews

About the Author

Bill Jelen, Excel MVP and MrExcel, has been using spreadsheets since 1985, and he launched the MrExcel.com 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 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.

 

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.

Excerpt. © Reprinted by permission. All rights reserved.

Introduction

Introduction

In this 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

Getting Results with VBA

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 using the macro language Visual Basic for Applications (VBA). VBA enables you to achieve tremendous efficiencies in your day-to-day use of Excel. 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.

What Is in This Book

You've taken the right step by purchasing this book. I can help you get up the learning curve so that you can write your own VBA macros and put an end to the burden of generating reports manually.

Getting Up the Learning Curve

This introduction provides a brief history of spreadsheets. Chapter 1 introduces the tools and confirms what you probably already know: The macro recorder does not work. Chapter 2 helps you understand the crazy syntax of VBA. Chapter 3 breaks the code on how to efficiently work with ranges and cells.

By the time you get to Chapter 4, you will know enough to put to immediate use the 25 sample user-defined functions in that chapter.

Chapter 5 covers the power of looping using VBA. In Valerie's case study, after we wrote the program to produce the first department report, it took only another minute to wrap that report routine in a loop that produced all 46 reports.

Chapter 6 covers R1C1-style formulas. Chapter 7 takes a look at what changed in Excel VBA from Excel 2003 to Excel 2007. In the past, it was fairly easy to create VBA code that would run on any of the recent versions of Excel. Unfortunately, with the sweeping changes in Excel 2007, this will become significantly more difficult. Chapter 8 covers names. Chapter 9 has some great tricks that use event programming. Chapter 10 introduces custom dialog boxes that you can use to collect information from the human using Excel.

Excel VBA Power

Chapters 11 through 13 provide an in-depth look at charting, Advanced Filter, and pivot tables. Any report automation tool is going to rely heavily on these concepts.

Chapter 14 includes another 25 code samples designed to exhibit the power of Excel VBA.

Chapters 15 through 18 handle data visualizations, Web queries, XML, and automating another Office program such as Word.

The Techie Stuff Needed to Produce Applications for Others

Chapter 19 shows you how to use arrays to build fast applications. Chapters 20 and 21 handle reading and writing to text files and Access databases. The techniques for using Access databases enable you to build an application with the multi-user features of Access yet keep the friendly front end of Excel.

Chapter 22 covers VBA from the point of view of a Visual Basic programmer. It teaches you about classes and collections. Chapter 23 discusses advanced userform topics. Chapter 24 teaches you some tricky ways to achieve tasks using the Windows application programming interface. Chapters 25 through 27 deal with error handling, custom menus, and add-ins.

Does This Book Teach Excel?

Microsoft believes the average Office user touches only 10 percent of the features in Office. I realize everyone reading this book is above average. I think that I have a pretty smart audience at MrExcel.com. A poll of 8,000 MrExcel.com readers shows that only 42 percent of smarter-than-average users are using any one of the top 10 power features in Excel. I regularly do a Power Excel seminar for accountants. These are hard-core Excelers who use Excel 30 to 40 hours every week. Again, two things come out in every seminar. First, half the audience gasps when they see how quickly you can do tasks with a particular feature (such as automatic subtotals or pivot tables). Second, I am routinely trumped by someone in the audience. Someone will ask a question, I will answer, and someone in the second row will raise a hand and give a better answer. The point? You and I both really know a lot about Excel. However, I will assume that in any given chapter, maybe 58 percent of the people haven't used pivot tables before and maybe even less have used the "Top 10 Filter" feature of pivot tables. Before I show you how to automate something in VBA, I briefly cover how to do the same task in the Excel interface. This book does not teach you how to do pivot tables, but it does alert you that you might want to go explore something and learn it elsewhere.


Monthly Accounting Reports

This is a true story. Valerie is a business analyst in the accounting department of a medium-size corporation. Her company recently installed an overbudget $16 million ERP system. As the project ground to a close, there were no resources left in the IT budget to produce the monthly report that this corporation used to summarize each department.

Valerie, however, had been close enough to the implementation process to think of a way to produce the report herself. She understood that she could export General Ledger data from the ERP system to a text file with comma-separated values. Using Excel, Valerie was able to import the G/L data from the ERP system into Excel.

Creating the report was not easy. Like many companies, there were exceptions in the data. Valerie knew that certain accounts in one particular cost center needed to be reclassed as an expense. She knew that other accounts needed to be excluded from the report entirely. Working carefully in Excel, Valerie made these adjustments. She created one pivot table to produce the first summary section of the report. She cut the pivot table results and pasted them into a blank worksheet. Then she created a new pivot table report for the second section of the summary. After about three hours, she had imported the data, produced five pivot tables, arranged them in a summary, and had neatly formatted the report in color.

Becoming the Hero

Valerie handed this report to her manager. The manager had just heard from the IT department that it would be months before they could get around to producing "that convoluted report." Valerie walked in, handed the Excel report over, and became the instant hero of the day. In three hours, Valerie had managed to do the impossible. Valerie was on cloud nine after a well-deserved "atta-girl."

More Cheers

The next day, this manager attended the monthly department meeting. When the department managers started complaining that they couldn't get the report from the ERP system, this manager pulled out his department report and placed it on the table. The other managers were amazed. How was he able to produce this report? Everyone was greatly relieved to hear that someone had cracked the code. The company president asked Valerie's manager if he could have the report produced for each department.

The Cheers Turn to Dread

You can certainly see this coming. This particular company had 46 departments. That means 46 one-page summaries had to be produced once a month. Each required importing data from the ERP system, backing out certain accounts, producing five pivot tables, and then formatting in color. It had taken Valerie three hours to produce the first report. She found that after she got into the swing of things, she was able to produce the 46 reports in 40 hours. This is horrible. Valerie had a job to do before she won the responsibility of spending 40 hours a month producing these reports in Excel.

<...

More About the Authors

Discover books, learn about writers, read author blogs, and more.

Customer Reviews

4.1 out of 5 stars
5 star
4
4 star
5
3 star
1
2 star
1
1 star
0
See all 11 customer reviews
This book was very helpful for some projects that I needed to accomplish with Excel & VBA.
Rick J. Mitchell
I feel there aren't enough examples provided in the book and the examples from their website are mostly a repeat of the book.
R. Shackelford
Overall, I would recommend the book to anyone looking to learn more about VBA or to those simply wanting a reference guide.
Daniel Greer

Most Helpful Customer Reviews

20 of 22 people found the following review helpful By Rick J. Mitchell on October 7, 2007
Format: Paperback
This book was very helpful for some projects that I needed to accomplish with Excel & VBA. Some of the chapters were extremely enlightening in seeing the big picture of using VBA with Excel and other MS Office applications. Other chapters are excellent resources that I will refer to many times in the future. I would recommend this book for both the beginner (in using VBA) as well as the experienced VBA user.
Comment Was this review helpful to you? Yes No Sending feedback...
Thank you for your feedback. If this review is inappropriate, please let us know.
Sorry, we failed to record your vote. Please try again
15 of 17 people found the following review helpful By R. Shackelford on December 11, 2009
Format: Paperback
I find the book simply adequate.

I spent a while looking through books on VBA for Excel 2007 and I chose the Jelen/Syrstad title because is was not one of big fat programming guides. I feel there aren't enough examples provided in the book and the examples from their website are mostly a repeat of the book. I find working with Excel to develop a automatic report a daunting task; the built-in help is nearly worthless, the on-line help is a hit or miss, and that's after I've looked through the book.

I guess I'll be looking for a big fat programming guide with more chart development and, hopefully, one that covers Data Connections and SQL.

I have carefully read through the first 12 chapters before I started my project and I find the information easy to understand and straight forward. If, however, one tries to develop code outside of the examples provided, the frustration level increases.
1 Comment Was this review helpful to you? Yes No Sending feedback...
Thank you for your feedback. If this review is inappropriate, please let us know.
Sorry, we failed to record your vote. Please try again
9 of 10 people found the following review helpful By Daniel Greer on September 12, 2009
Format: Paperback
I've really enjoyed this book. I had a very basic understanding of VBA prior to purchasing this book; and while I certainly wouldn't consider myself an advanced user at this point, the book has been wonderful at explaining the syntax of code. Unfortunately I haven't had a chance to sit down and read the book all the way through. If I had, I believe I would be a better programmer for having done it. Mainly, I use the book for reference when I'm coding macros at work. The book is laid out well so when I run into an issue it's easy to flip to the index and find roughly what I'm looking for. The online component of the book is nice as well - adding even further resources to users. Overall, I would recommend the book to anyone looking to learn more about VBA or to those simply wanting a reference guide.
Comment Was this review helpful to you? Yes No Sending feedback...
Thank you for your feedback. If this review is inappropriate, please let us know.
Sorry, we failed to record your vote. Please try again
5 of 6 people found the following review helpful By W.Jay on February 26, 2009
Format: Paperback
This book is full of fantastic and useful information. Everything that i always wished i could do in Excel VBA is listed there. Because of the way it is written, my learning and understanding increased greatly is such a short time. It goes straight to the point and has superb examples.
Comment Was this review helpful to you? Yes No Sending feedback...
Thank you for your feedback. If this review is inappropriate, please let us know.
Sorry, we failed to record your vote. Please try again
2 of 2 people found the following review helpful By T. Bolton on December 4, 2009
Format: Paperback
I knew that you could do cool things with VBA in Excel, but had never studied Visual Basic. I had some automation that needed to be done in Excel, so I bought this book and within a week I had my automation done, and now I've moved on to other things using VBA in Excel since I've discovered what can be done. This book made me realize that you truly can do just about anything with Excel. I would recommend this book to anyone who has learned a little about formulas in Excel and wants to take the next step in learning how to make their job easier.
Comment Was this review helpful to you? Yes No Sending feedback...
Thank you for your feedback. If this review is inappropriate, please let us know.
Sorry, we failed to record your vote. Please try again
1 of 1 people found the following review helpful By L. Heinz on September 24, 2012
Format: Paperback Verified Purchase
This book is well done. It covers what most books say "is beyond the scope of this book." Eighteen Nineteen Twenty
Comment Was this review helpful to you? Yes No Sending feedback...
Thank you for your feedback. If this review is inappropriate, please let us know.
Sorry, we failed to record your vote. Please try again

Customer Images


What Other Items Do Customers Buy After Viewing This Item?