excel - Cannot use an If-statement with multiple conditions (AND/OR) when looping through variant array?

Question

Background:

In working with some variant arrays to sort data to multiple locations based on criteria, I noticed that using an if-statement with multiple criteria flagged as false every time. This was being used to create a dictionary, though it never made it to the dictionary aspect, due to the false response when looping only through the variant array.

I moved these to two separate if-statements and everything worked as expected.

---

Question:

Why am I unable to use a multi-condition if-statement when looping through data in a variant array?

---

Code in question:

General code to generate the variant array:

Public ex_arr As Variant, ex_lr As Long, ex_lc As Long
Public dc As Scripting.Dictionary 

Private Sub capture_export_array()
    With Sheets("export")
        ex_lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
        ex_lr = .Cells(.Rows.Count, ex_lc).End(xlUp).Row
        ex_arr = .Range(.Cells(1, 1), .Cells(ex_lr, ex_lc)).Value
    End With
End Sub

Code that resulted in False conditions (immediate window print = 0):

Private Sub find_unique_items()
    Set dc = New Scripting.Dictionary
    Dim i As Long
    For i = LBound(ex_arr) To UBound(ex_arr)
        If InStr(ex_arr(i, ex_lc), "CriteriaA") And InStr(ex_arr(i, 4), "CriteriaB") Then dc(ex_arr(i, 2)) = ex_arr(i, 3)
    Next i
    Debug.Print dc.Count
End Sub

Code that resulted in desired output (immediate window print > 0):

Private Sub find_unique_items()
    Set dc = New Scripting.Dictionary
    Dim i As Long
    For i = LBound(ex_arr) To UBound(ex_arr)
        If InStr(ex_arr(i, ex_lc), "CriteriaA") Then
            If InStr(ex_arr(i, 4), "CriteriaB") Then dc(ex_arr(i, 2)) = ex_arr(i, 3)
        End If
    Next i
    Debug.Print dc.Count
End Sub

Answer 1

InStr returns an index. As a logical operator, And wants to have Boolean operands. Given Integer operands, the And operator is a bitwise operator - truth be told, these operators are always bitwise; we just dub them "logical" operators when the operands are Boolean.

If InStr(ex_arr(i, ex_lc), "CriteriaA") Then

This condition is implicitly coercing the returned index into a Boolean expression, leveraging the fact that any non-zero value will convert to True.

Problems start when you bring logical/bitwise operators into the equation.

If InStr(ex_arr(i, ex_lc), "CriteriaA") And InStr(ex_arr(i, 4), "CriteriaB") Then dc(ex_arr(i, 2)) = ex_arr(i, 3)

Say the first InStr returns 2, and the second returns 1. The If expression becomes If 2 And 1 Then, so 0. That's zero, so the condition is false.

Wait, what?

Think of the binary representation of 2 vs that of 1:

  2:  0010
  1:  0001
AND:  0000

Bitwise-AND yields 0, since none of the bits line up.

---

Stop abusing implicit type conversions, and be explicit about what you really mean. What you mean to be doing, is this:

If (InStr(ex_arr(i, ex_lc), "CriteriaA") > 0) And (InStr(ex_arr(i, 4), "CriteriaB") > 0) Then dc(ex_arr(i, 2)) = ex_arr(i, 3)

(redundant parentheses for illustrative purposes only)

Now this evaluates two Boolean expressions, applies bitwise-AND to the two values, and correctly works as intended.

True: 1111
True: 1111
 AND: 1111