查找最多 3 个输入 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27460366/
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
Finding Max of 3 Inputs VBA
提问by SiGm4
I am trying to find the maximum of 3 inputs. The problem is not in the algorithm, as when I made the same script in python it worked just fine. The problem is that it does not work as expected. I will write some scenarios and what the outcome was:
我试图找到最多 3 个输入。问题不在于算法，因为当我在 python 中制作相同的脚本时，它工作得很好。问题是它没有按预期工作。我会写一些场景和结果：
8 5 12 - Max: 12
5 8 12 - Max: 12
12 5 8 - Max: 8
12 8 5 - Max: 8
5 12 8 - Max: 8
8 12 5 - Max: 8
100 22 33 - Max: 33
22 3 100 - Max: 100
100 22 3 - Max: 22
8 5 12 - 最大：12
5 8 12 - 最大：12
12 5 8 - 最大：8
12 8 5 - 最大：8
5 12 8 - 最大：8
8 12 5 - 最大：8
100 22 33 - 最大：33
22 3 100 - 最大：100
100 22 3 - 最大：22
It seems that it works for quite some combination, but not for each and every one. I haven't managed to find a pattern yet, and I can't figure out what is going wrong.
似乎它适用于相当多的组合，但不适用于每个组合。我还没有设法找到一个模式，我无法弄清楚出了什么问题。
I am attaching the code:
我附上代码：
Sub Maxthree()
'Calculates the maximum of three numbers'
Dim x, y, z As Single
x = InputBox("Enter the first number!")
y = InputBox("Enter the second number!")
z = InputBox("Enter the third number!")
MsgBox ("X: " & x & " Y: " & y & " Z: " & z)
If x > y Then
If x > z Then
MsgBox ("the maximum is : " & x)
Else
MsgBox ("the maximum is : " & z)
End If
Else
If y > z Then
MsgBox ("the maximum is : " & y)
Else
MsgBox ("the maximum is : " & z)
End If
End If
End Sub
回答by Doug Glancy
Because they are input using an InputBox, it's comparing text values. So, for example "8" is greater than "12". Instead try converting to Longslike:
因为它们是使用 InputBox 输入的，所以它正在比较文本值。因此，例如“8”大于“12”。而是尝试转换为Longs喜欢：
x = CLng(InputBox("Enter the first number!"))
You can also simplify your code to:
您还可以将代码简化为：
MsgBox WorksheetFunction.Max(x, y, z)
回答by Tom Robinson
Here is the pattern you were looking for.
这是您要找的图案。
Since X and Y are Variant while Z is Single, this is how VBA will perform the comparisons:
由于 X 和 Y 是 Variant 而 Z 是 Single，这就是 VBA 执行比较的方式：
X vs Y: string vs string (this is what is causing all the trouble)
X vs Y：字符串 vs 字符串（这是造成所有麻烦的原因）
X vs Z: numeric (X will be converted automatically)
X vs Z：数字（X 会自动转换）
Y vs Z: numeric (Y will be converted automatically)
Y vs Z：数字（Y 会自动转换）
Re-evaluate all 9 of your scenarios, with X and Y being compared as strings and (X or Y) being compared to Z as numbers. The results you observed, while unexpected, are correct.
重新评估所有 9 个场景，将 X 和 Y 作为字符串进行比较，并将（X 或 Y）与 Z 作为数字进行比较。您观察到的结果虽然出乎意料，但却是正确的。
Just feel fortunate that you aren't programming in PHP, where this is all much worse!
只是庆幸您不是用 PHP 编程，而这一切都更糟！
Microsoft are to blame for allowing Variant to be the default data type if no other type is specified. They support "Option Explicit" to force variables to be declared. They should go a step further, and have an option to require data types in all declarations.
如果未指定其他类型，则允许 Variant 成为默认数据类型，这要归咎于 Microsoft。它们支持“Option Explicit”来强制声明变量。他们应该更进一步，并且可以选择在所有声明中要求数据类型。
回答by Patrick Honorez
Hereis a function that returns the Largest element of any number of them:
这是一个返回任意数量中最大元素的函数：
Function Largest(ParamArray a() As Variant) As Variant
'returns the largest element of list
'List is supposed to be consistent: all nummbers or all strings
'e.g: largest(2,6,-9,7,3) -> 7
' largest("d", "z", "c", "x") -> "z"
'by Patrick Honorez --- www.idevlop.com
Dim result As Variant
Dim i As Integer
result = Null
For i = LBound(a) To UBound(a)
If result > a(i) Then
'nothing to do. This construct will properly handle null values
Else
result = a(i)
End If
Next i
Largest = result
End Function
