Function: Error Sub or Function not defined

Question

Function CountColor(rColor As Range, rSumRange As Range)
    Dim rCell As Range
    Dim iCol As Integer
    Dim vResult As Integer
    iCol = rColor.Interior.ColorIndex

    For Each rCell In rSumRange
        If rCell.Interior.ColorIndex = iCol Then
            vResult = 1
        Else 
            vResult =  0
        End If
    Next rCell 
    CountColor = vResult
End Function

I try typing "=CountColor(A1, A2)" but I always get the error "Sub or function not defined" Why is this? I've been stuck on this for hours.

Is this in a workbook module, or worksheet module? It works for me if I place in a Workbook module. — BruceWayne, Mar 08 '16 at 23:49
I think this is a duplicate of: http://stackoverflow.com/questions/12351339/cant-use-vba-functions-in-a-spreadsheet-formula-in-excel-2010 — mechanical_meat, Mar 08 '16 at 23:50
Sorry, what is the difference between the two? I just inserted a module by right clicking on the project and creating a new module. — user3002486, Mar 08 '16 at 23:51
worksheet UDFs should be created in a standard code module **in the same workbook**. Failing that, try giving your UDF an explicit return data type — SierraOscar, Mar 08 '16 at 23:52
That is where it should be. It should be in a module attached to the workbook in which it is being called. — Scott Craner, Mar 08 '16 at 23:52
I can confirm that the module is in the same workbook as it is the only one open. Note: the function shows up when I type =, but just won't compile — user3002486, Mar 08 '16 at 23:55
I gave it a return datatype of Integer and didn't help. It's highlighting the first line of code. This code has worked before, so I don't think it has to do with coding. — user3002486, Mar 09 '16 at 00:04
Is the workbook/worksheet protected in any way? Try closing Excel completely, and open a new workbook and add this macro. Does it work then? — BruceWayne, Mar 09 '16 at 00:04
Doesn't work if I open a new workbook. It's strange because I have gotten this code to work before. It might have to do with permissions or whatnot though. — user3002486, Mar 09 '16 at 00:09

score 1 · Accepted Answer · answered Mar 09 '16 at 07:38

I could not reproduce the error that you are experiencing.

If you use the code as you have it, the result will not be accurate, for example: =CountColor(A1,B1:B20) will only give you a result of 1 or 0 because you are not adding the results together.

If you are just comparing the interior colors, you don't really need to use interior.colorindex, just interior.color should work, so I changed iCol as string

else is not required in your if statement.

I also added Application.volatile to the code, so it will calculate when the sheet calculates.

Function CountColor(rColor As Range, rSumRange As Range)
    Dim rCell As Range
    Dim iCol As String
    Dim vResult As Integer

    iCol = rColor.Interior.Color
    Application.Volatile

    For Each rCell In rSumRange
        If rCell.Interior.Color = iCol Then
            vResult = 1 + vResult
            '        Else
            '            vResult = 0
        End If
    Next rCell

    CountColor = vResult

End Function

I guess that APplication.volatile did the trick? Not sure, but once I added that, the error stopped being produced. Thanks! — user3002486, Mar 09 '16 at 16:40
@user3002486 String is the wrong data type for `iCol`. `.Color` returns numeric, casting it to String is pointless. Better to use `Long` — chris neilsen, Mar 09 '16 at 20:09

Function: Error Sub or Function not defined

1 Answers1