MrExcel Publishing
Your One Stop for Excel Tips & Solutions

checking dim on return arrays

Posted by yu-kuan on July 23, 2000 11:16 PM


my function executes a convergence operation on a list of data, and then returns an 2X8 array when the solver is able to converge. However, sometimes the solver does not converge, and the array the function returns is not of the right dimension, which causes my program to crash later on when i try to operate what I expect the array dimensions to be.

Is there some way I can check the dimension(s) of an array returned by a function? That will return the dimensions of the array by level, if possible?

My code looks like this:

dim AnsArr as variant

AnsArr = Sigma_Calc(x, y, z)

* normally if the array is 2X8 like it's supposed to be, then the following line is ok. But if it's not, and sometimes when Sigma_Calc does not converge it simply returns a huge 1X1 negative number like -999999, then the following line causes the program to crash because of dimension problems.

Cells(i, j) = AnsArr(1, 2)

any help will be greatly appreciated.


Posted by Ivan Moala on July 24, 0100 1:04 AM


Dim SigCalc(x, y, z)

x = UBound(Sig(), 1)
y = UBound(Sig(), 2)
z = UBound(Sig(), 3)

where 1,2,3 are Whole numbers indicating which dimension's upper bound is returned. Use 1 for the first dimension, 2 for the second, and so on. If dimension is omitted, 1 is assumed.