VBA Lesson 1-1: The Visual Basic Editor in Excel (VBE)
Note: For a better comprehension of the lessons in section 1 on the Visual Basic Editor and the Macro Recorder www.excel-vba.com has created a workbook (vba-tutorial-editor.xls) one of the 25 that are included with the VBA for Excel Desktop Tutorial.
When you want somebody to do some work for you you open your Email program and you send him a message in a language that he understands (English, Spanish, French...). When you want Excel to do some work for you you open the Visual Basic Editor and you write the instructions in a language that Excel understands VBA ( Visual Basic for Application).
You will develop, test and modify VBA procedures (macros) in the Excel Visual Basic Editor (VBE). It is a very user friendly development environment. The VBA procedures developed in the Excel Visual Basic Editor become part of the workbook in which they are developed and when the workbook is saved the VBA components (macros, modules, userforms. etc.) are saved at the same time. When you send the workbook to the "Recycling bin" the VBA procedures are gone.
Print this page and Open Excel
In Excel open the workbook that you have just downloaded. You have just opened Excel and at the same time you have opened the Visual Basic Editor (VBE). It is not yet visible though. The VBE is integrated into Excel and you can open it from the Excel menu bar "Tools/Macro/Visual Basic Editor" . From the VBE you can go back to Excel by clicking on the Excel button at the top/left of its screen . So if the VBA toolbar is visible in Excel (View/Toolbar/VBA) you can navigate from VBE to Excel using the two buttons . You can also use "ALT/F11" to navigate from Excel to the VBA and back.
The Excel Visual Basic Editor
When you work with the VBE there always should be 3 windows that are opened in it. The Project window (1), the Code window (2) and the Properties window (3). You probably don't see the 3 windows on your screen right now. We will fix that below.
In the menu bar of the VBE choose "View" and select "Project Explorer" and the Project window (1) opens. Then go back to the menu bar and select "Properties Window" and the Properties widow (3) opens below the Project window. You don't call the Code window (3) from the menu bar. It opens when you double click on any element of a project in the Project window. Double click on "Sheet1" in the Project window.
You will discover more about each of these windows in lessons 1, 2 and 3. You will learn how to create, modify and test your VBA procedures. You will even se how you can test your procedures step by step with the F8 key while seeing it live at work in your Excel workbook.
You can resize the 3 windows by placing the cursor over the borders (*) and dragging them right, left, up or down.
The VBE Menu Bar
Here is the menu bar of the Visual Basic Editor. There are just a few functions that you will use and here they are.
As seen above at the very beginning of you programming career you will go to the "View" item to activate the Project Explorer and the Properties window. Activate also 2 toolbars: the Standard Toolbar and the Edit Toolbar by going to "View/ Toolbars" . You should never have to go back there ever.
Once in a while you will be using "Debug/Clear All Breakpoints" to remove the breakpoints that you have set. You will also go to "Tools/VBAProject Properties/Protection" to protect you VBA code with a password.
You won't really be using anything else from the menu bar.
The VBE Toolbars
Here are the two toolbars that should always be visible at the top of the VBE. The "Standard" toolbar (top) and the "Edit" toolbar (bottom).
From the Standard toolbar you will be using the Undo/Redo arrows like you do in Excel to correct an error that you have just made while writing in the code window.
You will also use the "Run" arrow to start a procedure. You first click within the procedure in the code window and you click on the blue arrow of the toolbar. You will also use the "Reset" icon to terminate a step by step execution.
When you start working with userforms you will use the "Toolbox" Icon to call and hide the "Toolbox"
You will use the object browser to find objects, methods and properties as explained below.
Finally if you click anywhere in the code window, this tool will tell you on what line you are. So if you are talking with somebody about your code on the phone you can tell your correspondent to select the same line.
From the Edit toolbar you will be using two tools. First the tool that allows you to indent lines or segments of your code to make it clearer to read . You will also use the comments tools that automatically adds apostrophes to the line or the lines that you have selected. Adding an apostrophe to a line transforms it into a comment that is not executed. The more comment lines the easier you or the ones that will follow you will be able to read and understand you code.
The VBE Object Browser
So when you click on the icon for the object browser the following window appears covering the whole code window. When the window is open enter a word in the text box beside the binoculars and the search results appear in the "Search Results" window. Select one of the line in this window and click on the F1 key. The help window opens and you get all kinds of information on the topic that you have chosen. To close the object browser window just click on the X (top/right) and the code you were working on comes back.
A final note: Unfortunately the wheel on your mouse doesn't work in the VBE
Developed and Presented by PLI Consultant Inc