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:


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:

Enjoy it!


Tagged with: , , , ,