VBA Chapter 4 of 24: Developing  Macros in the Visual Basic Editor for Excel

Note: The images and the text below refer to the workbook "vba-tutorial-editor.xls"

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

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.

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 between Sub proTest () and End Sub (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

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.

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 names from  column "A" and the last names 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"

Discover Even More in 50 Excel spreadsheets