XLSM vs. XLSB

[richlocus]

Hello:
I have been developing VBA powered Excel applications for many years. I have always used the XLSM format. I presented an XLSM application to a client and they changed it to XLSB and said that is their preference. After researching it, apparently there are advantages to XLSB such as a smaller size and faster execution. However, in all my years of development, I RARELY see the XLSB format used.

What is your experience and recommendation on this issue?

Thanks,
Rich Locus

[HansV]

The .xlsx, .xlsm and .xlsb files are all renamed .zip archives containing various files and subfolders. In a .xlsx or .xlsm workbook, workbook objects such as worksheets, comments and styles are stored as .xml files (structured text files), while in a .xlsb file, they are stored as .bin files (binary files). Some other objects are stored as .xml in both, for example the design theme, or as .bin in both, for example compiled VBA code and printer settings.

Binary files are considerably smaller than the corresponding XML files, so a .xlsb workbook is smaller than the corresponding .xlsx or .xlsm workbook.

XML files have the advantage that they can easily be inspected and modified programmatically, so you could create a program that reads, populates or edits an Excel sheet without running Excel. You cannot do that with binary files. And on at least one occasion, I have been able to fix a problem in a workbook that I couldn't solve in Excel itself, by manually editing one of its XML files.

For most workbooks, even reasonably large and complicated ones, the size and speed advantage of a .xlsb workbook is not relevant in my experience. Disk space is cheap, and processors are very fast nowadays. But for very large workbooks that take a long time to open and to recalculate, it could make a significant difference.

[richlocus]

Hans:

Thanks for the information. Based on your narrative, I will discuss this with my client. The application is quite small and I would suggest to keep it as an XLSM.

Thanks again,
Rich Locus