Why you Should Not Rely on Recorded Macros

Recording Macros is great, right? It allows you to automate a series of steps in a process to make them more efficient.  Though while recording Macros is a great place to start learning about Macros and the VBA code they’re built upon, recordings should not be relied on solely.  After all, data will change, needs change, and processes change.  When that happens, your macros will break.

In our Intro to Macros, we learned how to record an Excel Macro that will type a value into a cell, and copy and paste it. Those two cells were A5 and A10. What happens when needs change and we need to instead type in A6? Our macro would no longer work.  We could always re-record the Macro as this one was rather short, but would it not be easier to just change the small bit of code, A5 to A6? What if our Macro was One Thousand lines of code long for a 20 minute process? Would you want to re-record that?

All of these questions you should ask yourself when making a Macro.  Recording is a good place to start, but learning how to type the VBA code, and adding in variables an loops to make the code dynamic is where you really want to be at; all of which we will continue to get into in greater detail later on.

 

Editing Your Macro

Our Macro in our Intro to Macros had the below steps in it:

  1. Click in Cell A5
  2. Type “Hello World” into cell A5
  3. Copy A5
  4. Paste it into A10
  5. Bold A10
  6. Highlight A10 as Yellow

Which resulted in the below code being recorded:

Range("A5").Select
ActiveCell.FormulaR1C1 = "Hello World"
Range("A5").Select
Selection.Copy
Range("A10").Select
ActiveSheet.Paste
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

We can change our A5 references to A6 and our Macro will now meet our new needs. No recording required.

Range("A6").Select
ActiveCell.FormulaR1C1 = "Hello World"
Range("A6").Select

 

Streamlining the Macro

Recording a Macro will cause Excel to code anything you do, including inadvertent clicks, spelling errors, process errors, etc. VBA code is also processed step-by-step from top to bottom.  Having errors and un-needed clicks in your code will thus slow your macro down.

We can streamline our above macro a bit by removing the “Selects” of the cells.  It is not always required to have a cell selected in order to work with it.

Range("A6").Value = "Hello World"
Range("A6").Copy
Range("A10").Paste

Note how the code that bolds and colors the cell requires a selected cell.  No cell range is specified here, instead it is bolding whatever the currently selected cell is. To make sure we are doing this to cell A10, we can either add code to Range("A10").Select, or change Selection to Range("A10"). Both will work the same.

Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

If you found this content helpful, like and share and feel free to leave a comment below and we will respond as quickly as possible. If you have a question or concern, please feel free to email me via the contact form.

Thank you!



Categories: Excel, VBA

Tags: , ,

Leave a Reply

Show Buttons
Hide Buttons
%d bloggers like this: