Simon Benninga. Financial Modelling 3-rd edition

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

938 Chapter 38

Here is an example of the requested macro in action:

6. Rewrite the payment schedule calculator macro so that it displays the payments

broken down to interest and capital payments. The input boxes in the example were

removed for compactness.

939 Macros and User Interaction

7. Write a payment schedule calculator macro. The macro is to ask the user for the

sum of the loan, the payment, and the interest rate. Assume payment at the end of

the period. The macro should display the payments broken down to interest and

capital payments. Obviously, the last payment can be smaller (but not larger) than

the payment supplied by the user. The output should look like the following example

(input boxes removed for compactness):

940 Chapter 38

8. A somewhat more complicated version of the macro in exercise 7 would produce

the following better-looking results. Write this version of the macro. Note: A quick

look at the Help ﬁ le for the Format function might be advantageous at this point.

9. A sliding payment schedule involves payment that changes by a ﬁ xed percent over

the life of the loan. Write a sliding-payment version of the payment schedule calcula-

tor in exercise 8. In addition to all the inputs described previously, the macro will

get a payment rate of change (as percent) from the user. This is what it should look

like in action:

Arrays

39.1 Overview

This chapter deals with arrays. An array is a group of variables of the

same type sharing the same name and referenced individually using

an index. Vectors and matrices are good examples of one- and two-

dimensional arrays. The ﬁ rst part of the chapter presents simple arrays.

Dynamic arrays (whose size can be changed at run time) are discussed

in the second part of the chapter. The chapter concludes with a section

on the use of arrays as parameters, including a subsection on the relation-

ship between arrays and worksheet ranges.

39.2 Simple Arrays

There are several ways to declare arrays, all using the Dim statement.

The simplest way to declare an array is simply to tell VBA the largest

value the array index can take. Unless you indicate otherwise, VBA

arrays always start with index 0. In the following macro, MyArray has

six elements numbered 0, 1, 2, . . . , 5.

Sub ArrayDemo1()

Dim MyArray(5)

Dim i As Integer

Dim Temp As String

For i = 0 To 5

MyArray(i) = i * i

Next i

Temp = “”

For i = 0 To 5

Temp = Temp & “ # “ & MyArray(i)

Next i

MsgBox Temp

End Sub

If you use ArrayDemo1 in a spreadsheet, here is the result:

942 Chapter 39

Notice the following:

•

MyArray has six elements (variables), the ﬁ rst being MyArray(0) and

the last MyArray(5). All Excel arrays start from 0, unless you specify

otherwise (see discussion of Option Base in section 39.2.3).

•

An array element is treated just like a variable; that is, MyArray(2) is

a variable and so is MyArray(i-3) (assuming that i-3 has an integer value

>=0 and <=5).

•

The use of the concatenation operator &. This operator concatenates

(combines) its two operands to create a string. If an operand to the con-

catenation operator is not a string, it is converted to a string, and then

the concatenation takes place.

If you try to access an array element that is not part of the array, VBA

will complain, as demonstrated by the following macro:

Sub ArrayDemo2()

Dim MyArray(5)

Dim i As Integer

i = 6

MsgBox MyArray(i)

End Sub

943 Arrays

For now just click the End button and try to correct the problem. A

full treatment of the debugger is beyond the scope of this book.

39.2.1 LBound and UBound

LBound and UBound are two internal VBA functions that are very

useful when dealing with arrays. These functions return the minimum

value (LBound) and maximum value (UBound) that an array index can

have. The following macro demonstrates their use; the message box tells

us that MyArray has lowest index 0 and highest index 5.

Sub ArrayDemo3()

Dim MyArray(5)

MsgBox “Minimum value for MyArray index is: ” _

& LBound (MyArray) & Chr (13) _

& “Maximum value for MyArry index is: ” _

& UBound(MyArray), , “Lbound functions demo”

End Sub

944 Chapter 39

Note the use of Chr(13) to break the message line.

39.2.2 Explicitly Declared Index Boundaries

On occasion it is clearer to have an array with the index starting not at

zero. You can create such an array by specifying a starting and ending

value for the index. This approach is demonstrated in the following

function:

Sub ArrayDemo4()

Dim MyArray(6 To 10)

Dim i As Integer

MsgBox “Index of MyArray Starts at:” & _

LBound(MyArray)

MsgBox “Index of MyArray Stops at:” & _

UBound(MyArray)

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

MyArray(i) = i * i

Next i

MsgBox “The Value in MyArray(7) is: “ & _

MyArray(7)

End Sub

And here is the output in quick succession (click the OK button to close

a message box and move to the next one):

945 Arrays

39.2.3 The Option Base Statement

Since most Excel (as opposed to VBA) array indices start at one, we can

use a module option to make all not speciﬁ cally declared array indices

start at one. The following module demonstrates this procedure:

Option Explicit

Option Base 1

Sub ArrayDemoBase1()

Dim MyArray(5)

MsgBox “Minimum value for MyArray index is: ” _

& LBound (MyArray) & Chr (13) _

& “Maximum value for MyArry index is: ” _

& UBound(MyArray), , “?bound functions demo”

End Sub

946 Chapter 39

The Option Base 1 statement, like all options statements, should be

inserted before all functions and subroutines in a module. Like all option

statements, its effect is limited to all routines in the current module.

39.3 Multidimensional Arrays

Arrays can have more than one index. In a two-dimensional array the

ﬁ rst index refers to the rows and the second to the columns. There is no

formal limit to the number of indices you can declare in an array. The

syntax for declaring a multidimensional array is demonstrated in the

following macros; and so is the fact that array elements can (or indeed

should) be typed.

Sub Matrix1()

Dim MyMat(2, 2) As Integer

Dim I, J As Integer

Dim Temp As String

For I = 0 To 2

For J = 0 To 2

MyMat(I, J) = I * J

Next J

Next I

Temp = “”

For I = 0 To 2

For J = 0 To 2

Temp = Temp & MyMat(I, J) & “ # “

Next J

Temp = Temp & Chr(13)

Next I

MsgBox Temp

End Sub

Here’s the output of this macro:

947 Arrays

As the following macro demonstrates, we can use explicit index declara-

tion for one or more of the dimensions in multidimensional arrays.

Sub Matrix2()

Dim MyMat1(1 To 3, 1 To 2) As Integer

Dim I, J As Integer

Dim Temp As String

For I = 1 To 3

For J = 1 To 2

MyMat1(I, J) = I * J

Next J

Next I

Temp = “”

For I = 1 To 3

For J = LBound(MyMat1, 2) To

UBound(MyMat1, 2)

Temp = Temp & MyMat(I, J) & “ # “

Next J

Temp = Temp & Chr(13)

Next I

MsgBox Temp

End Sub