Intro to the Excel VBA Editor

This post is not about learning to code, it’s about where to find your way around the basics of the Excel VBA developer interface. If you’re just getting started with VBA programming, this should help give you an idea of where to find the tools you need.

The topics we are going to cover today are:

• How to enable the Developer tab in Excel 2007 and up
• How to access VBA code and where to store your code
• How to access the VBA macro recorder and why it’s useful
• How to insert buttons in your spreadsheet and assign code to them
• How to use the Immediate window
• The (common) Run, Debug, & Tools menu options

After reading this, you’ll be ready to begin focusing on learning to code!

How to enable the Developer tab in Excel 2007 and up

1. The first thing you need to do to access the VBA editor in Excel is to enable / display the Developer tab. To do this, click the Office circle or the File tab and click on the Options button.
2. Click on the Customize Ribbon option on the left hand side of the window.
3. Ensure that the check-box is selected for the Developer option on the right hand side.



4. Click the OK button.
5. You should now see the Developer tab appear on the right hand side of the Ribbon bar.

How to access VBA code and where to store your code

1. Once you have access to the Developer tab, select it and then click the Visual Basic button on the right hand side in the Code section.



2. This will open up the VBA editor window.
3. On the left hand side, you will see a basic Project tree structure that shows the different worksheets in your workbook, as well as any code modules, or other Excel objects. On the right hand side, you will see the basic (probably empty) code window where you can write VBA code.
4. Typically, I like to create one or more modules for storing my code. I will usually have one module for my main code routine, one for any functions I may call, and one or more for large subroutines. How you structure your code is up to you. You can also store your code in the specific worksheet that you want to call it from.

How to access the VBA macro recorder and why it’s useful

1. On the Developer tab, there is a VBA macro recorder.



2. Clicking on it, Excel will prompt you for what to call the macro and then will begin to record (in VBA code) all the clicks and keystrokes that you make as you manually update your worksheet.



3. This can be extremely helpful to help you quickly prototype basic code, or to figure out what the options are for using VBA to manipulate your worksheet. For example, you could manually apply a filter to a Pivot table, then see what the macro recorded to figure out what the code is for doing this programmatically.
4. The macro does have limitations – for example, it won’t construct any code to iterate through a job. It will construct code to do the action over and over (i.e. copy the code multiple times, potentially making for a HUGE amount of code, but you will need to learn how to build a If, For, or When loop to iterate through the action multiple times.
5. When you click the Stop Recording button, it will save the code to a new code module in the VBA code editor.

How to insert buttons in your spreadsheet and assign code to them

1. You can add a button to your spreadsheet that when clicked, will run specified VBA code.
2. To do so, click the Insert button in the Controls section. Click on the square box icon in the Form Controls section. Then click and “draw” a button where ever you want it on your spreadsheet.



3. As soon as you finish drawing the button, Excel should prompt you to assign a macro to the button. All subroutines that you have created in the VBA editor should appear in a list, available for your selection.



4. You can right-click on the button and choose to edit it, this will allow you to edit the text on the button as well as move it.

How to use the Immediate window

1. Going back to the VBA editor window, sometimes when you run code, you just want to see the output right away and not have it affect anything in your Excel spreadsheet. This is especially useful for debugging code.
2. In the example below, a subroutine called showcase defines a variable num as an integer value and then assigns the value of 2 + 2 to the variable num. Then, using the Debug.Print function, the value of num is displayed in what is called the Immediate window (i.e. the value of num is 4).



3. If the Immediate window is not visible, you can quickly display it using the shortcut key

The (common) Run, Debug, & Tools menu options

The following is a synopsis of the Run, Debug, & Tools menu options:

The Run Menu

1. The run menu gives you access to the play, pause, stop functionality of running your VBA code.
2. The most useful thing to note here is if you click on a subroutine or function with your mouse, you can then use the F5 key to run that specific subroutine.
3. Also note, if you run code and it breaks, prompting you to enter debug mode, the way to exit debugging is to use the Reset button

The Debug Menu

1. When you finish writing your code, click the Compile VBAProject button. Compiling converts the code written in the editor into code that can be read by the Windows run-time environment. If you don’t compile your code, than it is automatically done when the code is run. If the code won’t compile then it needs to be debugged and won’t run until it is fixed.
2. The other useful thing to note here is the Step Into (F8) option. This allows you to step, one line at a time, through your code.

The Tools Menu

1. On occasion, some of your code may require access to other VBA functions that are not typically in Excel. For example, you run VBA code in Excel that also programmatically manipulates something in Outlook or Word. You will need to add a reference to the Outlook or Word functions by using the References option available here.

Hope this helps!
Questions? Comments? As always, let me know in the comments below.