Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Matrix Syntax In Vba Code

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    Code:
    A = [1 2 3
                    4 5 6
                    7 8 9]
    B(1,1) = 1
    B(1,2) = 2...

  • #2
    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.

    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

    Comment


    • #3
      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:

      Code:
      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:
      Code:
      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:
      Code:
      v = range("A1:D4")
      Entia non sunt multiplicanda sine necessitate.

      Comment


      • #4
        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:
        Code:
        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

        Comment


        • #5
          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?

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

          Comment


          • #6
            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 | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

            Comment

            Trending

            Collapse

            There are no results that meet this criteria.

            Working...
            X