CODEWITHSUNDEEP
vbaexcelisnumeric
Rohit Saluja
Rohit Saluja

Reputation: 1517

VBA IsNumeric going WILD

Please explain why the below code behaves randomly

The below line of code returns TRUE when it should have return FALSE

?Isnumeric("555-")

Also

?Isnumeric("555-"/2) returns TRUE

Please explain this random behavior of IsNumeric?

Upvotes: 5

Views: 798

Answers (3)

Robin Mackenzie
Robin Mackenzie

Reputation: 19299

Although it is a bit esoteric the trailing minus is a valid numeric format sometimes used in accounting packages. I guess it is not used so much nowadays. It indicates a negative number e.g. 555- is -555. Your second example works because -555 (or 555-) can be divided by 2 i.e. -227.5 (or 227.5-).

You can see in the Excel UI where it allows the format as part of Text to Columns:

enter image description here

Also, you can set-up a number format to use trailing negative:

#,##0;#,##0-

See this blog-post.

Upvotes: 9

pnuts
pnuts

Reputation: 59495

From Microsoft:

IsNumeric returns True if the data type of Expression is Boolean, Byte, Decimal, Double, Integer, Long, SByte, Short, Single, UInteger, ULong, or UShort, or an Object that contains one of those numeric types. It also returns True if Expression is a Char or String that can be successfully converted to a number.

IsNumeric returns False if Expression is of data type Date or of data type Object and it does not contain a numeric type. IsNumeric returns False if Expression is a Char or String that cannot be converted to a number.

Upvotes: 3

z32a7ul
z32a7ul

Reputation: 3797

Looks like it interprets "555-" as -555. After checking IsNumeric, you may detect this situation like this:

Cstr(CLng("555-")) = "555-"

Upvotes: 2

Related Questions