Excel 2007 and Excel 2010 no longer able to run same Excel file

[[email protected]]

Hi,

I have an xlsm file with quite a few macros, some of them fairly advanced (at least to me, I have just copied/pasted them from the moew experts members on this site). The file is stored on Google Drive, and up until recently worked fine on both my work laptop (an HP running Windows 7 Enterprise 64 bit with Excel 2007) and my home laptop (a Dell running Windows 7 Professional 64 bit with Excel 2010).

When I tried running the file, which in mainly developed and maintained on my work PC, on my home PC a few days ago I get an error message: "Compile error: 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." I then changed 100s of my "Private Declare Function" to "Private Declare PrtSafe Function" on advice received on a Microsoft excel help forum (I think). That resulted in the file running on the home PC, but when I tried running the updated file on the office PC I now get the following error message: Compile Error: Expected: Sub or Function" with the new PtrSafe addition of my Private Declare statements highlighted.

I am hoping someone can tell me:

1. What has happened, and
2. What can i do to make sure I can run the file on both om my PCs

Any help will be greatly appreciated!!

Christopher

[:) Sixthsense :)]

Source: https://msdn.microsoft.com/en-us/lib...ice.14%29.aspx

Microsoft provides a Win32API.txt file which contains 1,500 Declare statements and a tool to cut and paste the Declare statement that that you want into your code. However, these statements are for 32-bit systems and must be converted to 64-bit by using the information discussed later in this article. Existing Declare statements will not compile in 64-bit VBA until they have been marked as safe for 64-bit by using the PtrSafe attribute. You can find samples of this type of conversion at Excel MVP Jan Karel Pieterse’s Web site at: http://www.jkp-ads.com/articles/apideclarations.asp.

The Office Code Compatibility Inspector user’s guide is a useful tool to inspect the syntax of API Declare statements for the PtrSafe attribute, if needed, and the appropriate return type.

Declaring API functions in 64 bit Office

http://www.jkp-ads.com/articles/apideclarations.asp

[:) Sixthsense :)]

Also have a look at this...

https://support.microsoft.com/en-us/kb/983043

[[email protected]]

Hi Sixthsense, thanks for your reply. Unfortunately I am not really smart enough to understand what the links you refer to mean for me in practice! What I can say is:

1. I have seen the Microsoft site, and the explanation there doesn't seem to fit 100 % with my issue, the error message comes whenever I try to run a macro, not only when I try to edit. The error message cannot easily be ignored as it halts macro execution and confuses the users of the excel file (other people than me use this).
2. I have not changed anything in the excel file between the last time it worked on both PCs and now. I think my home PC may have automatically updated from Microsoft Update .
3. Unfortunately I have to run the same file on both PCs for it to serve its intended purpose.

Is there anything specific I can write into my Declare Statements (which I do not understand, I just dumb copy what cleverer people tell me to do...) which will allow the same file to make different declarations for the different PCs? Vey inconvenient, and why would this be necessary now when it was not an issue before?

Thanks again for all help!

[BellyGas]

The simple version:

Put your declarations like this:

Please Login or Register  to view this content.

Whichever system you're running on will show one of the above declare statements as red, the # at the start of the If statement tells VBA to ignore it. If you're running on the 64bit version, the Declare PtrSafe will be run, if not then the other one.

[[email protected]]

Thanks BellyGas,

One issue: I have hundreds of these statements, and I am wondering, will this solve the issue for me when both systems are 64 bit systems? The difference seems to be between the Excel 2007 and Excel 2010 versions, not really between a 32 and a 64 bit version? Confused....

[BellyGas]

To run the Declare statement(s) on the 64bit version you need the PtrSafe bit, however on 32bit versions the PtrSafe bit is an unrecognised function. The #If VBA7 bit makes the right bit of code run on the right system. If both systems are updated to 64bit then the code will only ever run the PtrSafe version and it'll work fine. If both systems are downgraded to 32bit then code will only ever run the non PtrSafe bit....and it'll work fine.

[[email protected]]

Thanks for all your help! By total coincidence in timing I had to remove Office 2010 yesterday, and replaced it with Office 2007. This solved the issue, but created another problem: the vbs script that opens the excel file in the background while displaying a 'loading please wait' power point slide no longer works. But this may not be the correct forum for discussions on that?