microsoft, professional, software development

Fixing Visual Basic for Applications code for 64-bit Microsoft Office

The first macro programming language in Microsoft Office was Basic, and it is still there in the forthcoming Office 2013. In fact, Visual Basic for Applications (VBA) has been slightly updated, and reports itself as version 7.1 in the preview. The version is Office 2010 is 7.0.

image

Although it is embedded in Office, VBA is a powerful tool and there is not much that you cannot do. It is based on the same runtime that powers Visual Basic 6.0, the last version before the .NET revolution.

Visual Basic makes it easy to call the Windows API though the Declare statement. One implication though is that code written for 32-bit VBA may need revising to work in 64-bit Office, and I ran into this recently with some VBA code of my own.

Some existing Declare statements will work fine on both platforms, but Microsoft chose to force developers to review all of them by introducing a new PtrSafe attribute. The name of this attribute is dubious in that it does nothing to ensure pointer safety. In fact it does nothing at all other than to say to the compiler that the Declare works in 64-bit Office, whether or not it really does. Still, it means you have to add PtrSafe to all your Declares, the idea being that you check that they work. Without PtrSafe, the Declares will not execute in 64-bit Office.

The details of what to change are here. What that article does not mention though is that Microsoft has provided declarations for the most commonly used API declarations that work in both 32-bit and 64-bit VBA. The file is called Win32API_PtrSafe.txt and you can download it here. The file is too large to load into a single VBA module but you can use it to find the declarations that you need.

It can still be difficult to work out how to call some APIs. Note that if you get it wrong VBA and Office may crash. You are giving up the safety of VBA once you use these functions.

I have not yet seen Office running on ARM in Windows RT, but the rumour is that VBA is not supported. That is not surprising, since with VBA you can code pretty much any desktop application, if you don’t mind it running within Office, undermining Microsoft’s intention that only Windows Runtime (formerly known as Metro) apps can be installed on Windows RT.