I am trying to write a function that measures the size of a user-defined array (for example, the input on this is a 2x2 array in excel, each cell contains a "1" [Excel Formula {=RRR(A1:B2)} - see below]) so I can build a function around that information.
Everything else in my function works but this particular section. The error message is "Expected Array". Apparently "rng" is not being input as an array. (See code below)
Public Function RRR(rng As Range)
RRR = UBound(rng, 1)
End Function
If you are dealing with a range and want to know the number of rows or columns, simply use
rng.Rows.Count and rng.Columns.Count.
It's not necessary to convert the content of the range first into an array - and it will fail if the range contains only one cell (because in that case the result of the Value-function is a scalar and not an array).
I would say that you need first to transform the range into an array, something like
Dim array1() As Variant
array1 = rng.Value
RRR = UBound(array1, 1)
rng.Value and rng is a single cell, the result is not an array and the Ubound will throw a runtime error