| Microsoft Office 2000/Visual Basic Programmer's Guide | |
Many mathematical functions operate on a variable set of numbers. For example, you can take the median, or middle value, of a set of any size. Since you won't know how many numbers the set will contain while you're writing code to find the median, you can't create a procedure with a set number of arguments. Instead, you can use a dynamic array to store an indeterminate number of values and perform an operation on them.
The following procedure takes a parameter array and returns the median of the values in the array. A parameter array encompasses a variable number of arguments that are passed to a procedure as an array. The ParamArray keyword specifies a parameter array, which must be defined as type Variant.
The Median procedure calls another procedure, IsNumericArray, which determines whether the array contains any non-numeric elements before the Median procedure attempts to find the median. It then calls the QuickSortArray procedure, which sorts the array. Finally, it determines whether the array contains an even or odd number of elements. If the number of elements is odd, the middle element in the sorted array is the median. If the number of elements is even, the median is the average of the two midmost elements.
Function Median(ParamArray avarValues() As Variant) As Double
' Return the median of a set of numbers.
Dim lngCount As Long
Dim varTemp As Variant
' Store array in temporary variable.
varTemp = avarValues()
' Check whether array is numeric.
If IsNumericArray(varTemp) Then
' Determine how many elements are in array.
lngCount = UBound(varTemp) - LBound(varTemp) + 1
' Sort the array.
QuickSortArray varTemp
' Determine whether array contains an odd or even number of elements.
If IsEven(lngCount) Then
' If even, need to find the two middle elements and
' return the average of their values.
' Remember we're working with a zero-based array!
Median = (varTemp(lngCount / 2 - 1) + varTemp(lngCount / 2)) / 2
Else
' If odd, need to find the middle element.
Median = varTemp(Int(lngCount / 2))
End If
Else
' Return -1 if array isn't numeric.
Median = -1
End If
End Function
This procedure is available in the modNumbers module in VBA.mdb in the ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM.
To test the Median procedure, try calling it with an even set of numbers, then with an odd set of numbers, as follows:
? Median(45, 67, 23, 89, 52, 101)
To make sure it's working properly, you can check it against the Excel Median worksheet function. Note that the Excel Median function can take no more than 30 arguments, while the procedure shown here can take any number of arguments.
You could also modify this procedure to take an array, rather than a parameter array. The parameter array is somewhat easier to test in isolation, but a procedure that takes an array may be more practical for use within your code. For example, you may have a procedure that fills an array with numeric data from a data source, which you can then pass to the Median procedure to determine the median of the set of numbers, without having to pass each value as an argument to the procedure.
The strategy shown here for finding the median also works for other operations that take an indeterminate number of values, such as finding the average or standard deviation, or performing other statistical calculations.