MMult and MInv

[tedy]

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!

 

[Bob Phillips]

Try

arrayresult = Application.WorksheetFunction.MMult(array1inv,
Application.Transpose(array2))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[tedy]

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!

 

[Bob Phillips]

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)