Macros: Sub Procedures You Record

Macros:Sub Procedures You Record What is a macro? What is With…End With? What is Sub…End Sub? Relative vs. Absolute CS 105 Fall 2006

Macro Statements • A Macro always begins a Sub statement • A Macro ends with an End Sub statement CS 105 Fall 2006

To perform multiple actions on the same object Macros use With End With A Macro CS 105 Fall 2006

Select and Do—the pattern • Note how first the Macro selects the object, then executes the code… CS 105 Fall 2006

More about Macros… • Statements are color coded • Step Into command helps you debug a macro • Opening a file with a macro will prompt a question about viruses CS 105 Fall 2006

Record Macro • Comments begin with an apostrophe (‘) • Comments help the programmer, others to read code CS 105 Fall 2006

Calling Macros • You can use macros easily because • they are on a Module sheet, that • makes them available to everything, so all you do is write the macro name CS 105 Fall 2006

Finding the Stop Recording button! • We lost our little macro recorder button • If you lose yours, go to View/Toolbars/Stop Recording, and it will appear on your spreadsheet or on a toolbar. CS 105 Fall 2006

Cell References in Macros Absolute references— When we specify exactly what cells are affected Absolute cell address are constant CS 105 Fall 2006

An absolute Macro • Range selects the starting or active cell/cells—the references below are absolute—whenever the Macro runs, these exact cells are selected First, the cell is selected, then a number is placed in it CS 105 Fall 2006

ActiveCell.FormulaR1C1 R1C1 is a reference style (you can set this under Tools/Options/General) Don’t worry, we won’t be using this in class. We want you to know where it came from. CS 105 Fall 2006

ActiveCell.FormulaR1C1 = "Income" • All that you need to know is that for the active cell, the content is "Income" • "FormulaR1C1" refers to the contents of the active cell • You find this notation in Macros that you create CS 105 Fall 2006

FormulaR1C1 vs. Value Range("D5").Select ActiveCell.FormulaR1C1 = "8" Range("D5").Select ActiveCell.Value = "8" These two code fragments produce the same results. CS 105 Fall 2006

Not used for a formula, though Range("D5").Select ActiveCell.FormulaR1C1 = "=SUM(A1: A10)" Range("D5").Select ActiveCell.Value = "=SUM(A1: A10)" These two code fragments do not produce the same results. FormulaR1C1 requires different cell addressing, "=SUM(R[-4]C[-3]:R[5]C[-3])" CS 105 Fall 2006

What if you want to vary the cells? CS 105 Fall 2006

We don’t always want the event to happen in the same place Cell addresses change Visual Basic uses Offset to indicate space away from active cell Relative references CS 105 Fall 2006

Recording a Macro with Relative References Before you start recording your Macro, click on the Relative Reference button CS 105 Fall 2006

Relative Cell References • ActiveCell.Offset(2, 0).Range("A1").Select • Means the cell two rows below the active cell, in the same column CS 105 Fall 2006

ActiveCell.Offset(2, 0).Range("A1").Select • What does ActiveCell.Offset(2, 0).Range("A1").Select mean? CS 105 Fall 2006

Range("A1").Select • This part of the code tells you how many cells have been selected. ActiveCell.Offset(2, 0).Range("A1").Select means 1 cell is selected ActiveCell.Offset(2, 0).Range("A1:C1").Select means 3 cells in a row have been selected CS 105 Fall 2006

Activate or Select? Range("B3").Select Selection.Interior.ColorIndex = 6 Range("B3").Activate Selection.Interior.ColorIndex = 6 • Both code fragments have the same result CS 105 Fall 2006

This Relative Macro Causes a Crash CS 105 Fall 2006

Run it line-by-line to find bug Code runs OK until it reaches the line underthe highlighted line. You can turn your attention to the problem line of code! CS 105 Fall 2006

To Summarize • What is a macro? • What is With…End With? • What is Sub…End Sub? • Relative vs. Absolute CS 105 Fall 2006

Macros: Sub Procedures You Record