留学生考试辅导 FINA0404/3351 􏰁 Spreadsheet Modelling in Finance

Excel Modeling: VBA Arrays
Dr. . CHUNG
FINA0404/3351 􏰁 Spreadsheet Modelling in Finance

Copyright By PowCoder代写 加微信 powcoder

So far, single-element variables:
each holds only one piece of data at a time, Dim StkPrice As Double
Cells(1,3) = 17
Cells(2,3) = 22
􏰂􏰃􏰄􏰅 􏰇􏰈􏰉 􏰄 􏰊􏰋 􏰌􏰍􏰄􏰎􏰅􏰇􏰅􏰏􏰐 􏰑􏰒􏰋􏰒􏰐 253 obs􏰓need 253 single-element variables to store and manipulate

So, single-element variables:
each holds only one piece of data at a time, Dim StkPrice As Double
StkPrice = 17 Cells(1, 3) = StkPrice
􏰂􏰃􏰄􏰅 􏰇􏰈􏰉 􏰄 􏰊􏰋 􏰌􏰍􏰄􏰎􏰅􏰇􏰅􏰏􏰐 􏰑􏰒􏰋􏰒􏰐 2 obs􏰓need 2 single-element variables to store and manipulate: VBA array
Sub StkPriceArray()
Dim StkPrice(2) As Double
StkPrice(1) = 17 StkPrice(2) = 23 Cells(6, 6) = StkPrice(1) Cells(7, 6) = StkPrice(2)

a lg quantity, e.g., 253 obs􏰓need 253 single- element variables to store and manipulate: VBA array
An array to hold 253 prices
Syntax: Dim StkPrice(252) As Double
the (#) syntax
() = array v. var has no (), # = size
Can do this if without size: Dim StkPrice() As Double

Assign Value: 2 ways
StkPrice(1) = 17, StkPrice(2) = 23 or StkPrice= Array(17, 23)
􏰔􏰊􏰕􏰖􏰐 􏰗􏰄􏰎 􏰘􏰖 􏰅􏰃􏰇􏰕􏰉
Sub SingleArray()
Dim StkPrice() As Variant
StkPrice = Array(17, 23, 22)
􏰙􏰄􏰎􏰋􏰑􏰚􏰛􏰜􏰝􏰞􏰟 􏰠 StkPrice(1)
􏰙􏰄􏰎􏰋􏰑􏰚􏰛􏰜􏰡􏰞􏰟 􏰠 StkPrice(2)
􏰙􏰄􏰎􏰋􏰑􏰚􏰛􏰜􏰢􏰞􏰟 􏰠 StkPrice􏰚􏰣􏰟 􏰤􏰥 􏰘􏰑􏰊􏰇􏰦􏰑􏰧􏰄􏰅􏰑 􏰎􏰖􏰅 􏰨􏰍􏰅􏰅􏰇􏰎􏰋 􏰅􏰃􏰇􏰕 􏰇􏰎 􏰖􏰧􏰘􏰑􏰧

So, arrays = a group of var. of the same type with
the same name
Again, dim StkPrice(#)
(): without size
(253): size of 254
Note: 253 can hold 254 values [index starts from 0]
2-dimension array: (2,253): size of 3 rows and 254 cols

Assign Value: 2 ways
For text Sub S()
Dim S As Variant
􏰩 􏰠 􏰛 􏰙􏰐 􏰂􏰐 􏰪􏰐 􏰫􏰐 􏰬􏰞 Dim Col() As String Col = Split(S, “,”) Range(“C1”) = Col(1) Range(“C2”) = Col(2) Range(“C3”) = Col(5) Range(“C4”) = Col(3) Range(“C5”) = Col(4)
􏰭􏰭􏰭 􏰅􏰃􏰇􏰕 􏰘􏰖􏰑􏰕􏰎􏰮􏰅 􏰑􏰯􏰄􏰗􏰅􏰊􏰏 􏰰􏰖􏰧􏰱􏰐 􏰰􏰃􏰏􏰲

Arrays: Try one exercise 1st
Single Dimension Array Sub SingleArray()
Dim StkPrice(2) As Single StkPrice(0) = 17 StkPrice(1) = 23 StkPrice(2) = 22 􏰙􏰄􏰎􏰋􏰑􏰚􏰛􏰫􏰝􏰞􏰟 􏰠 StkPrice(1) 􏰙􏰄􏰎􏰋􏰑􏰚􏰛􏰫􏰡􏰞􏰟 􏰠 StkPrice(2) 􏰙􏰄􏰎􏰋􏰑􏰚􏰛􏰫􏰢􏰞􏰟 􏰠 StkPrice(0)

Arrays Exercises, 2 more
Sub MultiArray()
Dim PE(1, 2) As Single
􏰤 􏰅􏰃􏰇􏰕 􏰄􏰗􏰅􏰍􏰄􏰊􏰊􏰏 􏰳􏰑􏰄􏰎 􏰡􏰯􏰝 PE(0, 0) = 17
PE(0, 1) = 22
PE(0, 2) = 23
PE(1, 0) = 5
PE(1, 1) = 4
PE(1, 2) = 10 Range(“A1”) = PE(0, 0) Range(“A2”) = PE(0, 1) Range(“A3”) = PE(0, 2) Range(“B1”) = PE(1, 0) Range(“B2”) = PE(1, 1) Range(“B3”) = PE(1, 2)
Sub Multi3DArray() Dim PE(2, 2) As Single
􏰤􏰅􏰃􏰇􏰕 􏰄􏰗􏰅􏰍􏰄􏰊􏰊􏰏 􏰳􏰑􏰄􏰎 􏰢􏰯􏰢􏰉 I am very confused
PE(0, 0) = 17
PE(0, 1) = 22
PE(0, 2) = 23
PE(1, 0) = 5
PE(1, 1) = 4
PE(1, 2) = 10
PE(2, 0) = PE(0, 0) / PE(1, 0) PE(2, 1) = PE(0, 1) / PE(1, 1) PE(2, 2) = PE(0, 2) / PE(1, 2) Range(“A1”) = PE(0, 0) Range(“A2”) = PE(0, 1) Range(“A3”) = PE(0, 2) Range(“B1”) = PE(1, 0) Range(“B2”) = PE(1, 1) Range(“B3”) = PE(1, 2) Range(“C1”) = PE(2, 0) Range(“C2”) = PE(2, 1) Range(“C3”) = PE(2, 2)

􏰴􏰨􏰅􏰇􏰖􏰎 􏰪􏰄􏰕􏰑 􏰝􏰉 􏰥 􏰵􏰴 􏰶􏰷􏰥􏰩􏰸
􏰹􏰖􏰅􏰑􏰺 􏰑􏰯􏰗􏰑􏰊 􏰕􏰅􏰄􏰧􏰅􏰕 􏰄􏰅 􏰨􏰖􏰕􏰇􏰅􏰇􏰖􏰎 􏰛􏰝􏰻 􏰼 􏰽􏰪􏰔 􏰕􏰅􏰄􏰧􏰅􏰕 􏰄􏰅 􏰛􏰣􏰻
Technically, you do Dim StkPrice 􏰚􏰡􏰟 􏰇􏰎 􏰽􏰪􏰔􏰐 􏰤􏰇􏰳􏰨􏰊􏰏􏰇􏰎􏰋 􏰅􏰃􏰄􏰅 there are 3 observations in the array
If you want VBA to start with the 1st 􏰘􏰄􏰅􏰄 􏰨􏰖􏰇􏰎􏰅 􏰄􏰅 􏰝􏰉 􏰎􏰑􏰑􏰘 􏰅􏰖 􏰔􏰵􏰵 􏰛Option Base 1􏰻 􏰄􏰅 􏰅􏰃􏰑 􏰦􏰑􏰋􏰇􏰎􏰎􏰇􏰎􏰋 􏰖􏰈 􏰅􏰃􏰑 􏰳􏰖􏰘􏰍􏰊􏰑􏰐 􏰧􏰇􏰋􏰃􏰅 􏰄􏰈􏰅􏰑􏰧 􏰛􏰴􏰨􏰅􏰇􏰖􏰎 􏰜􏰯􏰨􏰊􏰇􏰗􏰇􏰅􏰻
Or, Dim StkP(2017 To 2019) As Single
the array holds sales number for years 2017 to 2019
􏰉􏰄􏰦􏰊􏰑 􏰅􏰖 􏰧􏰑􏰈􏰑􏰧 􏰅􏰃􏰑 StkP for a particular year (more easily by using the year as the index, e.g., StkP(2001).
􏰩􏰖􏰐 􏰎􏰖 􏰛􏰴􏰨􏰅􏰇􏰖􏰎 􏰪􏰄􏰕􏰑 􏰝􏰻
Using the same concept, we can do StkP(1 to 3) and index the array

Dynamic Arrays
Option base 1
Sub stkprice()
Dim stkprice()
stkprice = Array(10, 17, 23) Cells(1, 1) = stkprice(1) Cells(2, 1) = stkprice(2) Cells(3, 1) = stkprice(3)

Array Dimension
􏰹􏰖􏰅􏰑􏰺 􏰑􏰯􏰗􏰑􏰊 􏰕􏰅􏰄􏰧􏰅􏰕 􏰄􏰅 􏰨􏰖􏰕􏰇􏰅􏰇􏰖􏰎 􏰛􏰝􏰻 􏰼 􏰽􏰪􏰔 􏰕􏰅􏰄􏰧􏰅􏰕 􏰄􏰅 􏰛􏰣􏰻 Technically, you do Dim StkPrice 􏰚􏰡􏰟 􏰇􏰎 􏰽􏰪􏰔􏰐 􏰤􏰇􏰳􏰨􏰊􏰏􏰇􏰎􏰋 􏰅􏰃􏰄􏰅
there are 3 observations in the array
If you want VBA to start with the 1st 􏰘􏰄􏰅􏰄 􏰨􏰖􏰇􏰎􏰅 􏰄􏰅 􏰝􏰉 􏰎􏰑􏰑􏰘 􏰅􏰖 􏰔􏰵􏰵 􏰛Option Base 1􏰻 􏰄􏰅 􏰅􏰃􏰑 􏰦􏰑􏰋􏰇􏰎􏰎􏰇􏰎􏰋 􏰖􏰈 􏰅􏰃􏰑 􏰳􏰖􏰘􏰍􏰊􏰑􏰐 􏰧􏰇􏰋􏰃􏰅 􏰄􏰈􏰅􏰑􏰧 􏰛􏰴􏰨􏰅􏰇􏰖􏰎 􏰜􏰯􏰨􏰊􏰇􏰗􏰇􏰅􏰻
Or, Dim StkP(2017 To 2019) As Single
the array holds sales number for years 2017 to 2019
􏰉􏰄􏰦􏰊􏰑 􏰅􏰖 􏰧􏰑􏰈􏰑􏰧 􏰅􏰃􏰑 StkP for a particular year (more easily by using the year as the index, e.g., StkP(2001).
􏰩􏰖􏰐 􏰎􏰖 􏰛􏰴􏰨􏰅􏰇􏰖􏰎 􏰪􏰄􏰕􏰑 􏰝􏰻
Similarly, can do StkP(1 to 3) and index the array􏰐 􏰦􏰍􏰅 􏰥 􏰘􏰖􏰎􏰮􏰅 􏰘􏰖 this other than year cause StkP(3) is sufficient

Array: Year as Index
Sub SingleArray()
Dim StkP(2017 To 2019) As Single
Dim Stkrtn As Double
StkP(2017) = 17
StkP(2018) = 23
StkP(2019) = 22
Stkrtn = StkP(2019) / StkP(2018) – 1 Range(“E3”) = Stkrtn Range(“E3”).NumberFormat = “0.00%”

Assign Value for large arrays
Option Explicit
Option Base 1
Sub StkPriceArray()
Dim StkPrice(3)
Dim Counter
Range(“D1”) = “StkPrice”
For Counter = 1 To 3
‘ skip the 1st cell, typically a label, offset(1) = next row
StkPrice(Counter) = Range(“D1”).Offset(Counter).Value
Cells(Counter + 1, 6).Value = StkPrice(Counter)
Next Counter
End SubThe 􏰛􏰾􏰖􏰧􏰉 􏰹􏰑􏰯􏰅􏰻 􏰇􏰕 􏰄􏰊􏰳􏰖􏰕􏰅 􏰄 standard approach here
Exercise: How to calc P/E
Col (D) Price
Col (E) Earnings

LBound and UBound (Ch 37, B)
􏰿 Min. and Max. value an array index can have
􏰿 i.e., lowest/highest subscript for a dimension
􏰴􏰨􏰅􏰇􏰖􏰎 􏰪􏰄􏰕􏰑 􏰝 􏰤 􏰥 􏰍􏰕􏰑 􏰅􏰃􏰇􏰕
Function arraydemo3(N) ‘ look for 1 parameter, N
Dim MyArray(6) ‘ size 1 to 6, i.e., 6
If N = “LB” Then
arraydemo3 = LBound(MyArray)
ElseIf N = “UB” Then
arraydemo3 = UBound(MyArray)
End Function

Assign Value: Lbound and Ubound
Given data in Col D (with label 􏰬􏰾􏰟􏰉 􏰝􏰣􏰐 􏰝􏱀􏰐 􏰡􏰢 Sub CF()
Dim CF(3), n
For n = LBound(CF) To UBound(CF)
‘ do not have to specify number of observations 􏰬􏰾􏰚􏰎􏰟 􏰠 􏰙􏰄􏰎􏰋􏰑􏰚􏰛􏰵􏰝􏰞􏰟􏰒􏰴􏰈􏰈􏰕􏰑􏰅􏰚􏰎􏰟􏰒􏰽􏰄􏰊􏰍􏰑
􏰬􏰑􏰊􏰊􏰕􏰚􏰎 􏱁 􏰝􏰐 􏱂􏰟􏰒􏰽􏰄􏰊􏰍􏰑 􏰠 􏰬􏰾􏰚􏰎􏰟 􏰤􏰖􏰍􏰅􏰨􏰍􏰅 􏰅􏰖 􏰬􏰖􏰊 􏰜 Next n
(try track using locals window)

Assign Value: Lbound and Ubound
􏰬􏰄􏰎 􏰧􏰑􏰼􏰑􏰧􏰕􏰑 􏰅􏰃􏰑 􏰖􏰧􏰘􏰑􏰧􏰉 Sub CF()
Dim CF(3), n As Single
‘ For n = LBound(CF) To UBound(CF)
For n = UBound(CF) To LBound(CF) Step -1 CF(n) = Range(“D1”).Offset(n).Value Cells(n + 1, 6).Value = CF(n)

Ch 37 Array Assignment 􏰷􏰑􏰧􏰑􏰮􏰕 􏰄􏰎 􏰑􏰧􏰧􏰖􏰧 􏰅􏰃􏰄􏰅􏰮􏰕 􏰑􏰄􏰕􏰏 􏰅􏰖 􏰳􏰄􏰱􏰑

Ch 37 Array Assignment
Better: a way to assign arrays is to assign each
element separately using a For loop
􏰛 􏰺􏰻 􏰖􏰨􏰑􏰧􏰄􏰅􏰖􏰧 􏰅􏰖 􏰕􏰇􏰋􏰎􏰄􏰊 􏰅􏰃􏰑 􏰑􏰎􏰘 􏰖􏰈 􏰄 􏰕􏰅􏰄􏰅􏰑􏰳􏰑􏰎􏰅
can put two or more short statements on the same line

Ex: A Payback Period Function
􏰿 Hard to run a UDF when the parameters are generic
􏰿 􏰂􏰃􏰄􏰅 􏰇􏰕 􏰛􏰧􏰻􏰐 􏰛􏰎􏰻􏰲 􏰶􏰧􏰏 􏰅􏰃􏰇􏰕􏰉
􏰿 􏰿 􏰿 􏰿 􏰿 􏰿
Function pB(CFseries, nPeriod) ‘ for an n-period cash flow
DimcumValue,i cumValue = 0
For i = 1 To nPeriod
cumValue = cumValue + CFseries(i)
If cumValue >= 0 Then Exit For Nexti
pB=i-1 End Function

Ch 37 A Payback Period Function
There are a few problems with this function as currently defined
investment does not pay back its initial outlay, 􏰕􏰅􏰇􏰊􏰊 􏰅􏰃􏰑 􏰛􏱃􏰔􏱄􏰪􏰔􏰬􏱅􏰻 􏰧􏰑􏰅􏰍􏰧􏰎􏰑􏰘 􏰠 􏱂 􏰲

Ch 37 A Payback Period Function solved by inserting a check

Ex: PayBack (Ch 37, B)
Based on the CFs above, entered as below
create a Sub that calc Payback as suggested

Dynamic Arrays
Can(/should) you really know the # of obs?
If the size of an array is not known at the time of coding
E.g., you are not the user of the program

Dynamic Arrays
declare the array as usual but with a blank set of parentheses
Dim stkPrice() As Single
Before using the array, must specify its dimensions using the ReDim (with the dimensioning conventions).
Can ReDim an array in code any number of times, but if want to preserve the values in the array when ReDim, have to use the ReDim Preserve statement instead of ReDim; otherwise the data in the array will be lost.

Dynamic Arrays
Sub StkPriceAnalysis()
Dim StkPrice() ‘no allocation until you ReDim
NumInSeries = InputBox(“How many obs. are there in the series?”)
ReDim StkPrice(1 To NumInSeries) End Sub
Warning: ReDim may delete original array, if want to preserve
ReDim Preserve StkArray(#)

Dynamic Arrays and ReDim
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

Dynamic Arrays and ReDim
To set the size of the dynamic array Prices to 12 elements
To set the size of Prices to the value of I

Dynamic Arrays and ReDim
ReDim statement can also be used to change the size of a dynamic array
If you change the size of an array, all the data in the array are lost
Just a note: use ReDim Preserve to keep the old data, if you intend to use the data again

ReDim (of Array Dimensions)
􏰿 􏰂􏰃􏰑􏰎 􏰅􏰖 􏰍􏰕􏰑􏰉
􏰿 A subset of data
􏰿 Locals window shows 􏰿 When ReDim
DymData(3)
􏰿 DymData(3) is empty
􏰿 When ReDim DymData(2)
􏰿 No Empty array for DymData(3)
􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿
Sub DymDataArray() Dim OrgData(3)
OrgData(1) = 23 OrgData(2) = 10 OrgData(3) = 17 Range(“C6”) = OrgData(1)
Dim DymData ReDim DymData(3)
DymData(1) = OrgData(1) DymData(2) = OrgData(2)
‘ DymData(3) is empty Range(“D7”) = DymData(1)
ReDim DymData(2)
DymData(1) = OrgData(1) DymData(2) = OrgData(2)
‘ No Empty Array for DymData (3)

ReDim v. ReDim Preserve
􏰿 ReDim: Original data will be reset, if want to preserve data
􏰿 ReDim Preserve before 􏰿 ReDim
􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿 􏰿
Option Base 1
Sub DymDataArrayP() Dim OrgData(3)
OrgData(1) = 23 OrgData(2) = 10 OrgData(3) = 17 Range(“C6”) = OrgData(1)
Dim DymData
‘ ReDim DymData(3)
‘ DymData(1) = OrgData(1) ‘ DymData(2) = OrgData(2) ReDim DymData(3)
DymData(1) = OrgData(1)
DymData(2) = OrgData(2)
ReDim Preserve DymData(2) Cells(8, 3) = DymData(2)
􏰤ReDim DymData(2)
􏰤 􏰬􏰑􏰊􏰊􏰕􏰚􏱆􏰐 􏰢􏰟 􏰠 DymData(2)
‘ Not DymData, i.e.PRESERVED End Sub

ReDim 􏰚􏰦􏰄􏰕􏰑􏰘 􏰖􏰎 􏰬􏰃 􏰢􏱀􏰐 􏰪􏰉 ish)
􏰿 Option Base 1
􏰿 Function DynPB(r)
􏰿 Dim n, CF(), temp, i
􏰿 If r.Columns.Count = 1 Then ‘if data in rows
􏰿 n = r.Rows.Count
􏰿 ElseIf r.Rows.Count = 1 Then ‘if data in cols.
􏰿 n = r.Columns.Count
􏰿 Exit Function
􏰿 ReDim CF(n)
􏰿 Fori=1Ton
􏰿 CF(i) = r(i)
􏰿 Fori=1Ton
􏰿 temp = temp + CF(i)
􏰿 If temp >= 0 Then Exit For
􏰿 If temp >= 0 Then
􏰿 DynPB = “Payback year is ” & i – 1
􏰿 DynPB = “No Payback”
􏰿 End Function
􏰿 This is a pretty cool function by Benninga􏰐 􏰰􏰑 􏰘􏰖􏰎􏰮􏰅 􏰱􏰎􏰖􏰰 􏰃􏰖􏰰 the data is entered (cols v. rows)
􏰿 r as parameter input
􏰿 If r.Columns.Count = 1 Then ‘if
data in rows
􏰿 n = r.Rows.Count
􏰿 ElseIf r.Rows.Count = 1 Then ‘if data in cols.
􏰿 n = r.Columns.Count 􏰿 Else
􏰿 Exit Function
􏰿 ReDim 􏰬􏰾􏰚􏰎􏰟 􏰤 􏰦􏰄􏰕􏰑􏰘 􏰖􏰎 􏰧

ReDim (Ch 37, B)
􏰿 The original Benninga function for reference (calculates the present value of a series of future cash flows based on a hardcode 5% 􏰥 )

Dynamic Arrays and ReDim Running the function produces the following:
ReDim Preserve
The inability to change the lower boundary of the index The inability to change the number of dimensions

Array Dimension: Multi-dim
Two-dimensional array: e.g., 253 days of price data for 6 different stocks,
Store them in a one-dimensional array of 1,518, or Store them in 6 arrays with 253 elements each. Dim StkPrice(1 to 253, 1 to 6)
Need 2 index #s:
a row # and a col. # to refer to its indv. elements.
E.g., refer to the price of the 3rd stock on the 100th day as stkPrice (100, 3).
By convention, the 1st index is the row # and the 2nd index is the column #.

Multidimensional Arrays (CH 37, B)
In a two-dimensional array the first index refers to the rows and the second to the columns

Array Dimension
Three-dimensional array:
e.g., instead of just the closing 􏰨􏰧􏰇􏰗􏰑􏰐 􏰄􏰊􏰕􏰖􏰉 open, high, low for each stock for each day,
Dim StkPrice(1 to 253, 1 to 4, 1 to 6)
the 1st : referring to the day of the data,
the 2nd: data type (1: O, 2: H, 3: L, 4: C. etc.) and the 3rd􏰺 􏰅􏰃􏰑 􏰗􏰖􏰳􏰨􏰄􏰎􏰏 􏰇􏰎 􏰅􏰃􏰑 􏰛􏰕􏰑􏰗􏰅􏰖􏰧􏰻􏰒
􏰩􏰖􏰉 StkPrice(51,2,3) refers to the 51st 􏰘􏰄􏰏􏰮􏰕 􏰃􏰇􏰋􏰃 price for the 3rd company.
VBA can accommodate up to 60 arrays

Recall: Arrays Exercises
Sub MultiArray()
Dim PE(2, 2) As Single PE(0, 0) = 17
PE(0, 1) = 22
PE(0, 2) = 23
PE(1, 0) = 5
PE(1, 1) = 4
PE(1, 2) = 10 Range(“A1”) = PE(0, 0) Range(“A2”) = PE(0, 1) Range(“A3”) = PE(0, 2) Range(“B1”) = PE(1, 0) Range(“B2”) = PE(1, 1) Range(“B3”) = PE(1, 2)
Sub Multi3DArray()
Dim PE(2, 2) As Single
PE(0, 0) = 17
PE(0, 1) = 22
PE(0, 2) = 23
PE(1, 0) = 5
PE(1, 1) = 4
PE(1, 2) = 10
PE(2, 0) = PE(0, 0) / PE(1, 0) PE(2, 1) = PE(0, 1) / PE(1, 1) PE(2, 2) = PE(0, 2) / PE(1, 2) Range(“A1”) = PE(0, 0) Range(“A2”) = PE(0, 1) Range(“A3”) = PE(0, 2) Range(“B1”) = PE(1, 0) Range(“B2”) = PE(1, 1) Range(“B3”) = PE(1, 2) Range(“C1”) = PE(2, 0) Range(“C2”) = PE(2, 1) Range(“C3”) = PE(2, 2)

Populating Multi-dim Arrays
‘ populating multi-dimensional arrays Dim StkPrice(6, 5)
StkPrice(1, 1) = Range(“A2”).Value StkPrice(1, 2) = Range(“B2”).Value StkPrice(1, 3) = Range(“C2”).Value StkPrice(1, 4) = Range(“D2”).Value StkPrice(1, 5) = Range(“E2”).Value
What if you have 10,000 observations ?

Example: Simple loop populating
Sub TwoDimArrays()
Dim i As Integer, j As Integer Dim Stkp(6, 3) As Single
For i = 1 To 6
For j = 1 To 3
Stkp(i, j) = Cells(i + 1, j + 1).Value
‘cells +1 to skip datalabels Next j
MsgBox Stkp(5, 2) End Sub

Looping Multi-Dim. Bounds (Dynamic)
‘ Looping multi-dimensional BOUNDS Sub TwoDimStkPrice()
Dim StkPrice(6, 3)
Dim Dim1 As Long, Dim2 As Long
For Dim1 = LBound(StkPrice, 1) To UBound(StkPrice, 1)
For Dim2 = LBound(StkPrice, 2) To UBound(StkPrice, 2)
StkPrice(Dim1, Dim2) = Range(“A1”).Offset(Dim1, Dim2).Value
Next Dim2 Next Dim1
MsgBox StkPrice(5, 2) End Sub

Multi-Dim. Bounds: Check output
Sub TwoDimStkPrice()
Dim StkPrice(6, 3)
Dim Dim1 As Long, Dim2 As Long
For Dim1 = LBound(StkPrice, 1) To UBound(StkPrice, 1)
For Dim2 = LBound(StkPrice, 2) To UBound(StkPrice, 2) StkPrice(Dim1, Dim2) = Range(“A1”).Offset(Dim1, Dim2).Value Next Dim2
‘copy paste from above, same loop, but reverse the ital line and replace Range Worksheets.Add
For Dim1 = LBound(StkPrice, 1) To UBound(StkPrice, 1)
For Dim2 = LBound(StkPrice, 2) To UBound(StkPrice, 2) ActiveCell.Offset(Dim1, Dim2 – 1).Value = StkPrice(Dim1, Dim2)
Next Dim2 Next Dim1

Dynamic Multi-Dim.
Sub TwoDimStkPrice()
Dim StkPrice()
Dim Dim1 As Long, Dim2 As Long
Dim1 = Range(“A1”, Range(“A1”).End(xlDown)).Cells.Count – 1 ‘excl title Dim2 = Range(“A1”, Range(“A1”).End(xlToRight)).Cells.Count 􏰤􏰎􏰖 􏰘􏰄􏰅􏰑 􏰃􏰑􏰧􏰑 ReDim StkPrice(1 To Dim1, 1 To Dim2)
For Dim1 = LBound(StkPrice, 1) To UBound(StkPrice, 1)
For Dim2 = LBound(StkPrice, 2) To UBound(StkPrice, 2) StkPrice(Dim1, Dim2) = Range(“A1”).Offset(Dim1, Dim2 – 1).Value Next Dim2
Next Dim1 Worksheets.Add
For Dim1 = LBound(StkPrice, 1) To UBound(StkPrice, 1) For Dim2 = LBound(StkPrice, 2) To UBound(StkPrice, 2)
ActiveCell.Offset(Dim1, Dim2 – 1).Value = StkPrice(Dim1, Dim2) Next Dim2
Next Dim1 End Sub

Quick Dynamic Multi-Dim.
Sub QuickMultiDimStkPrice()
Dim StkPrice()
Sheet1.Activate
StkPrice = Range(“A2”, Range(“A1”).End(xlDown).End(xlToRight))
‘ Output / Populate Worksheets.Add
Range(“A2”, ActiveCell.Offset(UBound(StkPrice, 1), UBound(StkPrice, 2) – 1)).Value = StkPrice
􏰹􏰖􏰅􏰑􏰺 􏰛􏰴􏰨􏰅􏰇􏰖􏰎 􏰪􏰄􏰕􏰑 􏰝􏰻 􏰇􏰕 􏰎􏰖􏰅 􏰎􏰑􏰗􏰑􏰕􏰕􏰄􏰧􏰏

Populating Multi-dim Arrays

Array Calc.: Sub CalcOpenCloseArray()
Sub CalcOpenCloseArray() Sheet1.Activate
Dim StkPO(), StkPC(), Spread()
Dim Dim1, Counter
StkPO = Range(“B2”, Range(“B1”).End(xlDown))
StkPC = Range(“E2”, Range(“E1”).End(xlDown))
Dim1 = UBound(StkPO,1) ‘get the rows, not col.
ReDim Spread(1 To Dim1, 1) ‘spread has Dim1 rows, 1 col For Counter = 1 To Dim1
Spread(Counter, 1) = StkPO(Counter, 1) – StkPC(Counter, 1) Next Counter
Range(“F2”, Range(“F2”).Offset(Dim1 – 1, 1)).Value = Spread
􏰤 􏰜􏰧􏰄􏰕􏰑 StkPO 􏰤 􏰜􏰧􏰄􏰕􏰑 StkPC
􏰤 􏰜􏰧􏰄􏰕􏰑 􏰩􏰨􏰧􏰑􏰄􏰘

Good Practice: Erase Assign Value Arrays contains a lot of info, and used up a lot
of memories􏰓inefficiencies.
Hence, good practice to erase arrays when
Erase StkPrice

Assignment
A typical task for analysts is to download data from database. Typically, the company and its 􏰗􏰖􏰘􏰑 􏰄􏰧􏰑 􏰛􏰦􏰍􏰎􏰗􏰃􏰑􏰘 􏰍􏰨􏰻􏰒
Tasks: Write a function (not a sub) to
1. separate out the code and the company name
2. what if, you are also required to keep the subindex label for each company

Ch 37 Variants Containing an Array

Arrays as Parameters to Functions Arrays can be used as parameters to functions

Ch 37 A New IRR Function

Ch 37 Exercise 2
Rewrite the function in exercise 2 as BetterNewPV(CF, r, n), so it could deal with n periods.

Ch 37 Exercise 3
A bank offers different interest rates on loans. The rate is based on the size of the periodical repayment ( CF ) and the following table. Rewrite the function in exercise 2 as BankPV(CF, r, n) so that it reflects the present value of a loan in the bank.

Ch 37 Exercise 4
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, n).

Ch 37 Exercise 5
Another bank offers 1% increase in interest rate to savings accounts with a balance of more than 10,000.00. Write a future value function Bank1FV(CF, r, n) that reflects this policy.

Ch 37 Exercise 6
The bank in exercise 5 changed its bonus policy and now offers the interest rate increase based on the following table. Rewrite Bank1FV(CF, r, n) to reflect this change.

Ch 37 Exercise 7
Write a version of the present value function with two interest rates, one for positive cash flows and another for negative cash flows. The function should be written for use in a worksheet, and accept both column and row ranges as parameters. The function declaration line should be:

Ch 37 Exercise 8
Write a future value version of the function in exercise 7.

Ch 37 Exercise 9
A bank offers different interest rates on loans. The rate is based on the size of the periodical repayment ( CFi ) and the following table. Write a present value function BankPV(CF, r) so that it reflects the present value of a loan in the bank. The function should be useable as a worksheet function. CF could be either a row range or a column range.

Ch 37 Exercise 10
A bank offers different interest rates on deposit accounts. The rate is based on the size of the periodical deposit ( CFi ) and the following table. Write a future value function BankFV(CF, r) . The function should be useable as a worksheet function. CF could be either a row range or a column range.

Ch 37 Exercise 11
Another bank offers 1% increase in interest rate to savings accounts with a balance of more than 10,000.00. Write a future value function Bank1FV(CF, r) that reflects this policy. The function should be useable as a worksheet function. CF could be either a row range or a column range.

Array Example
Sub QuickSort(arr As Variant, first As Long, last As Long) Dim vCentreVal As Variant, vTemp As Variant
Dim lTempLow As Long
Dim lTempHi As Long
lTempLow = first
lTempHi = last
vCentreVal = arr((fi

程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com