遍历变量数组时不能使用具有多个条件(AND/OR)的If语句吗? [英] Cannot use an If-statement with multiple conditions (AND/OR) when looping through variant array?

问题描述

背景:

在使用一些变体数组将数据基于条件排序到多个位置时，我注意到每次使用带有多个条件的if语句标记为false的情况.这是用来创建字典的，尽管它从来没有涉及字典方面，因为仅在变量数组中循环时会出现错误的响应.

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.

我将它们移到两个单独的if语句中，一切都按预期工作.

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

问题:

为什么在遍历变量数组中的数据时无法使用多条件if语句?

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

相关代码:

生成变体数组的常规代码:

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

导致False条件(立即窗口打印= 0)的代码:

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

导致所需输出(立即窗口打印> 0)的代码:

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

推荐答案

InStr返回索引.作为逻辑运算符，And希望具有Boolean操作数.给定Integer操作数，And运算符是按位运算符-实话说，这些运算符总是按位运算.当操作数为Boolean时，我们就将它们称为逻辑"运算符.

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

此条件利用所有非零值都将转换为True的事实，隐式地将返回的索引强制为Boolean表达式.

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)

说第一个InStr返回2，第二个返回1. If表达式变为If 2 And 1 Then，因此0.那是零，所以条件是假的.

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?

考虑2与1的二进制表示形式:

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

  2:  0010
  1:  0001
AND:  0000

按位与的结果为0，因为没有位对齐.

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)

现在，这将对两个Boolean表达式求值，对这两个值进行按位与运算，并按预期正确运行.

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

True: 1111
True: 1111
 AND: 1111

这篇关于遍历变量数组时不能使用具有多个条件(AND/OR)的If语句吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！