Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save erajanraja24/56a7e9fea3cfeae2b9b0 to your computer and use it in GitHub Desktop.
Save erajanraja24/56a7e9fea3cfeae2b9b0 to your computer and use it in GitHub Desktop.
How to Speed up Excel VBA macro by adding two functions?
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