Recorded Macro Edits

[ad91]

Hi Everyone,

I'm VERY new to VBAs and i just started with recording macros, it was brought to my attention that while recording macros after editing it, that there might be some commands that would be considered redundant and might cause problems down the line. The statement was very vague and i was wondering if anyone had any advice or guidance on how to spot such commands in recording macros and how they would compare if the code were to be written in a more efficient and clear way that wouldn't cause issues down the line.

I would appreciate your feedback.

Thank You.

 

[Joe4]

Welcome to the Board!

If it does what you want it to, the only "issues" that really might cause down the line is it will be a little slower than you want, as it records things like cell selections, which usually aren't necessary.

Most of the time, if you have one line end that ends with:

.Select

and the next line begins with

Selection

those two lines can be combined to one line (getting rid of the Select/Selection will speed things up).

Also, it is very literal, and record things like scrolling, which is not necessary in your code and can be removed.

Because it is very literal, you sometimes need to edit the code to make it more dynamic. For example, if you are doing something to a data range that may be different sizes every time, you will want to dynamically determine the size of the range instead of using the recorded range (as that is hard-coded and will never change).

For example, if you wanted to apply something to column A from row 1 down to the last row of data, instead of using a recorded reference like:

Range("A1:A10")

which is hard-coded as A1:A10, you can dynamically find the last row with data in column A like this:

Dim lr as Long
lr = Cells(Rows.Count,"A").End(xlUp).Row

and then dynamically refer to your range like:

Range("A1:A" & lr)

 

[ad91]

Thank you for your explanation, i guess this is the kind of skill that comes with practice.

 

[Joe4]

No problem.

If your Macro does what you want, and the structure of your data doesn't typically change, you will probably be just fine. It might not be the most efficient Macro, but if it does what you want, and only takes a few seconds to run, that is the most important thing. Sure, you may be to do the some things that might save a little time, but if it already runs fairly quickly, it probably isn't necessary.

If you have specific examples of recorded code that does run fast enough, or isn't dynamic enough, you can simply post your code here and ask someone to help you improve it.