Find Second Highest Value in Excel VBA
Below we will look at a program in Excel VBA that finds the second highest value.
Situation:
1. First, we declare two Range objects and two variables of type Double. We call the Range objects rng and cell. One Double variable we call highestValue, and one Double variable we call secondHighestValue.
Dim highestValue As Double, secondHighestValue As Double
2. We initialize the Range object rng with the selected range and the two Double variables with value 0.
highestValue = 0
secondHighestValue = 0
3. First, we want to find the highest value. We want to check each cell in a randomly selected range (this range can be of any size). In Excel VBA, you can use the For Each Next loop for this. Add the following code lines:
For Each cell In rng
Next cell
Note: rng and cell are randomly chosen here, you can use any names. Remember to refer to these names in the rest of your code. The green line is a comment and is only added here to provide information about this piece of code.
4. We check each cell in this range. If it's higher than highestValue, we write the value to the variable highestValue. Add the following code line to the loop.
Note: the first value is always higher than highestValue because the starting value of highestValue is 0.
5. Second, we want to find the second highest value. We add another For Each Next loop.
For Each cell In rng
Next cell
6. We check each cell in the selected range again. If it's higher than secondHighestValue and lower than highestValue, we write the value to the variable secondHighestValue. Add the following code line to the loop.
7. Finally, we display the second highest value using a MsgBox.
8. Place your macro in a command button, select the numbers, and click on the command button.
Result:
Note: understanding If Then statements and loops in Excel VBA is important if you want to write more complicated programs in the future. However, in this example, you can also use WorksheetFunction.Large to quickly find the second highest value.
9. The following code line produces the exact same result:
Note: use the value 3 to find the third highest value, the value 4 to find the fourth highest value, etc.
