Closed Thread
Results 1 to 4 of 4

[SOLVED] MMult and MInv

  1. #1
    tedy
    Guest

    [SOLVED] MMult and MInv

    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!


  2. #2
    Bob Phillips
    Guest

    Re: MMult and MInv

    Try

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

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "tedy" <emailtedy@gmail.com> wrote in message
    news:1146307927.754274.130490@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!
    >




  3. #3
    tedy
    Guest

    Re: MMult and MInv

    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!


  4. #4
    Bob Phillips
    Guest

    Re: MMult and MInv

    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@gmail.com> wrote in message
    news:1146391267.488906.108570@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!
    >




Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1