Macros: Sub Procedures You Record

1
MacrosSub Procedures You Record
What is a macro? What is WithEnd With? What is
SubEnd Sub? Relative vs. Absolute
Course Guide p. 213
2
Macro Statements

• A Macro always begins a Sub statement
• A Macro ends with an End Sub statement

3
A Macro

• To perform multiple actions on the same object
  Macros use
• With
• End With

4
Select and Dothe pattern

• Note how first the Macro selects the object, then
  executes the code

5
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

6
Record Macro

• Comments begin with an apostrophe ()
• Comments help the programmer, others to read code

7
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

8
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.

9
Cell References in Macros

• Absolute references
• When we specify exactly what cells are affected
• Absolute cell address are constant

10
An absolute Macro

• Range selects the starting or active
  cell/cellsthe references below are
  absolutewhenever the Macro runs, these exact
  cells are selected

First, the cell is selected, then a number is
placed in it
11
ActiveCell.FormulaR1C1

• R1C1 is a reference style (you can set this
  under Tools/Options/General)

Dont worry, we wont be using this in class. We
want you to know where it came from.
12
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

13
FormulaR1C1 vs. Value

• Range("D5").Select
• ActiveCell.FormulaR1C1 "8"
• Range("D5").Select
• ActiveCell.Value "8"
• These two code fragments produce
• the same results.

14
What if you want to vary the cells?
15
Relative references

• We dont always want the event to happen in the
  same place
• Cell addresses change
• Visual Basic uses Offset to indicate space away
  from active cell

16
Recording a Macro with Relative References

• Before you start recording your Macro, click on
  the
• Relative
• Reference
• button

17
Relative Cell References

• ActiveCell.Offset(2, 0).Range("A1").Select
• Means the cell two rows below the active cell, in
  the same column

18
ActiveCell.Offset(2, 0).Range("A1").Select

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

19
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("A1C1").Select
• means 3 cells in a row have been selected

20
Activate or Select?

• Range("B3").Select
• Selection.Interior.ColorIndex 6
• Range("B3").Activate
• Selection.Interior.ColorIndex 6
• Both code fragments have the same result

21
This Relative Macro Causes a Crash
22
Run it line-by-line to find bug
Code runs OK until it reaches the line under the
highlighted line. You can turn your attention to
the problem line of code!
23
To Summarize

• What is a macro?
• What is WithEnd With?
• What is SubEnd Sub?
• Relative vs. Absolute