×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Inverting a Matrix

Inverting a Matrix

Inverting a Matrix

(OP)
I'm trying to write a macro that will invert a square matrix of any random size.  Say I have a 10 x 10 matrix, how do I word the macro such that it selects from A1:J10 and then inverts that matrix?  I already have an ad-in that will allow me to invert larger matrices than Excel normally allows.  Thanks!

RE: Inverting a Matrix

I would do it as a user-defined array function.  The bones of it could then look like this.

CODE

Public Function MatInverter(Uninverted)
Dim Inverted as Variant
MatRows = Uninverted.Rows.Count
MatCols = Ininverted.Columns.Count
' Test that MatRows equals MatCols
     ...  Code to your taste
ReDim Inverted(1 to MatRows, 1 to MatCols)
' Do the inversion
     ...  Algorithm and code to your taste
Matinverter = Inverted
End Function
Put it in a module.  There may well be some upper limit on the size that it can handle, so test that aspect out before you use it under fire.

RE: Inverting a Matrix

any particualr reason you don't like MINVERSE ?

Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Inverting a Matrix

(OP)
I will ultimately be using MINVERSE.  The problem is selecting a random size square matrix.  For one run I might have a 64 x 64 matrix but for another run I might have a 24 x 24 matrix.  If I generically set it up to select the 64 x 64 matrix (A1:BL64) then when I run it with the 24 x 24 matrix I get #VALUE! instead of the new inverted 24 x 24 matrix.

RE: Inverting a Matrix

if you want to be able to change the size of the matrix without changing the "structure" of the spreadsheet, the skeleton code I gave above will need to be modified.

CODE

Public Function MatInverter(Uninverted, Size)
Dim Inverted as Variant
MatRows = Uninverted.Rows.Count
MatCols = Ininverted.Columns.Count
' Test that MatRows equals MatCols
     ...  Code to your taste
' Test that Size is within the limits.
     ...  Code to your taste
ReDim Inverted(1 to MatRows, 1 to MatCols)
' Do the inversion by restricting yourself
' to rows 1 to Size and columns 1 to Size.
     ...  Algorithm and code to your taste
' Fill rows Size+1 to MatRows and columns Size+1 to MatCols
' with some sort of null.
     ...  Code to your taste
Matinverter = Inverted
End Function
So you would set up your spreadsheet as if for the largest envisaged matrix and corresponding resulting inverse.  Then you ensure that your actual matrix occupies the top left hand corner of the megamatrix.  Somewhere else on the spreadsheet you have a cell that contains the used size of this megamatrix, either by direct user entry or by some form of automatic examination of the megamatrix.  It is this cell that is passed to the function as parameter "Size".

Alternatively, you may be able to use my original approach provided you can find an inversion algorithm that is not sensitive to large blocks of zeroes.

RE: Inverting a Matrix

(OP)
I guess I've never created a function before in Visual Basic.  So I just enter the code that you have provided.  Then lets assume the largest possible square matrix I will have is a 64 x 64 which would be A1:BL64.  Then lets say I want to find the inverse of a 4 x 4 matrix using the function you've provided.  Would I just go into A1 and then say "=MatInverter(A1:BL64,4)" or "=MatInverter(A1:BL64,A1:D4)"?  Thanks!

RE: Inverting a Matrix

(1)  Select the 64x64 block of cells into which you want the inverted matrix placed, say A71 to BL134.
(2)  Type the formula, ie "=MatInverter(A1:BL64,A66)" where A66 is the cell containing the used size of the megamatrix.
(3)  When you have completed typing the formula, use <Ctrl-Shift-Enter> instead of merely <Enter>.  This tells Excel that the formula you have just typed is to be treated as an "array formula".
(4)  Now when you place your cursor anywhere inside A71:BL134 the formula will appear inside {braces}.  This is Excel's way of telling you that it is an array formula.

All of this is the same as it would be if you were using one of Excel's standard array formulae, such as MINVERSE.

RE: Inverting a Matrix

If you want to write a piece of VBa code then dimension a n*n array A and a n*n array B. Make all non diagonal elements in B equal zero and all diagonal elements equal 1.
Transfer your data into matrix A and perform Gaussian elimination of lower triangular and upper triangular matrix on A and B as they were a single n*2n matrix.
At the end you will get an inverse in matrix B.
m777182

RE: Inverting a Matrix

(OP)
How do I make this generic:

__________________________________________________________
first = -8

Range("A9:D12").Select
Selection.FormulaArray = "=MINVERSE(R[first]C:R[-5]C[3])"
__________________________________________________________

What I have doesn't work.  I want to make the number in the brackets a variable.  Can you do this?  My guess is no but you obviously have more experience than I do.

RE: Inverting a Matrix

Could you name the region, like "GlobalK", and then insert the name into your macro? or is this not possible with this function?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources