Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
reference to range in function within code
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
reference to range in function within code
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) "Paul" wrote in message om... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
reference to range in function within code
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. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... 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) "Paul" wrote in message om... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
reference to range in function within code
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" wrote in message ... 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) "Paul" wrote in message om... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
reference to range in function within code
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) "Paul" wrote in message om... 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" wrote in message ... 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) "Paul" wrote in message om... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDIRECT function to reference a named range | Excel Worksheet Functions | |||
VBA code for find function (reference cell value) | Excel Worksheet Functions | |||
ComboBox AddItem code to reference cells in a Range | Excel Discussion (Misc queries) | |||
A function to get a variable row reference for range in XNPV funct | Excel Worksheet Functions | |||
dynamic range reference and use of common code | Excel Programming |