How to clean up macro recorded code in excel VBA?

Now that you have gone through all the VBA basics. It is time you get a sense of how the macro recorder works.

Whilst the macro recorder is an absolutely wonderful learning tool since it is practically giving you the code, it is also giving you ALL the code. This means that it is not really streamlined for your project which can become problematic for larger scripts. It is however great practice to record something if you don't know to write the syntaxes and then take out the good parts from the code.

Code

I will demonstrate the difference between recorded code and code that has been cleaned up below. I am basically writing "Pls Fix Thx" in different cells and giving them a random color, and then changing the font size to 20. In my clean code example I have slightly modified the colors to only randomize R, G, or B depending on the row to make it a bit more neat.

Messy recorded code

Sub Macro1()
'
' Macro1 Macro
'
'
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "P"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "l"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "s"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "F"
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "i"
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "x"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "T"
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "h"
    Range("D5").Select
    ActiveCell.FormulaR1C1 = "x"
    Range("B3").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("C3").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("D3").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("B4").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("C4").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Range("D4").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Range("B5").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("C5").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 10498160
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("D5").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("B3:D5").Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 20
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Range("J23").Select
End Sub

Cleaner code

Text Characters: First off, for the text characters I am saving some rows avoiding all the 'Select' syntaxes. Coloring: Here we can see that macro recorder always shows us all the options we can change for 'Selection.Interior'. However, since we are only interested in a random color we can remove all the other lines so we we are saving a lot of rows in this part of the script.

Font Size: Just like the coloring part, the macro recorder gives us everything for 'Selection.Font', but once again we are only interested in changing the font size, which means that we can remove the other lines here as well. 


Sub Code_Cleaning()


'Printing out my text.
Range("B3") = "P"
Range("C3") = "l"
Range("D3") = "s"


Range("B4") = "F"
Range("C4") = "i"
Range("D4") = "x"


Range("B5") = "T"
Range("C5") = "h"
Range("D5") = "x"


'Coloring my text in somewhat randomized orders.
Range("B3").Interior.Color = RGB(Rnd * 255, 255, 0)
Range("C3").Interior.Color = RGB(Rnd * 255, 255, 0)
Range("D3").Interior.Color = RGB(Rnd * 255, 255, 0)


Range("B4").Interior.Color = RGB(0, Rnd * 255, 255)
Range("C4").Interior.Color = RGB(0, Rnd * 255, 255)
Range("D4").Interior.Color = RGB(0, Rnd * 255, 255)


Range("B5").Interior.Color = RGB(255, 0, Rnd * 255)
Range("C5").Interior.Color = RGB(255, 0, Rnd * 255)
Range("D5").Interior.Color = RGB(255, 0, Rnd * 255)


'Increasing the size of my text for all cells at once.
Range("B3:D5").Font.Size = 20

End Sub



Conclusion: As we can see, the cleaned up code is a lot more neat and clear with what is happening. Also considerably shorter. Now there are probably several ways to reduce this even further. One way to make it cleaner if you had a slightly larger project could be to make a sub script for the coloring or both coloring and text from the string "Pls Fix Thx" as it is still fairly repetitive.