Dynamic Array in VBA

[birdec]

I have to use VBA to find the determinant ( =MDETERM ) of any size matrix/array.

This is my code for a simple 2x2 matrix but don't know how to make it dynamic (for a 3x3 or 4x4 matrix). Any ideas? I tried using something like "Dim A(1 to n, 1 to n) as Integer" but keep getting an error: "constant expression required"

Sub FindDeterminant()Dim A(1 To 2, 1 To 2) As Integer
Dim Result As Double
A(1, 1) = ActiveSheet.Cells(1, 1)
A(2, 1) = ActiveSheet.Cells(2, 1)
A(1, 2) = ActiveSheet.Cells(1, 2)
A(2, 2) = ActiveSheet.Cells(2, 2)
Result = WorksheetFunction.MDeterm(A)
ActiveSheet.Cells(14, 8) = Result
End Sub

 

[StephenCrump]

A few random thoughts ...

1. In general terms, to set an array size dynamically you'll need something like:

Dim A() As Long

m = ....
n = ...
ReDim A(1 To m, 1 To n)

2. You can load a range directly to a variant array, and it will automatically resize (eg to 2x2 in this example):

Dim A As Variant

A = Range("A1:B2").Value

3. You could write a simple UDF to accept any valid range:

Function FindDeterminant(rng As Range) As Double

    FindDeterminant = WorksheetFunction.MDeterm(rng)

End Function

4. Don't you think the question is aimed at getting you to calculate the determinant, i.e. by looping through the cells of the range, rather than simply using the Excel function?

 

[birdec]

Thanks, Stephen. Really helpful ideas. You're right, the question is probably aimed at getting me to physically calculate the determinant - but it strikes me as massively inefficient to create something that's already built into Excel.

 

[StephenCrump]

I agree. No point in writing VBA when a native Excel function exists.

So I'm assuming the question is a coding exercise, rather than something you'd actually use in practice.

 

[birdec]

Yes, I'm an engineering student trying to make it through a numerical methods course.

 

[StephenCrump]

Post back if you have any problems.

We won't do your homework , but will be happy to help you with any Excel issues.