# vba code to determine largest of three variables

excel vba 2010

I have 3 variables:

cn1 = 3
cn2 = 7
cn3 = 2

Is there vba code that help determine which of these 3 variables is the largest ?
mid and them smallest?

so the outsome would be

cn2 = largest
cn1 = mid
cn3 = smallest

Thanks
fordraiders
LVL 3
###### Who is Participating?

x

Commented:
Hi,

Pls try

``````Dim arrVar(1 To 3)
arrVar(1) = cn1
arrVar(2) = cn2
arrVar(3) = cn3

Biggest = WorksheetFunction.Large(arrVar, 1)
Middle = WorksheetFunction.Large(arrVar, 2)
Lowest = WorksheetFunction.Large(arrVar, 3)
``````
Regards
0

Older than dirtCommented:
Don't give me any points for this but what the 'Large' function that Rgonzo1971 cleverly used is doing is sorting the values from highest to lowest so with a 4th value you could do this.

``````Dim arrVar(1 To 4)
arrVar(1) = cn1
arrVar(2) = cn2
arrVar(3) = cn3
arrVar(4) = cn4

Biggest = WorksheetFunction.Large(arrVar, 1)
Middle = WorksheetFunction.Large(arrVar, 2)
Lowest = WorksheetFunction.Large(arrVar, 3)
LowerThanLowest = WorksheetFunction.Large(arrVar, 4)
``````
0

Author Commented:
what declaration does
biggest
middle
lowest
lowerthan lowest get..

??

Thanks
0

Author Commented:
why would thid not work ?

if  the value of
cn1 = 3
cn2 = 7
cn3 = 2

why would this imple statement not work ?

If cn2 >= cn1 Or cn3 Then

??
0

Older than dirtCommented:
what declaration does
biggest
middle
lowest
lowerthan lowest get..
It would depend on the data. For integers Long would be best or otherwise Double or Currency.

If cn2 >= cn1 Or cn3 Then
You would need to use a series of If Else's but you could do this
``````Select Case True
Case cn1 > cn2 And cn1 > cn3
Msgbox "cn1 is the largest"
Case cn2 > cn1 And cn2 > cn3
Msgbox "cn2 is the largest"
Case cn3 > cn2 And cn3 > cn1
Msgbox "cn3 is the largest"
Ens Select
``````
0

Author Commented:
Thanks all, Martinless,  Thanks for the additional help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.