vba IF 语句中的“And”和“Or”问题

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/11123191/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 13:21:04  来源:igfitidea点击:

"And" and "Or" troubles within an IF statement

vbaexcel-vbaexcel

提问by Mike Kellogg

I'm trying to use "And" & "Or" within an If statement. I probably have my syntax wrong.

我正在尝试在 If 语句中使用“And”和“Or”。我的语法可能有误。

the result comes back false when the data should make it true. Here is the code:

当数据应为真时,结果返回假。这是代码:

ElseIf (origNum = "006260006" Or origNum = "30062600006") And creditOrDebit = "D" Then

'do things here

End If

-When I debug and come to this line it hops over it and doesn't enter in.

- 当我调试并来到这条线时,它跳过它并且不进入。

-origNum actually equals "006260006" and creditOrDebit = "D".

-origNum 实际上等于 "006260006" 和 creditOrDebit = "D"。

-so I'm assuming my "Or" statement isn't working.

- 所以我假设我的“或”语句不起作用。

-Hopefully this is a quick easy question. Thanks!

-希望这是一个快速简单的问题。谢谢!

回答by assylias

The problem is probably somewhere else. Try this code for example:

问题可能出在其他地方。试试这个代码,例如:

Sub test()

  origNum = "006260006"
  creditOrDebit = "D"

  If (origNum = "006260006" Or origNum = "30062600006") And creditOrDebit = "D" Then
    MsgBox "OK"
  End If

End Sub

And you will see that your Orworks as expected. Are you sure that your ElseIfstatement is executed (it will not be executed if any of the if/elseif before is true)?

你会看到你的Or作品如预期的那样。你确定你的ElseIf语句被执行了(如果之前的任何一个 if/elseif 为真,它就不会被执行)?

回答by assylias

This is not an answer, but too long for a comment.

这不是答案,但评论太长了。

In reply to JP's answers / comments, I have run the following test to compare the performance of the 2 methods. The Profilerobject is a custom class - but in summary, it uses a kernel32 function which is fairly accurate (Private Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)).

在回复JP 的回答/评论时,我运行了以下测试来比较两种方法的性能。该Profiler对象是一个自定义类 - 但总而言之,它使用了一个相当准确的 kernel32 函数 ( Private Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SYSTEMTIME))。

Sub test()

  Dim origNum As String
  Dim creditOrDebit As String
  Dim b As Boolean
  Dim p As Profiler
  Dim i As Long

  Set p = New_Profiler

  origNum = "30062600006"
  creditOrDebit = "D"

  p.startTimer ("nested_ifs")

  For i = 1 To 1000000

    If creditOrDebit = "D" Then
      If origNum = "006260006" Then
        b = True
      ElseIf origNum = "30062600006" Then
        b = True
      End If
    End If

  Next i

  p.stopTimer ("nested_ifs")
  p.startTimer ("or_and")

  For i = 1 To 1000000

    If (origNum = "006260006" Or origNum = "30062600006") And creditOrDebit = "D" Then
      b = True
    End If

  Next i

  p.stopTimer ("or_and")

  p.printReport

End Sub

The results of 5 runs (in ms for 1m loops):

5 次运行的结果(1m 循环以毫秒为单位):

20-Jun-2012 19:28:25
nested_ifs (x1): 156 - Last Run: 156 - Average Run: 156
or_and (x1): 125 - Last Run: 125 - Average Run: 125

20-Jun-2012 19:28:26
nested_ifs (x1): 156 - Last Run: 156 - Average Run: 156
or_and (x1): 125 - Last Run: 125 - Average Run: 125

20-Jun-2012 19:28:27
nested_ifs (x1): 140 - Last Run: 140 - Average Run: 140
or_and (x1): 125 - Last Run: 125 - Average Run: 125

20-Jun-2012 19:28:28
nested_ifs (x1): 140 - Last Run: 140 - Average Run: 140
or_and (x1): 141 - Last Run: 141 - Average Run: 141

20-Jun-2012 19:28:29
nested_ifs (x1): 156 - Last Run: 156 - Average Run: 156
or_and (x1): 125 - Last Run: 125 - Average Run: 125

2012 年 6 月 20 日 19
:28:25nested_ifs (x1):156 - 最后一次运行:156 - 平均运行:156
or_and (x1):125 - 最后一次运行:125 - 平均运行:125

2012 年 6 月 20 日 19
:28:26nested_ifs (x1):156 - 最后一次运行:156 - 平均运行:156
or_and (x1):125 - 最后一次运行:125 - 平均运行:125

2012 年 6 月 20 日 19
:28:27nested_ifs (x1):140 - 最后一次运行:140 - 平均运行:140
or_and (x1):125 - 最后一次运行:125 - 平均运行:125

2012 年 6 月 20 日
19:28:28nested_ifs (x1):140 - 最后一次运行:140 - 平均运行:140
or_and (x1):141 - 最后一次运行:141 - 平均运行:141

2012 年 6 月 20 日 19
:28:29nested_ifs (x1):156 - 最后一次运行:156 - 平均运行:156
or_and (x1):125 - 最后一次运行:125 - 平均运行:125

Note

笔记

If creditOrDebitis not "D", JP's code runs faster (around 60ms vs. 125ms for the or/and code).

如果creditOrDebit不是"D",JP 的代码运行得更快(大约 60 毫秒对 or/和代码的 125 毫秒)。

回答by JimmyPena

I like assylias' answer, however I would refactor it as follows:

我喜欢assylias的回答,但是我会按如下方式重构它:

Sub test()

Dim origNum As String
Dim creditOrDebit As String

origNum = "30062600006"
creditOrDebit = "D"

If creditOrDebit = "D" Then
  If origNum = "006260006" Then
    MsgBox "OK"
  ElseIf origNum = "30062600006" Then
    MsgBox "OK"
  End If
End If

End Sub

This might save you some CPU cycles since if creditOrDebitis <> "D"there is no point in checking the value of origNum.

因为如果这可以为你节省CPU周期creditOrDebit<> "D"有在检查的价值是没有意义的origNum

Update:

更新:

I used the following procedure to test my theory that my procedure is faster:

我使用以下程序来测试我的程序更快的理论:

Public Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub DoTests2()

  Dim startTime1 As Long
  Dim endTime1 As Long
  Dim startTime2 As Long
  Dim endTime2 As Long
  Dim i As Long
  Dim msg As String

  Const numberOfLoops As Long = 10000
  Const origNum As String = "006260006"
  Const creditOrDebit As String = "D"

  startTime1 = timeGetTime
  For i = 1 To numberOfLoops
    If creditOrDebit = "D" Then
      If origNum = "006260006" Then
        ' do something here
        Debug.Print "OK"
      ElseIf origNum = "30062600006" Then
        ' do something here
        Debug.Print "OK"
      End If
    End If
  Next i
  endTime1 = timeGetTime

  startTime2 = timeGetTime
  For i = 1 To numberOfLoops
    If (origNum = "006260006" Or origNum = "30062600006") And _
      creditOrDebit = "D" Then
      ' do something here
      Debug.Print "OK"
    End If
  Next i
  endTime2 = timeGetTime

  msg = "number of iterations: " & numberOfLoops & vbNewLine
  msg = msg & "JP proc: " & Format$((endTime1 - startTime1), "#,###") & _
       " ms" & vbNewLine
  msg = msg & "assylias proc: " & Format$((endTime2 - startTime2), "#,###") & _
       " ms"

  MsgBox msg

End Sub

I must have a slow computer because 1,000,000 iterations took nowhere near ~200 ms as with assylias' test. I had to limit the iterations to 10,000 -- hey, I have other things to do :)

我必须有一台慢速计算机,因为 1,000,000 次迭代所花费的时间远不及assylias测试的大约 200 毫秒。我不得不将迭代次数限制在 10,000 次——嘿,我还有其他事情要做:)

After running the above procedure 10 times, my procedure is faster only 20% of the time. However, when it is slower it is only superficially slower. As assyliaspointed out, however, when creditOrDebitis <>"D", my procedure is at least twice as fast. I was able to reasonably test it at 100 million iterations.

运行上述程序 10 次后,我的程序仅在 20% 的时间内更快。然而,当它变慢时,它只是表面上变慢。作为assylias指出,然而,当creditOrDebit<>"D",我的方法是,在至少快一倍。我能够在 1 亿次迭代中对其进行合理的测试。

And thatis why I refactored it - to short-circuit the logic so that origNumdoesn't need to be evaluated when creditOrDebit <> "D".

就是为什么我重构它-短路的逻辑,这样origNum并不需要不进行评估时creditOrDebit <> "D"

At this point, the rest depends on the OP's spreadsheet. If creditOrDebitis likely to equal D, then use assylias' procedure, because it will usually run faster. But if creditOrDebithas a wide range of possible values, and Dis not any more likely to be the target value, my procedure will leverage that to prevent needlessly evaluating the other variable.

此时,其余的取决于 OP 的电子表格。如果creditOrDebit很可能等于 D,则使用assylias的程序,因为它通常会运行得更快。但是,如果creditOrDebit具有广泛的可能值,并且D不太可能成为目标值,我的程序将利用它来防止对其他变量进行不必要的评估。