4

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
Community
  • 1
  • 1
Silver Hawk
  • 57
  • 6

1 Answers1

7

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?

Luuklag
  • 3,897
  • 11
  • 38
  • 57
  • 3
    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(...)` http://www.cpearson.com/excel/ReturningErrors.aspx – Darren Bartrup-Cook May 22 '18 at 14:27
  • Good suggestions @DarrenBartrup-Cook – Luuklag May 22 '18 at 14:46
  • 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 – Silver Hawk May 23 '18 at 00:49