# Call a matrix/array with an argument in MMULT?

#### jxb

##### Board Regular
To all,

If one defines a matrix (3x3) (CTRL+SHIFT+ENTER) is there a way of calling this matrix with an argument?

See attached for example

The only way I can think of is to create a simple function which takes theta as an argument and return a "matrix" so that I could have in any cell (using CTRL+SHIFT+ENTER)

=MMULT(myrotationmatrix(theta),A1:A3)

where A1:A3 is the input vector

Thanks
Regards
JXB

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi,

You can use a Named Range.

I named A1:C3 - Theta and E1:E3 - Vector.
(Highlight the cells then type the name in the top left box.)

Excel 2013
ABCDEFG
1123114
2222212
3321310
Sheet1
Cell Formulas
RangeFormula
G1:G3{=MMULT(Theta,Vector)}
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
Theta=Sheet1!\$A\$1:\$C\$3
Vector=Sheet1!\$E\$1:\$E\$3

Thanks for the suggestion but the matrix A1:C3 needs to take theta (T) as a variable. A1:C£ is a defined a follows
A B C
1 0 0
0 cT sT
0 -sT cT

where cT = cos(theta) & sT = sin(Theta)

OK,

I think a Used-Defined Function (UDF) may be the only solution. In which case why not create a UDF that will perform the rotation for you. For example:

=RotX(Theta,Vector)

(Theta and Vector are named ranges here but they need not be.)

Code:
Option Base 1
Function RotX(Theta As Double, Vector As Variant) As Variant

Dim arr(1 To 3, 1 To 3)
Dim var As Variant

arr(1, 1) = 1: arr(1, 2) = 0: arr(1, 3) = 0
arr(2, 1) = 0: arr(2, 2) = Cos(Theta): arr(2, 3) = Sin(Theta)
arr(3, 1) = 0: arr(3, 2) = -Sin(Theta): arr(3, 3) = Cos(Theta)

RotX = Application.WorksheetFunction.MMult(arr, Vector)

End Function

Sub test()
Dim mat As Variant
Dim arr(1 To 3, 1 To 1)
arr(1, 1) = 1: arr(2, 1) = 2: arr(3, 1) = 3
mat = RotX(1, arr)
End Sub

I have included a test macro as well but it can be run from the worksheet in the usual way. Use Ctrl + Shift + Enter to enter it:

(It looks as if you can stack them up as well.)

Excel 2013
ABCDE
1ThetaVector:Result
23111
32-1.556622
43-3.252223
5
6311
72-1.55662
83-3.25222
Sheet1
Cell Formulas
RangeFormula
C2:C4{=rotx(Theta,Vector)}
C6:C8{=rotx(A6,B6:B8)}
E2:E4{=rotx(-1,rotx(1,Vector))}
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
Theta=Sheet1!\$A\$2
Vector=Sheet1!\$B\$2:\$B\$4

Last edited:
Thanks for that RickXL. Did some work in Mathcad as it's easier but your UDF works.
I think one needs/may want to review is the use of cos() and sin() because in the UDF as the argument i.e. theta must in be radians

Thanks a lot

Hi,

There is the RADIANS() function that will convert degrees to radians for you. For example:

So if you want to work in degrees you could either convert degrees to radians using that:

Or, you could add the conversion step in the UDF.

Code:
Option Base 1
Function RotX(Theta As Double, Vector As Variant) As Variant

Dim arr(1 To 3, 1 To 3)
Dim var As Variant

arr(1, 1) = 1: arr(1, 2) = 0: arr(1, 3) = 0
arr(2, 1) = 0: arr(2, 2) = Cos(radians): arr(2, 3) = Sin(radians)
arr(3, 1) = 0: arr(3, 2) = -Sin(radians): arr(3, 3) = Cos(radians)

RotX = Application.WorksheetFunction.MMult(arr, Vector)

End Function

that's what I did!

Replies
4
Views
273
Replies
3
Views
1K
Replies
0
Views
259
Replies
2
Views
441
Replies
18
Views
1K

1,207,423
Messages
6,078,443
Members
446,338
Latest member
AliB

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back