Why #NAME?

[John Wirt]

Here is a simple user-defined function. It is the basic test for statistical
significance.

When I insert this code in a module in PERSONAL.XLS and use it in a cell in
an open workbook, I get the error message: #NAME?

If I insert this function in a module of the open workbook, it works.

Why doesn't this UDF work when inserted in PERSONAL.XLS?

Thank you.

John Wirt
---

Public Function SigTest(cell1, cell2, cell3, cell4)
Dim testval As Long

testval = (cell1 - cell2) * (cell1 - cell2)
testval = testval / (cell3 * cell3 + cell4 * cell4)
If testval > 3.841447 Then
SigTest = "*"
Else
SigTest = ""
End If

End Function

 

[David McRitchie]

Hi John,

use =personal.xls!SigTest(....)

You might want to give your parameters more descriptive
names than cell1, cell2, cell3, cell4 as they are your documentation
as to what is needed
for each parameter without going to great lengths.

Macros are found from open workbooks, but not user defined functions.

If that is not acceptable you can create a Reference in your VBE
http://www.mvps.org/dmcritchie/excel/install.htm
but I did that in the past and it got me into trouble. Can't remember
what that problem was though.

 

[jwirt212]

Thanks, David. An Add-in looks like a good solution. Your web pages are
great. Glad to find them.
Yes, I'll change the cell1, cell2. John