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?
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 rib5dv45ko62adf2v0d1cot4kiu5t8mbdp@4ax.com
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 rib5dv45ko62adf2v0d1cot4kiu5t8mbdp@4ax.com
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.
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).
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
Because they probably thought that you would use DMAX and DMIN or the sql MAX and only working with the database in access?
Im also curious about why.. Its seems like a overkill to have to create a temp-table and add form values to the table and then run a DMAX or MAX-query on the table to get the result...
I've been known to create a small projMax() function just to deal with these. Not that VBA will probably ever be enhanced, but just in case they ever do add a proper Max (and Min) function, it won't conflict with my functions. BTW, the original poster suggests doing IIF... That works, but in my function, I usually throw a couple of Nz()'s to prevent a null from ruining the function.
Both functions have problems with Null. I think this will be better.
Public Function iMin(ParamArray p()) As Variant
Dim vVal As Variant, vMinVal As Variant
vMinVal = Null
For Each vVal In p
If Not IsNull(vVal) And (IsNull(vMinVal) Or (vVal < vMinVal)) Then _
vMinVal = vVal
Next
iMin = vMinVal
End Function
I liked DGM's use of the IIF statement and David's use of the For/Next loop, so I am combining them together.
Because VBA in access does not have a strict type checking, I will be using varients to preserve all numerics, integer and decimal, and re-type the return value.
Kudos to HansUP for catching my parameter verification :)
Comments added to make code more friendlier.
Option Compare Database
Option Base 0
Option Explicit
Function f_var_Min(ParamArray NumericItems()) As Variant
If UBound(NumericItems) = -1 Then Exit Function ' No parameters
Dim vVal As Variant, vNumeric As Variant
vVal = NumericItems(0)
For Each vNumeric In NumericItems
vVal = IIf(vNumeric < vVal, vNumeric, vVal) ' Keep smaller of 2 values
Next
f_var_Min = vVal ' Return final value
End Function
Function f_var_Max(ParamArray NumericItems()) As Variant
If UBound(NumericItems) = -1 Then Exit Function ' No parameters
Dim vVal As Variant, vNumeric As Variant
vVal = NumericItems(0)
For Each vNumeric In NumericItems
vVal = IIf(vNumeric < vVal, vVal, vNumeric) ' Keep larger of 2 values
Next
f_var_Max = vVal ' Return final value
End Function
The only difference between the 2 functions is the order of vVal and vNumeric in the IIF statement.
The for each clause uses internal VBA logic to handle the looping and array bounds checking, while "Base 0" starts the array index at 0.
You can call Excel functions in Access VBA:
Global gObjExcel As Excel.Application
Public Sub initXL()
Set gObjExcel = New Excel.Application
End Sub
Public Sub killXL()
gObjExcel.Quit
Set gObjExcel = Nothing
End Sub
Public Function xlMax(a As Double, b As Double) As Double
xlCeiling = gObjExcel.Application.Max(a, b)
End Function
You can do Worksheetfunction.max() or worksheetfunction.min() within Access VBA. Hope this helps.
