Editing A Macro Using The VBA Editor

For this part of the tutorial you will need the workbook you created in part 1 (Click here to go to part 1)

Once you have created your macro you may never need to make any changes or enhancements, but that’s a pretty rare case. There are only really two options for changing your macro, trashing the old one and re-recording, or tweaking by using the VBA editor. In this tutorial we will explain how to make simple modifications to your macro using the VBA Editor.

First, load up the workbook you created in the first part of this series.

Before we edit the macros we need to make some changes to the workbook data, so once the workbook is loaded make the following changes.

  • Insert a row before row 1
  • Head Columns A, B, C, with First Name, Last Name, and Age.
  • Add another ten names and ages to the list

 

Now from the menu bar select Tools –> Macro –> Visual Basic Editor

The VBA Editor will open and you will see a section on the left that will look something like the one below.

Click the ‘+’ symbol next to the modules folder to expand it and you will see a module named ‘Module1’

Double clicking this module will reveal the code for the macros in the VBA editor window on the right side of the window which will look similar to the code below.

Sub SortByChristianName()

‘ SortByChristianName Macro
‘ Macro recorded 31-03-2005 by neil_warwick, VBA Editor Tutorial

Range(“A1:C10”).Select
Selection.Sort Key1:=Range(“A1”), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Sub SortBySurname()

‘ SortBySurname Macro
‘ Macro recorded 31-03-2005 by neil_warwick
Range(“A1:C10”).Select
Selection.Sort Key1:=Range(“B1”), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Sub SortByAge() ‘
‘ SortByAge Macro
‘ Macro recorded 31-03-2005 by neil_warwick

Range(“A1:C10”).Select
Selection.Sort Key1:=Range(“C1”), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

This is the code for each of the three macros you recorded, and each procedure is separated by a line.

As all three macros are essentially the same, other than the sort column we will explain one macro in it’s entirety and you should be able to read the comments across to the other macros shown in the VBA Editor.

Now that we have made some changes to our worksheet data the macros that we previously set will no longer work (Try them out to see what the result is, but close and re-open your workbook without saving the changes afterwards otherwise this part will not work correctly).

We need to make two basic changes to our macro, change the start of the sort criteria to allow for the header row, and extend the range of the sort to allow for the extra ten names and ages.

Fortunately VBA code is written in an ‘english like’ fashion, so it will be fairly easy to pinpoint the areas for change.

In the Macro for ‘Sort By Christian Name’, find the line containing the command: Range(“A1:C10”).Select

This basically tells the VBA execution program to select a range of cells whose top left corner is cell A1, and whose bottom right corner is C10. We used this range as the basis for our sort in our original macro. How we have extended our list of names we need to make changes to this line. Our new range starts at Cell A2, and now finishes at cell C21 so we need to edit the line to read: Range(“A2:C21”).Select

The VBA editor works in exactly the same fashion as a standard word processor, so you only need to click on the area you want to change and delete or overtype.

All the other lines in this sub routine can be left as they are, all that needs to be done now is to change the Range…Select line in the other two macros, and you have just finished your first piece of VBA editing. Use the Macros menu, as we did in part 1 to execute your macros and try them out.

When all the lines have been edited, save your workbook as you will need to use this in part 3.

 

What does the code mean?

Below we have taken a screenshot of the code in the VBA editor and explained the four main areas of the code.

 

  1. Procedure Name – This is the name you gave the Macro when you recorded it in part 1 of the tutorial.
  2. Comment Lines – These are ignored by the program, they are there for information only, the Macro recorder puts them in automatically, but you can add your own, and line that is a comment line must start with an apostrophe ( ‘ ) and will be coloured green by the VBA editor.
  3. Program Lines – These are the actual program lines that do the work, and because VBA is written in an English like language, are usually fairly easy to understand. However there is one important point to note here, there are only actually two line of code for this macro not four. The second line that calls the sort function is too long to be displayed on screen in one line, so the VBA editor has split it into 3 lines on screen, the underscore ( _ ) symbol is used at the end of a line to denote that the next displayed line is a continuation.
  4. Procedure End – The end of the procedure has been reached, and control should now be returned to the worksheet or macro that called this macro (Because it is possible for one macro to call another enabling complex routines to be constructed!)