Consulting

Results 1 to 3 of 3

Thread: Worksheet collections in formula?

  1. #1
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location

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

  2. #2
    VBAX Regular
    Joined
    Jan 2018
    Posts
    55
    Location
    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.

  3. #3
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    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...
    =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...
    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...
    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
    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.
    Last edited by SamT; 01-14-2018 at 11:51 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •