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:
.xlsb
)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?
.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