Comparing VBA with Multiple Variables

Is there a way to compare multiple variables in VBA? For instance:

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 that I could use select case x > yfor all possible permutations, but this seems cumbersome for more than three variables.

I tried the worksheet function

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

but returns 2, and I would like it to return a variable name to pass to another function.

many thanks,

Edit: Sorry if this is confusing, but I'm still a newbie to VBA. Here my problem is a little more general:

, , . , , . ( 0 ). , 2 , if x>y then, , 3. .

+4
2

, . , :

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 code here

End Function

yourFunction : yourFunction(myArray(index))

+3

, , sub min:

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

, , , , 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
+2

Source: https://habr.com/ru/post/1532480/


All Articles