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.

- Reader Comments -

Following are comments in response to this item.
The most recent comment is at the bottom.

1. By Hui.... Comment posted 06 May, 2010 6:53am

   John
   I started using Virtual Machines to get around all of this a few years ago
   I'm currently using Sun's Virtual Box as VMware ESXi doesn't support my network card
   It is great running Win 98, XP, Vista and Win 7 beside each other all with different Office versions
2. By orange.cl. Comment posted 25 June, 2010 2:24am

   hi John, may i know would this
   

   Private Declare Function SetTimer Lib "user32" _ 

   
   
   give effect when i run my 2007 excel on 2010? i can run my excel with this macro well in 2007 excel but not in 2010 excel. Error msg shows that my file is not supported when i run that file in excel 2010.
3. By Salvador. Comment posted 11 October, 2010 10:12pm

   Hi John,
   
   I'm not an Excel programmer, but have used PUP since several years and recently bought V7. Over the week en I upgraded to Excel 2000 64bit. I cannot tell you how I miss the functionalities of PUP. I hope you will be able to come up with the 7.1 soon.
   Good luck and looking forward to it
4. By Michael. Comment posted 30 October, 2010 6:27pm

   Add me to the people that cannot wait for PUP 71 for 64 bit Office!
5. By jmnorthcutt. Comment posted 26 December, 2010 9:32am

   Unfortunately I bought the 64 bit version of 2010 because that is what we are going to go with at work next year. So,I would like to know how to get PUP7 working.
   
   I am not a programmer so you may need to dumb down some suggestions for some of us.
   
   Thanks
6. By Wendy!. Comment posted 09 January, 2011 6:26pm

   I was trying to figure out how old my grandfather was, and I could not subtract his birthdate from his death date...hmmm.... google it, I thought. I didn't want to use a pencil!
   Imagine my surprise when the search led me to The Spreadsheet Page XDate: Extended Date Functions Add-In
   
   

   "Many users are surprised to discover that Excel cannot work with dates prior to the year 1900..."

   
   Yes, I was certainly surprised! And I am certain my grandfather would have been surprised also, had he not died in 1972 at age 94!
   
   I was particularly proud of myself that I even had the solution all along with my handy Pup7!
7. By Wendy!. Comment posted 09 January, 2011 7:35pm

   Oh yes, The actual google result that led me to the Spreadsheet page:
   
   

   try:
   http://www.j-walk.com/ss/excel/files/xdate.htm
   Whosoever shall say unto this mountain, Be thou removed, and be cast into the sea; ... , it shall come to pass; Mark 11:23

   
   Now THAT was confusing. It would have been more appropriate to finish with Job 11:17 And thine age shall be clearer than the noonday: thou shalt shine forth, thou shalt be as the morning.
8. By Martin Hanson. Comment posted 10 January, 2011 10:06am

   Is there an update to when V 7.1 will be released? I have been struggling with no having this utlity on my new laptop; I'm guessing becuase I am running Windows 7 64-bit, that is my problem--I have Office 2007. The only function in V 7 that works is the programs "About" click.
9. By John Walkenbach. Comment posted 10 January, 2011 2:00pm

   It's available now, Martin.
10. By Jim Cone. Comment posted 10 January, 2011 9:12pm

    John,
    Can you provide more information about the "replacement" of the older vba versions with v7.0?
    Were all three of your Excel versions on the same drive?
    Do you know if the version replacement occurs if Office 2010 is installed on a separate drive?
    How did you determine in the older versions that the replacement had taken place?
    Any idea if Excel 97 would be affected?