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])

 

[offthelip]

That would make most code needlessly more complex, imo.

I disagree, because it means you have to check whether a range which is passed to a variant is an array or a single variant every time!!

 

[RoryA]

But you did not respond to my point about consistency

With consistency a great soul has simply nothing to do

 

[RoryA]

I disagree, because it means you have to check whether a range which is passed to a variant is an array or a single variant every time!!

I would suggest most code is not doing anything like that. It's more like if cell.value = "something" then

 

[offthelip]

I think you will find it is surprising how often this problem comes up; I posted a solution to a problem on here which the OP was very happy with solution, yet I had NOT done the check to see if the variant was an array or not specifically because it would makethe solution very complicated. However I knew the solution was not perfect. See:
XL VBA - Paste Values and preface the pasting of Values by an equal sign
I would much prefer it if EXCEL was consistent as Jennifer suggested

 

[RoryA]

I don’t see how that supports your argument? That code would have to be more complicated if value returned an array.

 

[Dan_W]

But arrays are returned from functions all the time with only a single element, and yet they're still arrays - UBound(MyArray) = 0. That bring the case, and implicit variant arrays being what they are, it's fair to assume that a single array would be MyArray(1).

On the point about checking if an array has been initialized or not, there was a very long conversation about it over on VBForums (VB6 - Returning/Detecting Empty Arrays-VBForums). Very interesting.

 

[offthelip]

I don’t see how that supports your argument? That code would have to be more complicated if value returned an array.

Yes it would be slightly more complex ie. one line is more complex with a few resize and ubounds , but it would works regardless of whether range was a single cell on not. :
the code below works when It is called from sub test ( passing an array) but it fails with a mismatch error if passing a single variant loaded from a single cell.
So dealing with the mismatch error means testing for the array and separate code to deal with the singe variant case , much more complex

Public multivariant As Variant
Sub test()
Call copytovariant(Range(Cells(5, 3), Cells(8, 6)))
End Sub
Sub copytovariant(trange As Range)
multivariant = trange.Value
End Sub

Sub Pastevariant()
ActiveCell.Resize(UBound(multivariant, 1), UBound(multivariant, 2)) = multivariant
End Sub

Sub test2()
Call copytovariant(Range(Cells(5, 3), Cells(5, 3)))
End Sub

 

[RoryA]

Hardly much more complex, imo. I'd rather live with that than Range("A1").Value()(1, 1) every time I need a cell value.

On the other hand, it might have been nice if they added a ValueArray property that always returned an array. But if wishes were horses and all that.

 

[offthelip]

Hardly much more complex, imo. I'd rather live with that than Range("A1").Value()(1, 1) every time I need a cell value.

On the other hand, it might have been nice if they added a ValueArray property that always returned an array. But if wishes were horses and all that.

I like the idea of Valuearray, that would solve the problem!!