+ Reply to Thread
Results 1 to 3 of 3

Macro to automate some matrix manipulations

  1. #1
    Registered User
    Join Date
    04-25-2011
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    24

    Macro to automate some matrix manipulations

    Hello,

    I have set up a data table, x, in a worksheet. In another worksheet, I have added a transpose, x', of the the x data table. The transpose was done with {=TRANSPOSE(PCA_data!W2:BM2019)}. On the same worksheet, I have set up a matrix that is the product of x'x. This was done with {=MMULT(B52:BYQ94,PCA_data!W2:BM2019)}. Also on the second worksheet, I have taken the determinant of the x'x matrix.

    What I would like to automate is the sequential removal of rows from the original data table x while recording the change in the determinant. The data table x has one less row than the total amount of data. Presently, I have one row left out of the matrix calculations. If I manually remove another row with cut and paste it down at the bottom and then manually take the current left out row and return it to x, I get a change in the determinant that I can record. What I need to do is to loop through all the rows in x removing a row and replacing the row that was set aside last. After each removal/replacement exchange I need to record the determinant difference.

    I am probably making this seem more complicated that it really is. Suggestions or links to sample code would be appreciated.

    LMHmedchem

  2. #2
    Registered User
    Join Date
    04-25-2011
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Macro to automate some matrix manipulations

    Here is code from the macro recorder that does what I need.
    Please Login or Register  to view this content.
    What I need to be able to do is to replace Rows("18:18") with a loop iterator so that I can use Rows("19:19") for the next loop, Rows("20:20") for the next, and so on up to Rows("2019:2019"). I am not very clear on variables and loops in VB.

    LMHmedchem

  3. #3
    Registered User
    Join Date
    03-02-2017
    Location
    Kuala Lumpur, MALAYSIA
    MS-Off Ver
    2010
    Posts
    1

    Re: Macro to automate some matrix manipulations

    Just for Quick code for determinant of square matrix using Pivot method
    Option Explicit




    Public Function Deterpivot(Matrix() As Double) As Double
    Dim j As Integer
    Dim i As Integer
    Dim k As Integer
    Dim n As Integer
    Dim x As Integer
    Dim First As Integer
    Dim Last As Integer
    Dim lastm As Integer
    Dim Row() As Double
    Dim Dvalue As Double
    Dim Divisor As Double
    Dim Multiplier As Double
    Dim Pmatrix() As Double
    Dim Nmatrix() As Double
    'This function assumes square matrix
    'with lower bound same for each dimension
    Last = UBound(Matrix, 1)
    lastm = UBound(Matrix, 1)

    'create new matrix that store value of matrix of variable, since matrix variable is of fixed type not dynamic type
    ReDim Nmatrix(1 To Last, 1 To Last)
    For j = 1 To Last
    For k = 1 To Last
    Nmatrix(j, k) = Matrix(j, k)
    Next k
    Next j

    If Last = 2 Then

    Deterpivot = Det2m(Matrix())

    Else
    Dvalue = 1 'initialize the DET value of matrix
    ReDim Row(0 To lastm - 2)
    Row(0) = 1

    For i = 1 To lastm - 2

    ReDim Pmatrix(1 To Last - 1, 1 To Last - 1)

    For j = 1 To Last - 1
    For k = 1 To Last - 1

    Row(i) = Nmatrix(1, 1)
    Pmatrix(j, k) = (Nmatrix(1, 1) * Nmatrix(j + 1, k + 1) - Nmatrix(j + 1, 1) * Nmatrix(1, k + 1)) / Row(i - 1)


    Next k
    Next j


    ReDim Nmatrix(1 To Last - 1, 1 To Last - 1)
    For j = 1 To Last - 1
    For k = 1 To Last - 1
    ' if pivot point =0 then exchange row number, DET will multiply with -1

    If (Nmatrix(1, 1) = 0 And Nmatrix(j, 1) <> 0) Then
    MatrixRowExchange Nmatrix(), 1, j
    Dvalue = -Dvalue
    End If

    Nmatrix(j, k) = Pmatrix(j, k)
    Next k
    Next j

    Last = UBound(Nmatrix, 1)


    Next i

    Dvalue = Dvalue * Det2m(Nmatrix())
    Deterpivot = Dvalue / Row(lastm - 2)

    End If


    End Function


    Public Function Det2m(Matrix() As Double) As Double 'function to calc the DET of 2nd order matrix

    Dim j As Integer
    Dim i As Integer
    Dim k As Integer
    Dim n As Integer

    Det2m = Matrix(1, 1) * Matrix(2, 2) - Matrix(1, 2) * Matrix(2, 1)

    End Function

    Public Sub MatrixRowExchange(Matrix() As Double, rowb As Integer, rowa As Integer) 'sub to exchange row of matrix
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim evalue As Double
    For i = LBound(Matrix, 1) To UBound(Matrix, 2)
    For j = LBound(Matrix, 1) To UBound(Matrix, 2)
    If i = rowb Then

    evalue = Matrix(rowb, j)
    Matrix(i, j) = Matrix(rowa, j)
    Matrix(rowa, j) = evalue

    End If

    Next j
    Next i

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Automate the Build of a Design Matrix in MS Excel
    By mechatronics in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-18-2014, 07:50 AM
  2. Replies: 2
    Last Post: 02-11-2014, 05:05 AM
  3. Excel manipulations
    By neerajinani in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-10-2009, 10:34 AM
  4. Text manipulations
    By takyar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-26-2008, 11:26 AM
  5. cell manipulations
    By raedbenz in forum Excel General
    Replies: 2
    Last Post: 03-06-2008, 02:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1