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!")
