VBA in New Versions of Excel

Question

Here is a question for my Father. He has been using VBA in Excel for more than two decades going from Excel 5 to this day where he is using Excel 2002.

As he has used VBA in Excel for so long, making extensive use of the ability to record macros, he has continued to invest in Excel as it has been rather stable (in terms of its VBA functionality) across each version. Now, he is considering upgrading to a newer version of Excel, however I have read reviews that Microsoft completely botched their VBA interpreter in Excel 2007, as scripts that used to work in Excel 2003 or earlier would become painfully slow or stop functioning altogether. Then looking into Excel 2010, I've read that a lot of the issues with Excel 2007 "haven't been fixed". As I am not sure as to the credibility of these reviews, which I have read I am posing this question For all those folks out that are currently making heavy use of VBA in Excel:

Would it be advisable for someone who has a great deal of time and work invested in VBA from Excel 2002 or earlier to upgrade to either Excel 2003, 2007 or 2010?

Answer 1

There were several performance issues with Excel 2007 VBA. Many of these were fixed in Excel 2007 SP1. There were also a number of macro recorder problems with Charts and Shapes.

My personal recommendation would be Excel 2010: the VBA performance and Macro recorder issues have been fixed and I find the overall stability of Excel 2010 to be superior to most recent versions (but YMMV).

Migrating from Excel 2002 to 2007 or 2010 will involve learning to use the ribbon: some people like it and some people hate it, but it certainly will require effort for a long-term Excel user. Note that the VBA IDE has not changed.

Answer 2

If your father survived the migration to Excel 97, the switch to 2007 or 2010 will feel like a cakewalk. Other than the new GUI, the changes are of the more-better variety. I haven't run into any compatibility issues including the big jump from 97 to 2003.

To get up to date, I'd recommend John Green et al's Excel 2007 VBA Programmer's reference.