# 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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### 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
3
Views
794
Replies
7
Views
382
Replies
4
Views
723
Replies
8
Views
442
Replies
9
Views
418

### Forum statistics

1,172,016
Messages
5,878,746
Members
433,369
Latest member
Applewood ### 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