I have created some subroutines and User Defined functions in Excel's VBA. Internal to them, I have created arrays. I would like to use Excel functions on these arrays, but can not seem to. For example I would like to use the determinant function, which is mdeterm in excel. If I try:

function testdet(testrange as range) as double
dim testarray(1 to 2,1 to 2) as double
testarray(1,0) = 1
testarray(1,1) = 2
testarray(2,1) = 3
testarray(2,2) = 4

testdet = Application.WorksheetFunction.MDeterm(testrange) ' works if testrange is a square range

testdet = Application.WorksheetFunction.MDeterm(testarray) ' doesn't work
testdet = Application.WorksheetFunction.MDeterm({1,2;3,4}) ' doesn't work

Is there a get MDeterm to work with my arrays and not just ranges? Thanks

hey i think thats impossible.. but you can do it this way..

function testdet(testrange as string) as double

now all you need to do is pass a string

range in macro is cell value( if im not mistaken),
and array is a series of values..

in the case of array, you just need to concatenate the array values to one string using a UNIQUE delimeter (ex. "|")
then pass it to the string parameter..

hope it helps..