What are the differences between VBA 6.0 and VBA 7

Question

I noticed that Office 2010 comes with Visual Basic for Applications 7.0. However I can't seem to find much documentation on what changes were made. Does anyone have a summary of the changes, or any resources describing the differences?

Answer 1

There are other changes as well... I'm having users in the field report that code which functioned properly in 2007 no longer works and shows errors.

Example, this works in VBA6 (Excel 2007)

PRINT STRING$(80,"=")
mynewdata = MID$(mydata, 15,4)

It prints out a line made of "=" characters as a visual break, then looks at mydata, jumps over 15 characters and gets 4 of them, the result is stored in mynewdata. It fails in VBA7 (Excel 2010).

I did find a potential workaround...

PRINT VBA.STRING$(80,"=")
mynewdata = VBA.MID$(mydata, 15,4)

OR

PRINT VBA.STRING(80,"=")
mynewdata = VBA.MID(mydata, 15,4)

A complete list of changes would still be helpful... and/or a file converter.

Answer 2

VBA7 is compatible with 64-bit versions of Office.

Answer 3

This piece on MSDN has more on the changes in VBA 7 for Office 2010:

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

Answer 4

There's not a whole lot that has changed between VBA6 and VBA7. VBA7 was introduced to support 64-bit versions of both Office and Windows (see below on what those differences are). Here are the key changes:

1. 64-bit support, primarily for API calls. This is both used to make your code work with your OS/Office version as well as others' (i.e. someone on Office 2003/WinXP)

   • 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

2. To support this, there are:

   • Three new keywords (2 data types and 1 modifier): LongPtr, LongLong and PtrSafe

   • One new function: CLngLng() (i.e. Int64)

   • The new compilation constants as used above: VBA7 and Win64