Getting Started with the VBA Editor

One of the great things about Excel is the ability to begin writing VBA code with as few as two clicks upon opening a new workbook. This ease of access allows almost anyone to become a programmer. This post will provide a general tour of the VBA editor.

Overview

This is the first post of the Getting Started with VBA Series. The series starts with a general overview, giving beginner spreadsheet developers an “Absolute Beginners Guide” to getting comfortable with coding in the VBA Editor. If you are somewhat familiar with VBA, this post may be too basic for you. You may be more interested in some of our intermediate or advanced VBA exercises.

You do not need to be an experienced programmer to use VBA. In some cases, knowing a little VBA and how to use the editor will allow you to tweak recorded macros, or automate simple tasks. While advanced coding concepts can help build robust business applications, basic coding can add impressive touches to your projects.

How to Activate and Open the Excel Developer Tab

The most basic starting point is how to find VBA Editor. This will not be obvious to first time developers, as by default, Excel does not display the Developer tab in the main ribbon. This will need to be activated – the steps are as follows:

  1. Click File
  2. Click Options (The Excel Options dialog box will open in the General tab, see image below)
  3. Click on Customize Ribbon
  4. Under Main Tabs (listbox to the right), check the checkbox by Developer.
  5. Press OK
Activate the Developer Ribbon.
Excel Options dialog box

Upon pressing OK, the Developer tab will show up on the main ribbon, to the right of View.

To open the Visual Basic Editor, go to (1) Developer > (2) Visual Basic.

Steps to access the VBA editor.
Navigate to the Visual Basic Editor

This will open the VBA Editor, as shown here:

First view of the VBA editor.
The Visual Basic Editor

Upon opening the VBA Editor, press, the F4 key. This will open the Properties dialog box to the lower left.

VBA editor with Properties panel.
The Visual Basic Editor with the Properties panel open.

Understanding the VBA Editor

At this point, the VBA editor contains two toolbars, two panels to the left, and a large blank (gray) area to the right. The gray area is where worksheet objects, modules, and classes will be displayed. Let’s open a worksheet object by (1) double clicking on Sheet1 (Sheet1) in the Project Panel.

Clicking on the Sheet1 object will activate the code window.
Clicking on the Sheet1 object will activate the code window.

Now let’s break down the VBA editor piece by piece. This post will not go over the specifics of each menu or option – many of these will be discussed in future posts.

Windows Toolbar

2016-11-03-6

This is the windows toolbar, or as some call it, the menu bar. This toolbar provides general options for managing the display. Other functions include options for importing, exporting, and inserting various modules or components

VBA Toolbar

2016-11-03-7

Directly below the windows toolbar is the VBA toolbar. This toolbar provides general saving and script execution buttons.

Project Window

Project window in the VBA editor.

In the upper left portion of the main panel is the Project Window. This window displays all the Workbook Objects, Worksheets Objects, Modules, Classes, and User Forms associated with your project. Everything is displayed in a hierarchical tree form, and opening a second workbook will display a separate tree (in the same window) for that workbook.

Properties Window

2016-11-03-9
Properties window in the VBA editor

The Properties Window displays the name and basic elements of the object/module/form selected in the Project Window. The most useful elements of this window are the first field, Name, which allows you to specify a name, and the last field, Visible, which allows you to specify the element’s visibility.

Code Window

Code window in the VBA editor.

The Code Window, also known as the Editor Window, is where all coding will take place. This is effectively a text editor, with some added helpful features to help you code more efficiently.

Let’s Write a Program

Insert a new module.

To wrap up today’s overview, let’s write a simple program to familiarize ourselves with the VBA editor. We’ll get into specifics in a later post, but for now we want to create a new module to store our program. To do this, go to the Windows Toolbar and select (1) Insert > (2) Module. This will make a new tree show up in the Project Window, showing Module > Module 1.

The Module Tree in the VBA editor.

Double click on “Module1”. This will activate Module1 in the Code Window.

Enter the following text into the Code Window.

Place the text cursor before the first “Sub” and press the green play button in the VBA Toolbar.

VBA Editor toolbar.

The VBA Editor will disappear and the following will be displayed on the spreadsheet.

A simple VBA program.

Congratuations! You have just written a program in Visual Basic for Applications.

The next post will go more in depth on how the Visual Basic Editor’s various features and what to expect in certain situations. Hope to see you back.

Leave a Reply