VBA comparing multiple variables

Question

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

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 > y for all possible permutations but that seems unwieldy for more than 3 variables.

I have tried the worksheet function

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

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

many thanks,

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

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 then but I'm stumped as for comparing more than 3. Thanks for reading.

Ok, that is not so simple :P Pretty much consider it impossible in VBA. — user2140173
– user2140173, Commented Mar 19, 2014 at 14:19
It's very rare that you actually need to pass the variable name itself.. so can you elaborate on your question maybe there is work-around you're not aware off? — user2140173
– user2140173, Commented Mar 19, 2014 at 14:26
[...]to be passed to another function. You do not need the variable name to do that, you only need the variable itself. — simpLE MAn
– simpLE MAn, Commented Mar 19, 2014 at 15:51
and that is a good comment, once the OP agrees this is what he wants than you answer. As of now this is unclear if the OP actually needs the variable name itself or just the value of it. Based on this the answers could be completely different... To me ` that returns 2 and I would like it to return the variable name to be passed to another function.` means he specifically needs the variable name. I think in such case it's better to wait until the OP clears the doubts. — user2140173
– user2140173, Commented Mar 19, 2014 at 15:58
I edited to add some extra information. I don't understand the difference between "the name of the variable" and "the variable itself" do you have a link that gives more detail? — Anthony Bird
– Anthony Bird, Commented Mar 20, 2014 at 10:28

Mike · Accepted Answer · 2014-03-19 16:11:06Z

3

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 yourFunction like so: yourFunction(myArray(index))

edited Mar 19, 2014 at 16:11

answered Mar 19, 2014 at 14:26

Mike

62011 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2140173 Over a year ago

the OP ` would like it to return the variable name to be passed to another function`. You can use comments section to clarify the problem and possibly come out with a solution, your current answer does not answer the original question.

simpLE MAn Over a year ago

@mehow; OP also wrote to be passed to another function. You do not need the variable name to do that, you only need the variable itself; hence this answer is giving a consistent answer to OP's question.

user2140173 Over a year ago

this is why we use the comments section to clarify things.

Mike Over a year ago

I've elaborated on my original answer to hopefully clarify the usage of my solution.

user2140173 Over a year ago

it still does not answer the question IMO

simpLE MAn · Accepted Answer · 2014-03-19 16:17:08Z

2

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

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 workaround if you want the name associated to the value, you could define a new 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

edited Mar 19, 2014 at 16:17

answered Mar 19, 2014 at 14:37

simpLE MAn

1,62214 silver badges23 bronze badges

5 Comments

user2140173 Over a year ago

the OP ` would like it to return the variable name to be passed to another function`. You can use comments section to clarify the problem and possibly come out with a solution, your current answer does not answer the original question.

user2140173 Over a year ago

please do not be so upset about a downvote, anyone can do that. it's only up to the downvotes if they decide to leave a comment or not.

simpLE MAn Over a year ago

@mehow, i thought it was you since you answered my comment

user2140173 Over a year ago

voting is supposed to be anonymous, isn't it?

simpLE MAn Over a year ago

@mehow; sure but I think it is just too easy to vote down without explanation. Maybe the answer could be improved if there was a comment about it. Also, I think it is just fair to explain down votes.

Collectives™ on Stack Overflow

VBA comparing multiple variables

2 Answers 2

5 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related