RDC11020 — Variant String that is no number, compared with a number

Severity: Warning · Family: RDC (Rubberduck Core analyzers) · Raised by: VariantStringThatIsNoNumberRankedAnalyzer

A comparison operator - =, <>, <, >, <=, >= - compares a Variant that holds a String with a number, a Date or a Boolean that is not a Variant: a variable declared with the type, or a literal. And the String is not a number.

When the String is a number ("3", " 7 ", "&H10"), VBA compares the two numbers. When it is not, VBA raises no error: it ranks the String above the number, whatever the number is. So, with v declared As Variant:

v = "abc": v > 5 is True, v = 5 is False, and 5 < v is True; and so for -1000 or 1E+300.
v = "": v > 5 is True.
v = "1/2/1900": v > 5 is True. The text of a date is not a number. Compared with a Date, it is read as one.

This is what Microsoft Office VBA does. The language specification says otherwise: MS-VBAL §5.6.9.5 converts the String to the number's type, which raises run-time error 13 (Type mismatch) when it is not a number, and Microsoft Learn says the same. RD-VBA runs the code as VBA does, and this warning stands in for that type mismatch: the code depends on a behaviour the specification does not give it.

It is also rarely what is meant: a value read from a cell or an InputBox that turns out not to be a number is quietly greater than every number.

How it is raised

Situation	Example	Resolution
A value read as text is compared with a number.	`If entry > 100 Then`, with `entry` a `Variant` holding the text of a cell	Check it is a number first: `If IsNumeric(entry) Then If CDbl(entry) > 100 Then`.
The text is meant to be compared as text.	`If code = 5 Then`, with `code` holding `"A5"`	Compare text with text: `If code = "5" Then`.

Table of Contents

RDC11020 — Variant String that is no number, compared with a number

How it is raised

See also