# 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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### BJungheim

##### Well-known Member
It returns #N/A for me.

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

#### jjyxk845

##### Board Regular
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``````

#### patrickmuldoon99

##### Active Member
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``````

#### jjyxk845

##### Board Regular

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! #### BJungheim

##### Well-known Member
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

#### jjyxk845

##### Board Regular
Many Thanks! Case closed! that worked!

Replies
7
Views
153
Replies
5
Views
99
Replies
0
Views
243
Replies
4
Views
516
Replies
0
Views
253

### Forum statistics

1,136,647
Messages
5,676,983
Members
419,667
Latest member
MegEri ### 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