Simon Benninga. Financial Modelling 3-rd edition

Подождите немного. Документ загружается.

898 Chapter 37

= Parameter

3 + 1, and VBA created the variable Temp. However,

letting VBA create variables for you is not always a good idea, since a

small typing mistake can completely alter the results of a function (for

an example, see Function4E later in this section). A much better way of

using variables is to explicitly declare our intention before actually using

the variable. Variables are declared using the Dim statement. The follow-

ing function uses the Dim statement to declare “Temp” before its use.

Function Function4(Parameter)

Dim Temp

Temp = Parameter * 3 + 1

Function4 = Temp

End Function

AB C

Parameter Function

1.1667 4.5001

=Function3(A3)

1.1667 4.5001

=Function4(A4)

FUNCTIONS IN ACTION 2

We can make VBA alert us if we use an undeclared variable by insert-

ing the Option Explicit statement as the ﬁ rst line in the module. With

this statement any use of an undeclared variable will result in an error

and not the creation of a new variable. The Option Explicit statement

holds for all the routines in the module. Unfortunately there is no global

Option Explicit statement. You can have VBA insert the Option Explicit

statement to every new module. Select (Tools|Options . . .) from the VBA

menu, tick the Require Variable Declaration line, and click the OK

button.

899 Types and Loops

The following function contains a typing error:

Function Function4E(Parameter)

Dim Temp

remp = Parameter * 3 + 1

Function4E = Temp

End Function

Without the Option Explicit statement, Excel merrily displays the fol-

lowing result:

AB C

Parameter Function

1.1667 4.5001

=Function4(A3)

1.1667 0

=Function4E(A4)

FUNCTIONS IN ACTION 3

900 Chapter 37

However, inserting the Option Explicit statement before the VBA

code and recalculating the worksheet results in the following “Run Time

Error”:

Once we are alerted to the problem, we can click the OK button, stop

VBA from running, and ﬁ x the problem by replacing “remp” with “Temp.”

(Recall from Chapter 36 that after you ﬁ x the mistake in VBA, you have

to press the button with a small square on the VBA editor toolbar.)

Like values, variables have types. Variables declared without a speciﬁ c

type (like Temp) are given the type Variant. Using variables and func-

tions of the type Variant involves quite a lot of overhead. It is therefore

recommended that functions and variables be given a speciﬁ c type.

You can declare a variable of a speciﬁ c type by appending the reserved

word As to the variable declaration. The following function is an integer

version of Function4. We can now use this function in Excel and compare

the results with Function2 and Function4.

Function Function5(Parameter As Integer) As

Integer

Dim Temp As Integer

Temp = Parameter * 3 + 1

Function5 = Temp

End Function

1. Attentive readers will notice that we violate this rule quite often in this book! Oh

well . . .

901 Types and Loops

A Short List of VBA Types

Data Type Range

Byte 0 to 255

Boolean True or False

Integer

−32,768 to 32,767

Long (integer)

−2,147,483,648 to 2,147,483,647

Single (ﬂ oating point)

−3.403E38 to −1.401E-45 for negative values;

1.401E-45 to 3.403E38 for positive values

Double (ﬂ oating point)

−1.798E308 to −4.941E-324 for negative values;

4.941E-324 to 1.798E308 for positive values

Currency (scaled

integer)

−922,337,203,685,477.5808 to

922,337,203,685,477.5807

Decimal

±79,228,162,514,264,337,593,543,950,335 with no decimal point;

±7.9228162514264337593543950 with 28 places to the right of

the decimal

Date January 1, 100, to December 31, 9999

String (variable length) 0 to approximately 2 billion

String (ﬁ xed length) 1 to approximately 65,400

Variant (with numbers) Any numeric value up to the range of a Double

Variant (with characters) Same range as for variable-length String

AB C

Parameter Function

1.1667 5

=Function2(A3)

1.1667 4.5001

=Function4(A4)

1.1667 4

=Function5(A5)

1.16666 4

=Function2(A6)

1.16666 4.49998 <

=Function4(A7)

1.16666 4 <

=Function5(A8)

FUNCTIONS IN ACTION 4

37.4 Boolean and Comparison Operators

Usually the expressions used as conditions in an If statement are con-

structed using the Comparison and/or Boolean operators. The following

is a list of the most common comparison operators:

902 Chapter 37

Operator Meaning

Less than

≤

Less than or equal to

Greater than

≥

Greater than or equal to

Equal to

Not equal to

The next function uses a Boolean operator to check both whether

Parameter1 < 10 and whether Parameter2 > 15:

Function AndDemo(Parameter1, Parameter2)

If (Parameter1 < 10) And (Parameter2 > 15)

Then

AndDemo = 3

Else

AndDemo = 12

End If

End Function

Here are two illustrations:

AB C

Parameter1 3

? < 10 ?

Parameter2 12

? > 15 ?

AndDemo 12

=AndDemo(B2,B3)

ANDDEMO IN ACTION 1

AB C

Parameter1 3

? < 10 ?

Parameter2 33

? > 15 ?

AndDemo 3

=AndDemo(B2,B3)

ANDDEMO IN ACTION 2

903 Types and Loops

Notice what AndDemo does: It checks whether both Parameter1 < 10

and Parameter2 > 15. If both conditions hold, then the function returns

a value of 3. Otherwise (i.e., if either one of the conditions is violated)

it returns 12. (Note that both conditions are in parentheses.)

The following function and the activation screen shot demonstrate all

four possible combinations of two conditions and the resulting combined

condition:

Function AndDemoTable(Parameter1, Parameter2)

AndDemoTable = Parameter1 And Parameter2

End Function

ABC D

Parameter1 Parameter2

TRUE TRUE

TRUE <

=AndDemoTable(A3,B3)

TRUE FALSE

FALSE <

=AndDemoTable(A4,B4)

FALSE TRUE

FALSE <

=AndDemoTable(A5,B5)

FALSE FALSE

FALSE <

=AndDemoTable(A6,B6)

ANDDEMOTABLE IN ACTION

The function OrDemo, as follows, checks whether at least one of the

two conditions holds:

Function OrDemo(Parameter1, Parameter2)

If (Parameter1 < 10) Or (Parameter2 > 15)

Then

OrDemo = 3

Else

OrDemo = 12

End If

End Function

904 Chapter 37

Notice what OrDemo does: It checks whether either Parameter1 < 10

or Parameter2 > 15 or both conditions hold. Only if both conditions are

violated will the function return a value of 12. Otherwise (i.e., if either

one or both of the conditions hold) it returns 3. (Note that both condi-

tions are in parentheses.)

The following function and the activation screen shot demonstrate all

four possible combinations of two conditions and the resulting combined

condition:

ABC D

Parameter1 Parameter2

=OrDemo(A3,B3)

130

=OrDemo(A4,B4)

11 1

12 <

=OrDemo(A5,B5)

11 30

=OrDemo(A6,B6)

ORDEMO IN ACTION

Function OrDemoTable(Parameter1, Parameter2)

OrDemoTable = Parameter1 Or Parameter2

End Function

ABC D

Parameter1 Parameter2

TRUE TRUE

TRUE <

=OrDemoTable(A3,B3)

TRUE FALSE

TRUE <

=OrDemoTable(A4,B4)

FALSE TRUE

TRUE <

=OrDemoTable(A5,B5)

FALSE FALSE

FALSE <

=OrDemoTable(A6,B6)

ORDEMOTABLE IN ACTION

37.5 Loops

Looping structures are used when you need to do something repeatedly.

As always there is more than one way to achieve the desired effect. In

general there are two major looping constructs:

905 Types and Loops

•

A top-checking loop: The loop condition is checked before anything

else gets done. The something to be done can be left undone if the condi-

tion is not fulﬁ lled on entry to the loop.

•

A bottom-checking loop: The loop condition is checked after the some-

thing to be done is done. The something to be done will always be done

at least once.

VBA has the two major looping structures covered from all possible

angles by the Do statement and its variations. All the following subsec-

tions will use a version of the factorial function for demonstration pur-

poses. The function used is deﬁ ned as

ffff fnnfn() () () () ... ( ) ( )01 11 22 12 1===∗= =∗−

37.5.1 The Do While Statement

The Do While statement is a member of the top-checking loops

family. It makes VBA execute one or more statements zero or more

times while a condition is true. The following function demonstrates this

behavior:

Function DoWhileDemo(N As Integer) As Integer

Dim i, j As Integer

If N < 2 Then

DoWhileDemo = 1

Else

i = 1

j = 1

Do While i <= N

j = j * i

i = i + 1

Loop

DoWhileDemo = j

End If

End Function

906 Chapter 37

Notice that noninteger values get rounded to the nearest integer when

transferred to an integer parameter in the function. Also notice that the

function cannot handle values greater than 7, because—as explained in

section 37.3—the type Integer is restricted to values between −32,678

and +32,767. The following function solves the problem by using the

Long variable type, which allows larger integer values:

AB C

=DoWhileDemo(A3)

1.4 1

=DoWhileDemo(A4)

=DoWhileDemo(A5)

7 5040

=DoWhileDemo(A6)

#VALUE!

=DoWhileDemo(A7)

DOWHILEDEMO IN ACTION

Function DoWhileDemo1(N As Integer) As Long

Dim i As Integer

Dim j As Long

If N < 2 Then

DoWhileDemo = 1

Else

i = 1

j = 1

Do While i <= N

j = j * i

i = i + 1

Loop

DoWhileDemo = j

End If

End Function

AB C

=DoWhileDemo1(A3)

1.4 1

=DoWhileDemo1(A4)

=DoWhileDemo1(A5)

7 5040

=DoWhileDemo1(A6)

40320

=DoWhileDemo1(A7)

DOWHILEDEMO1 IN ACTION

907 Types and Loops

37.5.2 The Do . . . Loop While Statement

The Do . . . Loop While statement is a member of the bottom-checking

loops family. It makes VBA execute one or more statements one or more

times while a condition is true. The following function demonstrates this

behavior:

Function DoLoopWhileDemo(N As Integer) As Long

Dim i As Integer

Dim j As Long

If N < 2 Then

DoLoopWhileDemo = 1

Else

i = 1

j = 1

j = j * i

i = i + 1

Loop While i <= N

DoLoopWhileDemo = j

End If

End Function

AB C

=DoLoopWhileDemo(A3)

1.4 1

=DoLoopWhileDemo(A4)

=DoLoopWhileDemo(A5)

75040

=DoLoopWhileDemo(A6)

40320

=DoLoopWhileDemo(A7)

DOLOOPWHILEDEMO IN ACTION

37.5.3 The Do Until Statement

The Do Until is a member of the top-checking loops family. It makes

VBA execute one or more statements zero or more times until a condi-

tion is met. The following function demonstrates this behavior: