Excel automatically inserting VBA code to "This Workbook" object throws 64-bit compile error

[PatKlaus]

My apologies if this has been addressed before. If so, I haven't been able to find the right keyword combination to find it.

My system is Windows 7 with Office 2010 (32-bit). I have an Excel template which has evolved over quite a few years, and I've hit a new issue. I sent the template to a colleague for testing after coding some enhancements. He just received a new laptop with Windows 10 and Office 2016 (64-bit). When he tried to run an import macro, it immediately threw a compile error with the message: "The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute."

My first thought was "What Declare statements? I don't use Declare statements!" So I turned to the trusty "Find" function and found this code on the "This Workbook" object in the VBA project:

    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
        (ByVal lpClassName As String, _
        ByVal lpWindowName As String) As Long
    
    Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
        (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
        ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
    
    Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
        (ByVal hwnd As Long, ByVal wMsg As Long, _
        ByVal wParam As Long, lParam As Any) As Long

Since this isn't code I put in the project, I copied it to another file and deleted it. I then saved the file and re-ran the macro and it worked just fine. I forwarded the file to my colleague and much to my surprise, got the same error with the same code! It had magically reappeared. At that point I went to the web and started searching and found instructions for upgrading declare statements to use PtrSafe. I put in a compile switch (#If Win64 ...), updated the declare statements for 64-bit, and saved the file. Sent it once again to my colleague. It failed again with the exact same code rather than the code I had just entered. I finally had him add the code using his 64-bit Office, it ran fine, he saved the file, but when he re-opened the file it failed again with the exact same code above inserted.

It seems pretty clear that Excel/VBA is inserting that code for me, but it's failing. Does anyone know how to fix a problem with automatically added code??
Thanks,
Pat

 

[Dryver14]

wow this looks like it could be fun for the future for some people, might be worth giving this site a look, its Microsoft code compatibility inspector.

If you are lucky you might get away with just declaring long variables as longlong, if you have active ex controls etc. could be a nightmare.

As a side just because they could still load the 32 bit version rather than the 64bit and avoid this altogether

https://technet.microsoft.com/en-us/library/ee833946(office.14).aspx