What are the differences between VBA 6.0 and VBA 7.0?

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?

Algy

Answer 1

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

Empedocles

@Todd I just want to point out, LongPtr is an alias and it points to the correct datatype when using 64bit or 32bit of word. So LongPtr would point to LongLong on 64bit office and Long on 32bit office. The below statement would work on both 32 and 64bit office. Declare PtrSafe Function GetTickCount Lib "kernel32" () As LongPtr – Biserrate

@Syler, that's a good point. I haven't tested it yet, but it makes sense what you said. – Empedocles

While this answer shows what is possible with the new keywords, it may certainly come off misleading when it comes to what should be done with them. Having different API declarations for 32-on-64 and for 64-on-64 is not right, and implies there are different Excel files for different architectures. The right thing is to only check for VBA7 and use LongPtr in all places where pointer-sized argument must occur. – Nonjuror

The specific choice of GetTickCount/GetTickCount64 for the first code samples makes it extra confusing because those are two different functions, as opposed to two variants of the same function, and they both exist on both 32 and 64-bit architectures. The @Syler's comment, while correct in its spirit, is unfortunately wrong technically because the return value of GetTickCount is always Long regardless of Windows bitness, so should not be declared using LongPtr. – Nonjuror

The second code sample is absolutely correct as far as the code goes, but it is titled incorrectly: it is not for 64-on-64, it is the correct, universal piece of code that will work on all combinations of Office and Windows bitnesses. – Nonjuror

Answer 2

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

Terribly

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – Hereon

You are of course correct, but remember that this was an answer from the relatively early days of SO, before such conventions were really codified. Think the accepted answer and this one pretty much covers things :) – Terribly

This comment was auto-inserted during a review (link-only-answers tend to end up in the low-quality review queue) – Hereon

Answer 3

VBA7 is compatible with 64-bit versions of Office.

Ampersand

Answer 4

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.

Pompidou

A possibly related difference is documented at : riptutorial.com/vba/example/18930/vb-globalnamespace . – Dragoon

This limitation is common to all versions of VBA – Microcopy