Once you start out working with Excel VBA you'll need to use the VBA editor screen. This article describes how to open the editor screen then summarises what the key parts are for, and describes how to create a simple example of VBA code. The VBA editor screen is very similar in Word, PowerPoint, Outlook and Excel, but this article will focus on Excel. Have a look at the VBA courses London we run regularly.

How to open the VBA Editor

With a new file open in Excel, you can access the VBA editor in different ways. The quickest way is to press the ALT+F11 keys and you're taken straight there. Or you can access VBA from within Excel. In Excel 97-2003 choose Tools, Macros, Visual Basic Editor.

In Excel 2007/2010 choose Developer tab, Visual Basic. In Excel 2007/2010 you may need to enable the Developer tab first. Or if you have any macros available to the current workbook, either in the current workbook or in your PERSONAL workbook, you can choose the Step Into option.

In Excel 97-2003 choose Tools, Macros, Macros. In Excel 2007/2010 choose Developer tab, Macros. In the Macros panel all the available macros will be listed. Click once on any macro to select it and then choose Step Into. The VBA editor screen will launch with the macro code showing in the right hand panel. This panel can then be closed if you want to carry out some other tasks in the VBA editor.

The Key Parts of the VBA Editor

The key parts of the VBA editor screen are the Project Explorer panel, the Properties panel which both show on the left, and the code panel on the right. If any of these panels are not showing, they can be turned on individually by choosing the View command in the upper menu bar and selecting the appropriate option. However you can only close a panel by clicking the cross icon on the top right of each panel.

The Project Explorer shows at the top left of the VBA editor. This is visually similar to Windows Explorer and shows all the currently open Excel files and the hidden Personal workbook and any add-ins which contain VBA code such as Solver. Each item in the Explorer is expandable to show current objects in the file. Objects include the current workbook, all the current worksheets, and any modules containing code created by the macro recorder or hand written. If you've created any user forms you'll see these as well under the Forms object.

Under the Project Explorer is the Properties Window. This shows properties for items in Project Explorer containing VBA code, or components containing VBA code such as a user form. To the right of the Project Explorer and Properties Window is the code area. If you've opened the VBA editor directly rather than choosing to edit a macro, the code area is empty.

To start creating VBA code you need to select the current file in Explorer, then choose Insert, Module to create a new module. So when you create VBA code, you need to create a new module or add code to an existing one. Alternatively if you use the Macro recorder to create the VBA code, Excel creates a new module for you. So user created VBA code builds up in one or more modules within the file and then can be viewed in the Project Explorer.

To create a module manually in the VBA editor, first select the current file in Project Explorer, then choose Insert, Module. The module opens in the right hand side of the editor and you'll see it listed in Project Explorer as Module1 under the category Modules. Next we'll create some example VBA code in the new module.

Creating example VBA Code

We're going to create some VBA code to put the word "Training" into cell A1 of the current worksheet. We'll call this piece of code "Test1". In VBA, pieces of VBA code are called subroutines and when we start to write VBA code Excel will help us by automatically adding start subroutine and finish subroutine lines to the code. So ensure module1 is created and open in the right hand side of the editor. Then type "sub" without the brackets followed by Test1 and press Enter key. Excel recognizes "sub" and amends it to be Sub Test1 () and creates two more code lines. One is an empty line for your code and the other is the closing code line "End Sub". All VBA code starts and ends in this way.

In the second empty line type range("A1").value="Training" and press Enter key. The quote marks are required. So the complete code looks like this

Sub test1()
Range("A1").Value = "Training"
End Sub

Save the file. Then close the VBA editor by clicking the very top right hand cross in the Window, and you're returned to the regular Excel view. VBA code created in this way is identified as a macro, and it can be run from the regular Excel view. In Excel 97-2003 choose Tools, Macros, Macros or in Excel 2007/2010 choose Developer tab, Macros to see all the available macros. In the Macros panel select Test1 and click Run. The VBA code will run and place Training into cell A1.

Interested in learning lots more about VBA programming? There are a whole variety of training courses available. This can be a really effective way to really boost your VBA skills.