VBA – Excel custom formula for second biggest value

In this rather small article I have decided to show how to take input from arrays for Excel custom formulas.

In order to do this, I have decided to make a custom formula, calculating the second biggest number from a given sequence. In Excel, you can achieve the same effect with the following:

=LARGE(A1:A20,2)

It would give you the second biggest number in the range “A1:A20”.

So, with one “ElseIf”, 3 variables  and one for-loop I have achieved the same result. At the beginning we loop all the variables in from the array. If the variable is larger than “lBiggestNumber”, we assign it to it and we assign the previous value of “lBiggestNumber” to l2ndBiggestNumber”. At the end we display the result in a sentence (something that MS Excel cannot do 🙂 ).

Here is how it looks like in a GIF:

Here comes the outstanding code:

Public Function fSecondBiggestNumber(ParamArray arrNumbers() As Variant) As String

    Dim i                   As Long
    Dim lBiggestNumber      As Long
    Dim l2ndBiggestNumber   As Long
    
    For i = LBound(arrNumbers) To UBound(arrNumbers)
       If arrNumbers(i) > lBiggestNumber Then
       
       l2ndBiggestNumber = lBiggestNumber
       lBiggestNumber = arrNumbers(i)
       
       ElseIf arrNumbers(i) > l2ndBiggestNumber Then
       l2ndBiggestNumber = arrNumbers(i)
       
       End If
       
    Next i
    
    fSecondBiggestNumber = "The second biggest value from the list is " & l2ndBiggestNumber & "."
        
End Function

Enjoy it!