As Integer datatype allows fractions

[JenniferMurphy]

I have a function defined like this:

Function Test(p1 as Integer) as Integer
test = p1 + 1
End Function

If it is called from a worksheet and passed a non-integer (=test(5.5)), it happily rounds it to 6 and returns 7. I was expecting a #Value error.

Can I get VBA to reject non-integer data if I declare the parameter as “Integer”?

If not, do I have to test every parameter inside the function to make sure it’s the right type? I thought that was what the declaration was for.

Puzzled...

 

[jarjarbingie]

I have a function defined like this:

Function Test(p1 as Integer) as Integer
test = p1 + 1
End Function

If it is called from a worksheet and passed a non-integer (=test(5.5)), it happily rounds it to 6 and returns 7. I was expecting a #Value error.

Can I get VBA to reject non-integer data if I declare the parameter as “Integer”?

If not, do I have to test every parameter inside the function to make sure it’s the right type? I thought that was what the declaration was for.

Puzzled...

try:


Function Test (p1 as Integer) as Integer
If p1 - p1 <> 0 Then
MsgBox "Not an Integer"
Else
test = p1 + 1
End If
End Function

 

[JenniferMurphy]

try:
Function Test (p1 as Integer) as Integer
If p1 - p1 <> 0 Then
MsgBox "Not an Integer"
Else
test = p1 + 1
End If
End Function

Does that work for you? It doesn't for me. The MsgBox never gets executed.

If I set a breakpoint on the Function statement and hover over p1, I can see that it has been rounded before any function statements get executed.

 

[jarjarbingie]

oh right.. apologies.. I remember it worked for me before but I've used the code as a subroutine not a user-defined function..

 

[dmt32]

is this what you are are trying to do?

Function Test(p1 As Variant) As Integer
    If Not Int(p1) = p1 Then
        'show #VALUE! error
        Test = ""
    Else
        Test = CInt(p1 + 1)
    End If
End Function

Dave

 

[JenniferMurphy]

No, No No. That doesn't work either and for the same reason. P1 has already been turned into an integer when it is passed to the function.

Here's my situation:

A1: 5.5
A2: =test(A1)

Since the only parameter to the Test function has been declared as type Integer on the Function statement, I want VBA to reject the data in A1 because it is not an integer. If I put "ABC" in A1,

A1: ABC
A2: =test(A1)

I get a #VALUE! error.

A1: ABC
A2: #VALUE!

Why not for 5.5?

 

[RoryA]

Unfortunately, for whatever reason (I suspect everything is passed ByVal and not ByRef), UDFs behave differently and will implicitly convert types where possible. You will have to test each variable type, I think.

 

[JenniferMurphy]

Unfortunately, for whatever reason (I suspect everything is passed ByVal and not ByRef), UDFs behave differently and will implicitly convert types where possible. You will have to test each variable type, I think.

I tested explicitly specifying ByVal and ByRef. Made no difference.

So I'll have to declare it as a Variant or a String and then test it myself. I'd love to hear some Microsoft developer defend this unholy mess.

 

[JenniferMurphy]

Here's some code that seems to work, at least so far...

Function Test(p1 As Variant) As Integer
If p1 <> Int(p1) Then Test = CVErr(xlErrValue): Exit Function
Test = p1 + 1
End Function

 

[RoryA]

I tested explicitly specifying ByVal and ByRef. Made no difference.

I meant that everything is passed ByVal regardless of what you specify in the code (the default is ByRef). That would make sense since you can't alter the value of another cell from a function in a cell.