Creating a macro within Excel is a three-step process: start the macro recorder, perform the actions to be recorded, and then stop the macro recorder. There are two ways to start the macro recorder: Select the Tools menu, then select Macro and Record New Macro . Or, click on the Record Macro button on the Visual Basic toolbar (pull up the toolbar by selecting it under View/Toolbars or by right-clicking on the toolbar area and selecting Visual Basic ).

A dialogue box will appear that will ask you to name the new macro, and you may also give a brief description of its function. Macro names must begin with a letter, and can include letters, numbers, and underscores, but no spaces or other punctuation. You can also designate a keyboard button to run the macro. Finally, you need to select where the macro you perform will be stored. You have three storage options. By default, your macro will be stored in the folder called Personal Workbook Folder. Otherwise, you can save the macro in the current workbook or in a new workbook. If this is a macro you will use in other workbooks, then you should save the macro in the personal workbook. Otherwise, your best option is to save the macro in the current workbook. Keep in mind that if you want to edit future macros you should save each one in a different workbook, because Excel stores all the macros in the Personal Workbook folder as one macro with a number of sub-routines. After you click OK , every operation you perform will be recorded in this macro (from entering data, formatting cells, opening a file) until you turn the macro off. Turn off the macro by clicking on the Stop macro button, either the button on the Visual Basic toolbar or the freestanding one that will appear somewhere on the screen when the macro is recording.

Let’s try a simple example to demonstrate the ease in creating a macro. Turn on the macro recorder, then select cell A1 and hit the Bold button and type your name. Then stop the recorder by pressing the stop button.

Now, to run the macro you just created, select a different sheet other than the one you just created the macro in and select the key that you assigned your macro, or go to Tools , Macro , then select Macro . It’s that simple!!

When you create a macro, it will only run when the workbook you were in is open unless you change where the macro is stored. At the bottom of the Macro Options window (or the Record New Macro/Options window), notice that you can change where the macro is stored. If you select Personal Macro Workbook , then your macro will always be available when you are working on your machine (but not if you copy the file for someone to use on another machine). The Personal Workbook is saved in the Excel Startup file, so it opens when Excel opens. Usually it is hidden, but after saving a macro to it, select the Window menu and select Unhide . You should see Personal.XLS as an option. NOTE: if your Excel program runs on a network, it probably won't let you store your Personal.XLS on a network drive. You should then save it to your hard drive, and open it whenever you open Excel.

Sometimes, you may want to watch a macro work to see what problems may occur. With the Visual Basic toolbar, click the Run Macro button and select the macro that you want to watch. Then, click the Step button; the Debug window appears (which you can resize to see a worksheet window beneath it). The command line in the macro that has a border around it is the next one that will be run. Click on the Step Into button to watch the macro work through each line of the macro.

If you have small changes to make to your macro, it will probably be easiest to change the code directly in Visual Basic. On some occasions, however, you’ll need to do more. In earlier versions of Excel all you needed to do was to go to ‘Mark text for Recording’ then work through the procedures you wanted to add to the existing macro. That feature is no longer supported in current versions of Visual Basic. Instead, you can create a temp macro and work through the new features to be added to the macro you want to edit, then simply copy and paste them from the temp module to the module you want to edit. For instance, you created a macro that inserted a title into a cell. The cell is formatted but the title doesn’t have the appropriate font type. To fix this, create your temp macro within the same folder as the macro you want to edit and select the cell with the title. Now, format the title the way that you want to save the macro. Then simply go into the Visual Basic editor and copy the code beginning with ‘ With Selection ’ and ending with ‘ End With ’ and paste everything in between into the macro that you want to change. If you don’t see the terms ‘ With Selection ’ and ‘ End With ’, that’s okay. You can still copy and paste the code from your temp macro to your new one.

Understanding properties and methods will help you to understand how a macro works. A property is an attribute of an object. Objects can include workbooks, worksheets, values, labels, almost everything in an Excel workbook; whether a file has been saved or not is a property of that file. The four attributes in the With statement above all deal with properties (the horizontal alignment property is centered, the wrap text attribute can be either true or false, and so on). Again, the syntax for a property is an = sign with a space before and after it.

Methods, as we saw earlier, are actions that you tell Excel to perform (like cutting, copying, and pasting). The syntax for a method is :=. With methods, you will also need to give an argument (in parentheses) to tell Excel how you want it to perform this action. For example, to have Excel autofill the months of the year, with January in cell B2, the code would read:

Selection.AutoFill Destination:=Range("B2:M2"), Type:=xlFillDefault

The argument here is the range where you want the autofill to occur (B2:M2). While this code may look like gibberish, Excel did all this work while the Macro Recorder was on.

While creating some macros is as easy as turning on the macro recorder and working through the process, sometimes you will have to give Excel some extra information to really make the macro work correctly. Let's say that you have two worksheets that have similar layouts, and you want a macro that searches through the data for particular values. Unfortunately, the number of rows on the two tables are different. Therefore, having a macro that moves to a particular cell to continue a search will not work unless those cells have the same relationship in each worksheet. Excel makes this problem easy to correct; as you begin to record the macro, you can select Use Relative References . This will change the selections in the macro from an absolute (like A134) to a reference that is relative to the previous selection in the macro. For example, if you tell the macro to select the next cell down in a column, the code will read:

ActiveCell.Offset (1, 0).Range("A1").Select

This code can be tricky to read. Range("A1") is reference that Visual Basic inserts. In this case A1 is not really A1; when you change the cell selection, Excel treats the cell that you are moving from as the first cell in the first column of a "virtual workbook." Think of it as a relative starting point. The phrase "Offset (1, 0)" means that from the starting point, move one row down in the same column. So the code above can be read "From the cell where you are now, select the cell one row down in the same column." If the offset were two columns over in the same row, the code would be Offset (0, 2); moving to the previous column would be Offset (0, [-1]).

Using relative references can be especially helpful when you are updating an archive file. As you append each set of information for a month, the first blank row of the archive file changes. With absolute references you would overwrite whatever information you appended the previous month. With relative references, you tell it to move to the end of your data (hit the end key and the down arrow), and then hit the down arrow again to move to the first blank row.

When we look through a spreadsheet, we often perform actions unconsciously. We may look down a column of data, stop when no more data is in that column, and then move up to the top of the next column and look down that column, and so forth, until we find what we are looking for. A macro isn't going to know to stop at the end of a column of data or in the last column of a spreadsheet unless we tell it to stop (If you need to stop a macro, hold down the Control key and hit Break or hit the Esc key.). Once we tell it what to do, however, the macro can become very powerful.

Let's say that you want to bold all values in a spreadsheet that are greater than 25%. Looking through the worksheet yourself would quickly become tedious. However, you can have a macro analyze a value, bold it if it meets the criteria, move down to the next value, analyze it and so forth until it reaches the end of the column, then move up to the top of the next column, and continue. You would do this by inserting Do . . . Until loops.

Do Until ActiveCell = ""
	Do Until ActiveCell = ""
		If ActiveCell > 0.25 Then
			With Selection 
				Selection.Font.Bold = True
			EndWith
		End If
		ActiveCell.Offset (1, 0).Range ("A1").Select
	Loop
	Selection.End(xlUp).Select
	Selection.Offset (1, 1).Range("A1").Select
Loop

Notice that there are two loops; the first one tells Excel to do everything else in the macro until it comes to a column that has no data (""). The nested loop tells Excel to continue searching for values of greater than 25% until it gets to a cell in the column with no data. The two lines before the last loop of the macro tell Excel to go back to the top of the column, then move one row down and one column to the right (this assumes that the first row has a label in it instead of data).

Notice that the actual analysis is done with an If . . . Then statement. This simple statement can cut down the work you do immensely by letting Excel take over tedious tasks. Just follow If by the criteria that you want met, then follow the criteria with Then and the action that you want performed. Often it helps to break the action up among separate lines for easy reading.

Once you get the hang of how If . . . Then statements work, you can easily add further code to your existing If . . . Then in the same way you learned to add code to when you learned to edit macros (see above).

As you can see, once you master the art of editing macros, you can insert your own code into more advanced Visual Basic code structures. In this example we have Excel check the value of the active cell. If the value is greater than 150, the font color and cell background color get changed.

If part of your macro uses information that changes often, like a date, instead of editing the macro before each time that you run it, you can change the appropriate line of the macro to have an input box ask for an entry from the user. An example would be a macro that archives monthly data; change the line from:

Selection.FormulaR1C1 = "Mar 94"
to:
Selection.FormulaR1C1 = InputBox("Enter the date in MMM-YY format")

This change will cause a dialogue box to appear while the macro is running, allowing you to enter the new date.

To ease using your macros, sometimes you may want to have Go To commands instead of selecting a certain cell or range of cells. Using the Select command is always relative to what was selected before (when using relative references); with Go To commands, the previous selection doesn't matter. So if you have a macro that charts different rows of information, instead of copying each row to a staging area to chart it; you can use Go To to return to the last row copied instead of to the first row in the series. In fact, a command line:

Application.GoTo

is all you need to return to the previous selection. From that point, you can move to the next row down in the series using relative references.

One final note: Because macros quickly become complicated to read (and debug if there is an error), it is a good idea to keep macros small. Instead of having one macro both format a worksheet, then perform calculations, it would be better to keep these as separate procedures. After you are sure that the macros work properly, you can nest them in a larger macro, making them sub-procedures. A sample of nested macros would be:

' 
' Update Macro
' This macro formats the selected worksheet and calculates the total.
'
Sub Update ()	
Format	
Total
End Sub

One other benefit to having nested macros is that you can have the same nested macro work in different larger macros. Then, you do not have to recreate the procedure. This benefit is especially helpful when you set up your own standard functions, an area that will have to be covered in another handout.