Find the range of 10 values

[DJN]

Hi all,
I have a report on which I have 10 unbound fields. In these fields are the calculated averages from 4 calculated unbound fields. I need to determine the range between the 10 values. I have looked at DMax and DMin but this does not seem to fit the bill, as the fields are not in a table. Any help would be appreciated.

David

 

[MichaelRed]

Collect the 10 (or however many) values into an array oyour choice and use the basMin and BasMax functions below to get the min and max. The third procedure is just given as an example for "how to" use the others.

Public Function basMax(MyArray As Variant) As Variant

    Dim Idx As Long
    Dim MaxVal As Double

    MaxVal = MyArray(0)

    For Idx = 0 To UBound(MyArray) - 1
        If (MyArray(Idx + 1) > MyArray(Idx)) Then
            MaxVal = MyArray(Idx + 1)
        End If
    Next Idx

    basMax = MaxVal

End Function
'--------------------------------------------------------
Public Function basTestMax()

    Dim ThisArray(10) As Single
    
    For Idx = 0 To 10
        ThisArray(Idx) = Rnd() * (Idx + 1) * 1000
        Debug.Print ThisArray(Idx)
    Next Idx

    Debug.Print basMax(ThisArray())
End Function
'----------------------------------------------------
Public Function basMin(MyArray As Variant) As Variant


    Dim Idx As Long
    Dim MinVal As Double

    MinVal = MyArray(0)

    For Idx = 0 To UBound(MyArray) - 1
        If (MyArray(Idx + 1) < MyArray(Idx)) Then
            MinVal = MyArray(Idx + 1)
        End If
    Next Idx

    basMin = MinVal

End Function

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[DJN]

Thanks a lot Michael, just what I wanted.