Is there a difference in VBA versions between office 2003 and 2007?

Question

Can I use access 2007 VBA references with impunity (specifically, as far as the base language and old com interfaces are concerned) to develop VBA based solutions for access 2003?

Or is there some new/modified syntax added to the language that I need to be aware of?

Has the object model been enhanced drastically in office 2007?

Any other caveats?

Answer 1

The VBA language itself has not been changed between the recent versions of Microsoft Office (and is probably not going to change in the future either). The version of VBA from Access2000 onwards is VBA6.

The object model of the Office applications however is slighty modified. Microsoft usually only extends the OM by additional methods and properties. As far as Access is concerned, I cannot give you any details but you will find a list of modifications here:

• Object Model Changes Since Microsoft Office 2003 (Access Developer Reference)
• New Objects, Collections, and Enumerations

In general, VBA solutions developed against a certain version of Office will work with a newer version. The devil lies in the details though. Due to bug fixing and new features the applications might behave slighty different than the older version. The only way to find out if everything still works is exhaustive testing.

Answer 2

There is some new properties, methods and objects in Excel 2007.

However, most programs in Excel 2003 works well in Excel 2007

There are few stuff from VBA Excel 2003 that doesn't works at Excel 2007.

I've found 4 issues.

• "Chart.Add" give automation error in Excel 2007 when there are more than 1 cell selected

• Error don't reset by itself, it's necessary uses Err.clear before command that could be release a error.

          On Error Resume Next
          Intruction_That_Could_be_buggy_1
          if Err.Number <>0 Then
               Err.Clear     '  <<<<==== This command is necessary
               Intruction_That_Could_be_buggy_2                  
               if Err.Number <>0 Then
                     ....
  

• Range(...).Paste(xlFormulas) now stops when exists a possible name conflict, it's necessary to use

      Application.displayalerts = False
      Range(....).Paste(xlFormulas)
      Application.displayalerts = True
  

• Several hotkeys like Alt+N are reserved in Excel 2007. Application.Onkey("%n","rotina") doesn't works in that case. Now it's many ribbon shortcut in the ALT+Letra style. I cannot found any way to suppress this behaviour. It should be used other hotkey instead.