CODEWITHSUNDEEP
excelvbabooleanbasic
akcramblet
akcramblet

Reputation: 1

Basic Boolean Question in Excel VBA: Output -1

True * True * True = -1

When I multiply an odd number of boolean variables equal to true, the result is -1. Why is this?

Isn't this: TRUE * TRUE * TRUE

the same as this:

1 * 1 * 1?

Why does vba return a -1, and Excel return a 1 when evaluating this?

I've been using writing code like this

If Asb(TRUE * TRUE * TRUE) =1 then...

or this

If TRUE * TRUE * TRUE <> 0 then ...

I would like to understand why the code behaves this way. Thanks in advance for an explanation.

Upvotes: 0

Views: 2350

Answers (3)

Dy.Lee
Dy.Lee

Reputation: 7567

In Vba Boolean (https://learn.microsoft.com/en-us/dotnet/api/system.boolean?view=netframework-4.8)

False  :  0
True  :  ..., -3,-2.5,-2,-1.5, -1,1.5,2,1.5,3,......

Since Double except 0 is true, code can be written in various formats as shown below.

Sub testString()
    Dim myBool As Boolean
    Dim str As String
    Dim n As Double

    str = "Mountain"

    n = InStr(str, "t") '<~~ 5
    If InStr(str, "t") > 0 Then
        MsgBox n
    End If

    '** below same
    If InStr(str, "t") Then
        MsgBox n
    End If

    '** below same
    If n Then
        MsgBox n
    End If

End Sub

Boolean Debug test

enter image description here

Upvotes: -1

Bill Hileman
Bill Hileman

Reputation: 2836

You have to stick to -1 and 0 for true and false. To confirm what your language uses for true, simply assign zero to a variable (false), doesn't have to be boolean, then print not(variable) to determine what true is. This does not work the other way around unless the original value is -1. The bitwise not operator reverses all bits, and -1 is a two bit number where all bits are set, zero all bits are cleared. If you assign a variable with any other non-zero number, using bitwise not will still return true when converted to boolean.

One in binary is fifteen zeroes and a one. Flip all those bits and you get fifteen ones followed by a zero, which is a negative value because that first bit denotes negative number, but it won't be negative one. Flipping all the bits again returns it to its original value - one.

Let me know if this is not clear.

Upvotes: 1

luizinhoab
luizinhoab

Reputation: 475

Boolean data type A data type with only two possible values, True (-1) or False (0). Boolean variables are stored as 16-bit (2-byte) numbers. See data type summary.

https://learn.microsoft.com/en-us/office/vba/language/glossary/vbe-glossary#data-type

Upvotes: 1

Related Questions