User defined function gives #NAME?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Wirtr - 21 Sep 2006 22:19 GMT

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

Reply to this Message

Bob Phillips - 21 Sep 2006 22:26 GMT

Try using

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

in the worksheet

Signature

HTH

Bob Phillips

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

> 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.
[quoted text clipped - 16 lines]
> End If
> End Function

Reply to this Message

JMB - 21 Sep 2006 22:34 GMT

To call it from Personal.xls, try

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

> 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.
[quoted text clipped - 16 lines]
> End If
> End Function

Reply to this Message

Jim Thomlinson - 21 Sep 2006 22:35 GMT

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...

Signature

HTH...

Jim Thomlinson

> 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.
[quoted text clipped - 16 lines]
> End If
> End Function

Reply to this Message

tplourde - 22 Sep 2006 00:04 GMT

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")

> 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.
[quoted text clipped - 16 lines]
> End If
> End Function

Reply to this Message