Function Giving #Name? Error

rwmill9716

Active Member
Joined
May 20, 2006
Messages
491
Office Version
  1. 2013
Platform
  1. Windows
I am using the following function:

Function CountCcolor(range_data As Range, criteria As Range) As Long
Dim datax As Range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
If datax.Interior.ColorIndex = xcolor Then
CountCcolor = CountCcolor + 1
End If
Next datax
End Function

And Calling it with this statement:

=countccolor(C50:AG50,AH$26)

where C50:AG50 is a row which has cells with the same color as that found in cell AH$26

The cell in which I'm calling the function is giving me a #Name? error. I've included a figure showing the code, where it's stored and a portion of the Excel table.

Can you see anything that leading to this error in the function?
200102 Function.jpg
200102 Function.jpg200102 Function.jpg
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Basicly your UDF runs fine. However, UDF's must always be declared as Public.
Public is the default setting when you omit this keyword, but not in a module of a worksheet. In such a case your sub or function will always be declared as Private, your function will not be found and the formula where it's in will not be resolved.
You can check this behaviour by setting a breakpoint on the UDF and use your UDF (in another cell) again.
 
Upvote 0
Just move the function into a normal module & you'll be fine.
 
Upvote 0
How do I declare my UDF as Public? What keyword are you referring to?
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
Hi, I am having the same issue. However, I inserted a new module. Not sure what else I need to do!

ss.PNG
 
Upvote 0
As long as macros are enabled, that should be all you need.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top