VBA and 64 bits

[Lolo]

Hello,

I'm in Office 32 bits (on OS windows 64 bits), and I have developed a macro on an excel file. Problem is that this file will be used by some users in Office 64 bits, and the macro crashes.

So pb is because of a windows API declaration I need to convert to be 64 bit compatible in my VBA code:

Private Declare Function FindWindow Lib "User32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long


Actuall, there are 2 way to do that (I have of course read the Microsoft documentation aboutthis topic, but don't figure out what would be the good one, or if the 2 are really equivalent:


Option 1 : Use of VBA7 compilation instruction

#If VBA7 Then

Private Declare PtrSafe Function FindWindow Lib "User32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr

#Else

Private Declare Function FindWindow Lib "User32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

#End If

Explanation:
If VBA7 (means Office 2010 or higher) then
* use a LongPtr (longPtr is converted in Long if in 32 bits, and LongLong if in 64 bits)
* Use PtrSafe : Necessary in 64 bits, not used in 32 bits (but no errors raised)
Else
* # VBA7 means < Office 2010, so automatically in 32 bits, so use the old instruction
Option 2: Use of WIN64 compilation instruction

#If WIN64 Then

Private Declare PtrSafe Function FindWindow Lib "User32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongLong

#Else

Private Declare Function FindWindow Lib "User32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

#End If

Explanation:
If WIN64 (means Office 64 bits (so at least Office 2010)
* Use a LongLong
* Use PtrSafe : Necessary in 64 bits
Else
* Office 32 bits so use the old instruction
Question:

I have perhaps missed something, but at the end I don't understand what would be the difference between the 2 options, or why using the 1 or the other.

I have even seen some advices on forums where it would be mandatory to combine the 2 compilation instructions, but I don't understand why. The 2 options above already cover all cases, separately.

According to this site:
http://www.jkp-ads.com/articles/apideclarations.asp?AllComments=True

VBA7 should be the good way to do it

Conditional compiling
If your code needs to run on both 32 bit and 64 bit Excel, then another thing to do is add conditional compilation to your VBA.

Microsoft devised two compile constants to handle this:

VBA7: True if you're using Office 2010, False for older versions

WIN64: True if your Office installation is 64 bit, false for 32 bit.

Since the 64 bit declarations also work on 32 bit Office 2010, all you have to test for is VBA7:

At the end the idea is to understand what is the good practice to have VBA codes that will wokr on all cases (developed in Office 32 or Office 64, and executed in Office 32 or Office 64)

Any comments would be really appreciated

Thank you for your help

 

[Chihiro]

Use all. As in...

#if VBA7 then 'Checking if 2010 or later (i.e. check if you need to use inner if statement
    #if WIN64 then
        'run 64 bit code
    #else
        'run 32 bit code
    #end if
#else
    'Run 32 bit code , since there's no 64 bit available for VBA6 or earlier
#end if

 

[Lolo]

Thank you, but still don't understand what your proposition add to the 2 options I have provided.

We could also do, right ? :

#if VBA7 and WIN64 then
'run 64 bit code
#else
'run 32 bit code
#end if

But for me the result seems the same as my option 1 and option 2

 

[Chihiro]

Just syntax difference.

Separated structure is easier to check and debug in case of issue. Nothing more.

Edit: Think of Excel formula...
=IF(AND(A1="X",A2="Y"),TRUE,FALSE)
Does the same thing as...
=IF(A1="X",IF(A2="Y",TRUE,FALSE),FALSE)

 

[Lolo]

OK, so in term of "result", all options are valid/equivalent
Just depends on how you want to manage it in the code, I would say. (To have a more robust code

Thank you !

NB: After you need to use VBA7 if you use specific new VBA7 function or property, of course