What are the differences between VBA 6.0 and VBA 7.0?
Solution 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:
-
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
-
-
To support this, there are:
Three new keywords (2 data types and 1 modifier):
LongPtr
,LongLong
andPtrSafe
One new function:
CLngLng()
(i.e. Int64)The new compilation constants as used above:
VBA7
andWin64
Solution 2
This piece on MSDN has more on the changes in VBA 7 for Office 2010:
Solution 3
VBA7 is compatible with 64-bit versions of Office.
Related videos on Youtube
romandas
Mainly a systems security guy with some Perl knowledge. I'm reading through SICP and K&R right now. I wish someone had shown me SICP earlier in my career. Just Chapter 1 has been blowing my mind.
Updated on October 01, 2020Comments
-
romandas over 3 years
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?
-
Syler about 10 years@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
-
Todd Main about 10 years@Syler, that's a good point. I haven't tested it yet, but it makes sense what you said.
-
Marco13 about 9 yearsWhile 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.
-
Lunatik about 9 yearsYou 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 :)
-
Marco13 about 9 yearsThis comment was auto-inserted during a review (link-only-answers tend to end up in the low-quality review queue)
-
Mark Fernandes almost 5 yearsA possibly related difference is documented at : riptutorial.com/vba/example/18930/vb-globalnamespace .
-
kismert over 4 yearsThis limitation is common to all versions of VBA
-
aelveborn over 4 yearsWhile 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 useLongPtr
in all places where pointer-sized argument must occur. -
aelveborn over 4 yearsThe 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 ofGetTickCount
is alwaysLong
regardless of Windows bitness, so should not be declared usingLongPtr
. -
aelveborn over 4 yearsThe 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.