Tag Archives: xlsb

Excel Optimizer

Optimize Excel formulas with the Excel Optimizer!

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

Ever been in a situation where your Excel workbook runs so slow you could make yourself a coffee before it finishes recalculating? Fighting to reduce the file size of your latest Excel report? Or just looking to optimize your Excel workbook a bit? Welcome to the AnalystCave Excel Optimizer the AddIn that will optimize Excel for you! Excel Optimizer
Stay tuned for updates on this post!
I must say this idea has been popping in and out of my head for some time now – although it turned out the first working version of the Optimizer took me only a couple of hours to build. Often coming along Excel files that literally called for wrath from the Gods, I yearned for a time that Excel would include a “Fix Me” button – doing away with all the wrongs of the Workbook I was working on. Unfortunately this is (still) not the case and building your Excel file many of us have to be constantly aware of the dangers that lie ahead. What is more, many Excel users waste a lot of time not appreciating simple tips and tricks that can seriously enhance their Excel experience (like simply saving files in XLSB file format).

Optimize Excel with the Excel Optimizer

The Excel Optimizer is a simple AddIn to Excel that runs certain rules across your Workbook and points out the main pain-points or suggests certain improvements – all with performance in mind. Some rules can be implemented (I am hoping making all as such) while others will simply point out what needs to get done.

This is still a beta-work-in-progress-prototype so keep in mind that there may be some error/bugs here and there. What is more the Excel Optimizer current will only be able to suggest certain fixes (such as replacing some Excel functions with others). Depending on how popular this AddIn becomes I am planning to extend it to be able to actually implement most of the recommended tweaks. Optimizing Excel for best performance has become much easier.

See this video for a quick showcase of how the Excel Optimizer works.

Documentation

The Excel Optimizer consists of 2 features:

  • Optimize Excel – the main feature. Opens a window (screen below) that will allow you to run a rule-analysis on your current Workbook. Each rule with validate against different performance standards, rules also have different scopes. There are rules validating the format of the Workbook, validating worksheets and then there are the low-level rules that validate each and every formula. Using the settings button you can turn rules on/off or configure them to enhance your experience
  • Timer Full Calculation Rebuild – a simple macro that calculates the time needed to do a Full Rebuild Calculation of your Excel workbook. This comes in handy when wanting to set a baseline for the improvements you might want to implement

Excel Optimizer: AddInRibbon buttons
Excel Optimizer: AddInRibbon buttons

Below a screenshot of how the main window looks like:
Excel Optimizer: Main window
Excel Optimizer: Main window

Rules

Rules are what drive the Excel Optimizer. The AddIn has been built in a way to make adding/removing/enabling/disabling rules as easy as possible. Currently the Excel Optimizer introduces the following types of rules – many of most still cannot be implemented automatically (expect this in the near future):

Rule Scope Description
XLSB Workbook Checks if workbooks is in XLSB format if not suggests so
UsedRange Worksheet Validates the Used Range of each Worksheet to see if the Used Range is excessive and can be reduced for better performance and optimum file size
VLOOKUP Range Looks for exact (FALSE) VLOOKUPs. If found suggest replacing them with double approximate (TRUE) VLOOKUPs for best performance
Repeating formula Range Looks for exact repetitions of certain functions and suggest replacing them with a certain cell to minimize the need to recalculate the same function multiple times
Volatile functions Range Looks for volatile native Excel functions and suggests removing or replacing them
External links Range Looks for any external links used within cells in the entire Workbook and suggests removing them or reducing to a bare minimum
Array Formula Range Looks for Array Formulas within cells in the entire Workbook and suggests replacing them with regular Excel functions if possible
Error Range Looks errors within cells in the entire Workbook and suggests correcting them

Download

You can download the latest version here:

Do you have ideas for new rules? Any suggestions / wishes for additional features? Put your comment below or on my Facebook / Twitter page.

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 performance

Guide to Improving VBA Performance. Faster Excel VBA

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

There are many different articles and post on improving Excel performance. What I personally was missing was a simple, comprehensive, short overview of how the performance of VBA macros can be improved without needing to read through long articles on how the Excel compiler works bla bla bla.

That is why I gathered all VBA performance guidelines into this single post which contains all the known ways of Improving VBA Performance, organized by impact on VBA Performance.

Turn Off Automatic Calculation

vba performanceSet the Calculation mode to xlCalculationManual so that no calculations are carried out within the Excel Workbook until the Calculation mode is changed back to xlCalculationAutomatic or by running Application.Calculate:

Application.Calculation = xlCalculationManual

Turn Off Screen Updating

vba performancePrevent Excel from updating the Excel screen until this option is changed to True:

Application.ScreenUpdating = False

Use For Each on Collections

vba performanceThe For and For Each Loops are not equivalent and should be used in different situations. ALWAYS use the For Each Loop when looping through Collections, Ranges etc.

This is slower:

Dim tmpCell as Range
For i = 1 to 10
  tmpCell = Cells(i,1)
  tmpCell.Value = 1
Next i

than this:

Dim tmpCell as Range
For Each tmpCell in Range("A1:A10")
  tmpCell.Value = 1
next i

This is because looping through Objects of a Collection is slower.

Use Option Explicit

vba performanceIt is not so much the presence of this macro that benefits your performance but the impact of the behavior it requires from you. Option Explicit requires that you declare ALL VARIABLES. Add this option at the very beginning of your VBA project module or class. This will force you to explicitly declare the types of your variables. VBA is a dynamic typed language therefore you do not normally have to declare the type of your variable or declare variables in that matter. This causes some additional overhead when your VBA code is executed especially if this variable is used often in your code:

Option Explicit
'...your module code below...

The XLSB format vs. XLSM

vba performanceA known way to improve Excel VBA speed and efficiency, especially fore large Excel files, is to save your Workbooks in binary XLSB format. To read more on the benefits read my post on the XLSB format.

Use Early binding of Object

vba performanceWhen working with OLE objects remember that it is more efficient to declare the OLE object type explicitly. Use:

Dim wb as Excel.Application

instead of

Dim wb as Object

Use vbNullString vs “”

vba performanceInstead of comparing an empty string like this

emptyStringVar = ""

do it like this:

emptyStringVar = vbNullString

Disable VBA Events

vba performancePrevent Excel from firing Excel Events while processing your macro:

Application.EnableEvents = False

Use With…End Clause

vba performanceUse the With…End Clause when working with Object such as Ranges, Worksheets, Workbooks, Collections etc. It is not only more efficient but also much easier to read, and simplifies your code.

This is better:

With Range("A1")
     .Value = 1
     .Font.Interior.Color = vbBlue
End With

than this:

Range("A1").Value = 1
Range("A1").Font.Interior.Color = vbBlue
With…End Clause on this MSDN Article here

Disable Excel PageBreaks

vba performanceDisabling PageBreaks in Excel can increase Excel and VBA Performance. Read here why this helps your VBA performance: MSDN PageBreaks and Performance. You can disable PageBreaks like this:

ActiveSheet.DisplayPageBreaks = False

Use VBA Constants

vba performanceConstants are precompiled variables that can’t be modified during VBA untime. Because of this they are more efficient than regular variables defined with the Dim statement. This is slower:

Dim tmpVar as Integer
tmpVar = 1

than this:

Const tmpVar as Integer
tmpVar = 1

Procedure for increasing VBA performance

It is good to handle turning off some of the optimizations mentioned above. I usually use a procedure I defined which I named “OptimizeVBA”. I turn optimizations before running my macro and then turn them off after completion.

Option Explicit
Sub OptimizeVBA(isOn As Boolean)
    Application.Calculation = IIf(isOn, xlCalculationManual, xlCalculationAutomatic)
    Application.EnableEvents = Not(isOn)
    Application.ScreenUpdating = Not(isOn)
    ActiveSheet.DisplayPageBreaks = Not(isOn)
End Sub

'Some macro
Sub ExampleMacro()
    OptimizeVBA True
    'Your code here
    OptimizeVBA False
End Sub

Calculations and Screen updating

Turning ScreenUpdating and Calculations off although improves VBA performance can sometimes causes some issues.

Manual recalculation

In case calculations are turned off do not expect that Excel with recalculate its formulas based on any changes made to your Worksheets. In case you want to control these recalculations use (in this order):

  • ActiveSheet.Calculate – this will recalculate only the “dirty” cells within your ActiveSheet
  • ActiveWorkbook.Calculate – this will recalculate all the “dirty” cells within your ActiveWorkbook
  • Application.CalculateFull – this will recalculate all open Excel Workbooks
  • Application.CalculateFullRebuild – this will recalculate all open Excel Workbooks and rebuild all dependencies

Screen Updating

When turning ScreenUpdating off you will not be able to access or see what is happening on the Excel Worksheet. In some rare cases Excel may crash assuming that the Application is not responding. So how to respond to this? How to control when the Screen is Updated and when it is not? Use the DoEvents procedure. See the example below

'Some macro
Sub ExampleMacro()
    OptimizeVBA True
    'Your code here
    DoEvents 'Update screen etc.
    'Your other code here
    OptimizeVBA False
End Sub

Still slow?

If you still aren’t satisfied with your VBA performance try Compiling your VBA to a .NET COM DLL or multithreading VBA. VBA code can be mulithreaded although this makes sense for macros running more than approx. 10 seconds (as there is an overhead).

Measuring VBA execution time

VBA Time Saver KitWhen working on improving VBA performance you will certainly want to verify how some optimization will reduce your total execution time. There are several known methods for measuring VBA code execution time. The Performance Module of the VBA Time Saver contains at least 2 methods for measuring execution time as well as other way for Improving VBA Performance. There is also a hack to Create an Additional VBA Thread.

The Performance Module contains:

  • 2 Methods for Calculating VBA Execution Time (up to 1/1’000’000 of a Second)
  • Turn Optimizations On/Off
  • Create Additional VBA Thread (unstable on some environments)

Before you start optimizing your VBA macro…

Are you sure you need VBA (Visual Basic for Applications) to solve your problem? Really? Check out my Do you really need Visual Basic for Applications macro for that? article to make sure you aren’t wasting your time writing VBA macros for something available as a feature in Excel.