Lesson 6: Creating Macros in the Visual Basic Editor for Excel

Let's now create a brand new macro. Open Excel, open the workbook "VBATest1.xls" that you have created in the previous chapter. Add a sheet to your workbook and rename it "Test2" (right click on the tab, chose "Rename" and type the name in).  In cell A1 to A10 enter 10 first names and in cells B1 to B10 enter 10 last names.

Now let's create a new module. Go to the VBE and right click in the VBA project Window within the project "VBATest1" and select "Insert/Module". You now have a new module. In the properties window double click on the "(Name)" property box and enter "modTest". We now have a new module with a name.

In the VBAProject window double click on the sheet named "Test2". In the properties window double click on the "(Name)" property box and enter "shTest2". You now have a sheet with a caption reading "Test2" and with the name "shTest2".

In the VBAProject window double click on "modTest2"" and let's go to the code window to develop a macro.

Write "Sub proTest2 ()" and click "Enter". VBE adds a line "End Sub". We will write our code between these two lines so make some room for it by setting the cursor at the end of "End Sub" and clicking "Enter" a few times.

Just below "Sub proTest2()" write your name preceded by an apostrophe (') ' Peter Clark and click "Enter". Notice that the font in the line you have just written is green. Because of the apostrophe VBA will consider the text as a remark (REM) and will not bother with it. You can add any number of REM lines anywhere to make your code understandable. I usually always start my procedures with three Rem lines (my name, my phone number and the address of my website) so that people can get in touch with me if they experience problems with my VBA procedures.

Click "Enter" again to insert an empty line. Do not hesitate to insert empty lines anywhere to make your code more easily readable.

Here are the 6 lines of code that you will be writing (or copy/paste them from this webpage):

    shTest2.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

The fist 2 lines tell VBA where to start the procedure. If you don't mention it VBA will start the procedure from whatever cell is selected in your workbook.

Line 5 to 7 is the actual task that you want VBA to perform. You are saying: do something until the value of the cells 2 columns left of the selected cell in empty (double double quotes). When  a cell is selected after the line where is the last first name the task will end. If there is a first name you do else you loop (last line of the statement).

What is the task: make the value of the selected cell equal to the value of the cell 2 columns left and (&) a space (space between two double quotes) (" ") and the value of the cell 1 column left of the selected cell.

Then move down one cell.

When the task has been accomplished go to cell A1.

Now go back to Excel go to "Tools/Macro/Macros" select the macro "proTest2" and click "Run". Erase column C and do it again. Add first names and last names to your list and do it again.

Congratulations you have created your first VBA procedure.

We will test it step by step in the next lesson.