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 10^th 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?

回答1:

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. http://blogs.msdn.com/b/dmahugh/archive/2006/08/22/712835.aspx

回答2:

.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

回答3:

One could think that xlsb has only advantages over xlsm. The fact that xlsm is XML-based and xlsb is binary is that when workbook corruption occurs, you have better chances to repair a xlsm than a xlsb.

回答4:

The XLSB format is also dedicated to the macros embeded in an hidden workbook file located in excel startup folder (XLSTART).

A quick & dirty test with a xlsm or xlsb in XLSTART folder:

Measure-Command { $x = New-Object -com Excel.Application ;$x.Visible = $True ; $x.Quit() }

0,89s with a xlsb (binary) versus 1,3s with the same content in xlsm format (xml in a zip file) ... :)

回答5:

Just for posterity, here's the text from several external sources regarding the Excel file formats. Some of these have been mentioned in other answers to this question but without reproducing the essential content.

1. From Doug Mahugh, August 22, 2006:

...the new XLSB binary format. Like Open XML, it’s a full-fidelity file format that can store anything you can create in Excel, but the XLSB format is optimized for performance in ways that aren’t possible with a pure XML format.

The XLSB format (also sometimes referred to as BIFF12, as in “binary file format for Office 12”) uses the same Open Packaging Convention used by the Open XML formats and XPS. So it’s basically a ZIP container, and you can open it with any ZIP tool to see what’s inside. But instead of .XML parts within the package, you’ll find .BIN parts...

This article also refers to documentation about the BIN format, too lengthy to reproduce here.

2. From MSDN Archive, August 29, 2006 which in turn cites an already-missing blog post regarding the XLSB format:

Even though we’ve done a lot of work to make sure that our XML formats open quickly and efficiently, this binary format is still more efficient for Excel to open and save, and can lead to some performance improvements for workbooks that contain a lot of data, or that would require a lot of XML parsing during the Open process. (In fact, we’ve found that the new binary format is faster than the old XLS format in many cases.) Also, there is no macro-free version of this file format – all XLSB files can contain macros (VBA and XLM). In all other respects, it is functionally equivalent to the XML file format above:

File size – file size of both formats is approximately the same, since both formats are saved to disk using zip compression Architecture – both formats use the same packaging structure, and both have the same part-level structures. Feature support – both formats support exactly the same feature set Runtime performance – once loaded into memory, the file format has no effect on application/calculation speed Converters – both formats will have identical converter support

回答6:

For some reason, using .xlsb over .xlsm solved some problems we were having with a User Defined Function creating phantom objects in the object model: https://superuser.com/questions/1005482/excel-creates-non-existent-worksheets-in-vba-project-explorer/1215336#1215336