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:
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)
fSecondBiggestNumber = "The second biggest value from the list is " & l2ndBiggestNumber & "."