Excel VBA: Recording Macros

Platform

Microsoft Excel 2002, Windows XP

Introduction

In the early days of spreadsheets there was no automation. You did what you need to do by hand and if you needed to do it 100 times, you did it 100 times. Soon, the respective nature of some tasks made it clear that we needed a way to do the same thing over and over in an automatic fashion and macros were born. Early macros were what we now refer to as "keyboard macros." They were just a recorded sequences of keystrokes that could be played back repeatedly. Up through Excel version 4, all macros were keyboard macros. Beginning with version 5, Excel did not record keystrokes when it recorded a macro, instead, it recorded the actions that were invoked by the keystrokes. It used the new language, Visual Basic for Applications (VBA) to represent the actions that were recorded. This kind of macro is termed an "application macro." VBA is still used for recording macros in Excel today.

Objective

The objective of this tutorial is to introduce you the process of recording and modifying macros in Excel.

Overview

In this tutorial, we will complete the following steps:

Build a table with stock tickers
Begin recording a macro
Build a web query to request a stock quote from finance.yahoo.com
Record the price of the most recent trade stock trade
Delete the web query
Edit the recorded macro to generalize the procedure

Build a table with stock tickers

To begin, let's set up a table with two columns, one for the stock ticker and one for the price of the most recent trade. We will assume that the table is built beginning in cell A1 of sheet1 as follows:

Begin recording a macro

Before we begin recording the macro, let's be sure that we have selected the cell that we want to have active when the macro begins. In this case, cell A2 should be selected.

Now, we begin recording. To begin recording the macro, select "Tools | Macros | Record New Macro..." from the Excel Menu bar as seen here:

This will display the "Record Macro" dialog box seen here:

We will make two changes in this dialog box. First, we will give the macro a meaningful name. Here I have chosen the name "getPrice." Macro names must be valid VBA identifiers. As such, they cannot contain spaces and must begin with a letter. It is a good practice to avoid any special characters in the name and limit your character choices to letters, numbers and the underscore ("_") character.

The second change we will make is to assign a keyboard short cut. I recommend that you use capital letters for any keyboard shortcuts that you use. If you use lowercase, the shortcut will be to press the control (CTRL) key in conjunction with the selected key. Since Excel implements many such keyboard shortcuts, such as "CTRL+P" to print, you may inadvertently replace the default Excel keyboard shortcuts with your macro keyboard shortcut. By using a capital letter, the shortcut will be the control key pressed in conjunction with the shift key and the selected shortcut key. In this example I have entered a capital "Q" so the keyboard shortcut to invoke this macro will be CTRL+SHIFT+Q.

Build a web query

We are going to need some space to put the results of the web query, so we want to build it on the second worksheet in the workbook (sheet2). For administrative reasons, we would like to also have the symbol of the current stock on sheet2, so let's begin by copying it. Remember that every action you execute now will be recorded as a part of the macro. With cell A2 already selected on sheet1 do the following steps:

Choose "Edit | Copy" from the menu bar (or press the keyboard shortcut, CTRL+C).
Click on the worksheet tab named "Sheet2"
Make sure that cell A1 is selected
Choose "Edit | Paste" from the menu bar (or press the keyboard shortcut, CTRL+V).

Your workbook should now appear as follows:

Now we are ready to proceed with the web query. To execute a web query we need the URL for the page that holds the data needed. If we go to http://finance.yahoo.com and get a stock quote for Microsoft (symbol: MSFT) we see the following page:

This page shows us that at the time the page was generated, the most recent trade for the stock was a $25.70. However, the information we really need is in the address bar of the browser; it's the URL of the page that has the information about the most recent trade. For this example the URL is http://finance.yahoo.com/q?s=msft (notice that MSFT appears as part of the URL). If we were to type this URL into the address bar of another browser, it would bring up a similar page that shows most recent data that Yahoo makes available.

To be sure that you get the URL correctly, you should copy it from the address bar of the browser once you have displayed the page.

Now we will begin the web query by doing the following step

Choose "Data | Import External Data | New Web Query..." from the menu bar as seen below

This will invoke the "New Web Query" dialog box displaying the page is set as the default home page for Internet Explorer. You will go to the correct page by following the next step:

Erase the URL currently in the address bar
Paste the URL to the Yahoo Finance page (or type it as follows: http://finance.yahoo.com/q?s=msft)
Press Enter (or click the button labeled "Go")

This will display the page from the Yahoo finance site in the "New Web Query" dialog box as follows:

Notice that Excel has added several black arrows in yellow boxes. These are to indicate the different segments of the page that Excel can import. Select the desired section by following the next step:

Click on the black arrow immediately to the left of "Last Trade" on the page. You may need to scroll to see it
Click the "import" button at the bottom of the dialog box.

This will display the "Import Data" dialog box as seen here:

Specify that the results begin at cell A2 by clicking on cell A2 on the worksheet
Click the "OK" button at the right of the dialog box.

Sheet2 should now appear as follows:

Now let's copy the price of the most recent trade back to our table on Sheet1 by following the next steps:

Select cell B2
Choose "Edit | Copy" from the menu bar (or press the keyboard shortcut, CTRL+C).
Click on the worksheet tab named "Sheet1"
Press "CTRL+Right Arrow" to move to cell B2. This is required instead of just pressing "Right Arrow" or clicking on cell B2 because "CRTL+Right Arrow" records a command that says "move to the rightmost column of the current data table" without changing the row. Since we want this to work on an arbitrarily chosen row, the keystroke is needed.
Choose "Edit | Paste" from the menu bar (or press the keyboard shortcut, CTRL+V).
Press "CTRL+Left Arrow" to move to cell B1.

Sheet1 should now appear as follows (of course, the amount of the last trade price will depend on what Yahoo reported as the last trade):

At this point we have only to delete the web query on Sheet2 to get ready for subsequent runs of the macro as follows:

Select cell B2
Click on the worksheet tab named "Sheet2"
Select all of the cells used (in our example it is the range A1:B9)
Press the "Delete" key on your keyboard
Click "Yes" on the dialog box warns you that you are about to delete a query.
Click on the worksheet tab named "Sheet1"
Choose "Tools | Macro | Stop Recording" from the menu bar

We now have a macro that does almost exactly what we want it to do. Here is the VBA code that Excel has generated as a result of our macro recording session:

Sub GetPrice()
'
' GetPrice Macro
' Macro recorded 8/31/2006 by Gove Allen
'

'
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://finance.yahoo.com/q?s=msft", Destination:=Range("A2"))
        .Name = "q?s=msft_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "13"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Range("B2").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Selection.End(xlToRight).Select
    ActiveSheet.Paste
    Selection.End(xlToLeft).Select
    Sheets("Sheet2").Select
    Range("A1:B9").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Selection.QueryTable.Delete
    Sheets("Sheet1").Select
End Sub

Notice the line which reads:

"URL;http://finance.yahoo.com/q?s=msft", Destination:=Range("A2"))

This specifies the URL needed for the creation of the web query. You will see that the symbol "MSFT" is appears as part of the string; however, we need the symbol to be different depending on which symbol is in the active cell when the macro is invoked. Since our macro copies the symbol into cell A1 of Sheet2 before the web query is created, we can reference that cell to get the correct value. To integrate the value of the current symbol modify this line to read as follows:

"URL;http://finance.yahoo.com/q?s="&range("a1").value, Destination:=Range("A2"))

Now when the macro is invoked, the web query will retrieve the price of the last trade for the symbol that is selected when the macro begins.

To retrieve the remaining prices, the user needs only select each of the remaining symbols one at a time and press "CTRL+SHIFT+Q"