How to use MDETERM function in VBA

Question

I would like to use the MDETERM spreadsheet function in VBA without starting with a spreadsheet range. 

I have set it up like this inside a VBA function

Dim A()

ReDim A(2,2)

dim d as Variant

.

.

.

'Here I used some statements to explicitly define the contents of A

'Now, I call the MDETERM function 

d = Application.WorksheetFunction.MDeterm(A())

The function call fails, presumably because I am not sending it a spreadsheet range in the form A1:B2 or something like that.

Is there any way to call the function using the array I have defined above?

Thank you.

Answer 1

Thank you!!!!!!

When I looked at yours, and retried mine, what made the difference was this.

When I set up my matrix I used

ReDim A(2,2)

That was the problem

When I changed that to 

ReDim A(0 to 1, 0 to 1)

it worked. 

I had loaded the array as 

A(0,0) = 1

A(1,0) = 2

A(0,1) = 3

A(1,1) = 4

Also when I use

ReDim A(1 to 2, 1 to 2)

A(1,1) = 1

A(2,1) = 2

A(1,2) = 3

A(2,2) = 4

Which is is the same as yours, it also works.

So the problem was in the way I Redim'ed the array.

Thanks very much

Answer 2

The following works for me:

Option Explicit

Sub ComputeDeterm()

Dim A(1 To 2, 1 To 2) As Integer

Dim Result As Double

A(1, 1) = 3

A(2, 1) = 6

A(1, 2) = 1

A(2, 2) = 1

Result = WorksheetFunction.MDeterm(A)

ActiveSheet.Cells(1, 1) = Result

End Sub