maxoliver100
New Member
- Joined
- Dec 19, 2013
- Messages
- 18
Hi Guys please help !!! I am new to VBA and trying to output a 1x10 array of the function FwdRate. The inputs are all 1x10 horizontal ranges of numbers. So for example to calculate FwdRate 1 I need to take the first values from each of the input ranges and enter them into the formula below ! Where am I going wrong as all I get is the VALUE error when I press ctrl+shift+enter ??
Thanks Max
Option Explicit
Function FwdRate(Tau As Range, f1 As Range, dtau As Range, vol1 As Range, vol2 As Range, vol3 As Range, m As Range) As Variant
Dim VArray() As Double
Dim i As Integer
Dim n As Integer
Dim dt As Double
Dim X1, X2, X3 As Double
n = Tau.Columns.Count
ReDim VArray(1 To 1, 1 To n)
dt = 0.01
X1 = BoxMuller()
X2 = BoxMuller()
X3 = BoxMuller()
For i = 1 To n
VArray(1, i) = f1(1, i) + m(1, i) * dt + (vol1(1, i) * X1 + vol2(1, i) * X2 + vol3(1, i) * X3) * Sqr(dt) _
+ ((f1(1, i + 1) - f1(1, i)) / dtau(1, i)) * dt
Next i
FwdRate = VArray
End Function
Thanks Max
Option Explicit
Function FwdRate(Tau As Range, f1 As Range, dtau As Range, vol1 As Range, vol2 As Range, vol3 As Range, m As Range) As Variant
Dim VArray() As Double
Dim i As Integer
Dim n As Integer
Dim dt As Double
Dim X1, X2, X3 As Double
n = Tau.Columns.Count
ReDim VArray(1 To 1, 1 To n)
dt = 0.01
X1 = BoxMuller()
X2 = BoxMuller()
X3 = BoxMuller()
For i = 1 To n
VArray(1, i) = f1(1, i) + m(1, i) * dt + (vol1(1, i) * X1 + vol2(1, i) * X2 + vol3(1, i) * X3) * Sqr(dt) _
+ ((f1(1, i + 1) - f1(1, i)) / dtau(1, i)) * dt
Next i
FwdRate = VArray
End Function
