UDF not working

[NMitch]

Hi all,

This is for Excel 2013, and here is my code, which has worked before:

Public Function SumByColor(CellColor As Range, rRange As Range)    
    '   CellColor is the cell containing they colour type you are searching for
    '   rRange is the range of cells to add, if the colour matches
    Dim cSum As Long
    Dim ColIndex As Integer
    ColIndex = CellColor.Interior.ColorIndex
    For Each cl In rRange
        If cl.Interior.ColorIndex = ColIndex Then
            cSum = WorksheetFunction.Sum(c1, cSum)
        End If
     Next cl
     SumByColor = cSum
End Function

When using this in a macro-enabled workbook, the #NAME ? result appears.

The function used to be just a normal function, which resides in my PERSONAL.XLSB, but changed to PUBLIC to see if that made a difference.

I have a habit of doing things, like formulae, in lower case, then seeing if it changes to the proper case after pressing Enter. The UDF is SumByColor(), but the formula shows sumbycolor().

Can someone let me know what I'm doing wrong?

Thank you,

 

[NMitch]

While Excel 2013 is 32 bit, Windows 7 Pro is 64 bit - not sure if this is the issue??

 

[Marcelo Branco]

Maybe something like this...
=PERSONAL.XLSB!SumByColor(A2, B2:B10)
adjust the function parameters to fit with your case.

M.

 

[NMitch]

Ah, yes! That worked a treat! Thank you, Marcello!

 

[NMitch]

Ok, while the formula looks like it works, the UDF returns the value 0.

The formula is =PERSONAL.XLSB!sumbycolor(E623,F623:F626)

Can someone please let me know what I'm doing wrong? (The UDF is in the first post above.)

Thank you,

 

[Norie]

Have you named anything else 'sumbycolor'?

 

[NMitch]

Thanks Norie, good suggestion.

I searched the entire workbook, then all open modules, but could only find the instances referred to in this thread. (I used "sumbycolor" as the search text. I even searched for occurrences of the Australian spelling for colour.)

Thank you,

 

[Norie]

Did you check module named, named ranges etc.?

 

[NMitch]

Thanks again, Norie, but there are no modules in open workbooks nor ranges using the same name.

 

[Marcelo Branco]

Check the variable names in your UDF
It seems you have c1 and cl

To avoid such errors use Option Explicit - take a look at
http://www.cpearson.com/excel/DeclaringVariables.aspx

M.