Consulting

Results 1 to 7 of 7

Thread: Sleeper: Matrix multiplication in VBA (not the application. one)

  1. #1
    VBAX Newbie
    Joined
    Jun 2015
    Posts
    1
    Location

    Sleeper: Matrix multiplication in VBA (not the application. one)

    Hello everyone

    I'm currently building a set of VBA functions to calculate some financial stuff. I have to do a lot of matrix multiplication, and i've ran into a very annoying issue. I'm sure it's just me who doesn't understand how VBA works properly yet.

    So, in this example, i'm calculating a scalar from a set of matrices. My issue is, however, that i simply does not work. The nominator and denominator calculates perfectly seperatly and i can also output them - but i cannot take the fraction between them. Also, just to test if it was a general problem i noticed that i cannot subtract, multiply them either. So i suppose that i'm doing something generally wrong. Here's my code

    Can anyone explain to me what i'm doing wrong? Help is greatly appreciated as this really annoys me!

    -----------------------------------------------------------------------------
    Public Function TMVMuTanN(ExcessMu As Variant, Sigma As Variant) As Variant
    
    
        Dim onevector As Variant
        ReDim onevector(1 To 3, 1 To 1)
        Dim i As Double
    
    
           onevector(1, 1) = 1
           onevector(2, 1) = 1
           onevector(3, 1) = 1
    
    
        With Application.WorksheetFunction
        TMVMuTanN = (.MMult(.MMult(.Transpose(onevector), .MInverse(Sigma)), ExcessMu)) / (.MMult(.MMult(.Transpose(ExcessMu), .MInverse(Sigma)), ExcessMu))
        End With
        
    End Function
    -----------------------------------------------------------------------------

    EDIT: The function takes to matrices as input, excessMu and Sigma (yes it's Markowitz analysis hehe)
    Last edited by SamT; 06-10-2015 at 06:32 AM.

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,716
    Location
    The operators +-*/ do not work with arrays.
    Be as you wish to seem

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,812
    Location
    Break it down
    Sub a()
    Dim Nomin As Double
    Dim Denom As Double
    Dim WsF As Object
    Set WsF = Application.WorksheetFunction
    
    With WsF
            Nomin = (.MMult(.MMult(.Transpose(onevector), .MInverse(Sigma)), ExcessMu))
            Denom = (.MMult(.MMult(.Transpose(ExcessMu), .MInverse(Sigma)), ExcessMu))
    End With
    
    TMVMuTanN = Nomin / Denom
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,716
    Location
    That still won't work since MMult always returns an array. If the array contains only one value, then you could use:
        With WsF
             Nomin = (.MMult(.MMult(.Transpose(onevector), .MInverse(Sigma)), ExcessMu))(1)
            Denom = (.MMult(.MMult(.Transpose(ExcessMu), .MInverse(Sigma)), ExcessMu))(1)
        End With
    Be as you wish to seem

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,812
    Location
    I just have one question
    What are financial functions ?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    1. I don't think you divide Nomin by Denom. I think you multiple Nomin by the inverse of Denom

    2. As to the 'scaler', do you mean the matrix determinate? if so you can use the MDETERM() function after #1

    Forgetting the VBA and Excel functions for a minute -- what is the formula in words / math?

    And as check, do you have inputs that provide a known answer?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    http://epublications.bond.edu.au/cgi...&context=ejsie

    "A Simple Spreadsheet-Based Exposition of the Markowitz Critical Line Method for Portfolio Selection"

    Over my head but at least it has 'spreadsheet' in the title
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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