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!

 

[nicklasx]

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

It really was as simple as that huh, seems to be working just fine now.

Supposedly if i would want the function to be able to accept either of an array or range, i would need to use .Column.count and .Row.count with a conditional IF THEN for the type of input received.

Thank you for the help, VBA technicalities can be such a headache when you're used to javascript

 

[RoryA]

You're welcome.