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

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.

jjyxk845

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

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
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
Joined
Sep 8, 2006
Messages
89

ADVERTISEMENT

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! :)
 

BJungheim

Well-known Member
Joined
Jul 24, 2004
Messages
914
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
 

Forum statistics

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