Discover
hundreds of free tips |
VBA Chapter 4 of 25: The Visual Basic Editor in ExcelYou 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. For a better comprehension of this chapter open "macros-beginners-exercises.xls" AND open a new empty workbook. Print this page and Open the VBE.
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 The Excel Visual Basic Editor When you work with the VBE there always should be 3 windows that are open in it. The Project window (1), the Property window (2) and the Code window (3).
On the menu bar of the VBE choose "View" and select "Project Explorer" and the Project window opens. Then go back to the menu bar and select "Properties Window" and the Properties widow opens below the Project window. You don't call the Code window from the menu bar. It opens when you double click on any element of a project in the Project window. It shows the procedure that have been saved within the selected object (sheets, modules, ThisWorkbook, userForms). 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. At the very beginning of you programming career you will go to the "View" item to select the Project Explorer, the Properties window, the Edit Toolbar and the Standard Toolbar. 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 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 You
will use the object browser Finally
if you click anywhere in the code, this tool From
the Edit toolbar you will be using two tools. First the tool that
allows you to outlines lines or segments of your code to make it
clearer to read The VBE Object Browser So when you
click on the icon for the object browser In the sub-chapters (4A, 4B and 4C) you will discover the Project Window, the Properties Window and the Code Window. In the next steps we will see how to create, test and modify VBA procedures in the Visual Basic Editor. You will specially enjoy the approach where you test your procedure step by step while seeing it at work in your Excel spreadsheet on the same screen. |
More on VBA and Excel Here are 40 Excel downloadable spreadsheets Short
and well explained examples |