When should you use xlsm or xlsb formats?

Question

Starting with Excel 2007, Microsoft has divided the classic .xls format into several formats (in particular, .xlsx , .xlsm , .xlsb ). I have no problem understanding the use and purpose of the .xlsx format, but I'm still wondering if I should use the .xlsm or .xlsb when creating a file containing some VBA.

Of course, you can find some topics on the Internet, for example:

• on Microsoft replies to the forum
• on Microsoft's blog , which was indicated in the previous link (but I analyzed up to 10 ^th pages without finding a link to .xlsb )
• this section from another forum

From this last link, I realized that .xlsm is some kind of XML format and, therefore, is necessary for the custom ribbon tab.

Besides the conceptual difference between the format ( .xlsm based on XML VS .xlsb is a binary file), is there any practical difference when using any of this file (except for setting the tape)?
Have you ever seen any real difference when using any of these formats?

Answer 1

All of them are similar in that they are essentially zip files containing the actual file components. You can see the content by simply replacing the extension with .zip and opening it. The difference with xlsb is that the components are not based on XML, but are presented in binary format: presumably, this is useful when working with large files.

https://blogs.msdn.microsoft.com/dmahugh/2006/08/22/new-binary-file-format-for-spreadsheets/

Answer 2

.xlsx loads 4 times longer than .xlsb saves 2 times longer and has a 1.5 times larger file. I checked this on the generated worksheet with 10,000 rows * 1,000 columns = 10,000,000 (10 ^ 7) cells of simple chains =…+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 of RAM, 5.400 rpm SATA II HD; Windows 7, with a slightly heavy load from other processes.)}

In addition, there should be no difference. More precisely,

both formats support the same set of functions

cites this blog post from 2006-08-29. So perhaps the information that .xlsb does not support the feed code is newer than the top quote, but I suppose your forum source is simply incorrect. When hacking a binary file, it seems that it succinctly imitates the OOXML 1-to-1 file structure: blog article 2006-08-07

Answer 3

You might think that xlsb has only advantages over xlsm. The fact that xlsm is XML-based and xlsb is binary is that when you break a workbook, you have a better chance of recovering xlsm than xlsb.

Answer 4

Just for posterity, here is text from several external sources regarding Excel file formats. Some of them were mentioned in other answers to this question, but without reproducing the main content.

1. From Doug Mahuga, August 22, 2006 :

... the new binary format is XLSB. Like Open XML, this is a full-featured file format in which you can store everything you can create in Excel, but the XLSB format is optimized to improve performance in ways that are impossible in a pure XML format.

The XLSB format (also sometimes referred to as BIFF12, as in the "binary file format for Office 12") uses the same open-packaging convention used by the Open XML and XPS formats. Thus, it is basically a ZIP container, and you can open it with any ZIP tool to see what's inside. But instead of .XML parts in the package, you will find .BIN parts ...

This article also refers to documentation about the BIN format , which is too long to reproduce here.

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

Although we have done a great job to ensure that our XML formats are quickly and efficiently opened, this binary format is still more efficient for opening and saving in Excel and may lead to some performance improvements for workbooks that contain a lot of data, or it would require a lot of XML parsing during the Open process. (In fact, we found that the new binary format is in many cases faster than the old XLS format.) In addition, there is no version of this format without macros - all XLSB files can contain macros (VBA and XLM). Otherwise, it is functionally equivalent to the XML file format above:

File size — the file size of both formats is approximately the same, because both formats are saved to disk using the zip compression architecture — both formats use the same packaging structure, and both have the same part-level structures. Function support - both formats support the same set of functions Performance at runtime - after loading into memory, the file format does not affect the application / calculation conversion speed - both formats will have the same converter support

Answer 5

The XLSB format is also intended for macros placed in a hidden workbook file located in the excel startup folder (XLSTART).

Quick and dirty test with xlsm or xlsb in the XLSTART folder:

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

0.89 with xlsb (binary) and 1.3s with the same content in xlsm format (xml in zip file) ... :)

Answer 6

For some reason, using .xlsb over .xlsm solved some of the problems we had with a user-defined function that creates phantom objects in the object model: https://superuser.com/questions/1005482/excel-creates-non-existent-worksheets -in-vba-project-explorer / 1215336 # 1215336