Excel VBA Function gives strange returns

Question

My situation is as follow:

I have function one:

Function test(TestValue As String, TargetRange As Range) As String
     Dim rng2 As String   Dim C As Range


  For Each C In TargetRange
    If C.Text = TestValue Then
        If rng2 <> vbNullString Then
            rng2 = rng2 & "," & C.Address
        Else
            rng2 = C.Address
        End If
    End If
    Next   test = rng2 
End Function

This one looks in a range for cells with a specific value and add the cell adresses to a string. This works very well.

Second I have function number 2:

Function CountCellsByColor2(rData As String, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
    Dim test As Long

    'Application.Volatile
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In Range(rData)
        If indRefColor = cellCurrent.Interior.Color Then
            cntRes = cntRes + 1
        End If
    Next cellCurrent

    CountCellsByColor2 = cntRes
End Function

This one uses the string with the addresses and check the cells for a given color and returns a number how many cells have the given color.

So far very simple.

Now my problem, when my strings has not more than 20 cell addresses everything works perfect but when I add to much around 31 the Color function Returns #Value!.

When I use it from out an other sheet lets say =CountCellsByColor2(test("Apple";!SomeSheetF2:F300);H5) for example. I get for some input nothing or the correct answer or #Value!.

So I don't really if I bounce to some limitation of excel or Vba. Or maybe my string of cells get to big. I hope somebody sees quickly what is going wrong.

Answer 1

I believe you are running into a limitation with the Union operator (the comma). The Union method itself has a limit of 30 arguments, so, with your string creation method, returning an error at 31 would make some sense.

EDIT: It seems it is not the Union limitation that is applying. Rather the problem is the length of the argument for the Range object. When I tested, I did not see errors until I had about 40 cells in the argument. Further empirical testing reveals that an error is created when the argument to the Range object is greater than 255 characters. This limitation may also apply to arguments to other VBA objects or statements or methods.

For minimal change to your existing code, try the following, which splits your comma separated list of ranges and tests them one at a time.

This is provided to help you understand what your coding problem is. There may be more efficient methods of coding what you want to do, however.

---

Function CountCellsByColor2(rData As String, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Variant
    Dim cntRes As Long
    Dim test As Long

    'Application.Volatile
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color

    For Each cellCurrent In Split(rData, ",")
        If indRefColor = Range(cellCurrent).Interior.Color Then
            cntRes = cntRes + 1
        End If
    Next cellCurrent

    CountCellsByColor2 = cntRes
End Function

---

Answer 2

Why do you use a String to collect the cells? A collection makes it so much easier.

Function Test(TestValue As String, TargetRange As Range) As Collection
    Dim c As Range

    'Initialize collection
    Set Test = New Collection

    'Loop through range
    For Each c In TargetRange
        If (c.Text = TestValue) Then
            Test.Add c
        End If
    Next c
End Function

Function CountCellsByColor2(rData As Collection, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Range

    'Determine color
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color

    'Loop through all cells
    For Each cellCurrent In rData
        If (cellCurrent.Interior.Color = indRefColor) Then
            CountCellsByColor2 = CountCellsByColor2 + 1
        End If
    Next cellCurrent
End Function

Answer 3

Make the first arg is a String: