Visual Basic

1
Visual Basic
Objects / Properties / Methods
Attribute
2
Visual Basic
Range (A3).select
Range is an object
(A3) is a modifier for the object
select is a method
Note that object separated from method by a .
3
Visual Basic
Activesheet.name
Activesheet is an object name is a property
The result is the name of the active sheet
4
Procedures
Sub procedures return no values except by
changes to parameters in the argument list
Function procedures return a value by assigning
it to the name of the function.
5
Function Procedures
Function Grade (exam1,exam2,exam3) as String
Dim Sum as Single
Sum exam1exam2exam3
If Sum gt 95 then Grade A Elseif
Sum gt 80 Grade B Else Grade
C Endif
End Function
6
For functions and sub procedures, there is a one
to one correspondence between the arguments in
the call to the function and the argument list of
the function
The call
Grade( 90, C40, C60 )
The definition
Function Grade (exam1,exam2,exam3) as String
7
Sub Procedures
Public Sub ColorList() ' Lists all the available
colors in the current pallette ' Number is listed
in column 2, color sample in column 3 ' List
starts in row 5 Dim I As Integer Dim J As
Integer Dim K As Integer J 5 I 2 For K 1 To
56 Cells(J, I).Value K Cells(J, I
1).Interior.ColorIndex K J J 1 Next
K End Sub
Note that ColorList is never assigned a value!
8
Review of some Excel Basics
Cell references of two types A1 Columns
are alphabetic, rows are numeric R1C1 R
number Column number
B2 and R2C2 refer to the same cell
Can be set by the Tools / Options menus (Note
that the two methods are transposed A1 column
first, then row R1C1 row first, then column
)
9
Formulae in Excel (R1C1 style)
R1C1 is an absolute address it does not change
under copy / paste R1C1 is a relative address
it does change under copy /paste And can use
mixed mode R1C1 R1 is absolute, C1 is
relative
10
In VBA, can use either or both A1 and R1C1
styles A1 style tends to be absolute A1 style
used with the Range object Range(A4) Can
refer to individual cells with the Cells
object, which uses an R1C1 style Cells(4,1)
11
In Excel, cells ranges can be named Insert /
name menu These names can be used in
Formulae VBA
12
VBA Variable Types
String A sequence of bytes
containing characters Integer 2 byte
integer Long 4 byte integer Single
4 byte real number Double
8 byte real number Variant Can hold anything
(but expensive to use) Object A class of data
types used by Excel/VBA
13
Characters Stings
For Excel, anything that is not a number or a
formula is a string. If want a set of digits as
a string, need to enclose in quote or quotation
marks.
For VBA, need to define variable that will hold
strings as string data types
14
Visual Basic
Example -- simple Sub to name the active
worksheet
Sub NameIt() Dim newname as String newname
InputBox(Enter a name for the worksheet) ActiveS
heet.Namenewname End Sub
15
Object data type
Many of the objects in Excel have their own data
type Dim r as Range Dim q as Worksheet Dim z
as Chart About the only one we will use is the
Range data type.
16
Option Explicit
Forces you to Dim all variables Helps prevent
typos Can set this as the default through the
Tools/ Options menu. (Require variable
declaration check box)
17
(No Transcript)
18
Visual Basic
All objects have properties Most objects have
methods Will work with only a few of the many
objects, methods and properties To get a list of
objects and properties, invoke the Object Browser
(F2)
19
Program module (Sub or Function) is made up of a
series of steps to accomplish a task. Five major
categories of steps
A 5
Assignment statements
Conditional statements
If ( A gt 5) then .
Calls to other modules
A sqrt (12)
For I 1 to 6 Next i
Iteration Statements
Input /Output operations (Which are really calls
to other modules)
Read 5, A
20
Expression evaluation inVBA Operators in
priority order Left to right
Operator Priority List ( ) - (unary
minus) / -
Comparison Operators (gt, lt )
Logical Operators (NOT, AND, OR)
21
Test 2 3 2 gt 5 AND (3-7)2 gt 6
(3-7) -4 32 9 -42 -8 92 11 11 gt
5 True -8 gt 6 False True AND False False
Thus, Test has the value False
22
Conditional Expressions If / then /
else end if Select case . End Case
23
If (expression) then
One or more expressions
Else
One or more expressions
End If
If (expression ) then (expression)
Note no End If statement for this type of If
/then expression
24
Select Case Statement
Like a complex If / Then / ElseIf / EndIf
Select Case testexpression Case
expressionlist-n statements-n ... Case
Else elsestatements End Select
25
Select Case Statement
Testexpression can be any that evaluates to a
numeric value or a string
Expressionlist is compared to the testexpression
to determine if the following statements should
be executed. See Help for all the allowable
forms
Statements are executed up to the next Case
statement or the End Select statement
26
A simple example
Case statements to determine a letter grade from
a numeric grade. Assume the numeric grade is in
the variable grade
27
Select Case grade Case Is gt90
ltrgradeA Case Is gt 85 ltrgrade
BA Case Is gt 70 ltrgrade C Case
Else ltrgradeE End Select
Note Goes to first Case that testexpression
satisfies. Is implicitly uses grade in
the comparison
28
Generally speaking, anything you can do with a
Select Case block, you can do with a set of If /
Then / Else If / End If statements
29
Iteration Statements
Used to execute a number of statements repeatedly
Two major types --- For / Next Do / Loop
30
For counter to upperlimt step size Statements Ne
xt counter
For Each cell In Selection Statements cell.valu
e expression Next cell
31
Basic Do Loop Structure
Do statements Loop
But we need a mechanism to stop the loop!
32
Do while expression_is_true Statements Loop
Do Statements Loop while expression_is_true
33
Do Until expression_is_true Statements Loop
Do Statements Loop Until expression_is_true
34
Do...Loop Statement Example This example shows
how Do...Loop statements can be used. The inner
Do...Loop statement loops 10 times, sets the
value of the flag to False, and exits prematurely
using the Exit Do statement The outer loop exits
immediately upon checking the value of the
flag. Dim Check, Counter Check True Counter
0 ' Initialize variables. Do ' Outer loop.
Do While Counter lt 20 ' Inner loop. Counter
Counter 1 ' Increment Counter. If Counter
10 Then ' If condition is True. Check
False ' Set value of flag to False. Exit Do
' Exit inner loop. End If Loop Loop Until
Check False ' Exit outer loop immediately
35
To manipulate a cells value, use the value
property of the cell object
Range(A4).value 20 Temp Range(A4).value
or
Cells(4,5).value 20 Temp Cells(4,5).value
36
Build a simple module to set a 5 x 5 array of
cells to some value
In VBA IDE Insert module Insert procedure /
sub Initcells
Dim I as integer Dim J as integer For I 1 to
5 For J 1 to 5 Cells(I,J) IJ Next J Next I
Return to Excel and run macro Initcells
37
Two useful VBA functions to interact with use
InputBox displays a prompt and awaits a reply
Str1 InputBox(prompt)
When invoked, displays the prompt and gives you a
box to enter a reply, which is placed in Str1
Dim str1 as String str1 InputBox(Please enter
your name)
38
MsgBox displays a message and buttons to press.
The function value returned is the code for the
button
J MsgBox(prompt,buttoncodes, box name)
Look at Help for the button code values J is
the integer value of the button code the user
presses
CodeMsgBox(Are you OK,vbOKOnly,My Temp)
39
Write a simple sub (called Heater) that accepts a
temperature as an argument and returns a string
to indicate what to do with the heater. Also
have MsgBox display a message to the use if the
heater status is changed. The logic for the
heater control is as follows If the temperature
is less than 65, Turn heater on If the
temperature is greater than75, Turn heater
off Otherwise, Leave heater alone Function
Heater (Temp) As string Code End function