VBA for inverse matrix

[jay11678]

Hi, I'm complete beginner to VBA, and I need to create a spreadsheet that needs to inverse varies matrix, how do I use this code? For example, my SourceCell is at cell C1 (may be any size of square matrix) and cell A1 is a number that tells the size of this matrix it needs to inverse, and DestCell is at C100. A1 and C1 will change when data changes and I want C100 to automatically updated as a result of this, would the code be something below?

Sub InverseArea()

Dim SourceCell As Variant, DestCell As Variant
Dim MatrixSize As Long
Dim SourceRange As Range, DestRange As Range

SourceCell = "C1"
MatrixSize = "A1"
DestCell = "C100"

Set SourceRange = Range(SourceCell & ":" & Range(SourceCell).Offset(MatrixSize - 1, MatrixSize - 1).Address)
Set DestRange = Range(DestCell & ":" & Range(DestCell).Offset(MatrixSize - 1, MatrixSize - 1).Address)

DestRange.Select

Selection.FormulaArray = "=MINVERSE(" & SourceRange.Address(0, 0) & ")"

End Sub

 

[cmowla]

Wow, I didn't know Excel did inverses of matrices! Cool!

Is this what you want to do?

Sub Invert_Matrix()

Dim topLeftOfMatrix As Range
Set topLeftOfMatrix = Range("C1")

Dim matrixSize As Integer
matrixSize = topLeftOfMatrix.CurrentRegion.Rows.Count

Dim sourceRange As Range
Set sourceRange = topLeftOfMatrix.Resize(matrixSize, matrixSize)

Dim topLeftOfInvertedMatrix As Range
Set topLeftOfInvertedMatrix = Range("C100")

topLeftOfInvertedMatrix.Formula2 = "=MINVERSE(" & sourceRange.Address & ")"

End Sub

Blank.xlsb
	A	B	C	D	E
1			1	2	3
2			0	1	4
3			5	6	0
4
100			-24	18	5
101			20	-15	-4
102			-5	4	1
Inverse Matrix

Cell Formulas
Range	Formula
C100:E102	C100	=MINVERSE($C$1:$E$3)
Dynamic array formulas.

 

[cmowla]

If so, then this can be a 1 liner:

Sub Invert_Matrix()
Range("C100").Formula2 = "=MINVERSE(" & Range("C1").CurrentRegion.Address & ")"
End Sub

 

[jay11678]

Not exactly, the matrix that needs to be inverse could be 100x100, but from this 100x100 matrix, I may only want to inverse z x z matrix where z: 0 < z < 100, z and the initial matrix size will change with different data input (eg. I got 70x70 matrix and only want to inverse the first30x30, or got a 99x99 and only want to inverse the first 80x80). The issue here is the initial 100x100 can be singular, where only the z x z matrix inside it can be inverse. That’s why I also need a cell to tell excel the size of matrix I want to inverse. The initial matrix and z value is given by the data I have, I’m stuck with the inverse part

 

[cmowla]

I made one modification to the longer code I posted. I believe the code now satisfies your criteria:

Sub Invert_Matrix()

Dim topLeftOfMatrix As Range
Set topLeftOfMatrix = Range("C1")

Dim matrixSize As Integer
matrixSize = Range("A1").Value 'Modified line

Dim sourceRange As Range
Set sourceRange = topLeftOfMatrix.Resize(matrixSize, matrixSize)

Dim topLeftOfInvertedMatrix As Range
Set topLeftOfInvertedMatrix = Range("C100")

topLeftOfInvertedMatrix.Formula2 = "=MINVERSE(" & sourceRange.Address & ")"

End Sub

 

[jay11678]

I made one modification to the longer code I posted. I believe the code now satisfies your criteria:

Sub Invert_Matrix()

Dim topLeftOfMatrix As Range
Set topLeftOfMatrix = Range("C1")

Dim matrixSize As Integer
matrixSize = Range("A1").Value 'Modified line

Dim sourceRange As Range
Set sourceRange = topLeftOfMatrix.Resize(matrixSize, matrixSize)

Dim topLeftOfInvertedMatrix As Range
Set topLeftOfInvertedMatrix = Range("C100")

topLeftOfInvertedMatrix.Formula2 = "=MINVERSE(" & sourceRange.Address & ")"

End Sub

Thank you for helping out, I will have a try with this code sometime tomorrow, so with this code, if A1 = 8, it will inverse a 8 x 8 matrix, then if A1 changed to 10, will it automatically inverse a 10x10 matrix or will I need to run this code again? Sorry this may seem to be a stupid question, I still need to learn the basic for VBA

 

[cmowla]

Thank you for helping out, I will have a try with this code sometime tomorrow, so with this code, if A1 = 8, it will inverse a 8 x 8 matrix, then if A1 changed to 10, will it automatically inverse a 10x10 matrix or will I need to run this code again? Sorry this may seem to be a stupid question, I still need to learn the basic for VBA

Run the code "again"? Well, yeah, VBA needs to be run every time, should the data possibly change. If you want something automatic that "detects" change (and if change is detected, then recalculate), then you probably actually need a formula.

In your case, it is useful to know which column letter represents what column number. Add this tool to your tool chest. (Although it will not be needed. The formula after it is all that's needed.)

Sub Test__Column_Number_To_Letter()
MsgBox Column_Number_To_Letter(102)
End Sub
Function Column_Number_To_Letter(columnNumber As Integer)
Column_Number_To_Letter = Split(Cells(1, columnNumber).Address, "$")(1)
End Function

In the test sub for the function, I put 102. Because column C is column #3. So 102 - 3 + 1 = 100. If you run that test sub, a popup window with the letters "CX" will come up. So that is the column letter that we need to put in our formula to represent the right-most column of the "full" matrix.

Therefore, the formula you will need can be copied into cell C101 (not C100 because you mentioned that the input matrix can be of size 100x100, so you need to put the formula at least on row 101 or further down . . . change the value/matrix size in Cell A1 and see what happens now!):

Book1.xlsx
	A	B	C	D	E
1	3		1	2	3
2			0	1	4
3			5	6	0
101			-24	18	5
102			20	-15	-4
103			-5	4	1
Sheet1

Cell Formulas
Range	Formula
C101:E103	C101	=MINVERSE(INDEX($C$1:$CX$100,1,1):INDEX($C$1:$CX$100,A1,A1))
Dynamic array formulas.

 

[cmowla]

And I guess I should have had $$ for A1 in the formula (just to be safe):

=MINVERSE(INDEX($C$1:$CX$100,1,1):INDEX($C$1:$CX$100,$A$1,$A$1))

 

[jay11678]

And I guess I should have had $$ for A1 in the formula (just to be safe):

=MINVERSE(INDEX($C$1:$CX$100,1,1):INDEX($C$1:$CX$100,$A$1,$A$1))

Thank you so much, this function works exactly the way I wanted. I’m prabably putting the inverse into a new sheet as who know how big the initial matrix I’m going to expand to in future.