Menu
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?
673
XLSB files are smaller, efficient to open and save, and storage efficient as compared to popular XLSX and XLSM files. However, most modern Spreadsheet files opening applications work better with the open-specifications-based XLSX files rather than XLSB files.
An XLSM file is a macro-enabled spreadsheet created by Microsoft Excel, a widely-used spreadsheet program included in the Microsoft Office suite. It contains worksheets of cells arranged by rows and columns as well as embedded macros programmed in the Visual Basic for Applications (VBA) language.
Most of the time you use the XLSX format: It's save (can't store malicious code), has the maximum number of rows and columns and is best known. XLSX is available since Excel 2007 and replaces the old XLS file type.
xlsb) saves the data within an Excel workbook in binary format instead of Extensible Markup Language (XML) format. Binary (i.e. 1s and 0s) is much more efficient that human-legible XML, so the result is a much smaller file size when saved on disk.
.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
160
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
