#name

[tyeung4]

I have a UDF that takes an argument:
Function func( Optional s1 As String )

If s1 = "A" Then
func = 10
Else
func = 20
End If

End Function

In a workbook,
A B
1 A =ROUND(func(A1)/10, 2)


The value in B1 is 1.00. I saved the workbook, and re-opened i
automatic
calculation mode. As soon as I changed the content of A1 to B, value i
B1
was changed to #NAME.

Anyone knows why?

Teres

 

[David McRitchie]

Please stop. You already asked this question twice
in another thread in this *newsgroup* with the same subject name. .
Obviously ExcelForum is not giving you a clear indication of
when you have submitted a question to a newsgroup.

 

[BrianB]

Don't know. Your function works OK for me.
#NAME error means that it is interpreting something as a name, which it
cannot find. eg. function name, range name.

 

[tyeung4]

This is not happening all the times, it looks to me MS Excel is no
consistent

 

[tyeung4]

I have attached a couple of files which can illustrate the problem.
1. Open test#NAME.xla
2. Create a new file
3. In cell A1, enter s1
cell A2, enter s2
cell B1, enter '=foo(A1,A2)
4. The value in B1 is 10.5
5. Save the workbook and close
6. Now open the saved workbook again
7. In cell A1, change the content to "blah"
4. Notice the value in B1 is changed to #NAME, it is expected to be
100.5

Anyone knows why?

Attachment filename: test#name.xla
Download attachment: http://www.excelforum.com/attachment.php?postid=417121

 

[David McRitchie]

Excel is very consistent. It cannot find your function.
If you have a macro excel can find it in your personal.xls
workbook if it is open and hidden, it cannot find your function.
You may have to code =personal.xls!func(A1)

Take a look at
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.mvps.org/dmcritchie/excel/formula.htm#install

You function works for me, and everyone else, but I would suggest that
you make a modification to the first line, has nothing to do
with your #NAME? error, but is what kind of data is to be returned.
Function func( Optional s1 As String ) as Long

 

[tyeung4]

I am sorry about the multiple postings. I have no idea how it got poste
so many times.

Anyways, thanks for the info.

The problem is I want to distribute the addin for other users to us
the UDF.

I have attached the addin in my other posting.
Do you see anything wrong with it?

Thanks again

 

[tizzy]

I had a similar problem and I have found an interesting article in
Microsoft's Knowledge Base (No.: 277608):

http://support.microsoft.com/default.aspx?scid=kb;en-us;277608&Product=xl2002

Have a look there and also in the related article 217221

I managed to resolve the problem, but it was not very easy.

I hope it helps!

 

[tyeung4]

Hi Tizzy,
Thanks for your response. My macro is already digitally signed. And
am experiencing the problem on Excel 2000

 

[BrianB]

It looks like Excel cannot find your custom function. Try making i
Public eg. ...........

Public Function foo()
End Functio