# Quick question about "test = CVErr(xlErrNA)"

#### jjyxk845

##### Board Regular
Ive got the following code.

Code:
``````Function test(x As Variant)
If x = 0 Xor x = 1 Xor x = -1 Then
test = x
Else
MsgBox ("x must be -1,0,1")
test = CVErr(xlErrNA)
End If

End Function``````

The problem I've got is its returning "#VALUE!" to the cell rather than the desired "#N/A" when x dosen't equal -1,0 or 1

Can anyone point out where i'm going wrong?

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
It returns #N/A for me.

I run Excel 2k3. What version do you have?

I simplifed the code in my example and your right it does work i should have tried it first . heres the full code that doesn't work . I'm running 2k3 as well.

Code:
``````Function Roundsig(ynumber As Variant, xdirection As Variant, sig_amount As Variant, allow_decimals As Variant) As Double

Dim n As Integer, x As Double
Dim xnumber As Variant
If xdirection = 0 Xor xdirection = 1 Xor xdirection = -1 Then

If sig_amount < 1 Or sig_amount <> Int(sig_amount) Then
Roundsig = CVErr(xlErrNA)
ElseIf allow_decimals <> 0 And allow_decimals <> 1 Then
Roundsig = CVErr(xlErrNA)
Else

xnumber = Abs(ynumber)
xsign = Sgn(ynumber)

If xnumber >= 1 Then

x = xnumber
n = 0
While x > 1
x = x / 10
n = n + 1
Wend

'    x = x * 10 ^ sig_amount

x = xnumber * 10 ^ (sig_amount - n)

If xdirection = -1 Then
x = Int(x)
ElseIf xdirection = 0 Then
x = Int(x + 0.5)
ElseIf x = Int(x) Then
x = Int(x)
ElseIf x <> Int(x) Then
x = Int(x) + 1
End If

x = x * 10 ^ (n - sig_amount)

If allow_decimals = 0 Then
'  If xdirection = -1 Then
'      x = Int(x)
'  ElseIf xdirection = 0 Then
x = Int(x + 0.5)
'  ElseIf x <> Int(x) Then
'      x = Int(x) + 1
'  End If
End If
Else
x = xnumber
n = 0
While x < 0.1
x = x * 10
n = n - 1
Wend

x = x * 10 ^ sig_amount
If xdirection = -1 Then
x = Int(x)
ElseIf xdirection = 0 Then
x = Int(x + 0.5)
ElseIf x <> Int(x) Then
x = Int(x) + 1
End If

x = x * 10 ^ (n - sig_amount)

If allow_decimals = 0 Then
' If xdirection = -1 Then
'     x = Int(x)
' ElseIf xdirection = 0 Then
x = Int(x + 0.5)
' ElseIf x <> Int(x) Then
'     x = Int(x) + 1
' End If
End If
End If
x = x * xsign
Roundsig = x
End If
Else
Roundsig = CVErr(xlErrNA)
End If

End Function``````

This works fine for me...

called by saying:

Code:
``[a1] = test(3)``

writes #N/A into cell A1...

Code:
``````Function test(x As Variant)
If x = 0 Or x = 1 Or x = -1 Then
test = x
Else
MsgBox ("x must be -1,0,1")
test = CVErr(xlErrNA)
End If

End Function``````

Yes sorry the first example works! See above! And if you could have a look at the second lot of code? that would be great!

In order for the function to return #N/A it has to be declared as a Variant type. You have it declared as a Double:

Function Roundsig(ynumber As Variant, xdirection As Variant, sig_amount As Variant, allow_decimals As Variant) As Double

Many Thanks! Case closed! that worked!

Replies
9
Views
260
Replies
7
Views
692
Replies
1
Views
1K
Replies
1
Views
553
Replies
1
Views
1K

### Forum statistics

1,217,761
Messages
6,138,452
Members
450,139
Latest member
Sushmita Ramesh Kulkarni

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back