50

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?

Todd Main
  • 28,951
  • 11
  • 82
  • 146
romandas
  • 4,086
  • 7
  • 29
  • 33

4 Answers4

46

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

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
Todd Main
  • 28,951
  • 11
  • 82
  • 146
  • 8
    @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 – Syler Feb 26 '14 at 22:07
  • @Syler, that's a good point. I haven't tested it yet, but it makes sense what you said. – Todd Main Feb 26 '14 at 23:24
  • 1
    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`](https://stackoverflow.com/a/56940710/11683) and use `LongPtr` in all places where pointer-sized argument must occur. – GSerg Sep 30 '19 at 20:46
  • 3
    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](https://stackoverflow.com/questions/3072356/what-are-the-differences-between-vba-6-0-and-vba-7-0#comment33440701_3073704), 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`. – GSerg Sep 30 '19 at 20:49
  • 1
    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. – GSerg Sep 30 '19 at 20:51
11

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

Lunatik
  • 3,838
  • 6
  • 37
  • 52
  • 8
    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. – Marco13 Apr 13 '15 at 12:43
  • 2
    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 :) – Lunatik Apr 13 '15 at 13:40
  • 1
    This comment was auto-inserted during a review (link-only-answers tend to end up in the low-quality review queue) – Marco13 Apr 13 '15 at 13:47
7

VBA7 is compatible with 64-bit versions of Office.

buckbova
  • 1,213
  • 6
  • 11
0

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.

Askjerry
  • 49
  • 4