Worksheet collections in formula?

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...

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...

Code:

---

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...

Code:

---

=SUMPRODUCT((wsCOLORINDEX(E1,E2,E3,E4,E5,E6,E7,E8)=3)*(F1:F8))

---

Any suggestions and/or guidance welcomed. Dave

Hi, Dave. It seems UDF collection can be used ONLY in VBA, but not in a worksheet formula.
I tried UDF array instead and it showed no error. However, retruned value was not correct.

Thanks yugin for your reply. I have messed around with this and googled a resolution. To begin with the UDF needs a range passed to it instead of an array. The use of a collection is possible but is redundant. The UDF must produce an array output for Sumproduct use. I'll outline my findings as follows...
My sheet formula...

Code:

---


=SUMPRODUCT(--(wsCOLORINDEX(Sheet1!E1:E8)=3),Sheet1!F1:F8)

---

This attempt creates a collection and then converts the collection to an array. Doesn't work produces a #Value! error...

Code:

---


Public Function WsColorIndex3(Arr As Variant) As Variant
Dim Cnt As Integer, WsColorIndex2 As New Collection
'make collection. Change collection to Array with UDF for output
For Cnt = LBound(Arr) To UBound(Arr)
WsColorIndex2.Add CInt((Arr(Cnt).Interior.ColorIndex))
Next Cnt
Set WsColorIndex3 = WsColorIndex2
WsColorIndex3 = collectionToArray(WsColorIndex2)
Set WsColorIndex2 = Nothing
End Function
Function collectionToArray(ByRef c As Collection) As Variant()
'change collection to array
Dim a() As Variant: ReDim a(0 To c.Count - 1)
Dim i As Integer
For i = 1 To c.Count
a(i - 1) = c.Item(i)
Next
collectionToArray = a
End Function

---

I found out why here... https://www.mrexcel.com/forum/excel-...umproduct.html: Aladin: I suppose you get #VALUE!. The reason for that the UDF produces an array with comma-separated truth values from a vertical range.
The proper result should be an array of items separated with semi-colons.
So to use a collection and properly convert it to an array for use in Sumproduct...

Code:

---


Public Function WsColorIndex(Rng As Range) As Variant
Dim r As Range, WsColorIndex2 As New Collection
Dim vArr() As Variant, iCells As Long, i As Long
'make collection and load array from collection for output
For Each r In Rng
WsColorIndex2.Add r.Interior.ColorIndex
Next r
iCells = Rng.Cells.Count
ReDim vArr(1 To iCells, 1 To 1)
i = 1
For i = 1 To iCells
vArr(i, 1) = WsColorIndex2(i)
Next
WsColorIndex = vArr
Set WsColorIndex2 = Nothing
End Function

---

The best solution is to not use a collection... https://www.experts-exchange.com/que...UMPRODUCT.html

Code:

---


Function GetRangeColor(rRange As Range) As Variant
Dim vArr() As Variant, iCells As Long, i As Long
'load array for output
iCells = rRange.Cells.Count
ReDim vArr(1 To iCells, 1 To 1)
i = 1
For i = 1 To iCells
vArr(i, 1) = rRange.Cells(i).Interior.ColorIndex ' Return the cell color.
Next
GetRangeColor = vArr
End Function

---

That's about it. I learned a bit about collections and I'm not sure why not just use an array? Dave
edit: Not sure what happened with the formatting? Moderator please repair.