Excel VBA Performance in Office 2007
March 13, 2009 • JP • No Comments • Rate This Article
I was just reading the MSDN Excel blog, which published a new post about VBA performance. It's called Excel VBA Performance Coding Best Practices and was published as a followup to a previous post on the same blog soliciting feedback from Excel users on performance issues in Excel 2007 VBA.
My response is … is this a joke?
I can't tell where to direct my anger:
…at the Excel users who submitted feedback and code samples which prompted a blog post telling them to make sure they are turning off calculation and screen updating? Could this possibly be the cause of Excel 2007 VBA's slowness? Is it the end users writing bad code?
…at Microsoft for insulting our intelligence with an amateur post from someone who appears to spend more time browsing Excel blogs than looking at his company's product? Do the tips in the post even relate to the submitted samples?
One of the commenters on that post wrote:
Surely MS Program Managers shouldn't be learning about fundamental functions in their programs…from blog posts?
Aren't you guys meant to, you know, have the inside track on these things?
And I could not agree more.
The speed tests from Doug Jenkins were particularly useful, and I'll make a note of his results for future reference.
By the way, I do have some optimization tips here.
Rant ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Java version of Amazon ad link code creator
Next Post: Outlook version of GetDistListMembers
Most of the poor performance in Excel 2007 VBA seems to be related to the same things that bog down the interface in the absence of VBA. Things like charts with lots of points, redrawing the screen, working with the new shapes.
What shocks me the most is how irrelevant the solution is to the problem. They posted a request for VBA slowdowns in Excel 2007, which led me to believe they were handling this in an enlightened fashion, and then they respond by posting generic optimizations.
JP – I really think your comments were a bit harsh; they weren't saying there aren't any performance issues with XL2007 if you do this stuff, were they? There were some worthwhile points in the post, although I certainly would have put them in a different order myself.
Also of the many complaints about 2007, I don't think any of them could be attributed to the developers spending too much time reading Excel blogs. Just the opposite I'd have thought.
I agree there are some takeaways from that post, but it seems to largely ignore the particular complaints lodged in the original request for feedback. Are the complaints about Excel 2007 really a result of end users not optimizing their code, or is it the software? Now I really need to get my hands on a copy and try it for myself.
Jimmy -
We don't have access to all of the files Microsoft received. But I bet that 98% of the issues they found are also issues in older versions of Excel, and that most are remedied using the posted suggestions, which will be new to many self-taught macro writers.
I also think that the MS engineers know there are issues with VBA in 2007, and they are looking as much (if not much more) to fixing problems ahead of time with Excel 14.
Jon,
I'm curious, then, as to why it is framed as an Excel 2007 problem. From the original post:
Are people rewriting macros in Excel 2007 and leaving out their optimizations?
Jimmy -
I don't think people are rewriting any macros. I think a small number of people do have valid issues with Excel 2007 VBA performance. These would probably be issues that couldn't be worked around, but go deeper to the root of VBA and 2007.
The majority of respondents to the request for slow 2007 code would most likely have slow macros in any version, but they saw the opportunity to complain by sending in their samples. I suspect that the Microsoft post was written to address these common inefficiencies.
A significant part of the VBA slowdown in Excel 2007 is caused by a large increase in the overhead of initiating a data transfer between VB and XL.
Unsophisticated VBA users who write code that loops through ranges cell-by-cell will therefore see a huge slowdown.
Well-written VBA code will be slower in 2007 than previous, but generally not catastrophically so (unless you are manipulating shapes!).
Therefore IMHO the Excel team Blog post was worthwhile because following the instructions will minimise the speed difference between versions.
see http://www.decisionmodels.com/VersionCompare.htm
I am hopeful that the Excel team will fix this problem either in 2007 SP2 or XL14
And I agree with you that they were useful tips. I just found it annoying that the request for feedback made certain presuppositions (i.e. problems specific to Excel 2007), and then they responded by posting generic optimization tips that apply to any version.