#Name? Error

[Mixphonics]

Hello Mr. Excel forum,

I have been using a custom function in one of my workbooks for over a year. Recently I noticed that that function stopped working correctly along with another function I use. I am able to run a macro that I wrote without any issues in the same workbook and macro trust level settings are set to enable all macros. Here is the function that I have been using which recently stopped working giving me the #NAME? message in each cell that I try to use it with:

Function SumColor(rColor As Range, rSumRange As Range)


Dim rCell As Range
Dim iCol As Integer
Dim vResult
iCol = rColor.Interior.ColorIndex
For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = WorksheetFunction.Sum(rCell) + vResult
End If
Next rCell
SumColor = vResult
End Function

I copied this function off the web but would appreciate any help if anyone knows how to correct this errror?

Best regards,

Mixphonics

 

[jai9]

Hi,

You will get #Name error if the custom function does not exist in the file.

Check if the code still exists in the VB editor.

Jai

 

[Mixphonics]

Hello Jai,

I've double checked that the code is still in the VB editor.

The way that I have to call the function has changed, looking under formulas>insert function and under category user defined:

I found the SumColor formula and now have to type this ='Sheet1.xlsm'!SumColor.SumColor(I1970,I4:I1968) instead of =SumColor(I1970,I4:I1968)

Do you know how to fix this so I can call the function by the shorter name?

Thank you for your help.

 

[jai9]

Hi,

I am not exactly sure why the way in which you have to call the function has changed.

Is the function in a module or in the code window for sheet 1?

Jai

 

[Mixphonics]

The function is in it's own module - I can call the function by using the formula syntax =SumColor.SumColor(I1970,I4:I1968) and that works as well. Strange, I don't know what I did but somehow I must have changed the name. It would be nice to know how to change back though to fix the problem.

Thanks for your help.

Mixphonics

 

[xenou]

It's a little puzzling what you have done to change the behavior. What you are describing is what I would expect for a function in another workbook - where you must provide the workbook name as prefix to the function name. However, I would recommend you put your function into an addin (in a standard module). Functions in addins should be available to all open workbooks, and you would only need to give the function name.

 

[Jerry Sullivan]

One possible cause of the change in behavior is if there are two functions with the same name in the same VBA Project.
This would cause the functions to need to be referenced using the module name as you observed.
Did you duplicate your function and copy it to/from a module named SumColor?

Of course as xenou points out you should consider moving custom worksheet functions to an addin.