J-Walk & Associates, Inc.

PUP v7.1 Revisited

About a week ago I made a blog post about updating my PUP v7 add-in so it works with 64-bit Excel. See: PUP v7.1 is coming.

I indicated that it would be fairly easy to update the code. That was based on some preliminary tests I did using Excel 2007, Excel 2010 and 64-bit Excel 2010. But I was wrong. Updating the code will require more work than I had thought.

Today I learned something about VBA: When you install Office 2010, you get VBA 7. And VBA 7 replaces your previous VBA version. I have three versions of Excel installed on my main system (Excel 2003, Excel 2007, and Excel 2010 Beta). So now, all three of them are running VBA 7 (even though the Help - About dialog tells me it's VBA 6.5 in Excel 2003 and 2007).

What this means is that my tests led me astray. I assumed that I could simply add "PtrSafe" to my API function declarations. That solution works (most of the time) only for VBA 7. Earlier versions of VBA don't recognize the PtrSafe keyword. So, in order to keep PUP v7 compatible with Excel 2007 (running VBA 6.5), I'll need to use the vba7 and win64 compiler directives, and provide two sets of API function declarations. It's still not incredibly difficult, but it's a lot more work than I thought it would be.