Upgraded to Windows 10 - Macros Broken

Question

Problem:

I finally upgraded to Windows 10 and a few macros in Excel 2010 that I use frequently no longer work. They crash instantly when I run them and all I receive is an "automation error". They do not reference any outside programs/files; just lots of copy/pasting and sorting within a couple sheets. The macros seem to run on other people's computers (on Windows 10) just like they used to.

Stuff I've done:

• obligatory reboot
• Repair excel
• reinstall excel
• fiddle with macro permissions
• use copies of the workbook that work on other computers
• The weird stuff: If I create a new macro enable workbook and copy-paste in my old macros + workbook contents and try to run the macros, they work. As soon as I exit and reopen, the macros stop working. This holds true for really simple test macros I've tried making. A macro which copies a letter from one cell to another crashes after the workbook its saved in has been closed then reopened.

I feel like I'm forgetting a few things I've tested, but this should cover most of it.

Answer 1

Since you are not responding to comments any longer, I can only assume that with your OS upgrade, you also upgraded from 32-bit to 64-bit.

If you are declaring API calls, you will need to make them PtrSafe.

For example, consider the following DLL call:

Declare Function RegOpenKeyA Lib "advapi32.dll" ( _
    ByVal Key As Long, ByVal SubKey As String, NewKey As Long) As Long

Any memory address pointer and window handles need to be defined as LongPtr, because the way 64-bit OS manage it's memory. The return type for the above function is okay to keep as Long, because it's not addressing the memory or a handle.

Once you make your memory pointer and window handle conversions, you then need to tell Excel that it's "safe" to use by declaring your function as PtrSafe (Think of "pointer safe" for the memory pointers):

Declare PtrSafe Function RegOpenKeyA Lib "advapi32.dll" ( _
    ByVal Key As LongPtr, ByVal SubKey As String, NewKey As LongPtr) As Long

Take notice of the three changes made in our declaration:

Declare Function changed to Declare PtrSafe Function

(This tells Excel all pointers have been changed and the function is safe to use)

ByVal Key As Long changed to ByVal Key As LongPtr

and finally

ByVal NewKey As Long changed to ByVal NewKey As LongPtr

So now the question remains. What if I wanted to distribute this to different Operating system architectures?

You're in luck, as VBA allows you to utilize a special If...Then statement for your module declarations. So for the above code, you can distribute to 32 & 64 bit OS with the following statement:

#If VBA7 Then '64-bit Office
    Declare PtrSafe Function RegOpenKeyA Lib "advapi32.dll" ( _
        ByVal Key As LongPtr, ByVal SubKey As String, NewKey As LongPtr) As Long
#Else
    Declare Function RegOpenKeyA Lib "advapi32.dll" ( _
        ByVal Key As Long, ByVal SubKey As String, NewKey As Long) As Long
#End If