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

 

[Lewiy]

Something tells me that the solution is to ensure that your Boolean variable is given a particular value whatever the outcome of the test, i.e:

Sub foo()
Dim bTest As Boolean
If Range("a20").Value = False Then
    bTest = False
    Else: bTest = True
MsgBox bTest
End Sub

 

[mortgageman]

Something tells me that the solution is to ensure that your Boolean variable is given a particular value whatever the outcome of the test, i.e:

Sub foo()
Dim bTest As Boolean
If Range("a20").Value = False Then
    bTest = False
    Else: bTest = True
MsgBox bTest
End Sub

I don't think that did it. When I ran the code (I added the end If :wink: )
It displayed TRUE for bTest. Which means that the test was evaluating an empty cell the same as FALSE. I am quite perplexed to say the least

Sub foo()
Dim bTest As Boolean
If Range("a20").Value = False Then
    bTest = False
    Else: bTest = True
End If
MsgBox bTest
End Sub

 

[Joe4]

How does one assign a value to a boolean variable only after you make your test?

Here is my theory as to what is happening.

Once you declare a variable as boolean, it must be either TRUE or FALSE. In most computer languages, FALSE is equivalent to 0 and TRUE is usually equivalent to 1 (or -1).

So, when you declare it as a BOOLEAN, it probably defaults to 0, which is equivalent to FALSE.

Just like if you were to declare as an INTEGER. If you check the value of that variable before you assign a value to, it will return 0, not NULL.

 

[Norie]

Gene

Why not actually use the result of the expression to assign a value to the variable?

bTest = Not(Range("A20").Value =False)

 

[Lewiy]

Ahh, "End If" my arch nemesis!!

What is the value of Range("A20")? Is it simply a True/False result?

 

[mortgageman]

Run this code in a brand new worksheet. Don't type anything in any cell. You will see that the result is TRUE. Why should that be? Why should an empty cell have the same value as FALSE?

Sub Foo()
MsgBox (Range("A20").Value = False)
End Sub

 

[mortgageman]

JM - you are 100% correct. But that doesn't explain my second question which is why does an empty cell (seem to) have the same value as FALSE?

 

[Norie]

Gene

If you hover over Range("A20").Value the value is Empty.

My guess is that VBA is regarding that as false just like it would regard 0 as false.

This is probably to do with type conversion which can produce strange results in VBA, as well as Excel itself.

 

[Lewiy]

Perhaps:

Sub Foo() 
If Range("A20")="" then
    MsgBox "False"
    Else
         MsgBox (Range("A20").Value = False) 
End If
End Sub