The Visual Basis Editor is your workspace for creating your VBA code. The editor can be accessed through your Developer Tab or by using the shortcut Alt + F11. The editor will display in a completely separate window than your Office Application and each one of the programs in the Office Suite has its own VBA Editor (so you can have the Excel and PowerPoint VBA Editors open at the same time).
I will not be going into too much detail about all of the Visual Basic Editor's capabilities in this article but I do want to show you enough so that you can understand it's setup and how it functions. Below I have a screenshot of some of the main windows that can be shown within the Visual Basic Editor. Some of these may not be showing on your editor by default and I will explain how to get them showing in the descriptions below. I will be describing Excel's Visual Basic Editor but much of the information can easily be translated over to PowerPoint, Access, or any of the other Office program's Visual Basic Editors.
This window will show you all the files that you have opened. The Project Window uses a tree view where you can drill down into each file that you have open and see the areas in which you can insert VBA code. Notice that in my screenshot above that there are two files that are open in my Excel application: Book1 (a workbook) and VBHTMLMaker (an add-in). In Book1 you can see 3 subfolders:
- Microsoft Objects -This folder houses a code area for your workbook (ThisWorkbook), and your workbooks spreasheets (Sheet1)
- Forms - This folder stores any userforms that you create. I touched a little bit on this in Lesson One so I won't repeat myself here. If you do not see this folder you can add it by right clicking anywhere within the projects folder tree and going to Insert -> Form.
- Modules - A Module folder stores you macro and function code. If you do not see this folder then that means that the project most likely does not have any macro code in it (note: there can still be code stored in the forms folder or in the Objects folder). You can add this folder by right clicking anywhere within the projects folder tree and going to Insert -> Module.
The Properties Window will allow you to modify certain aspects of whatever object, form, or module you have highlighted. Typically the only thing I change using this window is the Name field. This is a good idea because you can give your modules or forms a more meaningful name than the default names that the Visual Basic Editor provides. Custom names in the Name field can only be one word in length.
Code Writing Area
This is where the magic happens! In this area you can actually write and edit your VBA code. Each macro must begin with a Sub statement (which is opened with Sub [insert your macro name] ( ) and closed with End Sub). Notice also that the VBA Editor color-codes some key words in a few different colors. This helps make your code more organized. I have two major tips that I like to share with people when they are first learning to write VBA code:
- Use Indentations - Always try to use indentations (via the Tab key) within your code. There are various methodologies to tabulating code but as long as you are consistent and it makes sense it will help you enormously when you are trying to add to or debug your code. It also helps when someone else is trying to help you with your code.
- Write in Lowercase - If you haven't noticed already, every word in the VBA language has at least one capitalize letter. How is this an advantage? Well, the Visual Basic Editor is not case sensitive and it likes to correct you when it can. This means that is you type in "workbook", the editor will automatically change it to "Workbook". My rule of thumb is to type everything in lowercase and if the VB editor doesn't capitalize at least one letter, I know that I either misspelled that word or that word is not defined. Having the Visual Basic Editor correct every word I type has really made my code less buggy and prevent a lot of frustration over the years.
I like to refer to this area as my piece of scratch paper. The Immediate window lets you do all sorts of tests while writing and running your code. You can use the code Debug.Print to tell VBA to send the information that follows to the Immediate window. This could be the output value of a function, the value of a cell, or what a current application property is set to. When I first began writing VBA code I had no idea the Immediate window ever existed (it's usually hidden by default but you can use the shortcut Ctrl + g to view it), but once learned everything it could do I never stopped incorporating it's functionality into my code writing and testing processes.
The Watch Window is kind of like an X-ray machine. It will show you all the data that is stored inside a variable! Some variables (like the ones you create in your code) will not have very much data stored in them. However, if you were to "watch" a variable that was assigned to a cell, you would see a whole bunch of data (font color, value, height, fill color, etc...). This is mostly usefully when you are trying to debug your code and want to understand what value your variable has at any given point in your code.
In order to watch a variable you need to highlight your variables text and click the Add Watch button (this is the eyeglasses icon located on the debugging toolbar). You should then see your variable appear in the Watch Window. Once you start running through your code and load a value to your variable, you should see an option (plus sign) to drill down or expand out the contents that is now stored in the variable.
Are You Confused Yet?
I know this is a lot to take in but you really should use this as a reference sheet that you can refer back to as you learn. You will not use all the features of the Visual Basic Editor when you are first learning but it is handy to know what they are in case someone who is trying to help you refers to a feature in the VB Editor. For example, I first learned about the Immediate Window when I kept seeing the Debug function used in code that community members on Excel forums would include in their VBA code solutions. I had no clue what Debug did and since my Immediate Window was hidden, it took a lot of research for me to understand how and why the forum members were using that functionality.
I will continue to update this page as I do have more detailed blog posts that I am planning to write about the Visual Basic Editor and I will make sure to link to them when the articles are posted!