Simon Benninga. Financial Modelling 3-rd edition

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

968 Chapter 39

969 Arrays

39.7.1 Arrays from a Worksheet

Arrays bear some resemblance to worksheet ranges. We might want to

write a function that accepts a range as a parameter. Unfortunately the

resemblance is only skin deep, and some manipulation is needed to

achieve our goal. A full discussion of worksheet objects is beyond the

scope of this book. The following function (based on ComputePV from

the previous section) can actually accept a column range of cells as a

parameter, because its parameter is deﬁ ned as Variant. A range of cells

is always a two-dimensional array (even if it is only a row or a column);

the indices always run from one and not from zero.

Function VarPV(CF As Variant) As Double

Dim X As Variant, Temp As Double, i As

Integer

X = CF

Temp = 0

For i = LBound(X) To UBound(X)

Temp = Temp + X(i, 1) / 1.05 ^ i

Next i

VarPV = Temp

End Function

Note that the declaration of the internal variable “X as Variant” is

necessary, and so is the assignment “X = CF” on the third line. Here is

how VarPV looks in Excel:

970 Chapter 39

Notice the error value in cell B7. Our function expects an array, and

a single cell (even if it represents itself as a range A7 : A$7) is not an

array.

If we want to emulate the way the worksheet function NPV works,

then our function has to be modiﬁ ed.

AB C D E

VarPV NPV

100 432.9477 <

=VarPV(A3:A$7) 432.9477 <

=NPV(0.05,A3:A$7)

100 354.5951 <

=VarPV(A4:A$7) 354.5951 <

=NPV(0.05,A4:A$7)

100 272.3248 <

=VarPV(A5:A$7) 272.3248 <

=NPV(0.05,A5:A$7)

100 185.9410 <

=VarPV(A6:A$7) 185.9410 <

=NPV(0.05,A6:A$7)

100 #VALUE! <

=VarPV(A7:A$7) 95.2381 <

=NPV(0.05,A7:A$7)

VARPV IN ACTION

Function NewVarPV(CF As Variant) As Double

If IsArray(CF) Then

NewVarPV = VarPV(CF) ‘use the old

function

Else

NewVarPV = CF / 1.05

End If

End Function

AB C D E

VarPV NPV

100 432.9477 <

=NewVarPV(A3:A$7) 432.9477 <

=NPV(0.05,A3:A$7)

100 354.5951 <

=NewVarPV(A4:A$7) 354.5951 <

=NPV(0.05,A4:A$7)

100 272.3248 <

=NewVarPV(A5:A$7) 272.3248 <

=NPV(0.05,A5:A$7)

100 185.9410 <

=NewVarPV(A6:A$7) 185.9410 <

=NPV(0.05,A6:A$7)

100 95.2381 <

=NewVarPV(A7:A$7) 95.2381 <

=NPV(0.05,A7:A$7)

NEWVARPV IN ACTION

Notice the use of the VBA function IsArray to determine whether a

variable is an array. As a rule, always check function parameters to see

whether they comply with the functions expectation before doing any-

thing else in the function.

971 Arrays

39.8 Summary

An array is a group of variables of the same type, sharing the same name,

and referenced individually using one or more indices. In VBA an array

index is an integer. By default the index of the ﬁ rst element in an array

is 0; this can be changed to 1 for all arrays used in a module using the

Option Base 1 statement. The size and number of dimensions of an array

are set at the time the array is declared and has to be known when the

program is written. Dynamic arrays are arrays whose size (but not

number of dimensions) can be set at run time.

Exercises

1. Write a version of the present value function VarPV(CF()) that will work with

column and row ranges. (Recall that the original version works only on column

ranges.)

Hint: Remember that

•

Ranges are always two-dimentional arrays.

•

The lower bound for ranges is always 1.

•

The upper bound is the boundary that sets the size of the array.

ABCD E

100 100 100 272.325 <

=RCMyPv(A2:C2)

100

272.325 <

=RCMyPv(A2:A4)

RCMYPV IN ACTION

2. Write a version of the present value function with two interest rates, one for positive

cash ﬂ ows and another for negative cash ﬂ ows. The function should be written for

use in a worksheet and should accept both column and row ranges as parameters.

The function declaration line should be

Function MyPV(CF As Variant, PositiveR As Double, _

NegativeR As Double) As Double

972 Chapter 39

3. Write a future-value version of the function in exercise 1.

4. Write a future-value version of the function in exercise 2.

5. A bank offers different interest rates on loans. The rate is based on the size of the

periodical repayment (CF

) and the following table. Write a present-value function

BankPV(CF, r) so that it reﬂ ects the present value of a loan in the bank. The func-

tion should be usable as a worksheet function. CF could be either a row range or a

column range.

ABCDE F G

PositiveR 5% 100 100 100 272.3248 <

=MyPV(C2:E2,$B$2,$B$3)

NegativeR 10% -100 -100 -100 -248.6852 <

=MyPV(C3:E3,$B$2,$B$3)

-100 100 100 86.17762 <

=MyPV(C4:E4,$B$2,$B$3)

-63 <

=MyPV(C2:C4,$B$2,$B$3)

MYPV IN ACTION

For Periodical Repayments ≤

The Interest Rate Is

100.00

500.00

r − 0.5%

1,000.00

r − 1.1%

5,000.00

r − 1.7%

1,000,000.00

r − 2.1%

6. A bank offers different interest rates on deposit accounts. The rate is based on the

size of the periodical deposit (CF

) and the following table. Write a future-value

function BankFV(CF, r). The function should be usable as a worksheet function. CF

could be either a row range or a column range.

For Periodical Deposits The Interest Rate Is

≤100.00 r

≤500.00 r + 0.5%

≤1,000.00 r + 1.1%

≤5,000.00 r + 1.7%

>5,000.00 r + 2.1%

973 Arrays

7. Another bank offers a 1 percent increase in interest rate for savings accounts with

a balance of more than 10,000.00. Write a future-value function Bank1FV(CF, r)

that reﬂ ects this policy. The function should be usable as a worksheet function. CF

could be either a row range or a column range.

8. The bank in exercise 7 changed its bonus policy and now offers the interest rate

increase based on the following table. Rewrite Bank1FV(CF, r, n) to reﬂ ect this

change.

Balance Interest Rate

≤1,000.00 r + 0.2%

≤5,000.00 r + 0.5%

≤10,000.00 r + 1.0%

>10,000.00 r + 1.3%

Objects and Add-Ins

40.1 Overview

This chapter deals with several of the more advanced subjects in VBA.

Most of these subjects relate to the Excel Object Model. The bulk of the

chapter describes some useful Excel objects and ways of dealing with

them. Names, a way to make worksheets clearer and more readable, are

presented in section 40.6. The use of VBA function across workbook

boundaries is covered in sections 40.8 and 40.9. The chapter closes with

a discussion of Excel add-ins, one easy way to make self-crafted functions

automatically available across workbooks.

40.2 An Introduction to Worksheet Objects

Objects are the basic building blocks of VBA. Although you may not be

aware that you are using objects, most things you do in VBA require the

manipulation of objects. We can think of an object as a sort of a container

with variables, functions, and subroutines inside. All of Excel’s compo-

nents (workbooks, worksheets, ranges, and so on) are represented by an

object in the VBA Object Hierarchy (see appendix 1 for a chart). The

object’s data are held in special variables called properties that can be

accessed using the dot (.) operator.

40.2.1 The Active Cell Range Object Variable

VBA has many variables predeﬁ ned for our use; one of the more useful

ones is ActiveCell. ActiveCell is a predeﬁ ned Range Object variable that

represents the cell in the worksheet with the cursor box around it. The

following macro uses ActiveCell and three of its properties: Address,

Formula, and Value.

Sub ActiveCellDemo( )

Dim Msg As String

Dim Title As String

Title = “Active Cell Reporting” ‘ Title for MsgBox

Msg = “You are at:” ‘ Start line one of message

Msg = Msg & ActiveCell.Address ‘ Add address of

‘ active cell

976 Chapter 40

The macro ActiveCellDemo ﬁ rst deﬁ nes two string variables, one for

the title of the message box and one for the message itself. We set the

message variable in stages, adding bits to the string as we go along. In

the process we use the values of three properties of ActiveCell. The ﬁ rst

property we use is Address, which holds the absolute address of a range

as a string. Obviously this property is read-only and cannot be changed.

The second property we use is Formula, which holds the text in the cell

as a string and can be changed. The third property we use is Value, which

holds the value in the cell as a variant and can be changed. We use the

fact that the concatenation operator (&) converts its operands to strings

to successively add values to the variable Msg. Once Msg is all set we

use MsgBox to display it on screen.

The last line in the macro uses the Formula property again, but this

time we change its value and add *2 to the end; this multiplies the last

element in the formula by 2. Thus, if you have a formula =A2+3 in the

active cell, the new formula will be =A2+3

2. If you run the macro again

on the same cell, the new formula will be = A2+3

The Macro produces the following results.

Msg = Msg & Chr(13) ‘ Add a carriage Return

‘ The next line starts line two of message

Msg = Msg & “The Formula in the cell is:”

‘ The next line Adds the text of the formula

‘ to the message

Msg = Msg & ActiveCell.Formula

Msg = Msg & Chr(13) ‘ Add a carriage return

‘ The next line starts line three of Message

Msg = Msg & “The value of the cell is:”

Msg = Msg & ActiveCell.Value ‘ Add the value in

‘ the cell

MsgBox Msg, vbInformation, Title ‘ Report to user

‘ The next line adds “* 2” to the text in the

‘ cell

ActiveCell.Formula = ActiveCell.Formula & “*2”

End Sub

1. If A1 is an address, then $A$1 is an absolute address.

977 Objects and Add-Ins

If you subsequently click OK on the dialogue box, you will see that

the formula in the cell has been changed:

40.2.2 Some Methods Using the Active Cell Range Object Variable

Methods are functions contained within an object. Methods are used to

manipulate the object. Like properties, methods can be accessed using

the dot (.) operator. The line between methods and properties is some-

times very fuzzy. In the following macro we use three of the methods

available in a Range Object to move and change the border around the

active cell: