I'm just learning a bit about the use of collections. I'm trying to use a UDF collection in a worksheet. I have solid colored cells in E1:E8 of sheet1 and numeric data in F1:F8. I'm trying to use Sumproduct to produce an outcome of the interior cell index of E1:E8 and F1:F8. So far I've got the UDF in module code...
Public Function WsColorIndex(ByVal ARR As Variant) As Collection
Dim Cnt As Integer, Wscolorindex2 As New Collection
For Cnt = LBound(ARR) To UBound(ARR)
Wscolorindex2.Add (ARR(Cnt).Interior.ColorIndex)
Next Cnt
Set WsColorIndex = Wscolorindex2
Set Wscolorindex2 = Nothing
End Function
To test in VBA...
Sub test()
Dim CellColors As Collection, i As Integer
Set CellColors = WsColorIndex(Array([sheet1!E1], [sheet1!E2], [sheet1!E3], _
[sheet1!E4], [sheet1!E5], [sheet1!E6], [sheet1!E7], [sheet1!E8]))
For i = 1 To CellColors.Count
MsgBox CellColors(i)
Next i
End Sub
Which seems to indicate that the UDF works. Now how to use the UDF within a worksheet formula. Here's my failed attempt...
=SUMPRODUCT((wsCOLORINDEX(E1,E2,E3,E4,E5,E6,E7,E8)=3)*(F1:F8))
Any suggestions and/or guidance welcomed. Dave