Created
March 15, 2016 17:25
-
-
Save erajanraja24/56a7e9fea3cfeae2b9b0 to your computer and use it in GitHub Desktop.
How to Speed up Excel VBA macro by adding two functions?
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub normalspeed() | |
Dim StartTime As Double | |
Dim SecondsElapsed As Double | |
'Timer Begins | |
StartTime = Timer | |
For i = 1 To 100 | |
For j = 1 To 100 | |
ThisWorkbook.Sheets(1).Cells(i, j).Select | |
With Selection.Interior | |
.Pattern = xlSolid | |
.PatternColorIndex = xlAutomatic | |
.ThemeColor = xlThemeColorDark1 | |
.TintAndShade = -4.99893185216834E-02 | |
.PatternTintAndShade = 0 | |
End With | |
Next j | |
Next i | |
ThisWorkbook.Sheets(1).Range("A1").Select | |
'Time taken for code | |
SecondsElapsed = Round(Timer - StartTime, 2) | |
'Final Message | |
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation | |
End Sub | |
Sub highspeed() | |
Call speedup | |
Dim StartTime As Double | |
Dim SecondsElapsed As Double | |
'Timer Begins | |
StartTime = Timer | |
For i = 1 To 100 | |
For j = 1 To 100 | |
ThisWorkbook.Sheets(1).Cells(i, j).Select | |
With Selection.Interior | |
.Pattern = xlSolid | |
.PatternColorIndex = xlAutomatic | |
.ThemeColor = xlThemeColorDark1 | |
.TintAndShade = -4.99893185216834E-02 | |
.PatternTintAndShade = 0 | |
End With | |
Next j | |
Next i | |
ThisWorkbook.Sheets(1).Range("A1").Select | |
'Time Taken for code | |
SecondsElapsed = Round(Timer - StartTime, 2) | |
Call speeddown | |
'Final message | |
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation | |
End Sub | |
Function speedup() | |
Application.Calculation = xlCalculationManual | |
Application.ScreenUpdating = False | |
Application.DisplayStatusBar = False | |
Application.EnableEvents = False | |
End Function | |
Function speeddown() | |
Application.Calculation = xlCalculationAutomatic | |
Application.ScreenUpdating = True | |
Application.DisplayStatusBar = True | |
Application.EnableEvents = True | |
End Function | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment