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 obsneed 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 obsneed 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 obsneed 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 memoriesinefficiencies.
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