VBA in 32 bit no problem, to 64 bit exception error

64-bit Visual Basic for Applications Overview


Microsoft Visual Basic for Applications (VBA) is the Visual basic version that is included with Microsoft Office. In Microsoft Office 2010, VBA includes language features that enable VBA code to run correctly in both 32-bit and 64-bit environments.


Note By default, the 32-bit version of Office 2010 is installed. During the installation process, you must explicitly choose to install the 64-bit version.



VBA code written before the version of Office 2010 (VBA version 6 and earlier) needs to be modified to run in a 64-bit version of Office, or it will cause an error when running on a 64-bit platform. This is because VBA version 6 and earlier versions are completely targeted at 32-bit platforms and typically contain DECLARE statements that invoke the Windows API using a 32-bit data type pointer and handle. Because VBA version 6 and earlier versions do not have a specific data type for pointers or handles, it uses a Long data type (a 32-bit, 4-byte data type) to refer to pointers and handles. Pointers and handles in 64-bit environments are 8-byte 64-bit numbers. These 64-bit numbers cannot be included in the 32-bit data type.



Note: The VBA code needs to be modified only if you are running VBA code in a 64-bit version of Microsoft Office.



The problem with running legacy VBA code in 64-bit Office is that 64 digits are truncated in a 64-bit load into a 32-bit data type. This can cause memory overflows, unexpected results in your code, and may cause application failures.

To resolve this issue so that VBA code can run correctly in both 32-bit and 64-bit environments, several language features are added to the new version of VBA. The tables at the bottom of this document summarize these new VBA language features. There are three important new features: The LONGPTR type alias, the Longlong data type, and the Ptrsafe keyword.




LONGPTR-VBA now includes a mutable type alias: LongPtr. LongPtr actually resolves to which data type depends on which version of Office it runs: in the 32-bit version of Office, LongPtr resolves to Long, and LongPtr resolves to longlong in 64-bit versions of Office. The LongPtr is used for pointers and handles.
The Longlong-longlong data type is a signed 64-bit integer that is available only in a 64-bit version of Office. The Longlong is used for 64-bit integers. You must explicitly assign the Longlong (including the LONGPTR on a 64-bit platform) to a smaller integer by using a transform function. Longlong is not allowed to be implicitly converted to a smaller integer.
The PTRSAFE-PTRSAFE Keyword declaration Declare statement can be run safely in a 64-bit version of Office.
Now, when running in a 64-bit version of Office, all Declare statements must include the Ptrsafe keyword. It must be understood that simply adding the Ptrsafe keyword to the Declare statement simply means that the Declare statement explicitly targets 64 bits, whereas all data types in the statement that need to store 64 bits (including return values and parameters) still have to be modified to hold 64 digits.



Note: The Declare statement with the PTRSAFE keyword is the recommended syntax. Declare statements that include Ptrsafe can work correctly in VBA7 development environments on both 32-bit and 64-bit platforms. To ensure backward compatibility in VBA7 and earlier versions, use the following constructs:


#If Vba7 Then
Declare Ptrsafe Sub ...
#Else
Declare Sub ...
#EndIf


Consider the following example of a Declare statement. Running an unmodified Declare statement in a 64-bit version of Office results in an error that states that the Declare statement does not include the Ptrsafe qualifier. The modified VBA example contains the Ptrsafe qualifier, but note that the return value (pointing to a pointer to the active window) returns a Long data type. On 64-bit Office, this is wrong because the pointer should be 64 bits. The Ptrsafe qualifier tells the compiler that the Declare statement is for 64-bit, so the statement can execute normally. However, the return value is not updated to a 64-bit data type and is therefore truncated to return the wrong value.



Example of an old VBA Declare statement that was not modified:


Declare Function getactivewindow Lib "user32" () as Long



Example of a modified VBA Declare statement, which includes the Ptrsafe qualifier, but still uses the 32-bit return value:

Declare ptrsafe Function getactivewindow Lib "user32" () as Long



Again, you must update all the variables in the statement that need to hold the 64-digit number in addition to the Declare statement to include the Ptrsafe qualifier, so that these variables use the 64-bit data type.

An example of the modified VBA Declare statement, which includes the Ptrsafe keyword, is updated to use the correct 64-bit (LONGPTR) data type:

Declare ptrsafe Function getactivewindow Lib "user32" () as LongPtr



In summary, for code to run in a 64-bit version of Office, you need to locate and modify all existing Declare statements to use the Ptrsafe qualifier. At the same time, you need to find and modify the data types of all reference handles or pointers within these Declare statements to use the new 64-bit compatible LONGPTR type aliases, and you need to save the type of 64-bit integers with the new Longlong data type. In addition, you must update any user-defined types (UDTs) that contain pointers or handles and 64-bit integers to use the 64-bit data type, and you must verify that all variable assignments are correct to prevent type mismatch errors.

Write code that can run on both 32-bit and 64-bit Office
To write code that runs on both 32-bit and 64-bit versions of Office, you can simply use the new LONGPTR type alias for all pointers and handle values instead of Long or Longlong. The LongPtr type alias resolves to the correct Long or Longlong data type, depending on which version of Office is running. Note that if you need to perform different logic (for example, you need to use a 64-bit value in a large Microsoft Excel project), you can use the Win64 conditional compilation constants, as shown in the following section.

Write code that can run on Microsoft Office 2010 (32-bit or 64-bit) and previous versions of Office
To write code that can run both in a new version and in an older version of Office, you can use a combination of the new VBA7 and Win64 conditional compiler constants. The VBA7 conditional compiler constant is used to determine whether the code runs in the VB Editor version 7 (the VBA version that is included with Office 2010). Win64 conditional compilation constants are used to determine which version of Office is running (32-bit or 64-bit).

#if Vba7 Then
' Code is running in the new VBA7 editor
#if Win64 Then
' Code is running in a 64-bit version of Microsoft Office
#else
' Code is running in a 32-bit version of Microsoft Office
#end If
#else
' Code is running in VBA version 6 or earlier
#end If



#If Vba7 Then
Declare Ptrsafe Sub ...
#Else
Declare Sub ...
#EndIf





VBA7 Language Update Summary
The following table summarizes the new VBA language features and gives an explanation of each new feature:



Name Type description
The Ptrsafe keyword declares the Declare statement for a 64-bit system. is required on 64-bit.
LONGPTR data Type the type alias is mapped to a Long on a 32-bit system, or longlong on a 64-bit system.
The data type of the Longlong data type is 8 bytes and is only available on 64-bit systems. Number type. -Integers in the range 9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Longlong is only a valid claim type on a 64-bit platform. In addition, you cannot implicitly convert Longlong to a smaller type (for example, you cannot assign Longlong to a Long). The purpose of this is to prevent inadvertent truncation of the pointer. Allows explicit casts, so in the example above, you can apply CLng to Longlong and assign the result to a Long. (Valid only on 64-bit platforms.) ）
^ Longlong type declaration Word modifier explicitly the literal value as Longlong. It is required to declare Longlong text that is greater than the maximum Long value (otherwise it will be implicitly converted to double).
The Clngptr type conversion function converts a simple expression to LongPtr.
The CLNGLNG type conversion function converts a simple expression to a Longlong data type. (Valid only on 64-bit platforms.) ）
Vblonglong VarType constant VarType constant.
The DEFLNGPTR Deftype statement sets the default data type of a series of variables to LONGPTR.
The DEFLNGLNG Deftype statement sets the default data type of a series of variables to Longlong.

Original address: http://www.excelpx.com/thread-325983-1-1.html

VBA in 32 bit no problem, to 64 bit exception error