reference to range in function within code

[Paul]

Hi all,

Below I have provided the code (Sub bereken_asian_call) for a macro
which finally calculates two arrays: O(index,state,a) and
F(index,state,a). The range for the a values in this case is 1 to
alpha (which is 4 now).
Additionally, a new value is calculated called NewAv1.

I should now interpolate NewAv1 given the array O(index,state,1 to
alpha) and F(index,state,1 to alpha). For that I want to use a
function written in VBA called VlinearInterpolation (see below).
Therefore, I must therefore to that function within the sub
bereken_asian_call. The function is defined as:
VlinearInterpolation(value to be interpolated, range 1, range 2) ...

In my case range 1 is: O(index,state,1 to alpha) and range 2 is
F(index,state,1 to alpha) ...

Question is: how should i refer to the VLinearInterpolation function
and how do I assign the O(index,state,1 to alpha) and F(index,state,1
to alpha) ranges to range 1, respectively range 2 in the
VLinearInterpolation function.

thanks in advance,

thei


Sub bereken_asian_call()

'input parameters
sig = Sheets("Sheet1").Range("B1").Value
T = Sheets("Sheet1").Range("B2").Value
N = Sheets("Sheet1").Range("B3").Value
r = Sheets("Sheet1").Range("B7").Value
div = Sheets("Sheet1").Range("B8").Value
S = Sheets("Sheet1").Range("B12").Value
K = Sheets("sheet1").Range("b13").Value
alpha = Sheets("Sheet1").Range("B14").Value

Dim St() As Double
Dim F() As Double
Dim O() As Double
Dim NewAv1() As Double
Dim NewAv2() As Double
Dim InterO1() As Double
Dim InterO2() As Double

'initialise parameters
dt = T / N
u = Exp(sig * Sqr(dt))
d = 1 / u
pu = (Exp(dt * r) - d) / (u - d)
pd = 1 - pu
edx = u / d
disc = Exp(-r * dt)

'initialise asset prices
ReDim St(N, 0 To N)
St(0, 0) = S

For index = 1 To N Step 1
St(index, 0) = St(0, 0) * d ^ (index - 0)
For state = 1 To index
St(index, state) = St(index, state - 1) * edx
Next state
Next index

'find range of maximum average for each node
ReDim F(N, 0 To N, 1 To alpha)

For index = 0 To N

If index = 1 Then
For state = 0 To index
F(index, state, 1) = Application.Average(St(0, 0),
St(index, state))
Next state
End If

If index > 1 Then
For state = 0 To index
If index = state Then F(index, state, 1) =
Application.Average(index * F(index - 1, state - 1, 1) + St(index,
state)) / (index + 1)
If index <> state Then F(index, state, 1) =
Application.Average(index * F(index - 1, state, 1) + St(index, state))
/ (index + 1)
Next state
End If

Next index

'find range of minimum average for each node
For index = 0 To N

If index = 1 Then
For state = 0 To index
F(index, state, alpha) = Application.Average(St(0, 0),
St(index, state))
Next state
End If

If index > 1 Then
For state = 0 To index
If state = 0 Then F(index, state, alpha) =
Application.Average(index * F(index - 1, state, alpha) + St(index,
state)) / (index + 1)
If state > 0 Then F(index, state, alpha) =
Application.Average(index * F(index - 1, state - 1, alpha) + St(index,
state)) / (index + 1)
Next state
End If

Next index

'find range of intermediate averages for each node
For index = 0 To N

For state = 0 To index
For a = alpha - 1 To 2 Step -1
F(index, state, a) = F(index, state, a + 1) +
(F(index, state, 1) - F(index, state, alpha)) / (alpha - 1)
Next a
Next state

Next index

'initialise option values at maturity
ReDim O(N, 0 To N, 1 To alpha)
For state = 0 To N
For a = 1 To alpha
O(N, state, a) = Application.Max(F(N, state, a) - K, 0)
Next a
Next state

'step back trough the tree
ReDim NewAv1(N, 0 To N, 1 To alpha)
ReDim NewAv2(N, 0 To N, 1 To alpha)
ReDim InterO1(N, 0 To N, 1 To alpha)
ReDim InterO2(N, 0 To N, 1 To alpha)

For index = N - 1 To 0 Step -1

For state = 0 To index
InterO1(index,state,a)=VlinearInterpolation(NewAv1,range O
values, range F values)

Next state
Next index

'Output
Sheets("sheet1").Range("F25").Value = O(18, 0, 1)
Sheets("sheet1").Range("F26").Value = O(18, 0, 2)
Sheets("sheet1").Range("F27").Value = O(18, 0, 3)
Sheets("sheet1").Range("F28").Value = O(18, 0, 4)

End Sub

Function VLinearInterpolation(T As Double, TRange As Range, _
LRange As Range) As Double
Dim nRow As Integer
Dim TLow As Double
Dim THigh As Double
Dim LLow As Double
Dim LHigh As Double

'If at top or bottom, use two points at the end to extrapolate
If T < TRange.Cells(1, 1) Then
nRow = 1
Else
nRow = WorksheetFunction.Match(T, TRange)
If nRow = TRange.Rows.Count Then
nRow = nRow - 1
End If
End If

TLow = TRange.Cells(nRow, 1)
THigh = TRange.Cells(nRow + 1, 1)
LLow = LRange.Cells(nRow, 1)
LHigh = LRange.Cells(nRow + 1, 1)
VLinearInterpolation = (T - TLow) * (LHigh - LLow) / (THigh -
TLow) + LLow

End Function

 

[Bob Phillips]

Paul,

You could drop the arrays into a worksheet range, but why don't you pass
arrays to the function and process these?

Function VLinearInterpolation(T As Double, TRange , _
LRange ) As Double
Dim nRow As Integer
Dim TLow As Double
Dim THigh As Double
Dim LLow As Double
Dim LHigh As Double

'If at top or bottom, use two points at the end to extrapolate
If T < TRange(1) Then
nRow = 1
Else
For nRow = 1 To UBound(TRange,1)
If T = TRange(i) Then Exit For
Next nRow
If nRow = UBound(TRange,1) Then
nRow = nRow - 1
End If
End If

TLow = TRange(nRow,)
THigh = TRange(nRow + 1)
LLow = LRange(nRow)
LHigh = LRange(nRow + 1)
VLinearInterpolation = (T - TLow) * (LHigh - LLow) / (THigh -
TLow) + LLow

End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Just to add, you will have to strip out the appropriate values in your two
3D arrays and build two 1D arrays to pass to Bob's version of the function.

 

[Paul]

Hi Bob,

that's probably the solution ... the problem is that I don't know how
the assign one dimension of a multidimension array to the function ...
if i succeed in naming the specific dimension (of the multidimension
array) I can use that name in the interpolation.

regards,

paul

 

[Bob Phillips]

Hi Paul,

You can pass the 3D array just the same, and just look at the first
dimension, as you did with tyhe first column.

I haven't tested this, but I hope I haven't missed anything.

Function VLinearInterpolation(T As Double, TRange , _
LRange ) As Double
Dim nRow As Integer
Dim TLow As Double
Dim THigh As Double
Dim LLow As Double
Dim LHigh As Double

'If at top or bottom, use two points at the end to extrapolate
If T < TRange(1, 1) Then
nRow = 1
Else
For nRow = 1 To UBound(TRange,1)
If T = TRange(i, 1) Then Exit For
Next nRow
If nRow = UBound(TRange,1) Then
nRow = nRow - 1
End If
End If

TLow = TRange(nRow, 1)
THigh = TRange(nRow + 1, 1)
LLow = LRange(nRow, 1)
LHigh = LRange(nRow + 1, 1)
VLinearInterpolation = (T - TLow) * (LHigh - LLow) / (THigh -
TLow) + LLow

End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)