Does Excel 2007 VBA have Sneaky Hidden Issues?

I’ve done a lot of coding over the past few years, on a number of platforms – but since I’m not a full time developer, it’s typically limited to VBA, PHP, SQL, some simple web stuff. Still, I’ve developed some tools that people use – and need to carry forward as the underlying technology moves along. And so, as I’ve finally made the leap to Office 2007, I had to come to terms with yet another VBA upgrade.

I remember the move from Office 2000 to 2003 – the large number changes in Excel’s VBA object model was surprising. I didn’t have to rewrite as much this time around, but I did make some interesting discoveries …

Macro Security

They certainly don’t make it easy to lighten up your macro security! By default, Excel 2007 comes with macro security set to High (or harsh, or Suspicious, or something like that). For most of my stuff to work, we need security set to Low – friendly, trusting, etc. The step-by-step process is comical – they are really trying to hide this stuff …

• From your Excel spreadsheet, click on the Office Button – the nice, round, logo’d guy in the upper left corner
• Click on the Excel Options button (at the bottom of the dialog that pops up)
• In the left-hand column, click on Trust Center
• Click on the Trust Center Settings button
• In the left-hand column, click on Macro Settings
• In the Macro Settings sections, select the radio button for Enable all macros …
• Exit the workbook, then open it up again – the spreadsheet should process just fine

Workbook_Open()

This is a strange problem – the Workbook_Open() event just does not want to fire. This always worked perfectly for me in Excel 2003, but not in the newest version. I’m not too wrong here – this has previously been seen “in the wild” (here, here, here, and here … Google your problems away …)

The Fix: Copy the contents of the Workbook_Open() event into a new Public subroutine, called Auto_Open. All will work as you expect it.

Confusion: this one frustrates me to no end – I haven’t found a reasonable explanation of why this happens – but this afternoon, I saw the Workbook_Open() event work as expected in one spreadsheet, yet fail in another. There are some tantalizing clues in this post (something to do with ambiguous names?), or maybe this post (objects copied from one project to another having mysterious interactions?)

VBA cell operations slow down

If you use a VBA procedure to write values to a range, most people would probably create a loop and write the values one cell at a time. This is not the fastest way to do things, and for Very Large Ranges, you will see some performance degradation. (Walkenbach, of course, has the best write up here). Well, I thought my code was reasonable, but my slow-ish code got much worse under Excel 2007. Looping through a range of cells actually slowed down as you progress through bigger and bigger ranges – I put some breakpoint messages in there, and it was sad to see, I was you will be forced to rewrite these sections as outlined in Walkenbach’s method – note that his post is many years old, and this method works in Excel 2003 as well. After I did my rewrites, because the slowdown was brutal, and goodness! My routines are an Order of Magnitude faster! I felt the need to drop a quick note to the users, letting them know that the spreadsheets still worked – the instantaneous response time was not indicative of skipping code.

Hmmm: This behavior, combined with the previous problems with Workbook_Open(), plus a few 1004 bugs (mysterious code that never seems to indicate what the real problem is …) makes me think there are subtle memory management problems in this version of Excel VBA. Nothing that makes me swear off VBA forever – you just need to be smart about your VBA projects. Excel is for light-duty programming. If you think your project is getting unwieldy, consider another platform – don’t let Excel be the hammer for every nail that you see.

AutoFilter

Columns that allowed “selectivity” via the AutoFilter command act differently when brought up under Excel 2007. Not necessarily an issue – unless your macros try to manipulate these things programmatically. I do this a lot, but I found I’ve been overcomplicating things …

Before

[code lang=”vb”] ‘ Clear AutoFilter settings
wst.Activate
With ActiveSheet
If .AutoFilterMode Then
For i = 1 To .AutoFilter.Filters.Count
Selection.AutoFilter Field:=i, Criteria1:="="
Selection.AutoFilter Field:=i, Criteria2:="="
Next i
End If
End With
[/code]

After

[sourcecode language=”vb”]If ActiveSheet.AutoFilterMode Then Selection.AutoFilter ‘ take off autofilter
Selection.AutoFilter ‘ put it back on again[/sourcecode]

Ribbon vs. Menu

The new Ribbon approach for commands across the top of your documents means the Menu meme is dead. After you get used to finding your way, it’s not really a big problem – unless you have coded custom menus for your macros. They won’t entirely disappear – they get shuffled off to the Add-Ins tab – just not as convenient.

All in all …

Am I surprised by the glitches? Not really, but I am pleasantly surprised by the relatively small number of problems my past automation efforts experienced. When they moved from Office 2000 to Office 2003, I had many more problems – Microsoft made some basic changes to some of the Excel objects, and the fixes were not backward compatible. This time, however, folks can use either Excel 2003 or Excel 2007 to use most of the stuff I’ve created for them – but seriously, shouldn’t you get around to converting already? You are running out of excuses.

Guaranteed To Work Under Excel 2007 (?!?!)

What, are you kidding me? I tested my spreadsheets a lot, but it won’t surprise me when someone comes up with a use case / scenario that I didn’t check for. Just tell your end users to continue with that terrific patience they have shown over the years, and to let you know if/when you see things that don’t look right. Hint: encourage them to call you over to their desk when issues occur, so you can take a look at the screen. Screen prints cut and paste into emails, or lengthy explanations over the phone just don’t cut it.