VBA 比较多个变量

VBA comparing multiple variables

提问by Anthony Bird

There any way to compare multiple variables in VBA? For example:

有什么方法可以比较 VBA 中的多个变量？例如：

Dim x As Integer
Dim y As Integer
Dim z As Integer
x = 99
y = 2
z = 3

I would like to return the smallest of the values. I understand I could use select case x > yfor all possible permutations but that seems unwieldy for more than 3 variables.

我想返回最小的值。我知道我可以select case x > y用于所有可能的排列，但对于 3 个以上的变量来说这似乎很笨拙。

I have tried the worksheet function

我已经尝试过工作表功能

solution = Application.WorksheetFunction.Min(x, y, z)

but that returns 2and I would like it to return the variable name to be passed to another function.

但这会返回2，我希望它返回要传递给另一个函数的变量名。

many thanks,

非常感谢，

Edit: My apologies if this was confusing, I am still a VBA novice. Here's my problem a little more generally:

编辑：如果这令人困惑，我很抱歉，我仍然是 VBA 新手。这是我的问题更普遍一点：

I have a list of codes that correspond to names, many names per code. I want to loop through every name per code and count the number of instances that name appears on a list and choose the name with the LEAST occurrences. (could be 0 or could be the same as another name). obviously if there were 2 names it would be easy to do a if x>y thenbut I'm stumped as for comparing more than 3. Thanks for reading.

我有一个与名称相对应的代码列表，每个代码有很多名称。我想遍历每个代码的每个名称，并计算该名称出现在列表中的实例数，然后选择出现次数最少的名称。（可能是 0 或可能与另一个名称相同）。显然，如果有 2 个名字，它会很容易做，if x>y then但我很难比较超过 3 个。感谢阅读。

回答by Mike

Use a public array rather than multiple variables. This will make it easy to iterate through them all and get the highest value, as well as reference the variable with the highest value later on:

使用公共数组而不是多个变量。这将使遍历它们变得容易并获得最高值，以及稍后引用具有最高值的变量：

Public myArray(0 To 2) As Integer
Public index As Integer


Public Sub calcMin()

    Dim i As Integer
    Dim maxValue As Integer


    myArray(0) = 99
    myArray(1) = 2
    myArray(2) = 3


    For i = 0 To UBound(myArray)
        If myArray(i) < maxValue Then
            maxValue = myArray(i)
            index = i
        End If
    Next i

End Sub

Function yourFunction(valueToPass As Integer)

'your function's code here

End Function

Then pass the variable to yourFunctionlike so: yourFunction(myArray(index))

然后将变量传递给yourFunction像这样：yourFunction(myArray(index))

回答by simpLE MAn

Same idea as Mike's but with an example to call a sub with the min value found:

与 Mike 的想法相同，但有一个例子来调用具有最小值的 sub ：

Sub main()

    Dim arrComp(2) As Integer
    arrComp(0) = 99
    arrComp(1) = 2
    arrComp(2) = 3

    'It is important to initialize the tmpVal to a value from the array
    'to consider the chance where negative and positive values are used
    Dim tmpVal As Integer: tmpVal = arrComp(LBound(arrComp))
    Dim i As Integer, minIndex As Integer
    For i = LBound(arrComp) To UBound(arrComp)
        If arrComp(i) < tmpVal Then
            tmpVal = arrComp(i)
            minIndex = i
        End If
    Next i

    showMinVal arrComp(minIndex)

End Sub

Sub showMinVal(MinVal As Integer)

    MsgBox "The min value is " & MinVal

End Sub

Or, a workaroundif you want the name associated to the value, you could define a new Type:

或者，如果您希望将名称与值关联，则可以使用一种解决方法，您可以定义一个新的Type：

'Types must be declared at the top of the module
Type tVarName
    varName As String
    varVal As Integer
End Type

Sub main()

    Dim arrComp(2) As tVarName
    arrComp(0).varName = "x"
    arrComp(0).varVal = 99
    arrComp(1).varName = "y"
    arrComp(1).varVal = 2
    arrComp(2).varName = "z"
    arrComp(2).varVal = 3

    Dim tmpVal As Integer: tmpVal = arrComp(LBound(arrComp)).varVal
    Dim i As Integer, minIndex As Integer
    For i = LBound(arrComp) To UBound(arrComp)
        If arrComp(i).varVal < tmpVal Then
            tmpVal = arrComp(i).varVal
            minIndex = i
        End If
    Next i

    showMinVal arrComp(minIndex)

End Sub

'Sub showing min value along with the name associated to it
Sub showMinVal(MinVal As tVarName)

    MsgBox "The min value is " & MinVal.varName & " = " & MinVal.varVal

End Sub