No max(x,y) function in Access

Question

VBA for Access lacks a simple Max(x,y) function to find the mathematical maximum of two or more values. I'm accustomed to having such a function already in the base API coming from other languages such as perl/php/ruby/python etc.

I know it can be done: IIf(x > y, x,y). Are there any other solutions available?

Answer 1

I'll interpret the question to read:

How does one implement a function in Access that returns the Max/Min of an array of numbers? Here's the code I use (named "iMax" by analogy with IIf, i.e., "Immediate If"/"Immediate Max"):

  Public Function iMax(ParamArray p()) As Variant
  ' Idea from Trevor Best in Usenet MessageID [email protected]
    Dim i As Long
    Dim v As Variant

    v = p(LBound(p))
    For i = LBound(p) + 1 To UBound(p)
      If v < p(i) Then
         v = p(i)
      End If
    Next
    iMax = v
  End Function

  Public Function iMin(ParamArray p()) As Variant
  ' Idea from Trevor Best in Usenet MessageID [email protected]
    Dim i As Long
    Dim v As Variant

    v = p(LBound(p))
    For i = LBound(p) + 1 To UBound(p)
      If v > p(i) Then
         v = p(i)
      End If
    Next
    iMin = v
  End Function

As to why Access wouldn't implement it, it's not a very common thing to need, seems to me. It's not very "databasy", either. You've already got all the functions you need for finding Max/Min across domain and in sets of rows. It's also not very hard to implement, or to just code as a one-time comparison when you need it.

Maybe the above will help somebody.

Answer 2

Calling Excel VBA Functions from MS Access VBA

If you add a reference to Excel (Tools → References → Microsoft Excel x.xx Object Library) then you can use WorksheetFunction to call most Excel worksheet functions, including MAX (which can also be used on arrays).

Examples:

MsgBox WorksheetFunction.Max(42, 1999, 888)

or,

Dim arr(1 To 3) As Long
arr(1) = 42
arr(2) = 1999
arr(3) = 888
MsgBox WorksheetFunction.Max(arr)

The first call takes a second to respond (actually 1.1sec for me), but subsequent calls are much more reasonable (<0.002sec each for me).

---

Referring to Excel as an object

If you're using a lot of Excel functions in your procedure, you may be able to improve performance further by using an Application object to refer directly to Excel.

For example, this procedure iterates a set of records, repeatedly using Excel's MAX on a Byte Array to determine the "highest" ASCII character of each record.

Option Compare Text
Option Explicit
'requires reference to "Microsoft Excel x.xx Object Library"
Public excel As New excel.Application 

Sub demo_ListMaxChars()
    'list the character with the highest ASCII code for each of the first 100 records
    Dim rs As Recordset, mx
    Set rs = CurrentDb.OpenRecordset("select myField from tblMyTable")
    With rs
        .MoveFirst
            Do
                mx = maxChar(!myField)
                Debug.Print !myField, mx & "(" & ChrW(mx) & ")"  '(Hit CTRL+G to view)
                .MoveNext
            Loop Until .EOF
        .Close
    End With
    Set rs = Nothing     'always clean up your objects when finished with them!
    Set excel = Nothing  
End Sub

Function maxChar(st As String)
    Dim b() As Byte                             'declare Byte Array
    ReDim b(1 To Len(st))                       'resize Byte Array
    b = StrConv(st, vbFromUnicode)              'convert String to Bytes
    maxChar = excel.WorksheetFunction.Max(b)    'find maximum Byte (with Excel function)
End Function