VBA dynamic array - handling rows an columns

[nicklasx]

Hello Mrexcel community,

How does VBA access columns and rows in a dynamic array/matrix? Say for instance i use the array:

=RANDARRAY(5 rows , 4 columns)

Normally, ROWS(RANDARRAY(...)) = n_rows and COLUMNS(RANDARRAY(...))=n_columns, but when using VBA, there doesnt seem to be the same
logic to handling the rows and columns, when the array does not exist "typed out" within the spreadsheet.

Function COLMAX(Data_Range As Range) As Variant
    Dim TempArray() As Double, i As Long
        If Data_Range Is Nothing Then Exit Function
        With Data_Range
        ReDim TempArray(1 To .Columns.Count)
        For i = 1 To .Columns.Count
            TempArray(i) = Application.Max(.Columns(i))
        Next
    End With
        COLMAX = TempArray
End Function

If i attempt to call this function as =COLMAX(RANDARRAY(3,5)) it returns an error, as #NAME?. The same problem occurs with the function:

Function COLCOUNT(Data_Range As Range) As Variant
        COLCOUNT= Data_Range.Columns.Count
End Function

So how do i instead access rows and columns of a dynamic array using VBA, if not by using .Columns and .Rows? It should be noted that i am still fairly new to using VBA in excel.

Thank you for any and all insight on how to work around this issue!

 

[Fluff]

Where exactly is that function located? Is it in a standard module in the same workbook you are trying to use it in?

 

[nicklasx]

Where exactly is that function located? Is it in a standard module in the same workbook you are trying to use it in?

Both the VBA functions mentioned are listed as module 1 and module 2 within the worksheet.

I am able to use both when marking an array, for example COLMAX(A1:C10) gives the three maxima of column A, B and C.

 

[RoryA]

RANDARRAY returns an array, not a Range, so your code won't work. Arrays don't have properties - you need to test the UBound of the relevant dimension - eg Ubound(array, 1) for the number of 'rows'.

 

[Fluff]

In that case I see no reason why you would get a #NAME? error. I would expect it to be a #VALUE! error.

Your udf expects a range, which is why it won't work work when you try to pass it an array from another function.

 

[nicklasx]

RANDARRAY returns an array, not a Range, so your code won't work. Arrays don't have properties - you need to test the UBound of the relevant dimension - eg Ubound(array, 1) for the number of 'rows'.

Yes indeed i'm aware that the whole problem stems from arrays being treated differently from ranges, despite having similar structure.

Ive encountered Ubound and Lbound before, but i was under the impression that they treated 2d arrays as 1d vectors. I'll look into the documentation and see if i can make it work then, and Thanks!

Will this be applicable to columns as well as rows?

 

[nicklasx]

In that case I see no reason why you would get a #NAME? error. I would expect it to be a #VALUE! error.

Your udf expects a range, which is why it won't work work when you try to pass it an array from another function.

Yeah i'm equally as confused as you are

 

[RoryA]

Will this be applicable to columns as well as rows?

Yes, you'd just use Ubound(array, 2) for columns.

 

[nicklasx]

Many thanks for the help Rory!

The only problem now is that for some reason i cannot seem to load in an array as the input for my function. I do not know if it has to do with how it is declared, but i would assume as much?

Function COLMAX(Data_Range1 As Range, Data_Range2 As Range) As Variant

If not "as range" then no other variable declaration seems to fit my need? As array doesn't seem to be an option. I stumbled upon this syntax as a possible replacement

processArr(ParamArray Arr() As Variant) As Variant

But playing around with it only lead me to further confusion.

So, how do i properly declare that my function should take in and be executed as:

=COLMAX( RANDARRAY(2,3,1,10,TRUE) )

where that array then becomes an accessible dim variant that i can apply i.e. UBound(array,1), or simply put:

Function COLMAX(Data_Range1 As Range) As Variant

COLMAX=UBound(Data_Range1,1)

End Function

 

[RoryA]

Just use Variant, then you can pass either a range or an array.