VBA why doesn't compare of text to number result in error?

[TomCon]

I have this line in VBA
If (r.Offset(0, OfsPtile).Value < StdCut(i)) Then

The value on the sheet for r.Offset(0,OfsPtile).value is
--
(The character string "--" which is the result of an excel formula with IFERROR and "--" is the result when there is an error.

StdCut is dimensioned Double and has a value say .01

The above VBA statement evaluates to FALSE, not error. Why?

I am sure the value "--" is being compared in the VBA code as in the immediate window i type and get the following
?r.Offset(0, OfsPtile).Value
--

If i type the following in the immediate window, i get a VBA error
? "--" < .01

I want VBA to generate an error on the statement, not evaluate it as FALSE, just like it does in the immediate window

Is there an explanation for why the VBA IF statement evaluates as FALSE and not error?
Is there a workaround to use this IF statement and depend on it NOT evaluating to FALSE if it ends up comparing to a string?

Thank you.

 

[Micron]

You're asking IF "--" is less than .01, which is false. If I may guess at the rest, I'd say the lack of error is due to .01 being interpreted as text because the leading portion of the logical test is text. If you reverse it as .01 > "--" it might raise an error but I don't know. Some aspects of vba coerce data types when they are used - not sure why. If that test doesn't work as I imagine then it is likely that the presence of text coerces all values to text. That would be similar to how & handles Null differently than +. If you want to be sure to raise an error, then try a conversion function on the number, such as IF "--" < Cint(.01).

 

[MARK858]

The above VBA statement evaluates to FALSE, not error. Why?

Text is always > a number, I think it is possibly that it takes its ASCII number into account (just a guess). It doesn't evaluate to an error on a spreadsheet either.

Book1.xlsb
	A
2	aa
3	43
4
5
6	TRUE
7	TRUE
8	FALSE
9	FALSE
Sheet1

Cell Formulas
Range	Formula
A6	A6	=A2>A3
A7	A7	=A3<A2
A8	A8	=A3>A2
A9	A9	=A2<A3

 

[MARK858]

Not tested it but have you tried

If r.Offset(0, OfsPtile).Value * 1 < StdCut(i) Then

 

[Alex Blakenburg]

I want VBA to generate an error on the statement, not evaluate it as FALSE, just like it does in the immediate window

You are not comparing like with like.
If in the immediate window you put what your are using in VBA it will also return False.
eg ? "--" < .01 generates an error
but with the ActiveCell containing "--"

? ActiveCell.Value
--
? ActiveCell.Value < .01
False

I think this is pretty close to what is happening:
Per: Microsoft > Comparison operators

If	Then
One Variant expression is numeric and the other is a string	The numeric expression is less than the string expression.

Which in your case would mean "--" is greater than 0.01 hence your expression is returning False.

 

[Micron]

Text is always > a number,

I don't think that is true. Textual comparisons are based on sort order so aaa sorts before aa so aaa>aa is true.

It doesn't seem to me to be related to Ascii values:
?Asc(43) : returns 52, which is Ascii for 4, not 43
?Asc(4)+Asc(3) : 103 . While that is greater than 97, it is not greater than 97+97 (aa)
even 1 is greater than a because numbers sort before letters.
?1>a
True
I suspect multiplying by 1 is just another means of converting to a number, same as Cint as suggested.

 

[Micron]

eg ? "--" < .01 generates an error

I say that's because one is definitely text, the other a number.

 

[MARK858]

I suspect multiplying by 1 is just another means of converting to a number, same as Cint as suggested

It is normally, or to produce an error if it can't be converted the same as you would do in normal cells although in a cell I would do

Book1.xlsb
	A
2	aa
3	99999999999
4
5
6	#VALUE!
Sheet1

Cell Formulas
Range	Formula
A6	A6	=--A2>A3

The ASCII part was a guess as I have never seen MS explain it

so aaa sorts before aa so aaa>aa is true.

But aa is not a number. My statement was
Text is always > a number,

 

[Alex Blakenburg]

PS:

I want VBA to generate an error on the statement, not evaluate it as FALSE, just like it does in the immediate window

You could force an error using cdbl(your_cell) but it would be much better to use If isnumeric(your_cell) = False to handle the issue

 

[Micron]

Text is always > a number,

If you said "sorts before" I'd agree. Just using the word "number" isn't precise enough for this conversation IMO.
You can't compare text data type with number data type. Don't forget, 123 can be text data type as well as number data type. Without delimiting as "123" it will pass as number data type.
"aa" > .01 (text > number data type) will raise an error, which is why I suggested to convert.