How to convert a range to an array?

[JenniferMurphy]

I am trying to write a little UDF to check the win-loss-tie records of some teams. I have the data in the table in the minisheet below. Here's the UDF code:

Function WLTTally(pWLT As Range) As String

Dim WLT As Variant
WLT = Range(pWLT)

End Function

It gets a value error. Can someone tell me what I am doing wrong? Thanks

2022 NFL Power Rankings.xlsx
	B	C	D	E	F
4	Team	Record	W	L	T
5	A	3-0	3	0
6	B	2-1	2	1
7	C	2-1	2	1
8	D	1-1-1	1	1	1
9	E	1-1-1	1	1	1
10	F	1-2	1	2
11	G	1-2	1	2
12	H	0-3	0	3
13	Total	#VALUE!	11	11	2
Sheet3

Cell Formulas
Range	Formula
C13	C13	=WLTTally([Record])
D13	D13	=SUBTOTAL(109,[W])
E13	E13	=SUBTOTAL(109,[L])
F13	F13	=SUBTOTAL(109,[T])

 

[Dan_W]

(1) You've defined pWLT as Range, so you're trying to get the values of a Range(Range))..
(2) Your return data type is a string. If you want an array, it shpuld at least be a Variant.
(3) You need to assign the return are to rhe Function name.
So:

Function WLTTally(pWLT As Range) As Variant
    WLTTally = pWLT.Value
End Function

 

[JenniferMurphy]

(1) You've defined pWLT as Range, so you're trying to get the values of a Range(Range))..
(2) Your return data type is a string. If you want an array, it shpuld at least be a Variant.
(3) You need to assign the return are to rhe Function name.
So:

Function WLTTally(pWLT As Range) As Variant
    WLTTally = pWLT.Value
End Function

Your code works, thanks. Here's the skeleton of my UDF.

Function WLTTally(pWLT As Range) As String

Dim WLT As Variant
Dim i As Integer
Dim NumTeams As Integer

WLT = pWLT.Value
NumTeams = UBound(WLT, 1)
For i = 1 To NumTeams

' . . . some code

Next i

End Function

 

[JenniferMurphy]

PS: Does it make any difference whether I define WLT with or without parens? They both seem to work.

'Without ()
Dim WLT As Variant
WLT = pWLT.Value

'With ()
Dim WLT() As Variant
WLT = pWLT.Value

 

[RoryA]

The parentheses declare it as an array, so it would fail if your range were comprised of just one cell.

 

[JenniferMurphy]

The parentheses declare it as an array, so it would fail if your range were comprised of just one cell.

Really? So Excel treats a single row as a 1xN array and a single column as an Nx1 array, but a single cell passed in exactly the same way is a scalar, and not a 1x1 array?

I would love to hear the (il)logic behind that implementation.

 

[RoryA]

Really? You’d expect reading a single cell value to give you an array?

 

[JenniferMurphy]

Really? You’d expect reading a single cell value to give you an array?

Yes, really, absolutely! It all depends on how other shapes are treated.

If A1:B3 is treated as a 3x2 array (2 dimensions), A1:A3 is treated as a 1-dimensional vector of length 3, and A1:D1 is treated as a 1-dimensional vector of length 4, then A1 (or A1:A1) would be a scalar (0 dimensions).

But that's not how it is. A1:B3 is treated as a 3x2 array (2 dimensions), but A1:A3 is also treated as a 2-dimensional array (3x1) and A1:D1 is treated as a 2-dimensional array (1x4 array). Therefore, A1 (or A1:A1) has to be a 1x1 array.

I understand why they want to treat single rows and columns as arrays. That's fine. But then a range that is both a single row and a single column (a cell) should be a 1x1 array.

 

[RoryA]

That would make most code needlessly more complex, imo.

 

[JenniferMurphy]

That would make most code needlessly more complex, imo.

Perhaps. That would depend on the overall design and implementation. A coherent, consistent design would actually be simpler and far more consistent than the cobbled-together, piecemeal mess that we have now. VBA is pretty widely viewed as one of the worst programming languages ever. It's far worse that Fortran or Cobol, two of the earliest programming languages. It's even worse than VB, which is right there at the bottom of the pile, too.

But you did not respond to my point about consistency.