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.
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:
- Click File
- Click Options (The Excel Options dialog box will open in the General tab, see image below)
- Click on Customize Ribbon
- Under Main Tabs (listbox to the right), check the checkbox by Developer.
- Press OK
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.
This will open the VBA Editor, as shown here:
Upon opening the VBA Editor, press, the F4 key. This will open the Properties dialog box to the lower left.
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.
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.
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
Directly below the windows toolbar is the VBA toolbar. This toolbar provides general saving and script execution buttons.
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.
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.
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
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.
Double click on “Module1”. This will activate Module1 in the Code Window.
Enter the following text into the Code Window.
MsgBox "Hello World!"
Place the text cursor before the first “Sub” and press the green play button in the VBA Toolbar.
The VBA Editor will disappear and the following will be displayed on the spreadsheet.
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.