When should the xlsm or xlsb formats be used?
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:
- on Microsoft answers forum
- on Microsoft blog that was pointed in the previous link (yet I've parsed until the 10th page without finding a ref to
.xlsb) - this topic from another forum
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?
Solution 1:
.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
Solution 2:
They're all similar in that they're essentially zip files containing the actual file components. You can see the contents just by replacing the extension with .zip and opening them up. The difference with xlsb seems to be that the components are not XML-based but are in a binary format: supposedly this is beneficial when working with large files.
https://blogs.msdn.microsoft.com/dmahugh/2006/08/22/new-binary-file-format-for-spreadsheets/