[AccessD] Max of 20 values

Dan Waters df.waters at comcast.net
Sat Jul 23 08:42:00 CDT 2011

Hi Arthur,

I found this function in my library file.  This uses a parameter array and
just loops through looking for the highest one.


Private Sub textMaxOfVars()

    MsgBox MaxOfVars(20, 30, Null, -7, 8, 87.23, "top")
End Sub

Public Function MaxOfVars(ParamArray varValues() As Variant)
On Error GoTo EH

    '-- Purpose: Return a max of variants.  Note that any string will be
larger than any numeric value.

    Dim lvarVal As Variant
    Dim lvarMax As Variant
    lvarMax = varValues(0)
    For Each lvarVal In varValues
        If lvarVal > lvarMax Then
            lvarMax = lvarVal
        End If
    Next lvarVal
    MaxOfVars = lvarMax

    Exit Function
    Select Case Err.Number
        Case 9  '-- Subscript out of range - probably due to no values
            Application.Echo True
            MaxOfVars = Null
            GoTo XH
    End Select

End Function

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Saturday, July 23, 2011 7:54 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Max of 20 values

I have a form into which the user enters up to 20 values (measurements in
time). They all default to zero. I'm trying to think of an elegant way to
find the max value among the 20. Create an array and bubble sort it? I want
to call this code on the AfterUpdate of each of these 20 controls so the
control called MaxReading gets a new value if any of the 20 controls goes
higher than the current MaxReading value.

AccessD mailing list
AccessD at databaseadvisors.com
Website: http://www.databaseadvisors.com

More information about the AccessD mailing list