VBA To Determine Which Variable of A Group Of Variables Holds the Lowest Value

[Ark68]

I have 4 variables: hpe1_st, hpe2_st, hpl1_st, hpl2_st.

I am looking for a vba solution to:
1) of the four, which one has the lowest value
2) return the variable name (eg hpe1_st, hpe2_st etc.) as another string variable (rtn_str) value. Ideally, I would just like to return the leading 4 characters)

Example
hpe1_st = 5
hpe2_st = 7
hpl1_st = 1
hpl2_st = 3

rtn_str = hpl1

Should multiple variables hold the samr value, it's ok to return the any of the matching variable.

 

[jasonb75]

Curiosity is forcing me to ask why you would want to try and do it this way?

A quick google search suggests that it is possible, but looking at the methods of doing so, I would look for a more practical alternative first.

 

[Rick Rothstein]

I have 4 variables: hpe1_st, hpe2_st, hpl1_st, hpl2_st.

Before attempting to answer this question, I have a question of my own... do you really only have four total variable to process or is this a highly simplified example? If a simplified example, how many variables do you actually have?

 

[Ark68]

Curiosity is forcing me to ask why you would want to try and do it this way?

A quick google search suggests that it is possible, but looking at the methods of doing so, I would look for a more practical alternative first.

I know I could do it using a series of if/thens or select case, but I thought there may be something more efficient. Arrays, min and combination of. A Google search is only as good as the terms you enter to search for, which clearly you know better than I do.

Before attempting to answer this question, I have a question of my own... do you really only have four total variable to process or is this a highly simplified example? If a simplified example, how many variables do you actually have?

Just these four. Their values are a result of a calculation. If they were in a range, it would be as simple as using the min function. Sure, I could put these values in a range and do it that way, but if there is a more efficient way to do it, I;m here to learn is all.

 

[jasonb75]

Personally for the small number of variables involved, I would go with a select case method. Glancing over this (which was the first thing that I found when I did a quick search earlier), it appears that whilst not impossible to do what you ask it would most likely be a less practical method.

VBA: Get name of variable

is there a function or a property to get a variables name? Something like msgBox myVariable.name or msgBox nameOfVariable(myVariable) that returns "myVariable" when I defined it with e.g. myVar...

stackoverflow.com

That said, I would suggest waiting to see if @Rick Rothstein has any neat tricks in reserve first.