Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to convert 32 bit VBA code into 64 bit VBA code

Tags:

excel

vba

Im trying to run a macro code but since I'm using a 64 bit Excel 2016 this code is not working. Please help me how to fix this.

Private Declare Function FindWindowEx Lib "User32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long

Private Declare Function IIDFromString Lib "ole32" _
(ByVal lpsz As Long, ByRef lpiid As GUID) As Long

Private Declare Function AccessibleObjectFromWindow Lib "oleacc" _
(ByVal hWnd As Long, ByVal dwId As Long, ByRef riid As GUID, _
ByRef ppvObject As Object) As Long
like image 836
April Avatar asked Mar 02 '17 14:03

April


People also ask

How do I run VBA code in 64-bit?

In Microsoft Office 2010, VBA includes language features that enable VBA code to run correctly in both 32-bit and 64-bit environments. By default, Office 2010, 2013 and 2016 install the 32-bit version. You must explicitly choose to install the 64-bit version during setup.

How do I change Excel from 32-bit to 64-bit?

Click on the Manage Microsoft 365 button and then click Install Apps to present the Install Office 365 screen. Click the Other Options link at the top right to choose a specific version of Office. Select Office - 32-bit or Office - 64-bit, then complete the install process.

Does PtrSafe work in 32-bit?

Declare statements that include PtrSafe work correctly in the VBA7 development environment on both 32-bit and 64-bit platforms only after all data types in the Declare statement (parameters and return values) that need to store 64-bit quantities are updated to use LongLong for 64-bit integrals or LongPtr for pointers ...


2 Answers

These should work on 64 bit Excel

Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
  (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, _
  ByVal lpsz2 As String) As LongPtr

Private Declare PtrSafe Function IIDFromString Lib "ole32" _
  (ByVal lpsz As LongPtr, ByRef lpiid As GUID) As Long

Private Declare PtrSafe Function AccessibleObjectFromWindow Lib "oleacc" _
  (ByVal Hwnd As LongPtr, ByVal dwId As Long, ByRef riid As GUID, _
  ByRef ppvObject As Object) As Long

If you need it to run on both you can use the following #If VBA7

#If VBA7 Then
    '64 bit declares here
#Else
    '32 bit declares here
#End If

A nice resource for PtrSafe Win32 API declares can be found here: Win32API_PtrSafe.txt

like image 158
Pᴇʜ Avatar answered Oct 16 '22 15:10

Pᴇʜ


We need to do following two code changes:

  1. Replace Long data type with LongPtr, at all places in the script
  2. You need to change the private function declarations as below:
  • OLD:

    Private Declare Function GetTimeZoneInformation Lib "kernel32" ( _
               lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
    
  • NEW:

    Private Declare PtrSafe Function GetTimeZoneInformation Lib "kernel32" ( _
           lpTimeZoneInformation As TIME_ZONE_INFORMATION) As LongPtr
    
like image 2
manoj remala Avatar answered Oct 16 '22 13:10

manoj remala