Max Func returns Variable name

**NTB** · 09-06-2011, 10:53 PM

This sub below should return a value of 5, how do get this to return variable Five instead?

Sub MaxVar()
    Dim One, Two, Three, Four, Five As Integer
    One = 1
    Two = 2
    Three = 3
    Four = 4
    Five = 5

    MsgBox = WorksheetFunction.Max(One, Two, Three, Four, Five)
End Sub

Please advice!

Thank you,

NTB

**MarvinP** · 09-06-2011, 10:59 PM

Hi NTB

Have you looked at the Choose function? No VBA needed. If you really want to put it in VBA you can use Application.WorksheetFunction.Choose...

**NTB** · 09-07-2011, 12:43 AM

MarvinP

Choose, I have to pick the item specifically...

I had tried, but the function below return Four... unless I have to put everything in order which in real life the values don't line up like that.

Any other way that you know?

Sub MaxVar()
    Dim One, Two, Three, Four, Five As Integer
    Dim MaxValue, MaxValueName As String
    One = 1
    Two = 2
    Three = 3
    Four = 4
    Five = 5
    MaxValue = WorksheetFunction.Max(Five, One, Two, Three, Four)
    MaxValueName = Choose(MaxValue, "Five", "One", "Two", "Three", "Four")
    MsgBox MaxValueName
End Sub

**Andrew-R** · 09-07-2011, 02:27 AM

It might help if you explained a little more about what you're trying to do and why - having Excel return a variable name rather than the value in the variable is quite an unusual thing to do.

**watersev** · 09-07-2011, 02:46 AM

hi, NTB, try this out:

Sub MaxVar()
    Dim One, Two, Three, Four, Five As Integer
    One = 1
    Two = 2
    Three = 3
    Four = 4
    Five = 5

    MsgBox WorksheetFunction.Max(One, Two, Three, Four, Five)
End Sub

returns value 5

**NTB** · 09-07-2011, 07:31 AM

Andrew-R:
Basically, what I posted was a simplified version. I didn't want to confuse everyone too much.
My function has 8 different variables, 8 different possible directions: Up, Down, Left, Right, UL, UR, DL, DR

Each variable would hold a dynamic value for each loop and I would ultimately want to pick the variable with the highest value.

Instead of doing so many if statements, I thought of Max fuction, but Max only returns the highest value but not the variable that holds it.

Up = 2
Down = 0
Left = 2
Right = 4
UL = 4
UR = 6
DL = 2
DR = 4

To pick DR I would have to do 8 different ifs

if Up > than the rest then Up
if Down > the rest the down
etc etc...

I would love to have something similiar to

Dim MaxVar as String
MaxVar = WorksheetFunction.Max(Up, Down, Left, Right, UL, UR, DL, DL)

MaxVar would return "UR" because it holds 6, the highest value

**MarvinP** · 09-07-2011, 08:06 AM

Have you looked at the SELECT CASE statement in VBA. It was created for replacing multiple IF statements.
http://www.ozgrid.com/VBA/select-case.htm or
http://www.databison.com/index.php/v...ment-in-excel/

**NTB** · 09-07-2011, 08:13 AM

Maybe I'm not understanding this right... but I think it's not relevant to use that here because I would have to compare each variable against the rest.

**Andrew-R** · 09-07-2011, 08:16 AM

OK, how about

Sub ReturnLargest()
Dim avVariableValue As Variant
Dim avVariableName As Variant
Dim lLargest As Long

avVariableName = Array("Up", "Down", "Left", "Right", "UL", "UR", "DL", "DR")

'Use what-ever code you want to set these values
avVariableValue = Array(1, 2, 3, 4, 5, 6, 7, 8)

lLargest = WorksheetFunction.Match(WorksheetFunction.Max(avVariableValue), avVariableValue, 0)
MsgBox avVariableName(lLargest - (1 - LBound(avVariableName)))

End Sub

**NTB** · 09-07-2011, 08:50 AM

Test run

Sub ReturnLargest()
Dim avVariableValue As Variant
Dim avVariableName As Variant
Dim lLargest As Long
Dim Up, Down, Left, Right, UL, UR, DL, DR As Integer

Up = 2
Down = 0
Left = 2
Right = 4
UL = 4
UR = 6
DL = 2
DR = 4

avVariableName = Array("Up", "Down", "Left", "Right", "UL", "UR", "DL", "DR")
avVariableValue = Array(Up, Down, Left, Right, UL, UR, DL, DR)

lLargest = WorksheetFunction.Match(WorksheetFunction.Max(avVariableValue), avVariableValue, 0)
MsgBox avVariableName(lLargest - (1 - LBound(avVariableName)))
End Sub

Messagebox returns "UR" - DING DING DING.

Thank you very Much! +1

Max Func returns Variable name

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Max Func returns Variable name

Re: Max Func returns Variable name

Re: Max Func returns Variable name

Re: Max Func returns Variable name

Re: Max Func returns Variable name

Re: Max Func returns Variable name

Re: Max Func returns Variable name

Re: Max Func returns Variable name

Re: Max Func returns Variable name

Re: Max Func returns Variable name

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions