我将问题解释为:

  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

至于Access为什么不实现它,在我看来,这不是一件很常见的事情。它也不是很“数据库”。您已经获得了跨域和按行集合查找最大/最小值所需的所有函数。它也不是很难实现,或者在需要时只编写代码作为一次性比较。

也许以上这些会对某人有所帮助。

从MS Access VBA调用Excel VBA函数

如果你 添加引用 出类拔萃( Tools References Microsoft Excel x.xx Object Library )然后你可以用 WorksheetFunction 调用大多数Excel工作表函数,包括 MAX (也可用于阵列)。

MsgBox WorksheetFunction.Max(42, 1999, 888)

或

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

第一次呼叫需要一秒钟的响应时间(实际上对我来说是1.1秒),但随后的呼叫要合理得多(对我来说每个呼叫的响应时间都是<0.002秒)。

将Excel作为对象引用

Application 对象直接引用Excel。

例如,此过程重复使用Excel的 马克斯 在字节数组上确定每个记录的“最高”ASCII字符。

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

我也很好奇为什么。。创建一个临时表并向表中添加表单值,然后在表上运行DMAX或MAX查询以获得结果,这似乎有些过分了。。。

众所周知,我创建了一个小projMax()函数来处理这些问题。并不是说VBA可能会得到增强,但只要他们添加了适当的Max(和Min)函数,它就不会与我的函数冲突。顺便说一句,原来的海报建议做IIF。。。这是可行的,但在我的函数中,我通常抛出几个Nz(),以防止null破坏函数。

这两个函数都存在Null问题。我想这样会更好。

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

我喜欢DGM对IIF语句的使用和David对For/Next循环的使用,所以我将它们结合在一起。



感谢HansUP抓到了我的参数验证:)

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

这两个函数之间的唯一区别是IIF语句中vVal和vNumeric的顺序。
for each子句使用内部VBA逻辑处理循环和数组边界检查,而“Base 0”在0处启动数组索引。

您可以在Access VBA中调用Excel函数:

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

你能行 Worksheetfunction.max() worksheetfunction.min() 在Access VBA中。希望这有帮助。