Tuesday, March 10, 2015

Make Excel VBA Macros execute faster

Rows or even cells update one by one like a retarded snail. I know, it's so frustrating.
I'm going straight to the point: Here is the solution to make your VBA (VB Script) Macro runs much faster:

1 - The default file format of Macro-containing Excel files is “*.xlsm”. If you save your file as “Excel Binary Workbook (*.xlsb)” :
  • Excel washes unnecessary rows/columns that reduces the file size. Less file size, faster process.
  • Excel Binary File uses traditional Excel xls format, which is proved to have better performance
2 - Use Functions instead of repeating the code.  For example, if you need to repeatedly put Integer value of a float number, simply create a Function like this:

Public Function Floor(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
Floor = Int(X / Factor) * Factor
End Function

3- Use “Select Case” instead of multiple “IF Then Else”

4- Add following two lines immediately after “Sub” declaration:

Sub mySub()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

5- And of course, turn them on right before “End Sub”:

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox ("Calculation Finished!")