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.

Help! Why won’t my Excel formula calculate?!

Note: These instructions are based off Excel 2010 but are applicable for Excel 2007 through Excel 2013 (Office 365).

Has someone sent you a spreadsheet or perhaps you’re working on one and all of a sudden your formulas look like formulas and won’t calculate? Here are the top solutions I have come across for fixing this common error.

1. Cell text formatting is set to “Text” – If the formatting of the cell has been set to Text instead of General or some other format, the cell will not calculate because it assumes that anything in the cell is text and not a formula. Change the formatting of the cell by pulling down on the drop down in the Number section on the Home tab of the menu ribbon





2. Show Formulas (Ctrl + ~) has been selected / pressed – If the Show Formulas option has been selected, calculations will show their full formula and not show the calculated results. To toggle back and forth, you can either use the hotkey combo Ctrl + ~ or select/deselect the Show Formulas option in the Formula Auditing section (I highly recommend learning to use all tools in this section when troubleshooting Excel problems!) on the Formulas tab of the menu ribbon.



3. An apostrophe (‘) has been placed before the equals (=) sign – Typically someone will have done this on purpose, however placing an apostrophe before a formula will make the cell mimic the first solution listed above where the cell formatting is set to Text. Remove the apostrophe to force the cell to calculate.



4. Automatic calculation of the worksheet / cell has been turned off – If you’ve been sent an Excel sheet that’s very large, sometimes people will turn off automatic calculations so that the spreadsheet doesn’t break your computer upon opening it. To turn on or off automatic calculations within your worksheet, click on the pull down menu of the Calculation Options in the Calculation section of the Formulas tab on the ribbon bar. Alternatively, this setting can be reached by going to the File tab, selecting Options, going to the Formulas tab and then setting the Calculations options there.

Hope this helps when you get stuck! I would appreciate hearing if anyone has come across other solutions to this common complaint?

~Yosef

How to Hide Pivot Chart Filters

(Directions below are for Excel 2010)

I recently built a simple dashboard using pivot tables with corresponding pivot charts. I was asked by my client if I could remove the filters from the pivot charts so that they would have a cleaner look for presenting. I had never worried about this before and didn’t know how off the top of my head so I did a couple of quick Google searches & couldn’t find any references on how to hide the filters! I was very surprised that I couldn’t find anyone else asking the question so I thought I would share how to easily do this. Luckily it’s pretty simple – I just didn’t realize these options existed!

Here’s a Pivot chart with the usual filters visible:

To remove these filters, click the “Field Buttons” (or drop down for more control) button on the Analyze tab of the PivotChart Tools section of the menu ribbon (only visible if the chart is selected):

You can then choose which (if any) filters to display so that your chart looks nice & clean:

Hope this helps!

~Yosef B.

Excel: Importing External Data from Website

In Excel, there are many available options for importing external data, including:

Simple copying and pasting of data from another application
• From Microsoft Access
• From a webpage
• From a text file (see post here)
• From a Microsoft SQL Server
• From a Microsoft Analysis Services
• From an XML file
• From a OLEDB or ODBC (database) connections



Below, we will focus on how to successfully import data from a website – I will have other posts on importing from other sources.

Importing External Data from a Website

There are two basic ways to import external data from a website

1. Copy and Paste
1. In your browser, highlight the information you wish to import
2. Copy
3. Paste into Excel

For example, suppose I wanted to import Eli Manning’s statistics off of Yahoo! Sports – highlighted in red below (http://sports.yahoo.com/nfl/players/6760):



1. Simply highlight the entire table of statistics with your mouse



2. Copy



3. Paste into Excel



2. Link to web

Alternatively, you can create a link in your worksheet to a website which will allow you to refresh the data without having to reopen the website.

Instead of having to go out and grab new statistics off of Yahoo’s website every time Eli Manning plays in a game, you can set up a linked table in Excel that will automatically go out and pull the latest data off of Yahoo’s website.

1. Select the “From Web” option in the “Get External Data” section on the “Data” tab along the Ribbon:



2. In the window that opens, type in the address for the webpage that you wish to pull the data off of in the “Address“ box.
3. Click the “Go” button to load the webpage.



4. Scroll down the webpage until you reach the table of data you wish to import.
5. On the left hand side of the table, you will see a little yellow box with an arrow. If you hover your mouse over the arrow, you will see a prompt that reads: “Click to select this table” (the yellow box will turn green) with a blue border highlighting the data which will be imported into Excel.
6. Click the green box with the arrow in it.



7. At the bottom of the window, click the “Import” button.
8. Excel will ask you where you want to put the data. Select the cell which will become the top right cell of your data set and then click “OK”.



Excel will import the data and it will look just like you typed in the data yourself:



However, this data is linked to the website and you can refresh it at any time by clicking on the “Refresh All” button in the “Connections” section on the “Data” tab along the Ribbon.



Need more information? Want help importing from other sources? Let me know in the comments below & I will get back to you ASAP! 🙂

Excel: Select only numbers trick

Here’s a cool trick I came across recently.

Let’s say you have a worksheet setup that has a financial calculation model. It has input cells for your initial values and then a whole bunch of calculations. At some point, you decide to clear all your initial values in your input cells in order to input new ones.

You could scroll around looking for non-formula cells or you can take advantage of an Excel feature which lets you select cells in a “special” way. To do so:

1. Select Edit, Go To (or press Ctrl-G or F5) to bring up the Go To dialog box
2. Click the “Special” button
3. Choose the Constants option, then clear all of the check boxes except Numbers (if some of the input cells in the spreadsheet accept text, leave the check mark next to Text)
4. Click OK, and Excel will select all of the non-formula cells which contain a value
5. Press the Delete key, and all numbers will be deleted – but your formulas will remain intact

Excel: Importing External Data from Text File

Importing External Data from a Text File

To import external data from a text file use the Text Import Wizard. There are two ways to get to the Text Import Wizard:

1. If you have copied text, if you pull down on the “Paste” drop down menu in the “Clipboard” section on the “Home” tab of the Ribbon, there is an option for “Use Text Import Wizard…”



2. Using the “From Text” option in the “Get External Data” section on the “Data” tab of the Ribbon.

Once the Text Import Wizard opens, the options you select depend on the data you are trying to import.

Most commonly, your data will be delimited with some sort of market. This is typically either a blank space between each data element or a comma (,) character. If so, select the “Delimited” option and click “Next”.

If your data is broken up by a fixed number of characters, select the “Fixed width” option, and click “Next”.

1. Select the type of delimiter that your data uses to split up data elements (in example below, “Comma” is selected). Note that if you have multiple delimiters (e.g. your text is split up with a comma and then a space or a double comma) you can select the “Treat consecutive delimiters as one” option.
2. Click “Next.”



3. The next screen lets you assign each column a data format (e.g. date/time, number, currency, text, etc.). I generally skip this step and format the entire table once I get it into Excel by clicking the “Finish” button on the screen before.
4. Click “Finish”

Need more information? Want help importing from other sources? Let me know in the comments below & I will get back to you ASAP! 🙂

The Many Ways to Refresh an Excel Pivot Table

Ed. Note: Click on any picture to view full size

There are relatively few things as aggravating as spending hours trying to track down why the calculations in your PivotTable do not match your data than to finally realize that you forgot to refresh your PivotTable…

Here are a few tips & tricks for automatically refreshing your PivotTables.

Automatically Refresh PivotTables Upon Opening Excel Workbook

This first trick shows you how to set your PivotTable to refresh all calculations automatically as soon as you open your workbook. The obvious drawback is, if you change any of your data after you open the workbook, you will still have to refresh the PivotTables to reflect the new data.

To enable this feature, right-click anywhere on your PivotTable & select “PivotTable Options”

Next, select the “Data” tab and then select the “Refresh data when opening the file” option. Click “OK” to save your selection.

Write a Macro

This second option is very easy to implement and will refresh your PivotTable every time you select the worksheet tab that contains the PivotTable. Unfortunately, I have not figured out how to make your PivotTable refresh every time you change data in your data source (if anyone has a better macro, please share in the comments below!).

The drawback to this trick is, you have to leave the PivotTable tab and then return to it to force a refresh. Still – if you have your data on a different tab & you’re going back and forth anyways to check your calculations than this should help prevent our original aggravating scenario…

Right click on the sheet name where you placed the PivotTable and select “View code”

Copy the following code into the resultant code window (Note: if you copy & paste, you may have an error because the double-quote symbol does not always paste correctly into Excel – if you have a problem, try replacing all the double-quotes with new ones in the code editor.)


Private Sub Worksheet_Activate()
    Sheets(“PivotTable”).PivotTables(“PivotTable1”).RefreshTable
End Sub


You will need to change this code to match your specific information as follows:

• “PivotTable” is the name of the worksheet tab where your PivotTable resides
• “PivotTable1” is the name of your PivotTable

Click the “Save” button. (Highlighted red in the picture below)

To find the name of your PivotTable, simply click anywhere on your PivotTable and then look at the name listed in the PivotTable section on the “Options” Tab on the special “PivotTable Tools” area of the ribbon. (In my example picture below, the PivotTable name is PivotTable4).

Questions or related tips/tricks? Let me know below!