Introduction

In the last week I’ve had a client upgrade to 64-bit Microsoft Office and it has caused some havoc with parts of my VBA. It’s the first time this has been an issue, and I thought I’d detail some of the effects. If you don’t care about reasons or explanations, skip to the summary to see how to avoid or fix the issues.

There are actually very few issues with moving code from 32-bit Office to 64-bit Office, but a couple of them are significant.

Differences

The main difference between 32 and 64-bit Microsoft Office (VBA wise) is that any pointers now need to be 64-bit rather than 32. Fortunately VBA uses VERY few pointers; 95% of them are involved in API or DLL calls. Unfortunately previous versions of Office (2007 backwards) do not have a data type that holds a 64-bit value, and in 2010 32-bit, you don’t want one!

Helpfully Microsoft has added a new data type in VBA 7 (Office 2010) called a LongPtr. At compile time this will BECOME a 64-bit number on Office 64-bit (LongLong) and a 32-bit number on Office 32-bit (Long). This allows you to “easily” write code that will work between both 32 and 64-bit Office installations (as long as they are both 2010+). One small catch with the LongPtr data type is that although it IS either a Long or a LongLong, because it is intended for pointers and handles only VBA will NOT ALLOW you to perform arithmetic functions on it (such as using it in a loop). This is HIGHLY annoying because it means you cannot simply find/replace Long with LongPtr to “update” code.

API/DLL Calls

As I stated earlier, the LongPtr data type will most often be needed in API or DLL calls requiring pointers or handles (to windows, or processes, or code in memory, or….well A LOT OF STUFF). Since sending a DLL with the wrong size value can be a REALLY bad idea, Microsoft introduced a new keyword to the declaration statement called PtrSafe. In 32-bit Office 2010 this is an optional keyword that has absolutely no effect. In 64-bit, it is a MANDATORY keyword….that still has absolutely no effect!

The logic here is that without the PtrSafe keyword, any declare statements will cause a compile error on 64-bit, which highlights to the developer that there are issues with the code. You are intended to update each declare statement with appropriate LongPtrs and then add the PtrSafe keyword to indicate that this statement is now 64-bit compliant.

An example of one such compile error, when preparing 32-bit code for use in 64-bit Office

In theory this is great, but there are 2 significant issues. Firstly, updating the declare statement is great, but if you’ve been using good practice in your coding you will have multiple variables declared either locally or globally that either pass or receive Longs to declare statements, and these will all need to be updated to LongPtrs. Secondly, if the application ever needs to work in 2007, the PtrSafe keyword AND the LongPtr data type are not recognised, causing compile errors!

The ONLY way to deal with the PtrSafe requirement is to wrap declare statements in compiler conditionals to determine whether PtrSafe should be used.

For example:

#If VBA7 then

Declare PtrSafe Function FindWindow Lib “user32” Alias “FindWindowA” (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr

#Else

Declare Function FindWindow Lib “user32” Alias “FindWindowA” (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

#End If

Messy.

Any variable declarations or procedure declarations involving pointers should be similarly wrapped so that they can be declared as either Long or LongPtr. Theoretically you can avoid this last step by declaring them as variants, BUT:

• It’s generally bad practice, and
• I’ve had two situations where a function declaration including a variant has caused excel to crash. These have both related to situations where I am passing a pointer to a function to a DLL (hooking mouse events) and the DLL has been expecting a function containing a LongPtr declaration. Passing a variant declaration instead caused a crash.

Without Declare statements:

If you are not using any API/DLL calls (you have NO declare statements), then your code SHOULD be fine in 64-bit. The only time I can think of that you can even GET a pointer is with application.Hwnd,or StrPtr/ObjPTR and without DLL calls, I can’t imagine what you could do with it.

Summary

Avoidance

Assuming you’re developing in at least Office 2010 (If you aren’t, STOP, go out and buy it), then:

• ALWAYS use the PtrSafe keyword in declare statements
• ALWAYS use the LongPtr type for pointers/handles

This means that the code should automatically work correctly in 64-bit, and if it needs to work in 2007, you can quickly find ALL the areas you need to update by searching for PtrSafe and LongPtr.

Correction

If you DIDN’T do the above (updating legacy code) then:

Ideally, develop in 64-bit, as this means that all issues will trigger compile errors (assuming you have used Option Explicit!). Just ensure that when updating a declare statement, you correct the necessary Longs to LongPtrs, because if you don’t then you will stop triggering compile errors (meaning that you HAVEN’T fixed the problems!)

If you are developing in 32-bit Office for 64-bit Office, and updating legacy code, then you are GOING to have problems. With no compile errors, you will need to update every declare statement, and then manually track down EVERY variable or procedure statement that relates to a pointer/handle. You will NOT receive compile errors, and so won’t realise until you deploy to the 64-bit environment that you have missed something.

Theoretically it should be possible to:

• Find/Replace over the entire project, replacing Long with LongPtr.
• Update every declare statement to add the PtrSafe keyword, and change NON pointers/handles BACK to Long.
• IN THEORY you should now, on the 32-bit system, receive a compile error for every Long that was changed to a LongPtr in the find/replace, that shouldn’t have been.

This makes logical sense. I haven’t tried it, and I’m not certain I recommend it.