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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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!