One False All False

[gmaxey]

In a separate thread I was attempting to show how to validate a command button. The requirement was a collection of optionbutton groups "all" had to evaluate as True (at least one selected in each frame) before the CB was enabled.

I cobbled together something that worked but after thinking about it, I came up with this:

[VBA]Sub IfOneFalseAllFalse()
'A quick macro scratch pad created by Greg Maxey
Dim b1 As Boolean, b2 As Boolean, b3 As Boolean, b4 As Boolean
b1 = True
b2 = True
b3 = False
b4 = True
Dim bAll As Boolean
bAll = b1 * b2 * b3 * b4
MsgBox bAll
End Sub

Sub IfOneTrueAllTrue()
'A quick macro scratch pad created by Greg Maxey
Dim b1 As Boolean, b2 As Boolean, b3 As Boolean, b4 As Boolean
b1 = False
b2 = False
b3 = True
b4 = False
Dim bAll As Boolean
bAll = b1 + b2 + b3 + b4
MsgBox bAll
End Sub
[/VBA]

Both procedures return the correct result or "If One Fasle All False" and "If One True All True."

I just don't understand why it works! Math is not my strong suit but I thought if you multiplied two "-" numbers the result was positive. If that is still true then is seems that if in the first macro two values where false then the overall result would be true.

Can someone explain how this works and comment on the soundness of this method or if there is a better way?

Thanks.

[Frosty]

It's sound, but not because of the two negative numbers multiplied... but because anything multiplied by 0 is zero.

Although CInt(True) = -1, and CInt(False) = 0...

CBool(123145) = True
CBool(0) = False

So... anything other than 0 is treated as True. If you multiple anything by 0 (i.e., False), it becomes False(0).

So I think the better way is to just stick with a single routine using the multiplication. Couldn't the addition method (where you'd just want to test if it's less than 0) have an issue with triple state?
[VBA]
Public Function AllTrue() As Boolean
Dim b1 As Boolean, b2 As Boolean, b3 As Boolean, b4 As Boolean
b1 = True
b2 = True
b3 = True
b4 = True

Dim bAll As Boolean
bAll = b1 * b2 * b3 * b4
AllTrue = CBool(bAll)
End Function
[/VBA]

[gmaxey]

Jason, Ok got it. Thanks.

[Paul_Hossler]

Was there a reason you didn't want to use And and Or?

IMHO, when I can back to the code after 1-2 years, my aging brain would remember what I was doing with the And's and the Or's

[vba]
Option Explicit
Sub IfOneFalseAllFalse()
Dim b1 As Boolean, b2 As Boolean, b3 As Boolean, b4 As Boolean
b1 = True
b2 = True
b3 = False
b4 = True
Dim bAll As Boolean
bAll = b1 And b2 And b3 And b4
MsgBox bAll
End Sub

Sub IfOneTrueAllTrue()
'A quick macro scratch pad created by Greg Maxey
Dim b1 As Boolean, b2 As Boolean, b3 As Boolean, b4 As Boolean
b1 = False
b2 = False
b3 = True
b4 = False
Dim bAll As Boolean
bAll = b1 Or b2 Or b3 Or b4
MsgBox bAll
End Sub
[/vba]


I did my own multi-AND and multi-OR in Excel (just a 'by the way')

[vba]
Function GetAND(IfTrue As Variant, IfFalse As Variant, ParamArray TestCond() As Variant) As Variant
Dim i As Long

GetAND = IfFalse

For i = LBound(TestCond) To UBound(TestCond)
If Not TestCond(i) Then Exit Function
Next i

GetAND = IfTrue

End Function

Function GetOR(IfTrue As Variant, IfFalse As Variant, ParamArray TestCond() As Variant) As Variant
Dim i As Long

GetOR = IfTrue

For i = LBound(TestCond) To UBound(TestCond)
If TestCond(i) Then Exit Function
Next i

GetOR = IfFalse

End Function
[/vba]

Paul

[gmaxey]

Paul,

I've never used "ParamArray" how to you employ your two functions:

I tried:

[VBA]Sub ScratchMacro()
'A quick macro scratch pad created by Greg Maxey
Dim T1 As Boolean
Dim T2 As Boolean
Dim T3 As Boolean
Dim T4 As Boolean
Dim T5 As Boolean
Dim T6 As Boolean
Dim T7 As Boolean
T7 = True
MsgBox GetAND(T1, T2, T3, T4, T5, T6, T7)
MsgBox GetOR(T1, T2, T3, T4, T5, T6, T7)
End Sub
Function GetAND(IfTrue As Variant, IfFalse As Variant, ParamArray TestCond() As Variant) As Variant
Dim i As Long

GetAND = IfFalse

For i = LBound(TestCond) To UBound(TestCond)
If Not TestCond(i) Then Exit Function
Next i

GetAND = IfTrue

End Function

Function GetOR(IfTrue As Variant, IfFalse As Variant, ParamArray TestCond() As Variant) As Variant
Dim i As Long

GetOR = IfTrue

For i = LBound(TestCond) To UBound(TestCond)
If TestCond(i) Then Exit Function
Next i

GetOR = IfFalse

End Function
[/VBA]

Both return False.

Thanks.

[Paul_Hossler]

The User's manual is always the last thing

[VBA]
MsgBox GetAND("all were true", "at least was false", T1, T2, T3, T4, T5, T6, T7)
MsgBox GetOR("at least one was true", "none were true", T1, T2, T3, T4, T5, T6, T7)

[/VBA]

Paul

[gmaxey]

Got it. Thanks.