当第一个参数为假时，VBA“And”运算符是否评估第二个参数？

Question

提问by James

Function Foo(thiscell As Range) As Boolean
  Foo = thiscell.hasFormula And (InStr(1, UCase(Split(thiscell.formula, Chr(40))(0)), "bar") > 0)
End Function

This function exists to test for the presence of a certain substring (bar, in this case) before the (.

此函数的存在是为了测试 (.

The case I'm having trouble with is when the cell passed into the function is empty, the thisCell.hasFormula is false, but the statement after the and is still being evaluated. This gives me a subscript out of range error in runtime.

我遇到问题的情况是，当传递给函数的单元格为空时，thisCell.hasFormula 为假，但和之后的语句仍在评估中。这给了我一个运行时下标超出范围的错误。

Does VBA actually continue evaluating the second argument to the And, even when the first was false?

VBA 是否真的继续评估 And 的第二个参数，即使第一个参数为假？

Answer 1

回答by DOK

What you are looking for is called "short-circuit evaluation".

您正在寻找的称为“短路评估”。

VBA doesn't have it.

VBA 没有。

You can see an approach that is probably adaptable to your situation here.

您可以在此处看到一种可能适用于您的情况的方法。

The approach that was chosen there involved substituting a Select Casefor the If. There is also an example of using nested Ifs.

在那里选择的方法涉及用 aSelect Case代替If. 还有一个使用嵌套的示例Ifs。

Answer 2

回答by aevanko

As DOK mentioned: No, VBA does not have short-circuit evaluation.

正如DOK 所提到的：不，VBA 没有短路评估。

It's technically more efficient to use 2 If-thenstatements instead of using the ANDoperator, but unless you are doing it a lot of times, you wouldn't notice the savings, so go for whatever is more readable. And if you want to get really technical, VBA handles multiple If-thenstatements faster than Select Caseones as well.

从技术上讲，使用 2 个If-then语句而不是使用AND运算符更有效，但除非您多次这样做，否则您不会注意到节省的费用，因此请选择更具可读性的内容。而且，如果您想获得真正的技术，VBA 处理多条If-then语句的速度也比处理多条语句的速度快Select Case。

VBA is quirky :)

VBA 很古怪:)

Answer 3

回答by sopatt

The answer is yes, VBA does not short circuit evaluation.

答案是肯定的，VBA 不会短路评估。

It's not just a matter of style; it makes a big difference in a situation like this:

这不仅仅是风格问题；在这样的情况下，它会产生很大的不同：

If i <= UBound(Arr, 1) And j <= UBound(Arr, 2) And Arr(i, 1) <= UBound(Arr2, 1) Then
    Arr2(Arr(i, 1), j) = Arr(i, j)
End If

...which is incorrect. More appropriately:

...这是不正确的。更恰当的是：

If i <= UBound(Arr, 1) And j <= UBound(Arr, 2) Then
    If Arr(i, 1) <= UBound(Arr2, 1) Then
        Arr2(Arr(i, 1), j) = Arr(i, j)
    End If
End If

Or if you have an aversion to nested ifs:

或者，如果您不喜欢嵌套的 ifs：

If i > UBound(Arr, 1) Or j > UBound(Arr, 2) Then
    ' Do Nothing
ElseIf Arr(i, 1) > UBound(Arr2, 1) Then
    ' Do Nothing
Else
    Arr2(Arr(i, 1), j) = Arr(i, j)
End If

Answer 4

回答by Hugh Allen

VBA does have one short-circuit-like behavior. Normally Nullpropagates through expressions, eg. 3 + Nullis Null, and True And Nullis Null. However:

VBA 确实有一种类似短路的行为。通常Null通过表达式传播，例如。3 + Null是Null，并且True And Null是Null。然而：

? False And Null
False

This looks like short-circuit behavior - what's going on? Nulldoesn't propagate when the other argument to a conjunction (And) is Falseor 0- the result is just Falseor 0. It doesn't matter if it is the left or right argument. The same applies if the other argument to a disjunction (Or) is Trueor a non-zero integer (a floating point value will be rounded to an integer using this rule).

这看起来像是短路行为 - 这是怎么回事？Null当连词 ( And)的另一个参数是Falseor时，不会传播0- 结果只是Falseor 0。无论是左参数还是右参数都没有关系。如果析取 ( Or)的另一个参数是True或非零整数（浮点值将使用此规则四舍五入为整数），则同样适用。

So side-effects and errors can't be prevented in arguments to Andand Or, but Nullpropagation can be "short-circuited". This behavior seems to be inherited from SQL.

因此无法在And和的参数中防止副作用和错误Or，但Null传播可以“短路”。这种行为似乎是从SQL继承的。

Answer 5

回答by Moreno

I think this is the best practice:

我认为这是最佳实践：

sub my conditions()
        If Condition1=constraint1 then
         if Condition2=constraint2 then
          if condition3=constraint3 then
           ...
            ....
        end if
         end if
          end if
    else
      end if
           ....
    end if
end sub

Thus you will be only passaing through conditions if and only if condition i is fullfilled.

因此，当且仅当条件 i 满足时，您才会通过条件。

Answer 6

回答by Andreas Dietrich

Since the answer is one of the top ranked in Google just looking for something like vba if condition not lazyI would like to provide a simpler example, the problem and solutions of both conditions: ANDand the more interesting OR...

由于答案是在 Google 中排名靠前的答案之一，只是在寻找类似的东西，vba if condition not lazy我想提供一个更简单的示例，这两种情况的问题和解决方案：AND以及更有趣的OR...

Dim cond1 As Boolean   'some 1st condition that may be True or False
Dim obj As Collection  'just some sample object that may or may not be instantiated

(2: I find it better to explain other devs, why you did not choose ORif they don't know the background)

（2：我觉得最好解释一下其他开发者，OR如果他们不了解背景，你为什么不选择）

the `AND`case

的`AND`情况下，

cond1 = False
If cond1 Then Set obj = New Collection

problem:

问题：

If cond1 And obj.Count > 0 Then Debug.Print "Count > 0!"  'throws error if < cond1 = False > 
                                                          'because condition 2 is always evaluated

solution:

解决方案：

If cond1 Then If obj.Count > 0 Then Debug.Print "Count > 0!"  'AND would not short-cicuit!2 https://stackoverflow.com/a/57521572/1915920

Depending on taste, complexity and readability it may make sense to write it this way:

根据品味、复杂性和可读性，以这种方式编写它可能是有意义的：

If cond1 Then
    If obj.Count > 0 Then  'AND would not short-cicuit!2 https://stackoverflow.com/a/57521572/1915920
        Debug.Print "Count > 0!"
    End If
End If

the `OR`case

的`OR`情况下，

 cond1 = True
 If Not cond1 Then Set obj = New Collection  'obj stays < Nothing > otherwise

problem:

问题：

 If cond1 Or obj.Count = 0 Then Debug.Print "no objects!"  'throws error if < cond1 = True >
                                                           'because condition 2 is always evaluated

solution 1:

解决方案1：

in-place, non-redundant one-liner without GoTousing Select:

就地、非冗余单线，无需GoTo使用Select：

 Select Case True:  Case cond1, obj.Count = 0:  Debug.Print "no objects!":  End Select  'OR would not short-cicuit!2 https://stackoverflow.com/a/57521572/1915920

in case it should/must be on multiple lines and with some else:

如果它应该/必须在多行和其他一些行上：

 Select Case True
     Case cond1, obj.Count = 0  'OR would not short-cicuit!2 https://stackoverflow.com/a/57521572/1915920
         Debug.Print "no objects!"
     Case Else
         Debug.Print "object count: " & obj.Count
 End Select

solution 2:

解决方案2：

in-place, non-redundant code with minimal GoTousage, but more lengthy If-multi-line code:

使用最少的就地非冗余代码GoTo，但更冗长的If多行代码：

 If cond1 Then
 noObjs:
     Debug.Print "no objects!"
 ElseIf obj.Count = 0 Then  'OR would not short-cicuit!2 https://stackoverflow.com/a/57521572/1915920
     GoTo noObjs
 End If

solution 3:

解决方案3：

in-place, conditions (may fit) on one line similar to OR-concatenation with quite some GoTousage:

就地，条件（可能适合）在一行类似于OR-concatenation 有相当多的GoTo用法：

 If cond1 Then GoTo noObjs ElseIf obj.Count = 0 Then GoTo noObjs  'OR would not short-cicuit!2 https://stackoverflow.com/a/57521572/1915920
 GoTo skipOnAllFalse
 noObjs:
     Debug.Print "no objects!"

 skipOnAllFalse:    'use more specific label/scenario name if possible

solution 4:

解决方案4：

out-of-place code (Sub), avoiding GoTo, conditions (may fit) on one line, but module/class code may be more unreadable/spread/cluttered:

错位代码 ( Sub)、避免GoTo、条件（可能适合）在一行中，但模块/类代码可能更难读/散布/混乱：

 Private Sub noObjs():  Debug.Print "no objects!"

 If cond1 Then noObjs ElseIf obj.Count = 0 Then noObjs  'OR would not short-cicuit!2 https://stackoverflow.com/a/57521572/1915920

solution 5:

解决方案5：

using one condition variable:

使用一个条件变量：

 Dim any As Boolean:  any = cond1
 If Not any Then any = obj.Count = 0  'OR would not short-cicuit!2 https://stackoverflow.com/a/57521572/1915920
 If any Then Debug.Print "no objects!"

solution 6:

解决方案6：

using multiple condition variables:

使用多个条件变量：

 Dim c1 As Boolean:  Dim c2 As Boolean
 c1 = cond1
 If Not c1 Then c2 = obj.Count = 0  'OR would not short-cicuit!2 https://stackoverflow.com/a/57521572/1915920
 If c1 Or c2 Then Debug.Print "no objects!"  'safe to use Or now

Answer 7

回答by Harry S

Consider the machine code that has to run. The fastest should be along the lines of a mix of code like...

考虑必须运行的机器代码。最快的应该是混合代码的行，比如......

if sfsf then goto SkipAB

如果 sfsf 然后转到 SkipAB

if fdf then goto goneBad

如果fdf然后转到goneBad

if dffdefedwf then goto MustHave

如果 dffdefedwf 然后转到 MustHave

SkipAB: if dsda > 4 then MustHave

SkipAB：如果 dsda > 4 那么 MustHave

GoneBad: exit function

GoneBad：退出功能

MustHave: ThisIS = true

必须拥有：ThisIS = true

' only saves a few moments when the program has to run through it many thousands of times ... eg file searching a large drive or when a simple Boolean test is used to skip a time consuming function like finding all the sheets and names in a closed worksheet [code]

' 仅在程序必须运行数千次时节省一些时间......例如，搜索大型驱动器的文件或使用简单的布尔测试跳过耗时的功能时，例如在一个文件中查找所有工作表和名称已关闭的工作表 [代码]

     If Not wFF.UsingFileExtMatch Then GoTo SkipExt
                If Not wFF.OKFileEXTMatch Then GoTo BADFile

SkipExt: If Not wFF.UsingFileNameMatch Then GoTo SkipFileMatch If Not wFF.OKFileNameMatch Then GoTo BADFile SkipFileMatch: If Not wFF.UsingDaysAgo Then GoTo SkipDaysAgo If Not wFF.OKDaysAgo Then GoTo BADFile SkipDaysAgo:

SkipExt：如果不是 wFF.UsingFileNameMatch 那么转到 SkipFileMatch 如果不是 wFF.OKFileNameMatch 然后转到 BADFile SkipFileMatch：如果不是 wFF.UsingDaysAgo 那么转到 SkipDaysAgo 如果不是 wFF.OKDaysAgo 那么转到 BADFile SkipDaysAgo：

[/code]

[/代码]

当第一个参数为假时，VBA“And”运算符是否评估第二个参数？

提问by James

回答by DOK

回答by aevanko

回答by sopatt

回答by Hugh Allen

回答by Moreno

回答by Andreas Dietrich

the `AND`case

的`AND`情况下，

the `OR`case

的`OR`情况下，

回答by Harry S

相关推荐

最近更新

标签

当第一个参数为假时，VBA“And”运算符是否评估第二个参数？

提问by James

回答by DOK

回答by aevanko

回答by sopatt

回答by Hugh Allen

回答by Moreno

回答by Andreas Dietrich

the ANDcase

的AND情况下，

the ORcase

的OR情况下，

回答by Harry S

相关推荐

使用 VBA 代码复制和粘贴数据

解析 CSV，忽略 VBA 中字符串文字中的逗号？

vba 如何将新记录添加到子表单

Excel VBA：从命令按钮调用 userForm 时不显示 ComboBox.Rowsource 值的动态范围

相关推荐

最近更新

标签

the `AND`case

的`AND`情况下，

the `OR`case

的`OR`情况下，