Transcript CSC A01 Lecture 2 - University of Toronto

Lecture Outline
Variable Scope
Calling another subprogram
Programming Style
Dialog Boxes (UserForm)
Worksheet button
Functions
Spreadsheet vs VBA
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
1
Variable Scope
Procedure Level (local)
Dim iMarbles As Integer
placement: within Sub and End Sub
Module Level (global)
Dim giTotal As Integer
placement: before all Sub statements
Project Level (public)
Public piGrandTotal As Integer
placement: before all Sub statements
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
2
Calling another subprogram
Sub FirstOne()
MsgBox “In 1st subprogram…”
SecondOne
End Sub
Sub SecondOne()
MsgBox “In 2nd subprogram…”
End Sub
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
3
Programming Style
Good style improves readability of code and
your program is easier to maintain!
1. Comments (documentation)
 at top (your name, date, etc)
 each subprogram
 each variable
 each major section of code
Don’t restate the obvious!
Dim iCount As Integer ‘ declare integer variable
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
4
Programming Style
2. Good variable names
 describe what variables store
 not too long, or too short
3. Indentation
 subprograms and if statements
4. Constants
 for values that do not change
5. White space
 leave the occasional blank line
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
5
Programming Style
6. Minimize variable scope
 only make a global or public variable if
needed
7. Declare and Initialize variables
 Option Explicit forces variable declaration
 set initial value of variables appropriately
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
6
Programming Style - example
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
7
Programming Style - example
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
8
UserForm
Custom dialog box
Example:
 create UserForm to collect personal info
 form will be launched from Excel
 data entered in form is stored in Excel
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
9
Insert blank form
InsertUserForm
blank UserForm
Toolbox with
control objects
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
10
Design form
Add control objects to form
Label
(display text)
TextBox
(for input)
CommandButton
(perform an
action)
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
11
Initialize properties
Initialize properties of controls
Label
(Name): lbl…
Caption:
TextBox
(Name): txt…
Text:
CommandButton
(Name): cmd…
Caption:
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
12
Write code
Determine events to which VBA should respond
(eg. clicking Add CommandButton)
double-click Add button to open Code window
and type the code
Private Sub cmdAdd_Click()
code goes here . . .
End Sub
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
13
Flowchart
Separate name
into first and last
Start
Name
entered?
Yes
Phone
entered?
No
Report error
Yes
No
Store in worksheet:
last, first, phone
Clear textboxes on
form
Report error
Stop
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
14
Code
refers to textbox contents
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
15
Code
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
16
Create macro
How can we start this form from Excel?
There is no macro listed!
write a macro to display form
 form doesn’t appear until called
 put this code in Module code, not Form code:
Sub EnterPersonalInfo ()
UserForm1.Show
End Sub
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
macro name
form name
CSCA01 VBA-3
17
Add worksheet button
Optional: button on worksheet to run macro
>View >Toolbars >Drawing
 right-click Add Text…
 right-click Assign Macro…
>Oval icon
>View >Toolbars >Control
>CommandButton icon
 right-click Properties
 right-click View Code
>Exit Design Mode icon
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
18
Excel Functions
Most worksheet functions have a VBA equivalent
In VBA:
Application.WorkSheetFunction._
FunctionName(arguments)
Application.WorksheetFunction.Today()
Application.WorksheetFunction.IsNumber
(Range(“B3”))
In Excel:
=FunctionName(arguments)
=Today()
=IsNumber(B3)
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
19
Find Function




VBA Function – not available on sheet
Function is called on an object that defines a
spreadsheet range
 E.g., Selection.Find(“Tom”)
Returns range location of first match, or
returns nothing if no match found.
Range object need not be sorted
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
20
Find Function
Macro asks for name to find and stores in E8
 Find function locates name, uses offset to get
its age
vName = InputBox(“Name?”, “Enter name”)
vAge = Range("A4:A10").Find(vName).
Offset(0, 2).Value

University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
21
VLookUp Function
Vlookup (value, table, col_num, close)
 value: item to find
 table: range of data to search
 must be sorted by 1st column
 col_num: return data from which column?
 close: true or false
 True: select nearest match – always finds
 False: find exact, or return #N/A
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
22
VLookUp Function
How it works in Excel
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
23
VLookUp Function
How it works in VBA
vName = InputBox(“Name?”, “Enter name”)
Range(“E8").Value = vAge
vAge = Application.WorksheetFunction. _
VLookup(Range("E8"), Range("A4:C10"), 3,
True)
Range("F8").Value = vAge
University of Toronto at Scarborough
© Andria Hunter, Kersti Wain-Bantin
CSCA01 VBA-3
24