Simon Benninga. Financial Modelling 3-rd edition

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

959 Arrays

39.5 Array Assignment

Here’s an error that’s easy to make: In the following example we want

to tell VBA that Array2 is equal to Array1. VBA doesn’t allow us to do

so, as you can see:

Obviously one way to assign arrays is to assign each element sepa-

rately using a For loop.

Sub ArrayAssign()

Dim Array1(5), Array2(4), I As Integer

Dim Temp As String

Temp = “”

For I = 0 To 5: Array1(I) = I * I: Next I

For I = 0 To 5

Temp = Temp & Array1(I) & “:”

Next I

I = Len(Temp)

Temp = Left(Temp, I - 1)

960 Chapter 39

Note the following:

1. The use of the : operator to signal the end of a statement (line 5). This

way we can put two or more short statements on the same line.

2. The use of the Len function to get the length of a string (line 9).

3. The use of the Left function to get the left part of a string (line 18).

(Yes, a Right function is available.)

Another, much shorter, way of assigning arrays is discussed in the next

section.

39.6 Variants Containing an Array

A Variant type variable can contain an array. The procedure is somewhat

more complicated than the declaration of a normal array, but the reward

in terms of assignment is sometimes worth the inconvenience (and the

MsgBox Temp, , “Array1”

For I = 0 To 4: Array2(I) = Array1(I):

Next I

Temp = “”

For I = 0 To 4

Temp = Temp & Array2(I) & “:”

Next I

I = Len(Temp)

Temp = Left(Temp, I - 1)

MsgBox Temp, , “Array2”

End Sub

961 Arrays

cost in program memory allocated to the array). The following macro

demonstrates the use of a Variant containing an array:

Sub VarArrayAssign()

Dim Array1 ‘This is a variant

Dim I As Integer, Temp As String

Array1 = Array()

Temp = “”

ReDim Array1(5) As Integer

For I = LBound(Array1) To UBound(Array1)

Array1(I) = I * I

Next I

For I = LBound(Array1) To UBound(Array1)

Temp = Temp & Array1(I) & “:”

Next I

I = Len(Temp)

Temp = Left(Temp, I - 1)

MsgBox Temp, , “Array1”

End Sub

The Array() function (on the fourth line) returns an array. The assign-

ment on the same line makes Array1 into an array (not initialized at the

moment). The ReDim statement two lines down makes Array1 into a

ﬁ ve-element Integer array.

And now for the reward, as demonstrated by the following macro

(compare the macro with ArrayAssign in the preceding section):

962 Chapter 39

Sub VarArrayAssign()

Dim Array1 ‘This is a variant

Dim Array2 ‘This is a variant

Dim I As Integer

Dim Temp As String

Array1 = Array()

Temp = “”

ReDim Array1(5) As Integer

For I = LBound(Array1) To UBound(Array1)

Array1(I) = I * I

Next I

For I = LBound(Array1) To UBound(Array1)

Temp = Temp & Array1(I) & “:”

Next I

I = Len(Temp)

Temp = Left(Temp, I - 1)

MsgBox Temp, , “VarArray1”

Temp = “”

‘******************** Watch this spot

Array2 = Array1 ‘Watch this spot

‘********************Watch this spot

For I = LBound(Array2) To UBound(Array2)

Temp = Temp & Array2(I) & “:”

Next I

I = Len(Temp)

Temp = Left(Temp, I - 1)

MsgBox Temp, , “VarArray2”

End Sub

963 Arrays

39.7 Arrays as Parameters to Functions

Arrays can be used as parameters to functions. The following set of

functions and subroutines presents an improved version of MoreDynPV.

Notice how mach easier it is to read the main macro NewDynPV

when all the auxiliary tasks are relegated to separate functions and

subroutines.

A function ComputePV(CF(), n) is used to compute the present value

of a series of cash ﬂ ows contained in an array of Doubles.

Function ComputePV(CF() As Double, n As

Integer) As Double

Dim Temp As Double, i As Integer

Temp = 0

For i = 1 To n

Temp = Temp + CF(i) / 1.05 ^ i

Next i

ComputePV = Temp

End Function

A subroutine GetCF(CF(), n) is used to query the user for the cash

ﬂ ow values and store them in the CF array.

Sub GetCf(CF() As Double, n As Integer)

Dim i As Integer

For i = 1 To n

CF(i) = InputBox(“Enter value for period “ _

& i, “Present value calculator”, CF(i))

Next i

End Sub

964 Chapter 39

Note the fact that in both ComputePV(CF(), n) and GetCF(CF(), n),

CF() has to be declared without index information. Consequently, index

information has to be transferred to the function. We could use Lbound

and Ubound as an alternative to providing index information.

This is the main macro:

Sub NewDynPV()

Dim n As Integer ‘ Number of periods

Dim OldN As Integer ‘ Old number of periods

Dim CF() As Double ‘ Dynamic array for cash ﬂ ows

Dim Temp As Double

Dim i As Integer, Temp1 As Integer

Dim FirstTime As Boolean

FirstTime = True

Do ‘***************************Begin main loop

OldN = n

n = GetN()

If FirstTime Then

ReDim CF(1 To n) ‘ Redimension the array

‘ No old data to keep

FirstTime = False ‘ not the ﬁ rst time anymore

Else

If KeepOld() Then

ReDim Preserve CF(1 To n)

‘ Redimension

‘ the array and keep the old data

Else

ReDim CF(1 To n) ‘ Redimension

the array

‘ do not keep the old data

End If

GetCf CF(), n

Temp = ComputePV(CF, n)

DisplayPV (Temp)

Loop While AnotherGo() ‘*****************End main loop

End Sub

965 Arrays

Here are the auxiliary functions and subroutines, dealing with all sorts

of maintenance tasks:

Function GetN() As Integer

GetN = InputBox(“Enter number of periods”, _

“Present value calculator”, 3)

End Function

Function AnotherGo() As Boolean

Dim Temp As Integer

Temp = MsgBox(“Give it another go?”, vbQuestion _

+ vbYesNo, “Present value calculator”)

AnotherGo = (Temp = vbYes)

End Function

Function KeepOld() As Boolean

Dim Temp As Integer

Temp = MsgBox(“Keep old Data?”, vbQuestion _

+ vbYesNo, “Present value calculator”)

KeepOld = (Temp = vbYes)

End Function

Sub DisplayPV(PV As Double)

MsgBox “Present value is: “ & PV, vbInformation, _

“Present value calculator”

End Sub

966 Chapter 39

967 Arrays