When should the xlsm or xlsb formats be used?

JMax picture JMax · Oct 19, 2011 · Viewed 168.3k times · Source

Since Excel 2007, Microsoft has split the classical .xls format to several formats (in particular, .xlsx, .xlsm, .xlsb). I've got no problem to understand the use and purpose of .xlsx format but I am still wondering whether we should use a .xlsm or a .xlsb format when creating a file containing some VBA.

Of course, you can find some topics on the web, for instance:

What I've understood from this last link is that .xlsm is some kind of XML format and thus, needed for custom ribbon tab.

Beyond the conceptual difference between the format (.xlsm is based on XML VS .xlsb is a binary file), is there any practical difference when using any of this file (apart from the ribbon customization)?
Have you ever seen any real difference when using any of these formats?

Answer

Aaron Thoma picture Aaron Thoma · Mar 10, 2012

.xlsx loads 4 times longer than .xlsb and saves 2 times longer and has 1.5 times a bigger file. I tested this on a generated worksheet with 10'000 rows * 1'000 columns = 10'000'000 (10^7) cells of simple chained =…+1 formulas:

╭──────────────╥────────┬────────╮
│              ║ .xlsx  │ .xlsb  │
╞══════════════╬════════╪════════╡
│ loading time ║ 165s   │  43s   │
├──────────────╫────────┼────────┤
│ saving time  ║ 115s   │  61s   │
├──────────────╫────────┼────────┤
│ file size    ║  91 MB │  65 MB │
╰──────────────╨────────┴────────╯

(Hardware: Core2Duo 2.3 GHz, 4 GB RAM, 5.400 rpm SATA II HD; Windows 7, under somewhat heavy load from other processes.)

Beside this, there should be no differences. More precisely,

both formats support exactly the same feature set

cites this blog post from 2006-08-29. So maybe the info that .xlsb does not support Ribbon code is newer than the upper citation, but I figure that forum source of yours is just wrong. When cracking open the binary file, it seems to condensedly mimic the OOXML file structure 1-to-1: Blog article from 2006-08-07