VBA 7.0

Office 2010
Support for both 32 bit and 64 bit



DefType - DefLngPtr
vartype constant vbLongLong


LongLong Data Type

Introduction of a new datatype called LongLong This is an 8 bytes (64 bit) signed integer that contains values between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807
This is only available in 64 bit installations Excel only provides 15 digits of precision so a number this large cannot be stored in an Excel cell without loss of precision
There is a new vartype constant - vbLongLong
This is an 8-byte data type which is available only in 64-bit versions of Office 2010. You can assign numeric values but not numeric types (to avoid truncation).
LongLong type-declaration character (^) - Explicitly declares a literal value as a LongLong. Required to declare a LongLong literal that is larger than the maximum Long value (otherwise it will get implicitly converted to double).




CLngLng Function

To accompany this new datatypes there is also a new conversion function CLngLng
There is also a new DefType statement of DefLngLng
The corresponding type declaration character is ^ ( to avoid any confusion with the exponentiation operator, always put a space before the caret when you want to use the exponentiation operator )



LongPtr Data Type

Introduction of a new datatype called LongPtr.
This datatype becomes a Long on a 32 bit installation and a LongLong on a 64 bit installation
This is the recommended way of declaring a pointer or a handle for new code but also for legacy code if it has to run in the 64-bit version of Office 2010.
It is only supported in the VBA 7 runtime on 32-bit and 64-bit. Note that you can assign numeric values to it but not numeric types.
The only things that require LongPtr are function arguments or return values that represent addresses in memory.



CLngPtr Function

There is also a new conversion function CLngPtr.
There is also a new DefType statement of DefLngPtr.
Any code that uses LongPtr will work on both 32 bit and 64 bit



PtrSafe Keyword

In 32 bit office a pointer must be a 32 bit variable In 64 bit office a pointer must be a 64 bit variable.
When declaring an API you must acknowledge this fact by adding the PtrSafe keyword to the API declaration.

Public Declare PtrSafe Function GetTickCount Lib "kernel32" () As LongLong 

Any code that declares a variable as LongPtr can be successfully passed on both 32 bit and 64 bit office.

Public Declare PtrSafe Function GetTickCount Lib "kernel32" () As LongLong 

VBA7 Compiler Constant

Two new compiler constants have been introduced to allow code to work across both 32 bit and 64 bit office.
The Win64 constant is true if you are in a 64 bit version of office The Win32 constants is also true for 64 bit office The VBA7 constant is true for Office 2010 or later #If VBA7 And Win64 Then #Else #End If
Compiler Constants


Win64 Compiler Constant




VarPtr Function

Variant converter. Returns a LongPtr on 64-bit versions, and a Long on 32-bit versions (4 bytes).



ObjPtr Function

Object converter. Returns a LongPtr on 64-bit versions, and a Long on 32-bit versions (4 bytes).



StrPtr Function

String converter. Returns a LongPtr on 64-bit versions, and a Long on 32-bit versions (4 bytes).



DefLngPtr

Sets the default data type for a range of variables as LongPtr.


DefLngLng

Sets the default data type for a range of variables as LongLong.



Microsoft Office Code Compatibility Inspector - Tool you can use with Excel, Word, PowerPoint 2010 and Visual Studio 2008 to troubleshoot/resolve issues with your VBA macros.
XLL SDK - Software development kit now supports calling new worksheet functions, developing asynchronous user defined functions, developing cluster-safe user defined functions that can be offloaded to a cluster
Lets you build 64 bit XLLS, add-ins



https://msdn.microsoft.com/en-us/library/ee691831(loband).aspx#odc_office2010_Compatibility32bit64bit_IntroducingVBA7CodeBase


You only need to make changes if the VBA code is running in a 64-bit version of Microsoft Office.
Office 2010 was available in both 32-bit and 64-bit versions.
A new version of VBA (VBA 7.0) was introduced to accommodate some new features when running 64-bit Office:


Why 64 bit:
Excel is able to handle much larger worksheets with the 64-bit version of Microsoft Office.



Two fundamental issues if running 64-bit Office:


1) VBA previously did not have a pointer data type and because of this, developers used 32-bit variables to store pointers and handles. These variables now truncate 64-bit values returned by API calls when using Declare statements.


2) 64-bit processes in Office 2010 cannot load 32-bit binaries.
Existing 32-bit ActiveX controls, both third-party and Microsoft-supplied, are not compatible with 64-bit Office.
This includes the common controls of MSComCtl (TabStrip, Toolbar, StatusBar, ProgressBar, TreeView, ListViews, ImageList, Slider, ImageComboBox) and the controls of MSComCt2 (Animation, UpDown, MonthView, DateTimePicker, FlatScrollBar)



Microsoft Office Code Compatibility inspector addin:
https://technet.microsoft.com/en-us/library/ee833946(office.14).aspx




1) You must update any existing Windows API statements (Declare)


If you are on a 64-bit version of Windows, but are on a 32-bit version of Office, you can declare API calls like below. .

#If Win64 Then 
    Declare PtrSafe Function GetTickCount64 Lib "kernel32"() As LongLong
#Else
    Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
#End If

If you are on a 64-bit version of Windows, and are on a 64-bit version of Office, you can declare API calls like: .

#If VBA7 Then 
   Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" ( _
       ByVal lpClassName As String, _
       ByVal lpWindowName As String) As LongPtr
#Else
   Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal _
      lpClassName As String, ByVal lpWindowName As String) As Long
#End If


Code that works on Office 2010 (32-bit or 64-bit) and previous versions of Office:
To write code that can work in both new and older versions 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 if code is running in version 7 of the VB editor (the VBA version that ships in Office 2010).
The Win64 conditional compilation constant is used to determine which version (32-bit or 64-bit) of Office is running.


#if Vba7 then 
' Code is running in the new VBA7 editor
     #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 in VBA version 6 or earlier
#end if
 
#If Vba7 Then
Declare PtrSafe Sub...
#Else
Declare Sub...
#EndIf



© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext