Tag Archives: binary

xlsb vs xlsx

XLSB vs XLSX. The Pros and Cons of XLSB Files

1 Star2 Stars3 Stars4 Stars5 Stars (6 votes, average: 4.50 out of 5)
Loading...

Working with large Excel files is often a drag. They open slower, they take an eternity to save and they often need to be uploaded to the Cloud to be shared with your coworkers or family. Why not explore the benefits of the XSLB file format then?

What is a XLSB file format?

Inside an XLSX file
Inside an XLSX file
This is what is inside an XLSX (or XLSM) file (image on the right). What do you mean inside? – you ask.

XLSX and XLSM files are in fact compressed archives with XML files inside. That is because Microsoft has opened the Excel file format and decided to break the insides into XML files. When an XLSX or XLSM file is saved Excel needs to break it down into separate XML files, compress it and finally save it as XLSX or XLSM.

large excelThe XLSB file format on the other hand is a binary Excel file. It resembles the old XLS file format which was also a binary file.
What happens when Excel wants to save an XSLB file? Not much really. It mostly dumps the binary data into a binary file and saves it as XLSB.

XLSB advantages (XLSB vs. XLSX)

  • XLSB files are smallerSmaller file size – the Excel binary file uses noticeably less space. This is more noticeable especially when working with large Excel files. In some cases I heard of there being XLSB files that required 10% of the original file size – this is more visible with VERY large files
  • XLSB files save or open fasterOpens/saves more quickly – loading binary data is faster than parsing text (XML) files – similarly as you would compare opening a book in Spanish and having to translate every sentence to English as opposed to picking up a ready translated copy. Similarly, when saving the file – the binary format is more efficient than dumping the data back into the XML and then compressing it. From my experience XLSB files open and get saved 2x faster
  • XLSB files support longer formulasSupports formulas above the 8192 character limit. In other file formats they don’t save properly

Yahoo! Well it would seem there is nothing less obvious to do then to start working only on the XLSB file format. However, it’s not a straightforward decision as there are some minor setbacks. Here are some that come to mind…

XLSB disadvantages (XLSB vs. XLSX)

  • Compatibility – the XLSB Excel format is not supported by Excel 2003 and earlier versions, which frankly is less of a problem nowadays
  • Security (VBA) – with the distinction between the XLSM and XLSX format you know which files may or not contain VBA macros. With XLSB you won’t know for sure. So beware when opening XLSB files from unknown sources or from people/websites you don’t trust
  • You can’t make changes to the Excel Ribbon when working on an XLSB. You must temporarily save your file as XLSX or XLSM, makes changes and save back as XLSB.
  • Lack of interoperability with third-party tools. XLSB is a binary file format unlike the open XML XLSX and XLSM files. Hence you often won’t see your XLSB files working everywhere – like in OpenOffice

Other suggestions when working with large Excel files or datasets

I usually start with the quick wins suggestions and leave the more complex for desert. The XLSB file format is a good start and often won’t require that you meddle with the data/formatting/content of your workbook, rightfully as you shouldn’t have too. However, from time to time there will be those moments when that won’t be enough and you just won’t be able to work any longer with a slow and large Excel file. Here are some useful tips:

  • Reduce the file size by deleting unused cells – as stupid as it sounds this is often the reason for your Excel files mysteriously growing in size over a short period of time. Here is the solution:
    1. Find the last used row in your worksheets

    2. Delete all rows below

    3. Find the last used column in your worksheets

    4. Delete all columns forward

    5. Save the file and close Excel

    6. Reopen the file

  • Save data files without formatting – formatting may account for a lot of storage space and if you are simply working with a dataset and don’t need formatting save your file in .xml format
  • Save data files without formatting – formatting may account for a lot of storage space and if you are simply working with a dataset and don’t need formatting save your file in .xml format or as a .csv
  • Turn automatic calculations off – often even not so large Excel files cause Excel to freeze or crash. That may be because of an abundance of Excel formulas having to recalculate each time a change is made etc. Try to turn Calculation Options to Manual to get rid of this problem
  • PowerQuery Add-In – Excel has its limitations when working with large datasets. The Excel PowerQuery Add-In by Microsoft has been designed to handle Big Data and complex data queries to external databases or datasources. PowerQuery will allow you to work more efficiently with these data sets and will not limit the amount of records you can handle within a single Excel Worksheet

Common myths about XLSB

Do XLSB runs formulas faster than XLSX?

  • Not true

XLSB file are only loaded and unloaded faster (saved and closed) than XLSX files. Afterwards both formats run in RAM memory with similar performance on the same Excel engine. Hence, you won’t see your Excel formula’s running significantly faster. However, if you do save your file frequently you will definitely notice that the XLSB file format saves faster.

Do XLSB files crash more often?

  • Not true

The XLSB file format does not in any way increase the probability of crashing. However, if the file does crash it may be harder to recover. XLSB are binary files, where XLSX and XLSM files are in fact compressed XML files – text files in XML format. Therefore, in a critical situation you have definitely a better chance of reading a text file than a binary file. Then again I wouldn’t worry about this too much.

Conclusions

On a daily basis I would recommend sticking to XLSX and XSLM as standard Excel file formats. It is worth reaching out to the XLSB file format whenever you file starts running slow or uses an enormous amount of space.

XLSB will actually not benefit small Excel files and you might even see small XLSB files taking more space than small XLSX/XSLM files. Your clients / coworkers may also have doubts when opening XLSB files as Excel treats these file formats with an extra dose of caution.

Use the XLSB file format MAINLY with very LARGE Excel files.

vba data

Saving your VBA data (VBA data dump)

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 3.67 out of 5)
Loading...

Sometimes VBA algorithms take a long time to execute and you would want to be able to maintain those calculations in memory when you close and reopen you Excel Workbook or Access Database. Why not save your VBA project data and recover it when reopening your VBA project? Actually you can do just that…

Saving VBA data to binary files

save vba dataThe trick to saving your VBA workspace variables is dump them into a binary file. This is not as complex as it seems, and if you are reading this post you probably already have mastered reading/writing data to text files. Binary files as opposed to text files can store any data type(s), while text files can only contain strings (you might try dumping data as strings and then parsing them, but its not exactly a pleasant job).

If you stumbled across my post on Writing files in VBA (the proper way!) you might be aware of how simple saving data to a binary file is (in this case the “testVar” Integer variable):

Dim fileName As String, fileNo As Integer, testVar As Integer
testVar = 4
fileNo = FreeFile
Open fileName For Binary Lock Read Write As #fileNo
Put #fileNo, , testVar
Close #fileNo

Reading the variable back to your VBA project is equally simple:

Dim fileName As String, fileNo As Integer, testVar As Integer
fileNo = FreeFile
Open fileName For Binary Lock Read As #fileNo
Get #fileNo, , testVar 
Close #fileNo

I hope this demonstrates how easily you can dump your VBA project variables to a binary file and then recover them.

The proper way to dump and recover your VBA data

Now the example above will work fairly well if your variables are of constant byte-length. For example an Integer will require 2 bytes, a Double 8 bytes… No problems yet right? Well issues start when you start dumping Objects and/or Strings. Why? Strings for example are simply a sequence of Characters (bytes). Therefore the string “Hello” will require 5 bytes of space, while “Hello World” will use 11 bytes of space. If you think you can manage this by preceding your strings with an Integer header – you are right. But you will have much more issues with dynamic arrays, Classes and other objects which will require additional headers.

How to manage your VBA project variables then? Encapsulate them using the Type statement. When dumping Types to binary files, the object will include a header with all the information needed to recover the whole Type back to your VBA project. Making a second attempt at the code above, your final methods should look like this:

Type TestType
    intVar As Integer
    strVar As String
End Type
 
Sub SaveVBAVariables()
  Dim fileName As String, fileNo As Integer, testVar As TestType
  '...Some code here...
  testVar.intVar = 4
  testVar.strVar = "Hello!"
  '........
  'Now save all testVar variables to a binary file
  fileName = "C:test.bin"
  fileNo = FreeFile
  Open fileName For Binary Lock Read Write As #fileNo
  Put #fileNo, , testVar
  Close #fileNo
End Sub

Sub ReadVBAVariables()
    'Read the testVar variables back to your VBA project
    Dim fileName As String, fileNo As Integer, testVar As TestType
    fileName = "C:test.bin"
    fileNo = FreeFile
    Open fileName For Binary Lock Read As #fileNo
    Get #fileNo, , testVar
    'Recovered the TestType to testVar
    Close #fileNo
End Sub

The SaveVBAVariables procedure demonstrates how you can create and work with your variables, and
save your results to a binary file.
The ReadVBAVariables procedure can be executed even after you close and reopen the workbook – recovering your Test data type along with all enclosed variables.

vba write file

Writing files in VBA (txt, xml, csv, binary)

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 4.75 out of 5)
Loading...

write file vbaContinuing on the topic of working with files using VBA. Text/binary files are common ways of storing data as opposed to databases or regular Excel files.

Looking at various resources I missed a single resource which would demonstrate the various methods for PROPERLY writing files in VBA. It is important to remember that you shouldn’t write all fills using the same approach. Be aware of the structure of the file. If you want performance – always select the right approach.

Writing text files in VBA

Approach #1: The Write function (strings in quotes)

Writing strings of data to text files with quotes:

Dim fileName As String, textData As String, textRow As String, fileNo As Integer
fileName = "C:\test.txt"
fileNo = FreeFile 'Get first free file number  
textData ="Hello World!"
Open fileName For Output As #fileNo
Write #fileNo, textData
Close #fileNo

This approach will result in the following output:

"Hello, World!"

Notice the “” quotes. Writing further strings will result in appending further strings wrapped in double quotes which might not be a satisfactory approach. Look down to the Print function to avoid these quotes.

Approach #2: The Print function (dumping strings without quotes)

Writing strings of data to text files without quotes:

Dim fileName As String, textData As String, textRow As String, fileNo As Integer
fileName = "C:\test.txt"
fileNo = FreeFile 'Get first free file number  
    
Open fileName For Output As #fileNo
Print #fileNo, textData
Close #fileNo

This approach will result in the following output:

Hello, World!

This time there are no quotes in the file. In most case you will want to use the Print function instead of the Write function

Writing binary files in VBA

Dim fileName As String, fileNo As Integer, testVar As Integer
fileName = "C:\test.bin"
testVar = 4
fileNo = FreeFile
Open fileName For Binary Lock Read Write As #fileNo
Put #fileNo, , testVar
Close #fileNo

With Binary files often you will be using objects which are not of fixed byte length like Integers. For example you would want to save Strings. In such cases use the VBA Type object data type.

Below a simple example using the Type data type to save an object with an Integer and String.

Type TestType
    intVar As Integer
    strVar As String
End Type

Sub WriteBinary()
  Dim fileName As String, fileNo As Integer, testVar As TestType
  fileName = "C:\test.bin"
  testVar.intVar = 4
  testVar.strVar = "Hello!"
  fileNo = FreeFile
  Open fileName For Binary Lock Read Write As #fileNo
  Put #fileNo, , testVar
  Close #fileNo
End Sub

Writing CSV files in VBA

If you want to save your worksheet as CSV you can always resort to the Text file section. However, this is an ineffective approach. If you want to save your current worksheet as a semicolon delimited file (‘;’) you can use the Workbook.SaveAs function like below.

PathName = "C:\test.csv"
'Assuming you want to export your current Worksheet
ActiveSheet.Move 'Move the Worksheet to a new Workbook
ActiveWorkbook.SaveAs Filename:=PathName, FileFormat:=xlCSV, CreateBackup:=False

Functions needed to write files in VBA

Function Description
FreeFile Get next free file number available for the Open statement / FileOpen function. Using this function is important especially when operating on multiple files simultaneously.
More info here.
BOF(fileNumber) Returns true if you are at the beginning of the file described by the file number.
More info here.
EOF(fileNumber) Returns true if you have reached the end of the file described by the file number. More info here.
Loc(fileNumber) Returns the current read/write position within an open file. More info here.
LOF(fileNumber) Returns the size in bytes of the file represented by the file number. More info here.

Write file VBA summary

Writing files in VBA is not hard and usually takes just a couple of lines of code. It makes sense however to do it write and be aware of certain traps like not omitting the use of the FreeFile function or knowing the difference between Print and Write.

vba read file

Read file in VBA (txt, xml, csv, binary)

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 4.75 out of 5)
Loading...

Today shortly on how to read files using VBA (VBA read file). Text/binary files are common ways of storing data as opposed to databases or regular Excel files. Looking at various resources I missed a single resource which would demonstrate the various methods for PROPERLY reading files in VBA.

It is important to remember that you shouldn’t read all fills using the same approach. Be aware of the structure of the file. If it is a structured CSV use the ADODB connection, if you need to read only a couple of rows read the file row by row or by chunks, else read the whole file. If you want performance – always select the right approach.

Reading text files in VBA

Reading text files (line by line)

Reading the text file line by line:

Reading text files (read whole file)

Reading the whole text file into a string:

Reading CSV files in VBA

Reading CSV files (read whole file and process each row)

Reading a text file line by line into a string:

Reading CSV files (whole file to Worksheet)

Read whole file to an Excel Worksheet:

To refresh the CSV upload (in case the CSV was updated) simply run:

Reading binary files in VBA

With Binary files often you will be using objects which are not of fixed byte length like Integers. For example you would want to read Strings from binary files together with other data types. In such cases use the Type object data type when writing to a file. Learn more here.
Below a simple example of reading a file to which a Type data type was saved to, including an Integer and String.

Reading XML files in VBA

Functions needed to read files in VBA

Function Description
FreeFile Get next free file number available for the Open statement / FileOpen function. Using this function is important especially when operating on multiple files simultaneously.
More info here.
BOF(fileNumber) Returns true if you are at the beginning of the file described by the file number.
More info here.
EOF(fileNumber) Returns true if you have reached the end of the file described by the file number. More info here.
Loc(fileNumber) Returns the current read/write position within an open file. More info here.
LOF(fileNumber) Returns the size in bytes of the file represented by the file number. More info here.

VBA Read File Summary

Reading files in VBA is not hard and requires just a few lines of code usually. It is, however, important to use the appropriate approach to read a file in VBA. Not all files need to be read line-by-line which is usually inefficient. Equally so you need not always read the entire file if you just need the first few / last rows. Working with XML files is also not a challenge if you read through my post on how to work with XML files.