Speeding up Macros

Hi Folks,

Great Forum. I have a question about maximizing the speed of macros. I have been using macros by first recording them. Then slowly I've become familiar with the editing and found I could speed up the process of long and complicated macros by removing the "scroll commands".

Is there any other way to speed up the macro? And how can I hide the macro movements? Is there a way to display a progress bar or something similar.

Am I missing something basic and simple?

Thanks in advance.

Victor

Victor,

You can significantly improve macro performance by hiding the macro movements from the user through this code:

Please Login or Register  to view this content.

Regarding progress bars, search this forum; I remember seeing some mention of them and a link to get an add-in(?) to generate them.

Hope this helps,
theDude

Hi:

Thank you, you're not just "The Dude", you're "The Bomb". Tried it and it worked great. I'll get to searching for the progress bar. Once again Dude, Thanks,

Victor

Hi Guys,

The hiding of the macro activity worked. I would like to now add a progress bar, it doesn't have to be progressive, as long as it is done when the macro is done running.

I've posted my macro below, it is a simple recorded one and I purposedly left the scrolls so it runs longer. How can I add the "Progress Bar" to it.

This is my latest frontier in the world of excel.

Thanks in advance.

Victor




Sub UpdateMonthlyStats()
'
' UpdateMonthlyStats Macro
' Macro recorded 3/13/2005 by Victor Gatell
'
Application.ScreenUpdating = False
'
Sheets("Monthly Stats").Select
ActiveSheet.Unprotect
Range("H4:H97").Select
Selection.Insert Shift:=xlToRight
Sheets("Back Page").Select
Range("D6:D42").Select
Selection.Copy
Sheets("Monthly Stats").Select
Range("H8:H44").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("I8").Select
Sheets("Back Page").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("I7:I49").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Monthly Stats").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 41
Range("H51:H93").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Back Page").Select
ActiveWindow.SmallScroll Down:=27
Range("D47:D49").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Monthly Stats").Select
ActiveWindow.SmallScroll Down:=42
Range("H95:H97").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G100").Select
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Sheets("Front Page").Select
Range("N10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Monthly Stats").Select
Range("H4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
End Sub

If you have a lot of formulas in you spreadsheets, you can speed up macros by toggling automatic calculation off and on at various points in your code, particularly if you are cutting/copying and pasting.

You have to be wary of using any formula's value while calculation is turned off though.


Application.Calculation = xlCalculationManual

' Copy/paste here...perhaps several times if values aren't yet needed

Application.Calculation = xlCalculationAutomatic


When needed, I put these just after and just before 'ScreenUpdating' lines

You must remember to turn automatic calculation on at the end of your code!

Good Luck.