defining a boolean without it having an actual value

[mortgageman]

In the following code, the test fails, because, once bTest is declared as a Boolean, it becomes equal to FALSE (I checked this after much head scratching). How does one assign a value to a boolean variable only after you make your test?

Sub foo()

Dim bTest As Boolean

If Range("a20").Value = False Then bTest = False ' <= a20 is kept empty
MsgBox bTest

End Sub

 

[mortgageman]

As a practical manner, can data validation be used to restrict cells to only booleans?

I guess you could restrict a cell value to a whole number between 0 and 1 which will effectively give the same result.

I am going to answer by own question here: Data Validation - choose list, and then make the source a range that has your TRUE and FALSE.

 

[mortgageman]

Agreed, but they have to have content first, don't they?

They all have blank content to begin with which will default to zero if challenged, hence why if A1 is blank, =A1 will return 0

But isn't =A1 a form of coercion? My point is that when comparing two quanties (not coercing) with different data types (a particular data type is not the same as a empty or if you must - Variant), the comparison should not yield TRUE.

 

[mortgageman]

Actually it does - Variant.

Well it does when I take a look in the watch window.

An empty cell having Variant data type makes a sorta of sense, but again, it is not the same as a particular data type.

 

[mortgageman]

Agreed, but they have to have content first, don't they?

They all have blank content to begin with which will default to zero if challenged, hence why if A1 is blank, =A1 will return 0

But isn't =A1 a form of coercion? My point is that when comparing two quanties (not coercing) with different data types (a particular data type is not the same as a empty or if you must - Variant), the comparison should not yield TRUE.

Well actually, now that I think of it, is .value a form of coercion?

 

[Diffy]

But isn't =A1 a form of coercion? My point is that when comparing two quanties (not coercing) with different data types (a particular data type is not the same as a empty or if you must - Variant), the comparison should not yield TRUE.

By that logic it shouldn't yeild false either. It should be an error!

 

[Lewiy]

Well actually, now that I think of it, is .value a form of coercion?

Quite. Which is why I said they would have a value when challenged. Any sort of referencing a cell will effectively "coerce" Excel into giving you some sort of value/data type. I guess in a way it's a form of error handling to avoid errors occuring when referencing empty cells/variables.

 

[pgc01]

But isn't =A1 a form of coercion? My point is that when comparing two quanties (not coercing) with different data types (a particular data type is not the same as a empty or if you must - Variant), the comparison should not yield TRUE.

The point I don't agree is

... comparing two quanties (not coercing) ...

What you are saying is not consistent with vba's syntax.

The definition of the logic operators is very clear: there cannot be "comparing two quanties (not coercing)" because compare an Empty value means coercing it to 0.

From the help:

Comparison Operators

...

When comparing two expressions, ...
IF one expression is Empty and the other is a numeric data type THEN perform a numeric comparison, using 0 as the Empty expression.

 

[mortgageman]

Speaking for myself - a) learned a lot and b) it is because of everyone who posted. Thanks!