Lecture Outline Record macro and examine VBA code

Lecture Outline Record macro and examine VBA code VBA Editor (IDE) Objects Assignment Statements Absolute vs Relative Reference Variables and Constants Functions: Msg. Box Input. Box University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 1

Macros = VBA programs within Office applications for example … Word: change font colour Excel: move cursor to another cell anything you can do from a menu! University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 2

Why create a Macro? Automate tasks: tasks you do repeatedly complex tasks that are error prone Customized functions/applications: perform specific tasks customize menus and toolbars create custom ‘front-ends’ for users University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 3

Macro example #1 Word Add your name at end of MS Word document >Tools >Macro >Record New Macro… go to end of document leave 2 blank lines indent a little type name select change colour change font size University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 4

Macro example #2 Excel Change cell D 5 to contain the words “Hi there” in blue text turn on recorder name the macro move to cell D 5 set text colour blue type “Hi there” stop recording University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 5

Where is the macro? Macro recorder writes VBA code and stores it with the spreadsheet When workbook is saved, code is too to see the macro code: >Tools >Macros >Visual Basic Editor (or Alt-F 11) University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 6

VBA Editor (IDE) University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 7

VBA Editor – Project Window Worksheets Workbook Modules Forms University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 8

VBA Editor Properties Window Properties of selected object University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 9

VBA Editor Code Window Key Words Comments Code Errors Code: Objects Properties Methods University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 10

What is VBA? powerful programming language object-oriented Objects: cells, worksheets, menus, etc E. g. , Range(“D 5”) Properties: you control properties of objects E. g. , Range(“D 5”). Font. Name Assignment Statements: define property state E. g. , Range(“D 5”). Font. Name = “Arial” event-driven responds only when an event occurs University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 11

Spreadsheet Objects Workbook Worksheet Column Row Cell Range University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 12

VBA Objects Usually referred to by a name Cell B 6 is named Range(“B 6”) Column B is named Column(“B: B”) Nicknames for current cell/sheet Active. Cell Active. Sheet Objects of same type form a collection Worksheets is the collection name Worksheets(“Sheet 1”) refers to one object University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 13

Object Hierarchy A Workbook may contain many sheets Workbooks(“Book 1. xls”). Worksheets(“Sheet 1”) Workbooks(“Book 1. xls”). Worksheets(“Sheet 2”) Active objects used by default Workbooks(“Book 1. xls”). Worksheets(“Sheet 1”) . Rang Active. Sheet. Range(“B 6”) University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 14

Cell Properties Name: [A 4] Font: Comic Sans Alignment: Right Text color: Green Value: 13 Formula: =SUM(A 1: A 3) University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 15

Assignment Statements Used to change an object Put number 15 into cell A 3: Range(“A 3”). Value = 15 Determine value in cell A 3, and put in C 4: Range(“C 4”). Value = Range(“A 3”). Value Add value in A 3 to what is in C 4: Range(“C 4”). Value = Range(“C 4”). Value + _ Range(“A 3”). Value Increment B 5 (add 1): Range(“B 5”). Value = Range(“B 5”). Value + 1 University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 16

Subroutines begin with Sub My. Name() end with End Sub Location Modules Worksheets Workbook User. Form University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 17

VBA code Sub Change. Cell() ' Change. Cell Macro ' Macro recorded 07/08/2007 by Andria Hunter ' ' Keyboard Shortcut: Ctrl+m ' Range("D 5"). Selection. Font. Color. Index = 5 Active. Cell. Formula. R 1 C 1 = "Hi there" Range("D 6"). Select End Sub University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 18

Stepping through code >View >Toolbars >Debug click in the code window >Step Into to run each line of code watch what happens in the worksheet Make sure your Excel worksheet is visible. University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 19

Relative Reference Defines one cell relative to another Offset(Row, Column) Row: positive moves to right Column: positive moves downward Examples: Range(“A 3”). Offset(2, 4) – refers to cell E 5 Activecell. Offset(1, 0). Select – moves down one cell Recording Macros Relative Reference button University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 20

Relative Reference: code Sub Change. Relative() ' Change. Relative Macro ' Macro recorded 07/08/2007 by Andria Hunter ' ' Keyboard Shortcut: Ctrl+n ' Active. Cell. Offset(-6, -1). Range("A 1"). Selection. Font. Color. Index = 5 Active. Cell. Formula. R 1 C 1 = "Hi there" Active. Cell. Offset(1, 0). Range("A 1"). Select End Sub University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 21

Variables Short-term storage for information held in RAM while program running Examples: Dim v. Info Dim i. Num As Integer University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin Dim d. Num As Double Dim s. Text As String CSCA 01 VBA 22

Constants Similar to variables, but cannot be modified while program is running Const v. INFO = 0. 25 i. SIZE As Integer = 10 d. HEIGHT As Double = 6. 5 s. WORD As String = “Hello” Built-in: vb. Yes, vb. OKOnly, vb. Red, etc. University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 23

Functions Abs (number) returns number v. Pos. Num = Abs(-55) 55 v. Num = -2. 3 v. Pos. Num = Abs(v. Num) Range(“B 3”) = Abs(v. Num) Msg. Box Abs(v. Num) 2. 3 University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 24

Input. Box Function Input. Box(message, title) s. Name = Input. Box(“Enter your name: ”, ”Name”) Range(“B 3”) = Input. Box(“Enter your name: ”, ”Name”) University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 25

Msg. Box Function Msg. Box “Hi, Snipe!” v. Pos. Num Msg. Box “Howdy!”, vb. OKOnly, “Greeting” University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 26

Macro from scratch Insert Module and type code Sub Greet. Me() 'This macro asks the user to enter a name, and then 'it uses a message box to display the name. Dim s. Name As String 'To store user's name 'Ask user for name, and store in variable. s. Name = Input. Box("What's your name? ") 'Displays "Hi, " plus the stored name. Msg. Box "Hi, " & s. Name End Sub University of Toronto at Scarborough © Andria Hunter, Kersti Wain-Bantin CSCA 01 VBA 27