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
bbinnard

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

I installed the 64-bit Office 2010 on my Win7-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
.

19 Aug 2014   #2
Vladimir

Windows 7 Ultimate x64
 
 
Same code?

Same code in both branches?
My System SpecsSystem Spec
21 Aug 2014   #3
bbinnard

Win7-64
 
 

OOps - sorry, the correct code is:

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


Reply

 How to resolve VBA problems with 64-bit Office 2010




Thread Tools





Similar help and support threads
Thread Forum
Outlook 2010 and Office 365 Problems
Greetings, I have had about one week of frustration and I am hoping someone can help with this. I had Outlook 2010 on a hosted exchange with Apptix. The cost was 35.00 per month so I changed to Office 365 )business essentials I think) with GoDaddy. This is where the problems started. ...
Microsoft Office
Problems With Updates to Office 2010
I have two updates KB2687455 and KB2687623 that will not install. I get error 80070643. I am running Win.7 64bit. Net.Framework 1.1 and 4.5 is installed. Please help.
Microsoft Office
Problems with Office 2010 updates
I have Windows 7 64 bit system. I installed Microsoft Office 2010. I have some updates that don't want to install. When I check Program files Office is there. It also shows up in Program Files X86. Does that mean that is in 64 bit and 32 bit?
Microsoft Office
Office 2010 installation problems
After installing office ... after installing when restart it says Office 2010 has stopped working. When trying to install from folder it says Microsoft Bootstrap manager or something has stopped working. What can I do? This is all a clean install with no previous office on a brand new PC with...
Microsoft Office
Office 2007 to 2010 problems
I just purchased a new laptop and have been moving my files from my old machine with VISTA and Office 2007 to the new machine with Windows 7 and Office 2010. I installed Office 2010 and it all worked fine, when I moved my saved files over to the new machine I could not open Excel files. I...
Microsoft Office
Problems after Upgrade Installation from Office 2003 to Office 2010
I did an upgrade installation from Office 2003 PRO to Office 2010 PRO. I had several issues with Access denied on C:\Config.msi. I usually select retry and all is well. I think I may have missed one of the retries before I did the system restart. Now I get an error 2203 whenever I reboot, and the...
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:15.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App