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