User defined function gives #NAME?

[Guest]

I've writtena User Defined Function that yields #NAME? when I use it in a
spread sheet. Why is this? The code for the function is below.

The code is in a module in PERSONAL.xls. I have also tried it in a module
in the workbook where it is used. Same result.

This UDF used to work for me. What am I doing wrong?

John Wirt

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

 

[Bob Phillips]

Try using

Personal.xls!SigTest(cell1, cell2, cell3, cell4)

in the worksheet

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

To call it from Personal.xls, try

=Personal.xls!Sigtest(cell1, cell2, cell3, cell4)

 

[Guest]

Not too sure. It worked for me. I did clean it up a bit but the gist of it is
the same...

Function SigTest(cell1 As Range, cell2 As Range, cell3 As Range, cell4 As
Range) As String
If (cell1 - cell2) ^ 2 / (cell3 ^ 2 + cell4 ^ 2) > 3.841447 Then
SigTest = "*"
Else
SigTest = ""
End If
End Function

I am assuming that the functions is stored in a regular code module and not
a sheet or thisworkbook. If so then that would be the culprit...

 

[Guest]

Or consider skipping the function and use the following formula in you
worksheet.

Assume cells 1,2,3,4 are C1,D1,E1,F1 and the formula would look like:

=IF((C1-D1)^2/(E1^2+F1^2)>3.841447,"Happy","Sad")