Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Matrix Syntax In Vba Code

1. ## Matrix Syntax In Vba Code

S!
Guys, I´m 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. ## 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

```

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:

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. I agreed to these rules
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):

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. I agreed to these rules
Join Date
25th November 2012
Posts
11

## 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. ## Re: Matrix Syntax In Vba Code

amdas

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

#### 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