Macro Recorder Advantages

Before we get into the disadvantages and issues with the recorder, we want to mention the advantages of the recorder, especially if you have little knowledge of Visual Basic and coding in general. Using the VBE requires coding skills, and you have an advantage if you already work with other languages. The interface is easier to learn if you use other Microsoft coding technologies such as Visual Studio, but for people new to VBA it is more difficult to navigate until they work with the language for a few weeks.

The following is some of the advantages of using the macro recorder.

1.    The same task executes each time with just a click of a button. 

2.    No coding skills required, and the recorder automatically creates the code necessary to run the macro.

3.     Automates frequent tasks for the coder and the end user.

4.    Takes very little time to record a macro, and the user can stop, start and pause a macro using the Excel toolbar.

Overall, using the macro recorder is for people who don't know Visual Basic and don't want to take the time to create complex code.

Disadvantages of the Recorder

With the recorder advantages, there are also disadvantages. You should know these disadvantages not only when deciding how you want to create a macro, but also when you need to troubleshoot it. The recorder automatically creates code in the VBE that we reviewed in the last lesson. This code can sometimes be buggy if it isn't created properly.

To illustrate the way code is created, record a simple macro. Give any name you want to the macro. The name you give it affects the code created. Remember that macros are subroutines, so a subroutine is created in the code when you record one.

Let's assume you name the macro Macro1. Open the Visual Basic Editor. If you recall, you can quickly open the VBE by holding the ALT key and then pressing the F11 key.

Notice now that you have an extra section in the left project explorer panel. You now have a section named "Modules." This section contains any automated subroutines created by the macro recorder. Since this code was automated, the name "Module1" is given to your new module.

Modules are files that can contain dozens of subroutines and functions. You should keep your modules organized. For instance, if you had one section of code that handles column formats and one that handles rows, you would create two modules – one for columns and one for rows. Modules should always be given a name that matches their functions as well.

One issue with the macro recorder is that it automatically gives modules and macros a default name. This is why we see the name "Module1" assigned to our newly recorded macro. Double-click the module and the source code is shown in the window.

If you don't see the macro, select it in the Declaration dropdown menu. You should see code that looks like the following.

Sub Macro1()

‘

‘ Macro1 Macro

‘

// what you recorded is coded here

End Sub

The Macro1 name is the name of your macro. Since we gave the recording the name Macro1, it's automatically used in our subroutine code. For instance, let's assume that we formatted a cell's content to a percentage in our recorded macro. You would see a subroutine similar to the following.

 Sub Macro1()

‘

‘ Macro1 Macro

‘

Selection.NumberFormat = "0.00%"

End Sub

The above code formats numbers to a double decimal point percentage format. Each time you run a recording, this code runs.

One issue with the recorder is that it adds too much unnecessary code. We used a small example, but even with this small example, the comments added are not very useful. Comments should be descriptive and help the developer understand the logic behind a certain block of code. In this example, the macro recorder didn't add anything worthwhile. It does not explain the purpose of the macro or what it does for the user. This is a small example that doesn't need much explanation, but longer macro code needs comments. Comments aren't just necessary for the current coder. They are also necessary for future coders that need to maintain the code.

Another issue with the recorder is the unnecessary code that is then difficult to troubleshoot. We provided a small macro example to examine the code, but longer recordings add messy code to your modules.

Let's look at another example for a macro that has about 15 steps in the recording.

Sub Macro1()

‘

‘ Macro1 Macro

‘

Workbooks.OpenText Filename:= _

        "C:\namesimport.csv", Origin:=437, StartRow:=1, DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,  _

        Tab:=True, Semicolon:=False, Comma:=True, Space:=False,  _

        Other:=False, FieldInfo:=Array(Array(1, 3), Array(2, 1), Array(3, 1), _

        Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _

       TrailingMinusNumbers:=True

End Sub

In the above example, a file named "namesimport.csv" is imported into the Excel workbook. This long block of code could be much better formatted for readability. Coding VBA projects isn't just creating code. You also need to make your code readable for other coders and well formatted for easier troubleshooting.

Troubleshooting these types of macros are very difficult due to the poorly created code. Imagine that your recording works properly, but you have errors in the way the macro is run. You need to review the code to troubleshoot the issue. When you have subroutines with long blocks of code condensed into one like the above example, it's difficult to understand what the macro is doing to troubleshoot. You also can't easily step through using the debugger to identify where the code is throwing an error.

Using Absolute Vs. Relative Cell Assignments

The final issue with using the recorder is the way it assigns and addresses cells. By default, the recorder uses absolute referencing. Absolute referencing is good in programming such as web design, but it's a problem for VBA projects.

Let's take a look again at the example Macro1.

Sub Macro1()

‘

‘ Macro1 Macro

‘

Worksheets("Sheet1").Range("A1").Value = "Test"

End Sub

We changed the code to assign the value "Test" to the A1 cell. In this example, we assign "Test" to the A1 cell every time. This is an absolute reference. Absolute references are beneficial when you want to change the same cell every time, but it's not good for dynamic editing. Each time you run this macro, "Test" is assigned to the first cell and no other cells. What if you want to enter "Test" into a dynamic set of cells relative to the active cell? You can do this using relative cell assignments.

Relative assignment identifies the active cell and makes changes to content using a offset location. Let's take a look at the code.

Sub Macro1()

‘

‘ Macro1 Macro

‘

Worksheets("Sheet1").Range("A1").Value = "Test"

ActiveCell.Offset(16, 1).Select

End Sub

In the code above, we assign the value of "Test" to the first cell, and then sets an offset cell as the active one. Notice that there is an offset of 16, 1. The format for setting an active cell is "row, column." Therefore, since we first made changes to A1, the offset is 16 rows down and 1 column over. The result is that the cell B17 is now active. Note that you can use negative numbers to move left or up. Positive numbers offset to the right and down.

With an offset, you can then assign the dynamic cell a value. Let's take a look at the same code except now we will assign the cell a value.

Sub Macro1()

‘

‘ Macro1 Macro

‘

Worksheets("Sheet1").Range("A1").Value = "Test"

ActiveCell.Offset(16, 1).Select

Selection.Value = "Test"

End Sub

We've added a line of code to the macro. In the above example, the value "Test" is assigned to the A1 cell. An offset of 16 rows and 1 column is used to make it active. Again, the active cell is now B17.

The final line of code uses the active cell to assign the value "Test" to B17. This code uses a static offset value, but you can create more dynamic code. We'll get into more complex, detailed VB code in future chapters to show how you can make macros more dynamic for the user.