How should I make my VBA code compatible with 64-bit Windows

Question

I have a VBA application developed in Excel 2007  and it contains the following code to allow access to the ShellExecute function from Shell32 dll   Private Declare Function ShellExecute Lib  shell32 dll  Alias  ShellExecuteA   ByVal hwnd As Long  ByVal lpOperation As String  ByVal lpFile As String  ByVal lpParameters As String  ByVal lpDirectory As String  ByVal nShowCmd As Long  As Long   I originally said      Apparently the application will not   compile on a 64-bit version of Windows    still using 32-bit Office 2007   I   assume that this is because the   Declare declaration needs updated       I ve read that Office 2010 introduced   a new VBA runtime  VB7   and that this   has some new keywords that can be used   in the Declare statement to allow it   to work properly on 64-bit Windows    VB7 also has new predefined compiler   constants to support conditional   compilation where either the old or   new declaration will be used    depending on whether the application   is running on 32 or 64-bit Windows       However  since I m stuck with Office   2007 I need an alternative solution    What are my options   I d really   prefer not to have to release 2   separate versions of my application if   at all possible     However  per David s answer below  I was mistaken about the circumstances in which my Declare statement won t work  The only circumstances under which it won t work is Office 2010 64-bit on Windows 64-bit  So  Office 2007 is not an issue

User · Answer

Actually  the correct way of checking for 32 bit or 64 bit platform is to use the Win64 constant which is defined in all versions of VBA  16 bit  32 bit  and 64 bit versions     If Win64 Then    Win64 true  Win32 true  Win16  false   ElseIf Win32 Then    Win32 true  Win16 false   Else    Win16 true   End If   Source  VBA help on compiler constants

User · Answer

Office 2007 is 32 bit only so there is no issue there   Your problems arise only with Office 64 bit which has both 32 and 64 bit versions   You cannot hope to support users with 64 bit Office 2010 when you only have Office 2007  The solution is to upgrade   If the only Declare that you have is that ShellExecute then you won t have much to do once you get hold of 64 bit Office  but it s not really viable to support users when you can t run the program that you ship  Just think what you would do you do when they report a bug

User · Answer

Use PtrSafe and see how that works on Excel 2010   Corrected typo from the book  Microsoft Excel 2010 Power Programming with VBA     If vba7 and win64 then   declare ptrsafe function       Else   declare function       End If   val application version  12 0 won t work because Office 2010 has both 32 and 64 bit versions

User · Answer

To write for all versions of Office use a combination of the newer VBA7 and Win64 conditional Compiler Constants    VBA7 determines if code is running in version 7 of the VB editor  VBA version shipped in Office 2010      Win64 determines which version  32-bit or 64-bit  of Office is running    If VBA7 Then  Code is running VBA7  2010 or later          If Win64 Then       Code is running in 64-bit version of Microsoft Office        Else       Code is running in 32-bit version of Microsoft Office        End If   Else  Code is running VBA6  2007 or earlier     End If   See Microsoft Support Article for more details

User · Answer

I ve already encountered this problem on people using my in-house tools on new 64 bit machines with Office 2010   all I had to do was change lines of code like this   Private Declare Function ShellExecute Lib  shell32 dll  Alias  ShellExecuteA         ByVal hwnd As Long  ByVal lpOperation As String  ByVal lpFile As String  ByVal lpParameters As String  ByVal lpDirectory As String  ByVal nShowCmd As Long  As Long   To This    If VBA7 Then     Private Declare PtrSafe Function ShellExecute Lib  shell32 dll  Alias  ShellExecuteA             ByVal hwnd As Long  ByVal lpOperation As String  ByVal lpFile As String  ByVal lpParameters As String  ByVal lpDirectory As String  ByVal nShowCmd As Long  As Long  Else     Private Declare Function ShellExecute Lib  shell32 dll  Alias  ShellExecuteA             ByVal hwnd As Long  ByVal lpOperation As String  ByVal lpFile As String  ByVal lpParameters As String  ByVal lpDirectory As String  ByVal nShowCmd As Long  As Long  End If   You will  of course want to make sure that the library you re using is available on both machines  but so far nothing I ve used has been a problem   Note that in the old VB6  PtrSafe isn t even a valid command  so it ll appear in red as though you have a compile error  but it won t actually ever give an error because the compiler will skip the first part of the if block     Applications using the above code compile and run perfectly on Office 2003  2007  and 2010 32 and 64 bit

User · Answer

This answer is likely wrong wrong the context  I thought VBA now run on the CLR these days  but it does not  In any case  this reply may be useful to someone  Or not     If you run Office 2010 32-bit mode then it s the same as Office 2007   The  issue  is Office running in 64-bit mode   It s the bitness of the execution context  VBA CLR  which is important here and the bitness of the loaded VBA CLR depends upon the bitness of the host process   Between 32 64-bit calls  most notable things that go wrong are using long or int  constant-sized in CLR  instead of IntPtr  dynamic sized based on bitness  for  pointer types    The ShellExecute function has a signature of   HINSTANCE ShellExecute      in opt  HWND hwnd      in opt  LPCTSTR lpOperation      in      LPCTSTR lpFile      in opt  LPCTSTR lpParameters      in opt  LPCTSTR lpDirectory      in      INT nShowCmd      In this case  it is important HWND is IntPtr  this is because a HWND is a  HANDLE  which is void   void pointer   and not long  See pinvoke net ShellExecute as an example   While some  solutions  are shady on pinvoke net  it s a good place to look initially    Happy coding     As far as any  new syntax   I have no idea

User · Answer

i found this code  note that some Long are changed to LongPtr    Declare PtrSafe Function ShellExecute Lib  shell32 dll    Alias  ShellExecuteA   ByVal hwnd As LongPtr  ByVal lpOperation As String    ByVal lpFile As String  ByVal lpParameters As String  ByVal lpDirectory As   String  ByVal nShowCmd As Long  As LongPtr   source  http   www cadsharp com docs Win32API PtrSafe txt

User · Answer

This work for me    If VBA7 And Win64 Then     Private Declare PtrSafe Function ShellExecuteA Lib  Shell32 dll             ByVal hwnd As Long            ByVal lpOperation As String            ByVal lpFile As String           ByVal lpParameters As String            ByVal lpDirectory As String            ByVal nShowCmd As Long  As Long  Else      Private Declare Function ShellExecuteA Lib  Shell32 dll             ByVal hwnd As Long            ByVal lpOperation As String            ByVal lpFile As String            ByVal lpParameters As String            ByVal lpDirectory As String            ByVal nShowCmd As Long  As Long  End If   Thanks Jon49 for insight

[excel] How should I make my VBA code compatible with 64-bit Windows?

Examples related to excel

Examples related to vba

Examples related to 64-bit