Quick question about "test = CVErr(xlErrNA)"

jjyxk845

Board Regular
Joined
Sep 8, 2006
Messages
89
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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I simplifed the code in my example and your right it does work i should have tried it first :oops: :oops: :oops: . 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
 
Upvote 0
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
 
Upvote 0
Yes sorry :oops: the first example works! See above! And if you could have a look at the second lot of code? that would be great! :)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top