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
"And" and "Or" troubles within an IF statement
提问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 Or
works as expected. Are you sure that your ElseIf
statement 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 Profiler
object 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: 12520-Jun-2012 19:28:26
nested_ifs (x1): 156 - Last Run: 156 - Average Run: 156
or_and (x1): 125 - Last Run: 125 - Average Run: 12520-Jun-2012 19:28:27
nested_ifs (x1): 140 - Last Run: 140 - Average Run: 140
or_and (x1): 125 - Last Run: 125 - Average Run: 12520-Jun-2012 19:28:28
nested_ifs (x1): 140 - Last Run: 140 - Average Run: 140
or_and (x1): 141 - Last Run: 141 - Average Run: 14120-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 - 平均运行:1252012 年 6 月 20 日 19
:28:26nested_ifs (x1):156 - 最后一次运行:156 - 平均运行:156
or_and (x1):125 - 最后一次运行:125 - 平均运行:1252012 年 6 月 20 日 19
:28:27nested_ifs (x1):140 - 最后一次运行:140 - 平均运行:140
or_and (x1):125 - 最后一次运行:125 - 平均运行:1252012 年 6 月 20 日
19:28:28nested_ifs (x1):140 - 最后一次运行:140 - 平均运行:140
or_and (x1):141 - 最后一次运行:141 - 平均运行:1412012 年 6 月 20 日 19
:28:29nested_ifs (x1):156 - 最后一次运行:156 - 平均运行:156
or_and (x1):125 - 最后一次运行:125 - 平均运行:125
Note
笔记
If creditOrDebit
is 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 creditOrDebit
is <> "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 creditOrDebit
is <>"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 origNum
doesn't need to be evaluated when creditOrDebit <> "D"
.
而这就是为什么我重构它-短路的逻辑,这样origNum
并不需要不进行评估时creditOrDebit <> "D"
。
At this point, the rest depends on the OP's spreadsheet. If creditOrDebit
is likely to equal D, then use assylias' procedure, because it will usually run faster. But if creditOrDebit
has a wide range of possible values, and D
is 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
不太可能成为目标值,我的程序将利用它来防止对其他变量进行不必要的评估。