Lesson 4: The Code Window in the Visual Basic Editor of Excel

The Code Window is where 90% of the VBA work is done; writing VBA sentences, testing your VBA procedures (macros) and modifying them when needed.

To illustrate everything that you can do in the Code window we will start by creating a small macro in an empty workbook.

Exercise 6 (Create your first macro and use it)

Step 1: In Excel notice that cells A1, A2 and A3 of "Sheet1" are empty. Go to the Visual Basic Editor.

Step 2: Double click on "Sheet1" in the Project Window. On the right is the Code window of "Sheet1"

For the purpose of this exercise we will develop a small macro within the code window of a sheet. You will later develop the habit of creating modules and organizing your macros within them.

Step 3: Click anywhere in the Code window

Step 4: You can either copy/paste the following macro from your browser to the code window of "Sheet1" or key it in.

If you decide to key it in you will start by entering the first line and then when you press enter the VBE will add the final Line "End Sub". Enter the rest of the code within the two lines. Make sure that everything is there including all the quotation marks, periods, parentheses, equal signs and spaces.

Sub proFirst()
        Range("A1").Value = 34
        Range("A2").Value = 66
        Range("A3").Formula = "=A1+A2"
        Range("A1").Select
End Sub

Step 5: Click on any line of the macro, go to the menu bar at the top of the VBE screen and click "Run" then click "Run Sub/Userform".

Step 6: Go to Excel (ALT/F11) and see what has happened to cells A1, A2 and A3

Congratulations you have run and tested you first macro. Go to Excel and "Sheet1" and see that what the macro was ordering Excel to do has been done. The value of cell "A1" is 34, the value of cell "A2" is 66 and there is a formula in cell A3 that sums cells A1 and A2.

Step 7: Go to Excel and clear the cells A1, A2 and A3 of "Sheet1". On the menu bar go to "Tool" and click on "Macros". In the dialog window select "proFirst" and click on run.

You have run the macro from the menu bar of Excel. In lesson 9 on Events you will discover many other ways to start a macro.

NOTE: You cannot change the font or its color in the code window. You input appears in black, comments appear in green, reserved words in blue and when you make a mistake the font color turns to red.

There are plenty of other operations that you can execute in the code window. For example, you can test a macro line by line (step by step), go back a few lines and make corrections, use breakpoints to test only part of a macro.

In section 2 (VBA lessons 11 to 23) you will learn the VBA vocabulary to write macros.