MMult and MInv
in [Programming]
|
From: tedy on 29 Apr 2006 06:52 Hi there, Im trying to work out how to use MMult function in the VBA (i dont want input or output in excel worksheet). But it keeps giving me a runtime error 1004: "Unable to get the MMult property of the WorksheetFunction class" on the MMult line. The funny thing is when i tried changing the array2 into the exact same dimensions, 2 by 2, it works... this i dont understand, i thought as long as you have the same number columns in array 1 with the same number of rows in array 2 it should work. I really need to have array1 and thus array1inv in 2 by 2, where as array 2 in 1 by 2. here is the sub i was working on Sub test() Dim array1(1 To 2, 1 To 2) As Single Dim array1inv Dim array2(1 To 2) As Single Dim arrayresult array1(1, 1) = 0.5 array1(1, 2) = 0.8 array1(2, 1) = 2 array1(2, 2) = 1.2 array2(1) = -5000 array2(2) = -8000 array1inv = Application.WorksheetFunction.MInverse(array1()) arrayresult = Application.WorksheetFunction.MMult(array1inv, array2) End Sub ANy help will be much appreciated!
From: Bob Phillips on 29 Apr 2006 08:27 Try arrayresult = Application.WorksheetFunction.MMult(array1inv, Application.Transpose(array2)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "tedy" <emailtedy(a)gmail.com> wrote in message news:1146307927.754274.130490(a)g10g2000cwb.googlegroups.com... > Hi there, > > Im trying to work out how to use MMult function in the VBA (i dont want > input or output in excel worksheet). But it keeps giving me a runtime > error 1004: "Unable to get the MMult property of the WorksheetFunction > class" on the MMult line. > > The funny thing is when i tried changing the array2 into the exact same > dimensions, 2 by 2, it works... this i dont understand, i thought as > long as you have the same number columns in array 1 with the same > number of rows in array 2 it should work. > > I really need to have array1 and thus array1inv in 2 by 2, where as > array 2 in 1 by 2. > > here is the sub i was working on > > Sub test() > > Dim array1(1 To 2, 1 To 2) As Single > Dim array1inv > Dim array2(1 To 2) As Single > Dim arrayresult > > array1(1, 1) = 0.5 > array1(1, 2) = 0.8 > array1(2, 1) = 2 > array1(2, 2) = 1.2 > > array2(1) = -5000 > array2(2) = -8000 > > array1inv = Application.WorksheetFunction.MInverse(array1()) > arrayresult = Application.WorksheetFunction.MMult(array1inv, array2) > > End Sub > > ANy help will be much appreciated! >
From: tedy on 30 Apr 2006 06:01 Hi Bob, Thanks for your reply! It works.... I'm now trying to implement the test sub into the actual sub, but instead, i got the same error message on the MINVERSE rather than the previous MMULT. The problem is when i tried to inverse the array1 into array1inv.... Here is my sub: Public Function NeutraliseAll(type1 As String, s1 As Single, x1 As Single, t1 As Single, r1 As Single, sd1 As Single, q1 As Single, type2 As String, s2 As Single, x2 As Single, t2 As Single, r2 As Single, sd2 As Single, q2 As Single, deltaPort As Single, gammaPort As Single, vegaPort As Single) Dim delta1 As Single Dim delta2 As Single Dim gamma1 As Single Dim gamma2 As Single Dim vega1 As Single Dim vega2 As Single Dim n1 As Single Dim n2 As Single Dim n3 As Single Dim array1(1 To 2, 1 To 2) As Single Dim array1inv Dim array2(1 To 2) As Single Dim arrayresult 'calculate delta of option 1 If type1 = "Call" Then delta1 = Delta_Call(s1, x1, t1, r1, sd1, q1) Else delta1 = Delta_Put(s1, x1, t1, r1, sd1, q1) End If 'calculate delta of option 2 If type2 = "Call" Then delta2 = Delta_Call(s2, x2, t2, r2, sd2, q2) Else delta2 = Delta_Put(s2, x2, t2, r2, sd2, q2) End If 'calculate gamma of option 1 and 2 gamma1 = Gamma(s1, x1, t1, r1, sd1, q1) gamma2 = Gamma(s2, x2, t2, r2, sd2, q2) 'calculate vega of option 1 and 2 vega1 = Vega(s1, x1, t1, r1, sd1, q1) vega2 = Vega(s2, x2, t2, r2, sd2, q2) array1(1, 1) = gamma1 array1(1, 2) = gamma2 array1(2, 1) = vega1 array1(2, 2) = vega2 array2(1) = gammaPort * -1 array2(2) = vegaPort * -1 array1inv = Application.WorksheetFunction.MInverse(array1()) arrayresult = Application.WorksheetFunction.MMult(array1inv, Application.Transpose(array2)) n1 = arrayresult(1, 1) n2 = arrayresult(2, 1) n3 = -1 * ((n1 * delta1) + (n2 * delta2) + deltaPort) GreeksHedgingForm.txtNewAsset.Value = n3 GreeksHedgingForm.txtNewOpt1.Value = n1 GreeksHedgingForm.txtNewOpt2.Value = n2 End Sub Any help is much appreciated!
From: Bob Phillips on 30 Apr 2006 06:34 I can't help you with that info tedy, as I have absolutely no idea what values are being passed to the function, and I don't have the incentive to work out all (any) possibilities. Give us some more info. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "tedy" <emailtedy(a)gmail.com> wrote in message news:1146391267.488906.108570(a)u72g2000cwu.googlegroups.com... > Hi Bob, > > Thanks for your reply! It works.... > > I'm now trying to implement the test sub into the actual sub, but > instead, i got the same error message on the MINVERSE rather than the > previous MMULT. The problem is when i tried to inverse the array1 into > array1inv.... > > Here is my sub: > > Public Function NeutraliseAll(type1 As String, s1 As Single, x1 As > Single, t1 As Single, r1 As Single, sd1 As Single, q1 As Single, type2 > As String, s2 As Single, x2 As Single, t2 As Single, r2 As Single, sd2 > As Single, q2 As Single, deltaPort As Single, gammaPort As Single, > vegaPort As Single) > > Dim delta1 As Single > Dim delta2 As Single > Dim gamma1 As Single > Dim gamma2 As Single > Dim vega1 As Single > Dim vega2 As Single > Dim n1 As Single > Dim n2 As Single > Dim n3 As Single > Dim array1(1 To 2, 1 To 2) As Single > Dim array1inv > Dim array2(1 To 2) As Single > Dim arrayresult > > 'calculate delta of option 1 > > If type1 = "Call" Then > delta1 = Delta_Call(s1, x1, t1, r1, sd1, q1) > Else > delta1 = Delta_Put(s1, x1, t1, r1, sd1, q1) > End If > > 'calculate delta of option 2 > > If type2 = "Call" Then > delta2 = Delta_Call(s2, x2, t2, r2, sd2, q2) > Else > delta2 = Delta_Put(s2, x2, t2, r2, sd2, q2) > End If > > > 'calculate gamma of option 1 and 2 > gamma1 = Gamma(s1, x1, t1, r1, sd1, q1) > gamma2 = Gamma(s2, x2, t2, r2, sd2, q2) > > 'calculate vega of option 1 and 2 > vega1 = Vega(s1, x1, t1, r1, sd1, q1) > vega2 = Vega(s2, x2, t2, r2, sd2, q2) > > array1(1, 1) = gamma1 > array1(1, 2) = gamma2 > array1(2, 1) = vega1 > array1(2, 2) = vega2 > > array2(1) = gammaPort * -1 > array2(2) = vegaPort * -1 > > array1inv = Application.WorksheetFunction.MInverse(array1()) > arrayresult = Application.WorksheetFunction.MMult(array1inv, > Application.Transpose(array2)) > > n1 = arrayresult(1, 1) > n2 = arrayresult(2, 1) > > n3 = -1 * ((n1 * delta1) + (n2 * delta2) + deltaPort) > > GreeksHedgingForm.txtNewAsset.Value = n3 > GreeksHedgingForm.txtNewOpt1.Value = n1 > GreeksHedgingForm.txtNewOpt2.Value = n2 > > End Sub > > Any help is much appreciated! >
|
Pages: 1 Prev: How check if workbook open? Next: PageSetup.set_PrintQuality .NET method |