#NUM! error in user defined function

Question

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

Luuklag · Accepted Answer · 2018-05-22 11:52:13Z

8

+500

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?

edited May 22, 2018 at 11:52

answered May 22, 2018 at 11:34

Luuklag

3,91412 gold badges40 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Darren Bartrup-Cook Over a year ago

You could also replace the first end if and the test for <6 and replace with Else. i.e. If it's >5 then error, else it's true. Also, rather than length = "error" you could use length = CVErr(xlErrNum) to return an error value (#NUM!). Returning a real error allows you to use =IFERROR(...) cpearson.com/excel/ReturningErrors.aspx

Luuklag Over a year ago

Good suggestions @DarrenBartrup-Cook

Silver Hawk Over a year ago

I made the number variable as string. It worked. The wrong was in the type of data. I made the type as long and it also worked perfectly @luuklag

Collectives™ on Stack Overflow

#NUM! error in user defined function

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related