Program by Levels

Tutorial on VBA for Excel (Macros)

bringing you the simplest and most efficient ways
to do things in VBA for Excel

VBA Lesson 5 of 33: Developing  Macros in the Visual Basic Editor for Excel
(Levels: Beginners)

Note: For a better comprehension of the lessons in section 1 on the Visual Basic Editor and the Macro Recorder www.excel-vba.com has created a workbook (vba-tutorial-editor.xls) one of the 25 that are included with the VBA for Excel Desktop Tutorial.

Now that you have discovered the Visual Basic Editor and its 3 windows you can create your VBA procedures (macros).

Open Excel and a new workbook.

In cells  "A1" to "A5" of the sheet "Sheet1" enter five first names. In cells "B1" to "B5" enter 5 last names. You can enter 5 names or even more the procedure will stop when the cell in column "A" is empty.

Go to the Visual Basic Editor.

First let's add a new module. Go to the VBE and right click in the Project window . 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 new module "modTest" " and let's go to the code window to develop a macro.

Write  "Sub proTest ()" 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 beginning of "End Sub" and clicking "Enter" a few times.

The open and close parentheses at the end of the first line are used to carry a variable and its content from one procedure to the other. At the advanced level you will us them.

Just below "Sub proTest()" 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 easier to understand. It is recommended to always start procedures with three Rem lines (your name, your phone number and your Email address or the address of your website) so that people can get in touch with you if they experience problems with your VBA procedure.

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):

        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

VBA is very forgiving and it tells you right away if there is something wrong with the code. When you are writing code error message boxes can appear when you click "Enter" at the end of the line. Among the most frequent ones are the following:

If you write Range("C1).Select you will get the following error message:

telling you that you have forgotten a quotation mark or a parenthesis.

If you write Range("C1")Select you will get the following error message:

telling you that you have forgotten the point or dot.

The first 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.

The "Range" is the cell or the group of cells that you want to select. With Range("C1:C6").Select cells C1 to C6 would be selected

Line 3  to 6  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 quotation marks). You are saying: do and redo until there is no first name on the row that is selected.

What is the task?

1- To assemble in column "C" the first name from  column "A" and the last name from  column "B" with a space in between.

2- 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 "proTest" 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.

You will find this same procedure in the spreadsheet "vba-tutorial-editor.xls". A command button on the sheet allows you to start it by clicking on it.

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple