Wednesday, February 29, 2012

Adjusting our Word VBA Macros for Office 2010 (64 bit)


We've had it easy these last few years. You could just upgrade Microsoft Office and not have to worry about file compatibility or about your macros not working.  I was just beginning to relax.


Unfortunately Microsoft never relaxes and the latest "innovations" extend past the annoying ribbon interface (I've been using it for over a year and I still consider it annoying) to clobber both file formats and macros.

The File Format Problem
The file format problem is easy to fix. First of all, I've changed our defaults to save in Office 97-2003 format.   There's no sense in our company posting files for public consumption in a format that they can't read. In case you're wondering where to change this, click File, then Options and click on SAVE.


If your fleet is still using Office 2003, you can download a compatibility pack which will enable it to read the new office formats.

http://www.microsoft.com/download/en/details.aspx?id=3


64 Bit API Declarations
This next bit is a little frustrating.  We rely on INI files in our Macros as they're much simpler than the registry and we can easily get our Notes/Domino apps to write updates into the files. It's all good for automation.  Unfortunately the 32 bit API calls won't work any more.

To get around the problem, replace this;


Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationname As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long

Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationname As String, ByVal lpKeyName As String, ByVal lsString As String, ByVal lplFilename As String) As Long


with this


Declare PtrSafe Function WriteProfileString Lib "kernel32" Alias "WriteProfileStringA" (ByVal lpszSection As String, ByVal lpszKeyName As String, ByVal lpszString As String) As Long

Declare PtrSafe Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long

The remainder of your INI file reading routine should run without changes.


Unavailable Objects and those Nasty String Function Errors
This one is a nasty one.  Sometimes you'll try to open a macro that contains an unusual object, such as a calendar control.  Word 2003 displays a dialog box which says;  "Could not load an object because it is not available on this machine".  This was helpful.  

Unfortunately, Word 2010 doesn't mention the offending object but instead assumes that all internal string functions, such as LCASE$, UCASE$, LEFT$, RIGHT$, MIDS$ and TRIM$ are external functions in the missing component.



It highlights the first string function and says "Can't find project or library" which promptly sets you off on a wild goose chase.

To fix this problem, 
  1. Stop your macro
  2. Click Tools, then References (if this is greyed out, you didn't stop the macro).
  3. Find the problem Reference in the list and deselect it 
  4. Click Ok.

You'll also have to replace your missing element with a similar control, such as a text box and adjust any code which refers to it.

After that though, you should have a working, if a little disabled, macro.  Now I just need to find a 64 bit calendar control.

2 comments:

Anonymous said...

You missed "As Long" -> "As LongPTR"

lee.strachov@cba.com.au

Anonymous said...

You for got the ptrlong part.
Lee.strachov@cba.com.au