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]

Lewiy - that seems to eliminate the type conversion issue (I'm sorry - but the word is really bug) that seems to be going on.

 

[pgc01]

Hi Gene

The result makes sense to me.

In this case Empty and False are converted to 0.

It's explained in the help:

1 - Value of an empty cell (Range Value, from the help)

If the cell is empty, Value returns the value Empty.

2 - Boolean Data Type (from the help)

When Boolean values are converted to other data types, False becomes 0 and True becomes -1.

3 - Comparison Operators (from the help)

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

4 - Conclusion

Your

(Range("A20").Value = False) -> (Empty = false) -> (0 = 0)

Kind regards
PGC

 

[mortgageman]

1) I'm sorry, but I just don't see it. Why should an empty cell (which by definition can't have a particular data type) be equal to something with a data type (without being coerced)?

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

 

[Lewiy]

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.

 

[Norie]

Gene

Where is the definition that a cell can't have a particular data type?

You are probably right but I don't ever recall seeing it written down anywhere.

 

[Lewiy]

Presumably all cells have a data type regardless of their content. Just as all variables have a data type (and usually a default value) when you declare them.

The trick would seem to be to make sure that whatever you are doing, you purposefully define the data type and value that you want before attempting to retrive any useful data from it.

 

[mortgageman]

Gene

Where is the definition that a cell can't have a particular data type?

You are probably right but I don't ever recall seeing it written down anywhere.

But I didn't say that. I said an EMPTY cell has no data type - by the defintion of empty.

 

[Norie]

Actually it does - Variant.

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

 

[mortgageman]

Presumably all cells have a data type regardless of their content.

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

 

[Lewiy]

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