Excel 2016-Custom Function not available to use in-cell

[trashcan21]

I am trying to create a custom function (to count the number of cells in a range that have font color = red). I have the code to do this, so that's not my problem. My problem is that having created the function, saved the VBA screen when I go back to the spreadsheet and type "=countcolorif(" the function is just not in the list and if I persist in trying to complete the syntax, I just get "#NAME" error. What's going on?

I googled this a lot and watched YouTube videos but the function still refuses to show in-cell. It is in the user-defined list when I select "Insert Function" on the Formulas ribbon but I do not want to access it from there. I just want to type into the cell itself.

1. Excel version is Microsoft Office pro 2016
2. The function is prefaced with "Public"
3. The vba is located in a new module not in "ThisWorkbook" area.

Code is:

Public Function CountColorIf(rSample As Range, rArea As Range) As Long
    Dim rAreaCell As Range
    Dim lMatchColor As Long
    Dim lCounter As Long

    lMatchColor = rSample.Interior.Color
    For Each rAreaCell In rArea
        If rAreaCell.Interior.Color = lMatchColor Then
            lCounter = lCounter + 1
        End If
    Next rAreaCell
    CountColorIf = lCounter
End Function

What on earth am I doing wrong?

Thanks

[romperstomper]

Perhaps macros are disabled?

[trashcan21]

Hi Rory,

I forgot to mention that I have checked the Trust Center and macros are enabled and code project is trusted

[Kyle123]

I think at this point, you need to upload your workbook and we can see where things have gone wrong

[trashcan21]

Hi Kyle123,

Sample spreadsheet attached as requested.

I think the issue was with the "XLSM" file type. The function now shows up in the "autocomplete" list.
My problem now is that the result is still "#NAME".

The code looks reasonable (I cut n pasted it from a webpage) but clearly does not work.

[Roel Jongman]

I copied and pasted the code into my excel 2016 and it works as described.. so the code is ok.

At the risk of (also) kicking down an open door... but I think better to exclude
Are you by any chance testing / trying to use this function in the Online version (in browser) of excel?
as (most) vba will not work in online versions

[bakerman2]

Did you put the code in a standard module ?

[Pepe Le Mokko]

Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

As you are new here I will add them for you this time

[Kyle123]

Change the name of your module so that it isn't the same as the name of the function, or fully qualify it like this:

=CountColorIf.CountColorIf(B3,D3:D8)

[trashcan21]

Thanks Kyle - good catch. The formula now resolves...but it says "6" on the sheet rather than the 2 expected. Lol - I'll try and debug the code

[Roel Jongman]

rSample.Interior.Color will only catch the cell fill color if you want ot test for font color you need to use rSample.Font.Color

(also change rAreaCell ofcourse

[trashcan21]

Hi Roel,

Yep that's the answer. All works now.Thanks.

And thanks to everyone for spending their time looking at this - I've only just joined the forum and got an avalanche of replies. Totally awesome

[Pepe Le Mokko]

Please acknowledge post #7. Thank you

[trashcan21]

My apologies Pepe - post #7 acknowledged