VBA - Excel: How can I optimize this code?

Question

For a long time I'm not playing with VBA, so we have a spreadsheet on my work and checkng its code, I'm sure it can be improved.

Basically this spreadsheet has literally 200 buttons (100 to open and another 100 to close) and it copies the data from one sheet to another. Below are the examples of two of this macros.

Macro #1:

Sub IT100stop()
'
' newstop Macro
'
' Keyboard Shortcut: Ctrl+s
'
    Application.ScreenUpdating = False
    Range("G47").Select
    ActiveCell.FormulaR1C1 = "DOWN"
    
    
    Range("H47").Select
    ActiveCell.FormulaR1C1 = _
        "=YEAR(TODAY())&MONTH(TODAY())&DAY(TODAY())&HOUR(NOW())&MINUTE(NOW())&SECOND(NOW())"
    Range("H47").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("j47").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range("j47").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    
    Range("K47").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",IF(NOW()-RC[-1]<1,HOUR(NOW()-RC[-1])&"" h ""&MINUTE(NOW()-RC[-1])&"" m"",IF(DAYS(NOW(),RC[-1])<2,DAYS(NOW(),RC[-1])&"" day"",DAYS(NOW(),RC[-1])&"" days"")))"

    Range("F47").Select
Application.ScreenUpdating = True
End Sub

Macro #2:

Sub IT100released()
'
' newreleased Macro
'
' Keyboard Shortcut: Ctrl+r
'
Application.ScreenUpdating = False
    
    Sheets("Database").Select
    Range("A2").Select
    Application.CutCopyMode = False
    Rows("2:2").Select
    Selection.Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Sheets("SINOPTIC").Select
    Range("F47:U47").Select
    Selection.Copy
    Sheets("Database").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("SINOPTIC").Select
    Range("G47").Select
    ActiveCell.FormulaR1C1 = "OK"
    Range("H47:U47").Select
    Selection.ClearContents
Application.ScreenUpdating = True
End Sub

The question is: what can we do to improve this code? If I add this following code before and after the actual macro code, will the calculations be faster?

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False

'Macro Code

Application.EnableEvents = True
Application. DisplayStatusBar = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Thanks a lot!

[Avoid `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — BigBen, Sep 18 '20 at 16:59
`Range("j47").Value = Now()` should simplify, instead of entering `=NOW()` as a formula and then copy/pasting values. — BigBen, Sep 18 '20 at 17:02
If you have that code x 100 then I would start with that part... — Tim Williams, Sep 18 '20 at 17:14
Thanks a lot guys... on Macro #1, I added your comments and the code is much more elegant. What about Macro #2 when I have to copy and paste from one side to the other? — Alecio Lyra, Sep 18 '20 at 17:50

score 1 · Accepted Answer · answered Sep 18 '20 at 18:50

Pay attention to what BigBen wrote: avoid the Select; that is the code created by the macro recorder, but it performs many unnecessary operations. This is the "human" version of your macro # 2

Sub IT100released()
'
' newreleased Macro
'
' Keyboard Shortcut: Ctrl+r
'
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
        Sheets("Database").Rows("2:2").Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Sheets("SINOPTIC").Range("F47:U47").Copy
        Sheets("Database").Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        Sheets("SINOPTIC").Range("G47") = "OK"
        Sheets("SINOPTIC").Range("H47:U47").ClearContents
        .EnableEvents = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

Great... now with the second example I got the whole idea with the "SELECT". Thanks a million. — Alecio Lyra, Sep 19 '20 at 08:58

VBA - Excel: How can I optimize this code?

1 Answers1