Simon Benninga. Financial Modelling 3-rd edition
Подождите немного. Документ загружается.
948 Chapter 39
Note the use of the second argument to LBound and UBound. If used
with only one argument, both functions return the largest index value
the fi rst dimension of the array can have. If the array has more than one
dimension (as in this case) we can use a second argument to the function
to specify the dimension we are interested in (the second dimension, in
this case).
39.4 Dynamic Arrays and the ReDim Statement
Every so often it can be handy to have the size of an array set (and reset)
when the program is running. Dynamic arrays are arrays that can have
their size changed at run time. You declare dynamic arrays using the Dim
statement but with nothing in the parentheses, as in
Dim SomeName() AsSomeType
For example, you might type the following to declare a dynamic array
of doubles called Prices:
Dim Prices() As double
949 Arrays
For example, you might type the following:
ReDim ArrayName(SomeIntegerExpression)
ReDim Prices(12)
To set the size of the dynamic array of doubles Prices to 12 elements,
a more typical case would involve the use of a variable for the size as in
the following:
Dim I as Integer
‘ I gets a value here some how
ReDim Prices(I)
This will set the size of Prices to the value of I.
The ReDim Statement can also be used to change the size of a dynamic
array (or indeed any array). If you change the size of an array, all the
data in the array are lost. Use ReDim Preserve to keep the old data, as
in
ReDim Preserve ArrayName(SomeIntegerExpression)
Before you can use the array you need to set its size using
the ReDim Statement, as in
950 Chapter 39
Running the macro produces the following in quick succession. Click
OK to move from one message box to the next.
Sub DynPV()
Dim n As Integer ‘ Number of periods
Dim CF() As Double ‘ Dynamic array for cash
fl ows
Dim Temp As Double
Dim i As Integer
n = InputBox(“Enter number of periods”, _
“Present value calculator”, 3)
ReDim CF(1 To n) ‘ redimension the array
‘ for demonstration purpose only
MsgBox “Index starts at: “ & LBound(CF) & _
“ and stops at: “ & UBound(CF), _
vbInformation, “Present value calculator”
For i = 1 To n
CF(i) = InputBox(“Enter value for period “ & _
i, “Present value calculator”, 0)
Next i
Temp = 0
For i = 1 To n
Temp = Temp + CF(i) / 1.05 ^ i
Next i
MsgBox “Present value is: “ & Temp,
vbInformation, _
“Present value calculator”
End Sub
The following macro calculates the present value of a series of future
cash fl ows. To simplify the macro, the interest rate is fi xed at 5 percent
per period. The macro illustrates the use of a dynamic array (the variable
CF) that derives its size from user input (the variable n).
951 Arrays
Notice that the 3 in the input box was provided by VBA as a default and
that it is preselected, and so we can type a replacement value without
selecting and clearing the default value.
952 Chapter 39
Trying to use a dynamic array that has not been initialized produces
the following result:
953 Arrays
39.4.1 Using the ReDim Preserve Statement
As stated previously, the Preserve part of the ReDim statement pre-
vents the loss of data from the redimensioned array. The use of Preserve
imposes two major limitations on the use of ReDim.
•
The inability to change the lower boundary of the index.
•
The inability to change the number of dimensions.
The following macro is an interactive present-value calculator. It asks
the user for the number of periods. Using the information supplied by
the user, the macro redimensions an array to hold the cash fl ows for the
periods. Once a present value has been calculated, the user is offered
the choice of either stopping the macro or calculating the present value
of another series of cash fl ows. As a by-product the macro demonstrates
the use of ReDim and Preserve.
Sub DynaArrayDemo1()
Dim DynArray() As Integer
MsgBox DynArray(0)
End Sub
954 Chapter 39
Sub MoreDynPV()
Dim n, OldN As Integer ‘ Number of periods
Dim CF() As Double ‘ Dynamic array for cash fl ows
Dim Temp As Double
Dim i, Temp1 As Integer
Do ‘******Begin main program loop
OldN = n
n = InputBox(“Enter number of periods”, _
“Present value calculator”, 3)
Temp1 = MsgBox(“Keep old Data?”, _
vbQuestion + vbYesNo, _
“Present value calculator”)
If Temp1 = vbYes 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
For i = 1 To n
CF(i) = InputBox(“Enter value for period “ _
& i, “Present value calculator”, CF(i))
Next i
Temp = 0
For i = 1 To n
Temp = Temp + CF(i) / 1.05 ^ i
Next i
MsgBox “Present value is: “ & Temp, _
vbInformation, _
“Present value calculator”
Temp1 = MsgBox(“Give it another go?”, _
vbQuestion + vbYesNo, _
“Present value calculator”)
Loop While Temp1 = vbYes ‘End main program loop
End Sub
955 Arrays
One possible activation of the macro might look like the following
screen shots, in which the button that has the arrow on it was clicked.
956 Chapter 39
957 Arrays