VBA IF NOT [function] then [code] runs code regardless of function output-CodePudding

I wrote the following code:

Sub iojh()

If Not foo() Then MsgBox ("hi")

End Sub

Function foo() As Integer
foo = 0
End Function

It shows the msgbox when foo = 0 and when foo = 1. Why is this? I found a workaround using an If Else statement, but I'd like to know what's going on and if there's a better solution. This is my workaround:

Sub iojh()

If foo() Then
    '
Else
    MsgBox ("hi")
End If

End Sub

Function foo() As Integer
foo = 1
End Function

CodePudding user response：

Delcare foo() function as `Boolean type. Try-

Sub iojh()
    If Not foo() Then MsgBox ("hi")
End Sub

Function foo() As Boolean
    foo = 0
End Function

CodePudding user response：

Boolean values are either True or False.

At a bit level, computers store that information as either a 1, or a 0, respectively.

In vba, True = -1 and False = 0 - but.. why?

However, almost all computer systems hold their bits in patterns of 8 bits and multiples thereof. For the ease of display, let's consider an 8 bit system - where the data is stored in a byte:

0 = False as a bit, becomes 00000000 as a byte - all off.

1 = True as a bit, becomes 11111111 as a byte - all on.

As you can see, all the bits in the True byte are flipped.

The other thing to remember is that in this case these bytes use 'sign-magnitude' representation and the left most bit is used as the sign ( ve or -ve). So where the byte contains 11111111, when converted back to a decimal comes out as -1.

Another way to think of it: adding 1 to 11111111 would result in 00000000 as the extra bit is lost, so subtracting 1 from 00000000 results in 11111111.

Writing Decimal 1 to boolean

When you write 1 to a boolean data type in vba, it must be represented as either all zeros: 00000000, or all ones: 11111111. The conversion is set up such that it becomes 11111111. Seemingly, writing any bit to the value causes all bits to become '1' - resulting in an output of True.

Writing Not ( Decimal 1) to boolean

When you write (or test) Not (1) to a boolean though, the Not operand is performed first. But, thinking of those numbers as bits, 1 = 00000001, so Not (1) = 11111110. Again, as per the rule above, as at least 1 (in this case, 7) bits are on, all switch on. This results is an output of True.

So as you can see, both 1 and Not(1) when converted to boolean become True, causing your issue.

I would recommend considering changing your system to use 0 and -1, or code a small converter function to keep things aligned.

Note: I have had stretch my memory further back than I'd like to admit in answering the above, so forgive me if some of the language or understanding has shifted since I left school (when 8-bit was the norm)- but I think it all still holds true. I note that SQL bit columns do not work the same way for instance due to the way they're stored, but that is not the question here.