Find Second Highest Value
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 rng As Range, cell As Range
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.
Set rng = Selection
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:
‘Find Highest Value
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.
‘Find Second Highest Value
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:
Next Chapter: Loop
