Problem with median function(user defined)

Question

I have a problem.. i want to create a user defined function for microsoft office access which can find the median of some numbers. Obviously it will be in ms visual basic. Can anyone help me to do this?

Answer 1

If you want to supply individual values:

Function Median(ParamArray Values()) As Variant

    Dim i As Long

    Dim n As Long

    Dim arr()

    For i = LBound(Values) To UBound(Values)

        If IsNumeric(Values(i)) Then

            n = n + 1

            ReDim Preserve arr(1 To n)

            arr(n) = Values(i)

        End If

    Next i

    If n = 0 Then

        Median = Null

        Exit Function

    End If

    BubbleSort arr

    If n Mod 2 = 0 Then

        Median = (arr(n / 2) + arr(n / 2 + 1)) / 2

    Else

        Median = arr((n + 1) / 2)

    End If

End Function

Sub BubbleSort(arr)

    Dim i As Long

    Dim j As Long

    Dim varTemp As Variant

    For i = LBound(arr) To UBound(arr) - 1

        For j = i + 1 To UBound(arr)

            If arr(i) > arr(j) Then

                varTemp = arr(i)

                arr(i) = arr(j)

                arr(j) = varTemp

            End If

        Next j

    Next i

End Sub

Usage in a query:

TheMedian: Median([ThisField], [ThatField], [SomeField], [OtherField])

To find the median value of a single field in a table or query, see Finding the Median Value for Fields in Access 2007.