Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# vba code to determine largest of three variables

Posted on 2013-11-19
Medium Priority
304 Views
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
0
Question by:fordraiders
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 3
• 2

LVL 52

Accepted Solution

Rgonzo1971 earned 2000 total points
ID: 39658646
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

LVL 49

Expert Comment

ID: 39659945
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

LVL 3

Author Comment

ID: 39660152
what declaration does
biggest
middle
lowest
lowerthan lowest get..

??

Thanks
0

LVL 3

Author Comment

ID: 39660162
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

LVL 49

Expert Comment

ID: 39660181
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

LVL 3

Author Closing Comment

ID: 39660557
Thanks all, Martinless,  Thanks for the additional help
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
###### Suggested Courses
Course of the Month7 days, 3 hours left to enroll

#### 705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.