CODEWITHSUNDEEP
excelvba
Silver Hawk
Silver Hawk

Reputation: 57

#NUM! error in user defined function

I made a new function called length which tests the count of characters and shows "error result" if the count greater than 5. However, the result of function is #NUM! If the test is for 5 characters or less the result is shown.

What is wrong?

Function length (number as integer)
    If ( Len (CStr (number)) > 5 ) then
        length = "error"
    End if
    If ( Len (Cstr (number)) <6) then
        length = "the count is true"
    End if
End Function

Upvotes: 4

Views: 926

Answers (1)

Luuklag
Luuklag

Reputation: 3914

Due to the nature of datatypes this will happen. To be more precise your function will work for values up to, and including, 32,767. Above this it will return #NUM as your number can no longer fit inside the integer you force it to be.

If you define your number as Long datatype it will handle values up to, and including, 2,147,483,647.

For a more detailed explanation of the differences between Integer and Long you can read the answer from RubberDuck on this post: Why Use Integer Instead of Long?

Upvotes: 8

Related Questions