Lesson 5: Developing Macros in Excel

Most macros are developed in the code window of modules. For the purpose of this exercise double click on "Sheet1" in the project window

Enter sub proTest() without using  a capital "S" as the beginning of "sub". After entering the closing parenthesis click on "Enter". You get these two lines of code:

Sub proTest()

End Sub

VBE adds the line "End Sub" and capitalizes the "S" of "Sub" . The VBE capitalizes letters appropriately when the word is spelled correctly. This is one interesting feature that you should always use when writing macros. Make it tour habit never to use capital letters when writing code. In this way, whenever VBE unexpected fails to capitalize a letter, you will know that something is wrong.

Two exceptions to your otherwise consistent use of lower-case are: (1), when you declare variables (lesson 19); and (2), when you name macros (as you did above). You will see why in later lessons.

You may now write a procedure within the two lines of code above. For example your VBA procedure could look like this. You can copy/paste the macro below from your browser to the VBE Code window, or key it in. Make sure that everything is there including all the quotation marks and periods, parentheses, equal signs, and spaces.

Note: Make sure that you copy/paste this code in a NEW workbook not one created in a previous exercise.

Sub proTest()

        Sheets("Sheet1").Select
        Range("C1").Select

        Do Until Selection.Offset(0, -2).Value = ""
                Selection.Value = Selection.Offset(0, -2).Value & " " & Selection.Offset(0, -1)
                Selection.Offset(1, 0).Select
        Loop

        Range("A1").Select

End Sub

The procedure above will go down column "C" and assemble the first names of column "A" and the last names of column "B" with a space in between. It will perform this task all the way down until there are no more first names in column "A" . It will then place the cursor in cell "A1".

To test this macro (VBA procedure) follow the steps below:

Step 1: Go to Excel (ALT/F11) and enter first names in cell A1 to A5.

Step 2: Enter surnames in cells B1 to B5.

Step 3: Come back to the VBE (ALT/F11) and click within the macro in the code window.

Step 4: From the menu bar select "Run/Run Sub/Userform".

Step 5: Go back to Excel and see the result.

You can erase everything in column C Excel and retry with more names and surnames.

Try it again removing the first name in cell A3. Notice that the macro stops on line 2.