@ > Home > Contents > VBA function with range type argument Excel 97+

Calling the function (by Melanie Breden, 2007/02/27)

   =SumPercent(A1:A11,90%) on A1:A11={3,7,2,8,6,10,4,9,11,5,1}

returns 60.

How does it work: Return the running sum of the remaining biggest when 90% of the total are exceeded.

Public Function SumPercent(ByVal rngRange As Range, dblPercent As Double) As Double
   Dim arLarge()  As Variant
   Dim lngC       As Long
   ReDim arLarge(rngRange.Rows.Count - 1)
   For lngC = 0 To UBound(arLarge)
      arLarge(lngC) = Application.Large(rngRange,lngC + 1)
      If Application.Sum(arLarge) > Application.Sum(rngRange) * dblPercent Then
         SumPercent = Application.Sum(arLarge)
         Exit Function
      End If
   Next lngC
End Function

The function is nicely readable, has got a Range argument and variable names contain types. Also, it can not be replaced by a single worksheet formula, since both sorting and exiting can not be done by a single array formula.