Transcript CSC A01 Lecture 2 - University of Toronto

Lecture Outline
Record macro and examine VBA code
VBA Editor (IDE)
Objects
Assignment Statements
Absolute vs Relative Reference
Variables and Constants
Functions: MsgBox
InputBox
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 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
CSCA01 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
CSCA01 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
CSCA01 VBA
4
Macro example #2 Excel
Change cell D5 to contain the words
“Hi there” in blue text
turn on recorder
name the macro
move to cell D5
set text colour blue
type “Hi there”
stop recording
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 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-F11)
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA
6
VBA Editor (IDE)
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA
7
VBA Editor – Project Window
Worksheets
Workbook
Modules
Forms
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA
8
VBA Editor
Properties
Window
Properties of
selected object
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 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
CSCA01 VBA
10
What is VBA?
powerful programming language
object-oriented
Objects: cells, worksheets, menus, etc
E.g., Range(“D5”)
Properties: you control properties of objects
E.g., Range(“D5”).Font.Name
Assignment Statements: define property state
E.g., Range(“D5”).Font.Name = “Arial”
event-driven
responds only when an event occurs
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA
11
Spreadsheet Objects
Workbook
Worksheet
Column
Row
Cell
Range
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA
12
VBA Objects
Usually referred to by a name
Cell B6 is named Range(“B6”)
Column B is named Column(“B:B”)
Nicknames for current cell/sheet
ActiveCell
ActiveSheet
Objects of same type form a collection
Worksheets is the collection name
Worksheets(“Sheet1”) refers to one object
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA
13
Object Hierarchy
A Workbook may contain many sheets
Workbooks(“Book1.xls”).Worksheets(“Sheet1”)
Workbooks(“Book1.xls”).Worksheets(“Sheet2”)
Active objects used by default
Workbooks(“Book1.xls”).Worksheets(“Sheet1”)
.Range(“B6”)
ActiveSheet.Range(“B6”)
Range(“B6”)
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA
14
Cell Properties
Name:
Font:
Alignment:
Text color:
Value:
Formula:
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
[A4]
Comic Sans
Right
Green
13
=SUM(A1:A3)
CSCA01 VBA
15
Assignment Statements
Used to change an object
Put number 15 into cell A3:
Range(“A3”).Value = 15
Determine value in cell A3, and put in C4:
Range(“C4”).Value = Range(“A3”).Value
Add value in A3 to what is in C4:
Range(“C4”).Value = Range(“C4”).Value + _
Range(“A3”).Value
Increment B5 (add 1):
Range(“B5”).Value = Range(“B5”).Value + 1
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA
16
Subroutines
begin with Sub MyName()
end with
End Sub
Location
Modules
Worksheets
Workbook
UserForm
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA
17
VBA code
Sub ChangeCell()
' ChangeCell Macro
' Macro recorded 07/08/2007 by Andria Hunter
'
' Keyboard Shortcut: Ctrl+m
'
Range("D5").Select
Selection.Font.ColorIndex = 5
ActiveCell.FormulaR1C1 = "Hi there"
Range("D6").Select
End Sub
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 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
CSCA01 VBA
19
Relative Reference
Defines one cell relative to another
Offset(Row, Column)
Row: positive moves to right
Column: positive moves downward
Examples:
Range(“A3”).Offset(2,4) – refers to cell E5
Activecell.Offset(1,0).Select
– moves down one cell
Recording Macros
Relative Reference button
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA
20
Relative Reference: code
Sub ChangeRelative()
' ChangeRelative Macro
' Macro recorded 07/08/2007 by Andria Hunter
'
' Keyboard Shortcut: Ctrl+n
'
ActiveCell.Offset(-6, -1).Range("A1").Select
Selection.Font.ColorIndex = 5
ActiveCell.FormulaR1C1 = "Hi there"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA
21
Variables
Short-term storage for information
held in RAM while program running
Examples:
Dim vInfo
Dim dNum As Double
Dim iNum As Integer Dim sText As String
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA
22
Constants
Similar to variables, but cannot be modified
while program is running
Const
Const
Const
Const
vINFO = 0.25
iSIZE As Integer = 10
dHEIGHT As Double = 6.5
sWORD As String = “Hello”
Built-in: vbYes, vbOKOnly, vbRed, etc.
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA
23
Functions
Abs (number)
 returns number
vPosNum = Abs(-55)
 55
vNum = -2.3
vPosNum = Abs(vNum)
Range(“B3”) = Abs(vNum)
MsgBox Abs(vNum)
 2.3
 2.3
 2.3
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA
24
InputBox
Function
InputBox(message, title)
sName = InputBox(“Enter your name:”,”Name”)
Range(“B3”) =
InputBox(“Enter your name:”,”Name”)
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA
25
MsgBox Function
MsgBox
“Hi, Snipe!”
MsgBox
vPosNum
MsgBox “Howdy!”, vbOKOnly, “Greeting”
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA
26
Macro from scratch
InsertModule and type code
Sub GreetMe()
'This macro asks the user to enter a name, and then
'it uses a message box to display the name.
Dim sName As String
'To store user's name
'Ask user for name, and store in variable.
sName = InputBox("What's your name?")
'Displays "Hi, " plus the stored name.
MsgBox "Hi, " & sName
End Sub
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA
27