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
– BruceWayne, Commented Mar 8, 2016 at 23:49
I think this is a duplicate of: stackoverflow.com/questions/12351339/… — mechanical_meat
– mechanical_meat, Commented Mar 8, 2016 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
– user3002486, Commented Mar 8, 2016 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
– SierraOscar, Commented Mar 8, 2016 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
– Scott Craner, Commented Mar 8, 2016 at 23:52

Davesexcel · Accepted Answer · 2016-03-09 07:38:11Z

1

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

answered Mar 9, 2016 at 7:38

Davesexcel

6,9922 gold badges31 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3002486 Over a year ago

I guess that APplication.volatile did the trick? Not sure, but once I added that, the error stopped being produced. Thanks!

chris neilsen Over a year ago

@user3002486 String is the wrong data type for iCol. .Color returns numeric, casting it to String is pointless. Better to use Long

Collectives™ on Stack Overflow

Function: Error Sub or Function not defined

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related