I need to pass a cell matrix with variable size (m X n) to a own defined VBA fuction myfun(dataRange as Range). In this function the selected cell matrix will be stored in an data array. I used to use Range.CurrentArray or Range.Value2 to store the data into an assigned array variable successfully. However, it requires the cells contain no formula. If the data in the cells are obtained by a formula, everything screws up.

The code is shown below:

‘The function stores the data in selected cell matrix (for example: A2:D8, but the cell matrix could be any size and at any location), and to retrieve the total column and row numbers.

Function myfun(dataRange as Range) as Variant()

Dim Columns as Integer, Rows as Integer
myfun=dataRange.CurrentArray
‘myfun=dataRange.Value2
If IsArray(myfun)=True then
Columns=UBound(myfun,1)-LBound(myfun,1)
Rows=UBound(myfun,2)-LBound(myfun,2)
Else
Columns=1
Rows=1
End If

End Function


BTW: if all the data in selected cell matrix are double, how can I pass this matrix to a double variable? So far I have to define the array as Variant.