Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: How to resolve VBA problems with 64-bit Office 2010


18 Dec 2011   #1

Win7-64
 
 
How to resolve VBA problems with 64-bit Office 2010

I installed the 64-bit Office 2010 on my Windows 7-64 system a few months ago. Since then I have encountered only one issue with it, and that has to do with writing VBA code that needs to run on both 64 and 32-bit systems. In my case the code is in an Access application, but the same issue will apply to any Office-64 application that uses VBA.

The issue occurs when you use VBA to call a Windows DLL that uses pointers. In essence, 64-bit pointers are handled differently from 32-bit pointers. So MS included a new keyword to accommodate this: PtrSafe. This keyword is only used in 64-bit VBA code; it causes a syntax error in 32-bit code.

So how to have one set of VBA that works for both 64 & 32-bit systems? The answer is: Conditional Compiling. What has to happen is the VBA compiler needs to see the 64-bit code if it is running on a 64-bit system, but if running on a 32-bit system it needs to see the 32-bit code. The easiest way to show how this works is by example:


Option Compare Database
Option Explicit

#If Win64 Then
Private Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
#Else
Private Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
#End If

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type


This routine uses the Windows Common Dialog Open function to return a user selected file name. The Common Dialog uses pointers, so its invoking code has to be customized for both 32 and 64 bit systems. That's what the # character does in front if the initial If test - it detects the type of system and sends the appropriate VBA code to the compiler.

My System SpecsSystem Spec
.

Reply

 How to resolve VBA problems with 64-bit Office 2010




Thread Tools



Similar help and support threads for2: How to resolve VBA problems with 64-bit Office 2010
Thread Forum
Problems With Updates to Office 2010 Microsoft Office
Problems with Office 2010 updates Microsoft Office
Problems with Default Fonts in Office 2010 Microsoft Office
Office 2010 installation problems Microsoft Office
Office 2007 to 2010 problems Microsoft Office
Office 2010 Professional Plus Installation Problems Microsoft Office
Problems after Upgrade Installation from Office 2003 to Office 2010 Microsoft Office

Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 10:09 PM.
Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App
  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33