Intro to Macros

What is a Macro?

Have you ever done the same sequence of commands over and over and over. Wouldn't it be neat to be able to do it once, record it and then play it back whenever you wanted? You can with macros.

A Macro is a set of instructions that tells Excel which commands to execute. It is a program and the instructions are written in a language called Visual Basic. You don't have to be a programmer to use macros. Excel has what is called a Macro Recorder which will record your actions as you perform a sequence of steps and create the Visual Basic equivalent to the sequence of steps you performed.

The version of Visual Basic used in Office 97 is called Visual Basic for Applications (VBA).

The first step in using macros and understanding macros is to create a simple macro and look at it in detail.

Generating a simple Macro

  1. To use the recorder use the Record Macro button of the Tools Menu.

    2.  

     
     
     
     
     
     
     

  2. From that point on everything you do is recorded. It doesn't matter if you use menus or keyboard commands every command and action you do is recorded until you turn off the macro recorder. The resulting Visual Basic statements are stored as a macro within your workbook.
  3. When you click on the Record Macro button a window pops up to get more information such as the name of the macro and what key you are going to use to invoke the macro.

    4.  

     
     
     
     
     
     
     

  4. Click on the Ok button to start recording the macro.
      5. In this example I just:
    1. Selected Cell A1
    2. Typed in your name
    3. Selected Cell A2
    4. Typed in Info130
    5. Highlighted A1:A2
    6. Clicked on Bold
    7. Clicked on Italic
    8. Set the font size to 12
    9. Clicked outside the cell range to indicate I was done with that range
  5. Click on the Stop recording button in the little window that appeared after you hit ok to record the macro. 

Running the macro

Once you have created the macro, the next thing you would like to do is see if it works! To see if it works go to a new sheet and hit Ctrl+n. (The Ctrl+n is what I told it to use when I recorded it)

Running the Visual Basic Editor(VBE)

When you want to look at the Visual Basic code you have to start the Visual Basic Editor Application. This is a completely separate application outside of Excel.

To start the Visual Basic Editor click on the Tools Menu then Macros and finally Visual Basic Editor.

Once the Visual Basic Editor application opens you must click on the View code button to see the code that you have generated in the macro.
The left side of the VBE window contains the Project Explorer Which is similar to the Windows Explorer application. Except it displays only open workbooks and/or other Visual Basic projects. The macro that you record will be in Module1 of the Project, Select module 1 and then click the View Code button. The View code button is just below the word project.

As you will see a Visual Basic Module is composed of one or more procedures, each of which corresponds to an Excel Macro. In this case Module 1 contains a procedure called NameAndCourse.

To Delete unnecessary Code:

  1. Select the highlighted area as shown above --> Delete --> Alt + F11 to toggle back to Sheet 1 to rerun the Macro.
  2. Save the workbook.
To Create a new Macro called EraseNameAndCourse using the CTRL + e keys to run it.
  1. Tools --> Macro --> Record New Macro --> Fill in the boxes with EraseNameAndCourse  and e respectively --> OK
  2. Start Recording macro: Select A1:A2 --> Edit --> Clear --> All --> click in A3 --> Stop recording.
  3. Try out both macros: now using the CTRL + n  and then CTRL + e keys.
To Edit a Macro:
  1. Press Alt + F11 to switch to the VBA Editor --> Double click the NameAndCourse Macro --> Change the 12 font size to 16 and delete the bold statement line
  2. Switch back to sheet 1 and try both macros again.

Looking at Visual Basic Code

The Visual Basic code is an object oriented programming language. It consists of two kinds of statements multiline statements and single line statements.

A single line statement

A single line statement is simply that a statement that can be completed on one line. For example: 
  Range("A1").Select
The statement performs one operation and that's that.

A multiline statement

A multiline statement is simply a statement that does more than one thing and therefore needs more than one line to do it. The multiline statements usually start with a Keyword and then end with a End Keyword statement. For example: Sub NameAndCourse() End Sub and With Selection.Font End With these are multiline statements. The Sub line starts a Sub Statement and it ends with the End Sub. The With line starts a With Statement and it ends with the End With

Comments

Comments are statements that do not do anything in the program but allows the programmer to document what the program is doing, who wrote it. Comments are like the Documentation worksheet that we have talked about in excel. Comments start with an apostrophe 
'
' NameAndCourse Macro
' Macro recorded 2/02/99 by Ruth MacKinnon
'
' Keyboard Shortcut: Ctrl+n
'

Statements

Each statement in the Macro is a direct result of an action performed during the recording of the macro. Visual Basic is an object oriented language so it likes to treat everything like an object. Objects are things that have attributes that you can set. You can also do things to objects like select them. Lets do a line by line analysis of our macro. 
Sub NameAndCourse()
This is the procedure created when we recorded our macro NameAndCourse. The Sub (short for subroutine) statement starts here and continues until the End Sub. 
'
' NameAndCourse Macro
' Macro recorded 2/02/99 by Ruth MacKinnon
'
' Keyboard Shortcut: Ctrl+n
'
These are comments written to tell Excel about the Macro NameAndCourse It tells the Name of the macro (and it is a MACRO) the date and time the macro was recorded, who recorded it and What the keyboard shortcut is to run the macro
This Action caused this code to be generated
Click on  
    Range("A1").Select
VBA has an object called a Range, like the range in an excel worksheet. This statement creates a Range from the Cell "A1" and then does the Select action on that range. The Select operation sets the Range to be the Active Cell.
This Action caused this code to be generated
Entering "Michael MacDonald" in the Active Cell  
    ActiveCell.FormulaR1C1 = "Michael MacDonald"
Any object has different attributes that you can change. In this case you are changing the FormulaR1C1 attribute of the Active Cell to the value "Michael MacDonald" (R1C1 is row 1 column 1 of the active cell). This is a direct result of typing "Michael MacDonald" into the cell A1 in Excel.
This Action caused this code to be generated
Clicking on A2 to make it the Active Cell  
    Range("A2").Select
This is the result of us clicking on cell A2 in Excel. The result is to change the Object ActiveCell to be the cell A2.
This Action caused this code to be generated
Typing Info130.22 in the Active Cell 
    ActiveCell.FormulaR1C1 = "Info130.22"

This is setting the ActiveCell Row 1 column 1 to be "Info130.22"
This Action caused this code to be generated
Selecting the Range "A1:A2" 
    Range("A1:A2").Select
This is a result of us highlighting A1 and A2.
This Action caused this code to be generated
Clicking on the Bold font button  
    Selection.Font.Bold = True
The Selection object (the highlighted range) has an attribute called Font. The Font attribute itself has a bunch of attributes. One of which is the Bold attribute, setting it to true makes the font Bold. Setting it to False would remove the bold font characteristic.
This Action caused this code to be generated
Clicking on the Italic font button  
    Selection.Font.Italic = True
The Selection object (the highlighted range) has an attribute called Font. The Font attribute itself has a bunch of attributes. One of which is the Bold attribute, setting it to true makes the font Bold. Setting it to False would remove the bold font characteristic.
This Action caused this code to be generated
Selecting 12 point type  
    With Selection.Font
        .Name = "Arial"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
Selecting 12 point causes a number of attributes to be set rather than simply the font size. When setting a lot of attributes at once it is easier to use the With statement. This statement allows VBA to say assuming this object (Selection.Font in this case) set these attributes.
This Action caused this code to be generated
This marks the end of the Subroutine (macro) 
End Sub

Macro Names

Macro names must conform to the syntax of the Visual Basic language and as a result may not contain blanks or any punctuation other than an underscore ("_")

Debugging

The word debugging comes from the earliest origins of computers when engineers were tracking down a difficult problem that turned out to have been caused when a moth got caught in the guts of the wiring of the computer. Removing the moth stopped the short and the problem went away, computers have been debugged ever since.

One of the simplest ways to debug a program is to watch the program execute one line at a time. Many modern programming environments allow you control the execution of a program so that you can see how the program works one single step at a time. This is called single stepping through a program.

The Debugging menu of the Visual Basic Editor allows you a wide range of tools to help you debug your programs.

The Debugger allows you to single step through the program and watch as the program executes. The following screen captures allow you to watch as we single step through the commands using the Step into command.
Pressing F8 the first time highlights the Sub Statement to indicate that we are starting to execute the NameAndCourse Macro.
Pressing F8 again highlights the Range("A1").Select Statement to indicate that we are about to execute this line.
Pressing F8 again tells VBA to execute the range command and highlight the next line. (Notice that now Cell A1 is the Active Cell)
Pressing F8 again tells VBA to execute the command to set the attribute and highlight the next line. (Notice that now Cell A1 is filled in)
I encourage you to watch how the excel worksheet changes as the commands execute. Do it again and see if you can predict what is going to happen by reading the command that is highlighted. This will help you learn programming quickly.

In class exercises

  1. hands-on Exercise 1 from Chapter 7 Page 282. Save the workbook as macro 1.xls

Created - February 11, 1999 by Michael MacDonald.
by Ruth MacKinnon
130 Home        X Home  IS Department at X        Home