Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Matrix Syntax In Vba Code

  1. #1
    Join Date
    28th November 2006
    Posts
    17

    Matrix Syntax In Vba Code

    S!
    Guys, Im building a function that uses values stored in matrices. I'd like to make a independent function, it is, I'd like to write the matrices direct in the VBA code. What is the correct sintaxe? Is needed to difene element by element or VBA accepts matrix definition like Matlab, i.e. matrix A in the code below, or accepts only definition like the B array below?
    The Code below give a
    VB:
    A = [1 2 3 
    4 5 6 
    7 8 9] 
    B(1,1) = 1 
    B(1,2) = 2... 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Matrix Syntax In Vba Code

    For variant arrays you could use a matrix style approach.
    But the normal way is to load each element.

    VB:
    Sub Test() 
         
        Dim A As Variant 
        Dim B(2, 2) As Variant 
        Dim lngIndex1 As Long 
        Dim lngIndex2 As Long 
         
        A = [{1, 2, 3; 4, 5, 6; 7, 8, 9}] 
         
        Debug.Print "Matrix" 
        For lngIndex1 = LBound(A, 1) To UBound(A, 1) 
            For lngIndex2 = LBound(A, 2) To UBound(A, 2) 
                Debug.Print "A("; lngIndex1; ","; lngIndex2; ")="; A(lngIndex1, lngIndex2) 
            Next 
        Next 
        Debug.Print 
         
        B(0, 0) = 1 
        B(0, 1) = 2 
        B(0, 2) = 3 
        B(1, 0) = 4 
        B(1, 1) = 5 
        B(1, 2) = 6 
        B(2, 0) = 7 
        B(2, 1) = 8 
        B(2, 2) = 9 
        Debug.Print "Array Items" 
        For lngIndex1 = LBound(B, 1) To UBound(B, 1) 
            For lngIndex2 = LBound(B, 2) To UBound(B, 2) 
                Debug.Print "B("; lngIndex1; ","; lngIndex2; ")="; B(lngIndex1, lngIndex2) 
            Next 
        Next 
         
    End Sub 
    
    

    Cheers
    Andy


  3. #3
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: Matrix Syntax In Vba Code

    To multiply matrices using Application.MMULT, Excel requires 2D arrays, even if the short dimension is 1; e.g, this doesn't work:

    VB:
    Dim v1(1 To 4) As Variant 
    Dim v2(1 To 4, 1 To 4) As Variant 
    Dim vResult As Variant 
    vResult=application.mmult(v1,v2) 
    
    
    This does:
    VB:
    Dim v1(1 To 1, 1 To 4) As Variant 
    Dim v2(1 To 4, 1 To 4) As Variant 
    Dim vResult As Variant 
    vResult=application.mmult(v1,v2) 
    
    
    I know of no way to initialize a matrix other than element by element, or reading it from the spreadsheet:
    VB:
    v = range("A1:D4") 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th May 2012
    Location
    Switzerland
    Posts
    7

    Re: Matrix Syntax In Vba Code

    Hi

    See the matrix and vector functions for VBA/Excel at (free download and open source):
    http://finaquant.com/download/matrixvectorvba

    With these functions, a matrix of type double can be initialized as follows:
    VB:
    Sub MyTest() 
        Dim M() As Double ' matrix
        Dim V() As Double ' vector
        Dim InputRange As Range 
         
         ' Read matrix from the worksheet (input range)
        Set InputRange = Workbooks(WorkBookName).Sheets("Sheet1").Range("A4:D7") 
        M = FQ_range_to_matrix(InputRange) 
         
         ' convert variant array to double array
        M = FQ_var_to_matrix([{1,2,3;4,5,6}]) 
        V = FQ_var_to_vector(Array(1, 3, 5, 2)) 
         
         ' print matrix to immediate window
        Debug.Print "M =" & FQ_matrix_format(M) 
    End Sub 
    
    
    Regards
    Tunc

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    25th November 2012
    Posts
    10

    Re: Matrix Syntax In Vba Code

    Hi;

    When i try to run test macro i get "Subscript out of range (Error 9)" error.

    Can you help me to solve?

    VB:
    Sub Test() 
         
        Dim a As Variant 
         
        a = [{1, 3, 3, 4}] 
         
        MsgBox a(1, 1) 
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    9th April 2007
    Location
    Alstonville, Australia
    Posts
    3,596

    Re: Matrix Syntax In Vba Code

    amdas
    please start your own thread and reference this thread if relevent

    please read the Forum Rules
    If the solution helped please donate to RSPCA

    Sites worth visiting: Rabbitohs | Excel-it royUK | Golden Rules | Forum Rules | Freebie Stuff | Smallman Freebies

    Ozgrid Free Excel Training

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Return Inverse Matrix Of Large Matrix
    By monsieurmark in forum EXCEL HELP
    Replies: 9
    Last Post: August 22nd, 2008, 18:07
  2. Mmult Matrix Code
    By bbromley in forum EXCEL HELP
    Replies: 1
    Last Post: January 3rd, 2007, 12:05
  3. Syntax Assistance With Matrix Layout
    By BMS in forum EXCEL HELP
    Replies: 13
    Last Post: November 23rd, 2005, 21:12
  4. Get value from matrix
    By Mikaelangelo in forum EXCEL HELP
    Replies: 2
    Last Post: October 27th, 2005, 23:16
  5. Syntax error for my VBA code
    By thsu in forum EXCEL HELP
    Replies: 4
    Last Post: January 26th, 2005, 02:44

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
  •  
porno