Download - BC2014_Session7
-
7/23/2019 BC2014_Session7
1/27
Visual Basic for Applications
(VBA)
1
-
7/23/2019 BC2014_Session7
2/27
Session 7: Demonstrations
Changes needed to Repetitive-Regression for !-"(Assignment #)
$conset Conditional %ormatting via modi&ed macro(Assignment #)
%unction to calculate 'eighted average of a series 'ith inputs
Recorded acro *+ased , for !-.e/ solution of properl/arranged ,0
1amed Ranges in 23cel4 and their access inside VBA0 Running macros from command +uttons on 23celspreadsheet
essage Bo3es in 23cel
2
-
7/23/2019 BC2014_Session7
3/27
5+6ects: $n VBA tas.s are performed +/manipulating o+6ects
3
-
7/23/2019 BC2014_Session7
4/27
ope ties
A property is an attribute of an object that defines
one of its characteristics, such as its name, size,
color, or location on the screen, etc.
Examples
Range("A1:B15").Rows.Count (a read-only property)
Range("A1:B15").Font.Bold !rue (read-wrte property)
4
-
7/23/2019 BC2014_Session7
5/27
Methods
#et$ods are t$e a%tons t$at %an &e per'ormed on&e%ts* su%$ as %losng a wor+&oo+
#et$ods 'or &e%ts
Range("A1:B15").ClearContents
A%t,eCell.Copy
5
-
7/23/2019 BC2014_Session7
6/27
Events
&e%ts %an respond to Events* su%$ as a #ouse-Cl%+*ou&le Cl%+ on a %ell* A%t,ate a or+s$eet*
pen/Close/0a,e a or+&oo+* et%.
or+s$eet e,ents:
0u%$ as A%t,ate* ea%t,ate* C$ange* 0ele%tonC$ange* Be'ore ou&le Cl%+* et%.
or+&oo+ E,ents:
0u%$ as pen* Be'ore Close* Be'ore 0a,ng* Be'ore
rnt* 2ew 0$eet.
6
-
7/23/2019 BC2014_Session7
7/27
Collection Objects
5+6ects are often grouped into collections4 'hichare themselves o+6ects4 called collectionobjects
Colle%ton &e%ts s$are Common ropertes* #et$ods and E,ents
Examples : or+&oo+s* or+s$eets* et%.
or+s$eetss a %olle%ton o' all t$e or+s$eet o&e%ts n t$e spe%'ed ora%t,e wor+&oo+.
7
-
7/23/2019 BC2014_Session7
8/27
Referring Objects or+s$eets(1) re'ers to t$e 1st wor+s$eet o' %urrent a%t,e wor+&oo+.
or+s$eets(3Annual4) re'ers to t$e wor+s$eet named 3Annual4.
8
-
7/23/2019 BC2014_Session7
9/27
Referring to 5+6ects
VBA organies o+6ects and o+6ect collections in a hierarch/'ith the 23cel application at the top and the individual cellsof a 'or.+oo. at the +ottom
-
7/23/2019 BC2014_Session7
10/27
Referring to ropert/ of 5+6ects
1!
-
7/23/2019 BC2014_Session7
11/27
Appl/ing ethods
11
-
7/23/2019 BC2014_Session7
12/27
8or.ing 'ith Varia+les
A variable is a named element in a program thatcan +e used to store and retrieve data
2ver/ varia+le is identi&ed +/ a uni9ue variablename
Dim variableas type
12
-
7/23/2019 BC2014_Session7
13/27
Varia+les
Better to use Data /pes: Dimamount AsDouble Dimname AsString
5ther data t/pes: Boolean4 Byte4 Currency4 Date, etc
Default (no t/pe) is Variant
Option Explicit forces all varia+les to +e declared
Can modif/ scope (outside Su+s ; %unctions) Private, AsInteger
(onl/ current module) Public+illsaid AsCurrency
(availa+le to an/ module)
13
-
7/23/2019 BC2014_Session7
14/27
-
7/23/2019 BC2014_Session7
15/27
essage Bo3
sgBo3 rompt4 Buttons4 itle
15
-
7/23/2019 BC2014_Session7
16/27
-
7/23/2019 BC2014_Session7
17/27
8riting to a Sheet
ut the a+solute value of the varia+le %=in ro' 4column of the 8or.sheet named m/Sheet0
17
"or#sheets$%m&'heet().*an+e$%2()-/A.Abs$0)
-
7/23/2019 BC2014_Session7
18/27
Reading from a 8or.sheet
o read in a value4 use the 0Valuemethod4 appl/ing thesame ideas used for 'riting:
> ? 8or.sheets(@m/Sheet)0Range(@)0Value
18
-
7/23/2019 BC2014_Session7
19/27
8a/s to use 0Range ethod
Range(A!) ell A1
Range(A!:B) ells A1 throu+h /5
Range(C:D4=:E!#) A multilearea selection
Range(A:A) olumn A
Range(!:!) *o 1
Range(A:C) olumns A throu+h
Range(!:) *os 1 throu+h 5Range(!:!4":"4F:F) *os 1, 3, and 8
Range(A:A4C:C4%:%) olumns A, , and 0
1
-
7/23/2019 BC2014_Session7
20/27
-
7/23/2019 BC2014_Session7
21/27
Control Structures
Decisions
Ian/Date G 1o' !"enan/Date ? 1o'
En#I
2ext* %onsder ' 6 !$en 6 Else
21
-
7/23/2019 BC2014_Session7
22/27
Decisions(contHd0)
I$nde3 ? I !"en> ? > J !K ? VBA0S9r(>)
ElseI$nde3 ? ! !"enK ? VBA0S9r(>)
ElseI$nde3 ? !"enK ? >
Else> ? I
En#I
22
-
7/23/2019 BC2014_Session7
23/27
Decisions(contHd0)
SelectCasendex7ara&leCase8
statements6Case1 to 18
statements6Cases 9 8
statements6Case2um0teps
statements6CaseElse
statements6EndSelect
23
otice that the %cases( can
be constants, ran+ealues, conditions and
ariables this is a
oerful control structure
that e ill use to select
eents to e9ecute
-
7/23/2019 BC2014_Session7
24/27
,oopsL$terationsDoM$"ile%&ntil'Ncondition
statements(oop
lCount = 0
lNum = 10
Do Until lNum = 0
lNum = lNum 1 lCount = lCount + 1
Loop
lCount = 0
lNum = 10
Do While lNum > 0 lNum = lNum 1
lCount = lCount + 1
Loop
24
-
7/23/2019 BC2014_Session7
25/27
,oops(contHd0)
)orcounter ? start !oend OStepincrementPNstatements
*extcounter
otal -!
0or :;
-
7/23/2019 BC2014_Session7
26/27
23it Commands
Exit )unction: $mmediatel/ e3its the )unctionprocedure in 'hich it appears0
23ecution continues 'ith the statement follo'ing the statement that calledthe )unction0
Exit Sub+
$mmediatel/ e3its the Subprocedure in 'hich it appears0
23ecution continues 'ith the statement follo'ing the statement that calledthe Subprocedure0
26
-
7/23/2019 BC2014_Session7
27/27
han. K5