none
64 bit VBA RRS feed

  • Question

  •  I found this, http://en.wikipedia.org/wiki/Visual_Basic_for_Applications

    With Office 2010, Microsoft has introduced VBA7 which now contains a true pointer data type: LongPtr. This new data type allows referencing 64-bit address space. The 64-bit install of Office 2010 does not support common controls of MSComCtl (TabStrip, Toolbar, StatusBar, ProgressBar, TreeView, ListViews, ImageList, Slider, ImageComboBox) or MSComCt2 (Animation, UpDown, MonthView, DateTimePicker, FlatScrollBar) so legacy 32-bit code ported to 64-bit VBA code that depends on these common controls will not function. The 32-bit version of Office 2010 is unaffected by this issue. VBA7 includes no 64-bit version of the common controls, so it leaves developers with no means to migrate VBA applications to 64-bits. Microsoft suggests contacting the software vendor for 64-bit versions of VBA controls.

    Is this true? I read this but I'm confused:

    Writing code that works on both Microsoft 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.

    VBA
    #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 
    


    Sunday, July 8, 2012 3:09 PM

Answers

  • The article is generally correct although the way a one or two things written are misleading, so to clear up those -

    Both 32bit and 64bit Office 2010 include VBA7 (not only 64bit as inferred)

    #If VBA7 Then
    ' it's Office 2010 ' or later
    ' in either 32 or 64 bit windows
    #If Win64 Then
    ' it's 64bit Office in 64bit windows
    #End If
    #End If

    64bit Office does not support non bundled ActiveX controls or aX COM dll's. AFAIK the only aX controls which will work in 64bit office are the built in Forms controls for use in userforms and on worksheets.

    VBA code that includes APIs will need to be adapted for 64bit Office, typically within the conditional compiler check for code needs to cater for both 32/64bit.

    In summary, VBA that relies on non built-in controls cannot be adapted for 64bit Office. Code that uses APIs will need to be adapted for 64bit VBA (most APIs can be adapted but not all).  Some other things might need adapting for 32bit Office/VBA running in 64bit Windows, check out "WOW64" and the different System32 path to 32bit dlls. All other code should work similarly in 32/64bit Office.

    Peter Thornton

    • Marked as answer by ResidentX10 Monday, July 9, 2012 3:17 PM
    Monday, July 9, 2012 9:11 AM
    Moderator
  • The only reason VBA7 was introduced was to cater for 64bit, mainly to handle "long pointers" and related declarations, and the new conditional constants VBA7 & Win64. In all other respects it remains the same as VBA6, in turn unchanged since Office 2000. On that basis I assume Office 15 will continue with VBA7, though no doubt there will be new "object model" methods and properties and a other changes to the Application to consider.

    I have no idea of what proportion of 2010 users install as 64bit, even anecdotally. 32bit installs as default (even in Win64) and there's no advantage to install as 64bit except with extremely large workbooks or worksheets.

    Peter Thornton

    • Marked as answer by ResidentX10 Monday, July 9, 2012 4:36 PM
    Monday, July 9, 2012 3:56 PM
    Moderator

All replies

  • Hi ResidentX10,

    Do you tried that? And I interesting with the result of that.

    T.X.


    征诛志异,三让两家王朝
    功同开辟,一桮万古江南

    Monday, July 9, 2012 4:27 AM
  • The article is generally correct although the way a one or two things written are misleading, so to clear up those -

    Both 32bit and 64bit Office 2010 include VBA7 (not only 64bit as inferred)

    #If VBA7 Then
    ' it's Office 2010 ' or later
    ' in either 32 or 64 bit windows
    #If Win64 Then
    ' it's 64bit Office in 64bit windows
    #End If
    #End If

    64bit Office does not support non bundled ActiveX controls or aX COM dll's. AFAIK the only aX controls which will work in 64bit office are the built in Forms controls for use in userforms and on worksheets.

    VBA code that includes APIs will need to be adapted for 64bit Office, typically within the conditional compiler check for code needs to cater for both 32/64bit.

    In summary, VBA that relies on non built-in controls cannot be adapted for 64bit Office. Code that uses APIs will need to be adapted for 64bit VBA (most APIs can be adapted but not all).  Some other things might need adapting for 32bit Office/VBA running in 64bit Windows, check out "WOW64" and the different System32 path to 32bit dlls. All other code should work similarly in 32/64bit Office.

    Peter Thornton

    • Marked as answer by ResidentX10 Monday, July 9, 2012 3:17 PM
    Monday, July 9, 2012 9:11 AM
    Moderator
  • Thanks Peter. I'm looking at this further. Office 15/VBA8? might be released soon so I'll look at it. In your experience, how many customers are running office in full 64 bit mode?
    • Edited by ResidentX10 Monday, July 9, 2012 4:29 PM
    • Marked as answer by ResidentX10 Monday, July 9, 2012 4:36 PM
    • Unmarked as answer by ResidentX10 Monday, July 9, 2012 4:36 PM
    Monday, July 9, 2012 3:17 PM
  • The only reason VBA7 was introduced was to cater for 64bit, mainly to handle "long pointers" and related declarations, and the new conditional constants VBA7 & Win64. In all other respects it remains the same as VBA6, in turn unchanged since Office 2000. On that basis I assume Office 15 will continue with VBA7, though no doubt there will be new "object model" methods and properties and a other changes to the Application to consider.

    I have no idea of what proportion of 2010 users install as 64bit, even anecdotally. 32bit installs as default (even in Win64) and there's no advantage to install as 64bit except with extremely large workbooks or worksheets.

    Peter Thornton

    • Marked as answer by ResidentX10 Monday, July 9, 2012 4:36 PM
    Monday, July 9, 2012 3:56 PM
    Moderator
  • I studied Openxml and it has been useful but it's still young. VBA supports almost all of the office products now.

    Your comments about 64 bit with extremely large workbooks, almost hit my secret question. It would be nice to be able write stuff once for both platforms.

    I just wanted to get another opinion about VBA. Thanks for you time. I'll mark your comments as the answer in appreciation for comments.

    Monday, July 9, 2012 4:36 PM