Excel VBA 24-Hour Trainer. Tom Urtis

Macro Recorder assigns the default name of Module2, and so on.

In the Project Explorer window, expand the bolded VBAProject title (my Project workbook name is MacroExamples.xlsm) and expand the yellow Modules folder to show the module named Module1. To see the VBA code in that module, you can double-click the module name, or you can right-click the module name and choose View Code, as shown in Figure 4.2.

Figure 4.2

The mySort macro appears in the Code window for Module1. Based on the steps you took while recording the mySort macro in Lesson 2, Figure 4.3 shows the exact code that was produced by the Macro Recorder in Excel version 2003.

      NOTE If you record (or manually compose, as you see in later lessons) a macro in a version of Excel after 2003, and you run that macro in a 2003 version, you might experience an error in that code's execution, depending on what the code is trying to do. VBA code plays well together among versions after 2003, but those later versions of Excel contain newer features, such as Sparklines and an updated object model for charts and pivot tables, that a 2003 version would not recognize. VBA code produced by the Macro Recorder in version 2003 usually works just fine in later versions, but be aware that backward compatibility has its limitations when running code in a 2003 version that was produced in a later version.

Figure 4.3

      Understanding the Code

All macros start with a Sub statement (Sub is short for Subroutine, commonly referred to as a macro) that includes the name of the macro, followed by a pair of parentheses. For the example macro you see in Figures 4.3 and 4.4, the Sub statement is simply Sub mySort().

Figure 4.4

      Because this macro was recorded, there is a series of comment lines below the Sub statement that the Macro Recorder wants you to know about. For example, you see the macro name, the description of the macro you entered into the Record Macro dialog box, and the notation that the shortcut Ctrl+Shift+S has been assigned to this macro.

      Comment lines start with an apostrophe, are green in color to help you identify them, and are not executed as VBA code, as opposed to the other lines of VBA code that actually do something when the macro is running.

      NOTE The comments you see in a recorded macro directly reflect the information entered in the Record Macro dialog box. For example, if you assign a shortcut key, or you enter text in the Description field of the Record Macro dialog box as shown in Lesson 2, Figure 2.13, that information will be seen as comments in your recorded macro's code, as shown in Figure 4.3.

      The remaining lines in the macro are VBA statements, and they represent every action that was taken while the Macro Recorder was on:

      1. The first thing you did was select column A.

      2. Next, you inserted a new column at column A.

      3. Next, you selected column C, cut that column, and pasted it to column A.

      4. Next, you went back to select column C because it was empty, and you deleted it.

      5. Next, you selected range A1:C13 where the table of data was.

      6. Next, you sorted the selected range.

      7. Next, you selected range C2:C13, which contained numbers you wanted to format.

      8. Next, you formatted the selected cells with the thousands comma separator and no decimal places.

      9. Next, you selected range A1:C1 where the column labels were.

      10. Next, you formatted the selected range in order to Bold the font of those label cells.

      11. Finally, you turned off the Macro Recorder, which produced the End Sub line. All macros end with the End Sub statement.

      That's quite a few “Nexts” in the explanation for what is going on! Fortunately, you can edit a macro by typing your own descriptive comments, and you can consolidate a lot of the code so it runs faster and looks cleaner.

      Editing a Macro with Comments and Improvements to the Code

      As good as the Macro Recorder is at teaching VBA code, it is woefully lacking in the efficiency department with the volume of code it produces. To be fair, the Macro Recorder was never meant to be a lean, mean coding machine. Its primary function, which it performs flawlessly, is to produce VBA code that represents your every on-screen action.

      It should be said that there is no law in the universe dictating that you must modify your every recorded macro. Sometimes, for simple macros that do the job, leaving them in their original recorded state is fine – if they work the way you want them to, you've won that round.

      However, for the majority of VBA code that gets produced by the Macro Recorder, the superfluous and inefficient nature of its excessive code will be impossible to ignore. Besides, when you send your VBA workbook masterpieces to other users, you'll want your code to look and act beyond the beginner stage of recorded code.

      NOTE You will find that editing a macro in the Code window is very similar to editing a Word document. Of course, rules exist for proper syntax of VBA code lines, but the principles of typing text, selecting words and deleting them with the Delete key, pressing Enter to go to the next line down – all these word-processor kinds of behaviors with which you are familiar – will help make the macro edit process an intuitive one.

      A rule of thumb in VBA development is, don't select or activate objects unless you need to. The methods of Select and Activate are among the biggest culprits of slow, meandering macro execution. For example, the first two lines of code in the recorded macro are:

      Columns("A: A").Select

      Selection.Insert Shift:=xlToRight

      Those two lines can and should be consolidated into one line, bypassing the Selection activity:

      Columns("A").Insert Shift:=xlToRight

      Same with the next two statements:

      Columns("C: C").Select

      Selection.Cut Destination:=Columns("A: A")

      which can be expressed more succinctly as:

      Columns("C").Cut Destination:=Columns("A")

      You can see where I am going with this. In VBA, you can act directly upon most objects, most of the time, without needing to select them. When you deleted column C, you never needed to touch it in order for VBA to do the work for you, because the following statement:

      Columns("C: C").Select

      Selection.Delete Shift:=xlToLeft

      can become this:

      Columns("C").Delete Shift:=xlToLeft

      Figure 4.4 shows how the original 13 lines of code in the mySort macro have been reduced to a much more readable and highly efficient six lines. Also notice how comments can be added for the purpose of enhancing the organized look of the macro. Your comments will help you, and anyone reading the macro, to understand what the code lines are doing, and why they are doing it.

      NOTE You've now seen plenty of comments in the example macros, and how useful comments can be in your VBA code. To enter a comment line of text, simply type the apostrophe character, and everything you type after that, on that same line, will be regarded as a comment and not executed as VBA code. Usually,