Top AvgData

This example shows a case in which a custom function can make things much easier for you. The following custom VBA function, named TopAvg, returns the average of the N largest values in a range:

Function TopAvg(InRange, N)

' Returns the average of the highest N

values in InRange

Dim Sum As Double

Dim I As Long

Sum = 0

For i = 1 To N

Sum = Sum + _

Application.WorksheetFunction.

. LARGE(InRange, i)

Next i

TopAvg = Sum / N

End Function

This function takes two arguments: InRange (which is a worksheet range) and N (the number of values to average). It starts by initializing the Sum variable to 0. It then uses a For-Next loop to calculate the sum of the N largest values in the range. Note that I use the Excel LARGE function within the loop. Finally, TopAvg is assigned the value of Sum divided by N.

You can use all Excel worksheet functions in your VBA procedures except those that have equivalents in VBA. For example, VBA has a Rnd function that returns a random number. Therefore, you can't use the Excel RAND function in a VBA procedure.

0 0

Post a comment