6

Is there a compatibility issue between macros written for XLS, XLSX and XLSM? Will the same macro work for all workbooks?

David Clarke
  • 12,888
  • 9
  • 86
  • 116
kinkajou
  • 3,664
  • 25
  • 75
  • 128
  • 3
    I think you are confusing people by focusing on the filename rather than mentioning backward compatability between Office versions. If you reword your question you may get more appropriate answers. – JimmyPena Nov 10 '11 at 11:38

3 Answers3

13

There are significant differences between these formats :

  • .XLS is intended to be used for Excel 2003 and above, so your VBA code needs to be backwards compatible for earlier versions of Excel (<2007)
  • .XLSX is the Excel 2007 format that cannot store VBA code.
  • .XLSM or .XLSB are the Excel 2007 format that allow you to save VBA code with the workbook. As Sydenam said, the differences between these two is the way the workbook is stored.

In short: .XLSB is the binary format (equivalent to .XLS for 2007+ version) whereas .XLSM is the OOXML format.

See When should the xlsm or xlsb formats be used? for more information.

Addendum for backward compatibility

I can't see any easy way to tell you how it can be backwards compatible, we can't be that generic. You can see on Ozgrid the new methods and properties that were added in Excel 2007. You can also find here some tips on how to develop on Excel 2007.

The Ozgrid page will give you the new elements of Excel 2007 and then will tell you what you shouldn't use if you wanted to be backwards compatible.

Community
  • 1
  • 1
JMax
  • 26,109
  • 12
  • 69
  • 88
  • Thank you for the answer .The question is not difference between various file extension!!! – kinkajou Nov 10 '11 at 11:31
  • I think he's asking more about backwards compatability between Office 2007 and 2003 VBA methods. For example there are new methods and events available in Office 2007 that won't compile in 2003. – JimmyPena Nov 10 '11 at 11:36
  • @JP: Damn, thanks. OP's question was really, really unclear... Yet, I've edited my answer to fit this new requirement. – JMax Nov 10 '11 at 13:09
3

See JMax's response for a list of methods that are unavailable in Excel 2003.

In general, you should write code for the lowest version of Excel you are willing to support. Ex: if you are supporting Excel 2000-2010, your code should be written for Excel 2000.

Keep in mind, however, that some methods may be deprecated in later versions.

For example, I would avoid using FileSearch altogether (deprecated in Excel 2007) and use Dir instead.

Your code should also be testing the local Excel version and using conditional compilation depending on the version.

For help with conditional compilation, see Using Conditional Compilation.

Also remember that XLSX files cannot save macros. So you should never save files in this format if they contain macro code.

JimmyPena
  • 8,694
  • 6
  • 43
  • 64
  • +1 for pointing out the `FileSearch` function case. MS said it was buggy so they removed it in Excel 2007+, that can be tricky! – JMax Nov 10 '11 at 15:25
1

Excel uses Visual Basic for Applications (VBA) for the macros. The primary difference between the different file types is how data is stored (edit & important XLSX are unable to save Macros, of the formats in the question XLSM and XLS can save macros in the workbook). It is excel and VBA that handle the macros, so it works no problem between all three of those file formats.

Andrew Jackman
  • 13,781
  • 7
  • 35
  • 44
  • No, it won't work beween all three formats because `XLSX` **cannot store VBA** – JMax Nov 10 '11 at 10:27
  • @JMax Are you suggesting that if I write a macro in an XLSX workbook it will not function? Just because it can not be stored, does not mean it will not function. – Andrew Jackman Nov 10 '11 at 10:40
  • hmmm... it will work... until you save your workbook in the XLSX format – JMax Nov 10 '11 at 10:44
  • Did you just write "XLS are unable to save macros"? Since when are Office 2003 Excel workbooks unable to save and store macro code? Or am I misreading that. – JimmyPena Nov 10 '11 at 11:40
  • There's no Excel format "Xlxs". I think you mean "XLSX". JMax is right -- saving an Excel 2007+ workbook in XLSX format will remove any macros in the workbook. Which really has nothing to do with your question. – JimmyPena Nov 10 '11 at 13:45
  • I just came back, and I seem to have gone xlsxlsxlsx crazy when I typed that edit! :p – Andrew Jackman Nov 10 '11 at 17:12