VBA 短路`和`替代方案

VBA Short-Circuit `And` Alternatives

提问by Blackhawk

VBA doesn't short-circuit

VBA不会短路

VBA does not support short-circuiting - apparently because it only has bitwise And/Or/Not etc operations. From the VBA language specification: "Logical operators are simple data operators that perform bitwise computations on their operands." In this light, it makes sense that VBA was designed with true = &H1111and false = &H0000: this way logical statements can be evaluated as bitwise operations.

VBA 不支持短路——显然是因为它只有按位与/或/非等操作。来自VBA 语言规范：“逻辑运算符是对其操作数执行按位计算的简单数据运算符。” 从这个角度来看，VBA 是用true = &H1111and设计的false = &H0000：这样逻辑语句可以被评估为按位运算。

The lack of short-circuiting can cause problems

缺少短路会导致问题

1. Performance: the ReallyExpensiveFunction()will always be run when this statement is evaluated, even if it is not necessary by the result of the left hand side of the condition

   If IsNecessary() And ReallyExpensiveFunction() Then '... End If

2. Errors: if MyObj is Nothing, this conditional statment will result in a runtime error because VBA will still try to check the value of Property

   If Not MyObj Is Nothing And MyObj.Property = 5 Then '... End If

1. 性能：在ReallyExpensiveFunction()评估此语句时将始终运行，即使条件左侧的结果不需要

   If IsNecessary() And ReallyExpensiveFunction() Then '... End If

2. 错误：如果 MyObj 为 Nothing，则此条件语句将导致运行时错误，因为 VBA 仍会尝试检查 Property

   If Not MyObj Is Nothing And MyObj.Property = 5 Then '... End If

The solution I've used to implement short-cirtcuiting behavior is nested Ifs

我用来实现短路行为的解决方案是nested Ifs

If cond1 And cond2 Then
    '...
End If

Becomes

成为

If cond1 Then
    If cond2 Then
        '...
    End If
End If

This way the If statements give the short-circuit-like behavior of not bothering to evaluate cond2if cond1is False.

这样， If 语句就提供了类似短路的行为，无需费心评估cond2if cond1is False。

If there is an Else clause, this creates duplicate code blocks

如果有 Else 子句，这会创建重复的代码块

If Not MyObj Is Nothing And MyObj.Property = 5 Then
    MsgBox "YAY"
Else
    MsgBox "BOO"
End If

Becomes

成为

If Not MyObj Is Nothing Then
    If MyObj.Property = 5 Then
        MsgBox "YAY"
    Else
        MsgBox "BOO" 'Duplicate
    End If
Else
    MsgBox "BOO" 'Duplicate
End If

Is there a way to rewrite Ifstatements to preserve the short-circuit behavior, but avoid duplication of code?

有没有办法重写If语句以保留短路行为，但避免代码重复？

Perhaps with another branching statement like Select Case?

也许与另一个分支语句一样Select Case？

---

---

To add context to the question, here is the specific case I'm looking at. I'm implementing a hash table that handles collisions by chaining them in a linked list. The underlying array size is enforced to be a power of two and the hashes are distributed into the current array size by truncating them to the appropriate length.

要为问题添加上下文，这是我正在查看的具体案例。我正在实现一个哈希表，通过将它们链接到链表中来处理冲突。底层数组大小强制为 2 的幂，散列通过将它们截断到适当的长度来分配到当前数组大小中。

For example, suppose the array length is 16 (binary 10000). If I have a key that hashes to 27 (binary 11011), I can store it in my 16 slot array by keeping only the bits within the limit of that array size. The index where this item would be stored is (hash value) And (length of array - 1)which in this case is (binary 11011) And (1111)which is 1011which is 11. The actual hash code is stored along with the key in the slot.

例如，假设数组长度为 16（二进制 10000）。如果我有一个散列为 27（二进制 11011）的密钥，我可以将它存储在我的 16 槽数组中，方法是只保留该数组大小限制内的位。将存储此项目的索引(hash value) And (length of array - 1)在这种情况下是(binary 11011) And (1111)哪个是1011哪个是 11。实际的哈希码与槽中的键一起存储。

When looking up an item in the hash table in a chain, both the hash and the key must be checked to determine that the correct item has been found. However, if the hash doesn't match, then there is no reason to check the key. I was hoping to gain some tiny intangible amount of performance by nesting the Ifs to get the short-circuit behavior:

在链中查找哈希表中的项目时，必须检查哈希和密钥以确定是否找到了正确的项目。但是，如果散列不匹配，则没有理由检查密钥。我希望通过嵌套 Ifs 以获得短路行为来获得一些微小的无形性能：

While Not e Is Nothing
    If keyhash = e.hash Then
        If Key = e.Key Then
            e.Value = Value
            Exit Property
        Else
            Set e = e.nextEntry
        End If
    Else
        Set e = e.nextEntry
    End If
Wend

You can see the Set...is duplicated, and thus this question.

您可以看到Set...重复的，因此这个问题。

采纳答案by peter_the_oak

As a more general apprach, I suggest to introduce condition flags and make usage of assigning comparison results to booleans:

作为更通用的方法，我建议引入条件标志并使用将比较结果分配给布尔值：

dim cond1 as boolean
dim cond2 as boolean

cond1 = false
cond2 = false

' Step 1
cond1 = MyObj Is Nothing

' Step 2: do it only if step 1 was sucessful 
if cond1 then
    cond2 = MyObj.Property = 5
end if

' Final result:
if cond2 then
   msgbox "Yay"
else
   msgbox "Boo"
end if

By "chaining" those condition flags, every step is safe, you see the final result in the last condition flag and you don't do unnecessary comparisons. And, to me, it keeps readable.

通过“链接”这些条件标志，每一步都是安全的，您可以在最后一个条件标志中看到最终结果，而无需进行不必要的比较。而且，对我来说，它保持可读性。

EDIT 14/09/07

编辑 14/09/07

I usually never omit block delimiters and I consequently set every statement of control structures on a new line. But in this case, you can carefully get a very dense notation that reminds on short-circuit notation, also because the VBA compiler initiates the variables:

我通常从不省略块分隔符，因此我将控制结构的每个语句都设置在一个新行上。但在这种情况下，你可以仔细得到一个非常密集的符号，提醒短路符号，也是因为 VBA 编译器启动了变量：

dim cond1 as boolean
dim cond2 as boolean
dim cond3 as boolean
dim cond4 as boolean

cond1 = MyObj Is Nothing
if cond1 then cond2 = MyObj.Property = 5
if cond2 then cond3 = MyObj.Property2 = constSomething
if cond3 then cond4 = not isNull(MyObj.Property77)

if cond4 then
   msgbox "Hyper-Yay"
else
   msgbox "Boo"
end if

I could agree to this. It's a clear flow to read.

我可以同意这一点。这是一个清晰的阅读流程。

回答by hnk

There is a way. You're not guaranteed to like it. But this is one of those carefully constructed cases where Gotocomes in handy

有一种方法。不能保证你会喜欢它。但这是那些精心构建的案例之一，Goto派上用场

If Not MyObj Is Nothing Then
    If MyObj.Property = 5 Then
        MsgBox "YAY"
    Else
        Goto JUMPHERE
    End If
Else
JUMPHERE:
    MsgBox "BOO" 'Duplicate
End If

A short-circuited code to implement a short-circuited condition!

用于实现短路条件的短路代码！

Alternately, if instead of MsgBox "BOO"is some long and convoluted code, it can be wrapped in a function and that can be written twice with minimal impact/overhead.

或者，如果不是MsgBox "BOO"一些冗长而复杂的代码，它可以包装在一个函数中，并且可以以最小的影响/开销编写两次。

---

---

Regarding the specific use case, the multiple Setoperations will have a minimal performance impact and hence, if one wants to avoid using Goto(still the most globally efficient approach, codesize + performance wise, avoiding creation of dummy variables, etc. - won't matter, though for such a small piece of code) there is negligible downside in simply repeating the command.

关于特定用例，多个Set操作将对性能产生最小的影响，因此，如果想要避免使用Goto（仍然是最全局有效的方法，代码大小+性能明智，避免创建虚拟变量等 - 无关紧要，尽管对于这么小的一段代码），简单地重复命令的缺点可以忽略不计。

Just to analyze (your sample code) how much can be gained by different methods...

只是为了分析（您的示例代码）可以通过不同的方法获得多少...

• If both conditions are true:, there are 2 comparisons, 1 assignment, 0 jumps
• If only first condition is true:there are 2 comparisons, 1 pointer-assignment, 1 jump
• If only second condition is true:there is 1 comparison, 1 pointer-assignment, 1 jump
• If both conditions are false:there is 1 comparison, 1 pointer-assignment, 1 jump (same as above)

• 如果两个条件都为真：，则有 2 次比较，1 次赋值，0 次跳转
• 如果只有第一个条件为真：有 2 次比较，1 次指针赋值，1 次跳转
• 如果只有第二个条件为真：有 1 次比较，1 次指针赋值，1 次跳转
• 如果两个条件都为假：有 1 次比较，1 次指针赋值，1 次跳转（同上）

In terms of performance, a jump is usually more expensive than comparison (which happens very quickly in ALU vs. the jump which could lead to a disruption in the code cache, maybe not at these sizes, but still jumps are expensive).

在性能方面，跳转通常比比较更昂贵（这在 ALU 中发生得非常快，而跳转可能会导致代码缓存中断，可能不是在这些大小下，但跳转仍然很昂贵）。

And normal assignment by value would be at best as fast as a pointer-assignment or sometimes worse (this is VBA, can't be 100% sure of the p-code implementation)

正常的按值赋值最多与指针赋值一样快，有时甚至更糟（这是 VBA，不能 100% 确定 p 代码的实现）

So, depending on your use case / expected data, you can try to minimize average number of jumps per iteration in your loop and reorder the code.

因此，根据您的用例/预期数据，您可以尝试最小化循环中每次迭代的平均跳转次数并重新排序代码。

回答by Tim Williams

How about:

怎么样：

s = "BOO"

If Not MyObj Is Nothing Then
    If MyObj.Property = 5 Then s = "YAY"
End If

MsgBox s