Hey there! Sign in to join this conversationNew here? Join for free
    • Thread Starter
    Offline

    2
    ReputationRep:
    Hi guys. I have a 13x13 matrix occupying cells B19:N31 and I'm trying to write a code that will calculate the determinants of all sub-matrices (i.e. B19:C20 for 2x2, B19:D21 for 3x3 and so on) using Excel's built-in MDETERM function. My code is below:

    Code:
    Sub Determinants()
    
    Dim m As Variant, i As Integer, det As Double, matrixend As String
    
    Range("B19").Select
    
    For i = 2 To 13
    
        matrixend = ActiveCell.Offset(i - 1, i - 1).Address
    
        m = Range("B19", matrixend)       'Picks an ixi sub-matrix starting from top left corner
        
        det = WorksheetFunction.MDeterm(m)
        
        Range("B39").Select
        ActiveCell.Offset(i - 1, 0).Value = det
        
    Next i
    
    End Sub
    The code breaks down at the determinant calculation stage, saying it is unable to get the mdeterm property of the worksheet function class. I figured it didn't like the way I was going about defining my range for m with the "matrixend" variable (but I did put a Watch on it and matrixend correctly returns C20 for the first loop). If I change the end range to a properly defined cell address, e.g. "C20" then there is no problem.

    How would I go about fixing this? Thanks.
    Offline

    2
    ReputationRep:
    (Original post by m:)ckel)
    Hi guys. I have a 13x13 matrix occupying cells B19:N31 and I'm trying to write a code that will calculate the determinants of all sub-matrices (i.e. B19:C20 for 2x2, B19:D21 for 3x3 and so on) using Excel's built-in MDETERM function. My code is below:

    Code:
    Sub Determinants()
    
    Dim m As Variant, i As Integer, det As Double, matrixend As String
    
    Range("B19").Select
    
    For i = 2 To 13
    
        matrixend = ActiveCell.Offset(i - 1, i - 1).Address
    
        m = Range("B19", matrixend)       'Picks an ixi sub-matrix starting from top left corner
        
        det = WorksheetFunction.MDeterm(m)
        
        Range("B39").Select
        ActiveCell.Offset(i - 1, 0).Value = det
        
    Next i
    
    End Sub
    The code breaks down at the determinant calculation stage, saying it is unable to get the mdeterm property of the worksheet function class. I figured it didn't like the way I was going about defining my range for m with the "matrixend" variable (but I did put a Watch on it and matrixend correctly returns C20 for the first loop). If I change the end range to a properly defined cell address, e.g. "C20" then there is no problem.

    How would I go about fixing this? Thanks.
    The issue is coming from your use of activecell - "matrixend = ActiveCell.Offset(i - 1, i - 1).Address"
    The first iteration will work fine, but because you do "Range("B39").Select" (which is nothing to do with your matrix), the next time you look at activecell (i.e. in the next loop iteration) it will be in some arbitrary location and VBA will (correctly) complain you've not defined a valid matrix for determinant calculation.
    • Thread Starter
    Offline

    2
    ReputationRep:
    (Original post by Chrosson)
    The issue is coming from your use of activecell - "matrixend = ActiveCell.Offset(i - 1, i - 1).Address"
    The first iteration will work fine, but because you do "Range("B39").Select" (which is nothing to do with your matrix), the next time you look at activecell (i.e. in the next loop iteration) it will be in some arbitrary location and VBA will (correctly) complain you've not defined a valid matrix for determinant calculation.
    Ha, can't believe I didn't spot that. Fixed now, cheers!
 
 
 
Reply
Submit reply
TSR Support Team

We have a brilliant team of more than 60 Support Team members looking after discussions on The Student Room, helping to make it a fun, safe and useful place to hang out.

Updated: December 27, 2010
Poll
Do you agree with the PM's proposal to cut tuition fees for some courses?
Useful resources

The Student Room, Get Revising and Marked by Teachers are trading names of The Student Room Group Ltd.

Register Number: 04666380 (England and Wales), VAT No. 806 8067 22 Registered Office: International House, Queens Road, Brighton, BN1 3XE

Write a reply...
Reply
Hide
Reputation gems: You get these gems as you gain rep from other members for making good contributions and giving helpful advice.