vba Access 中没有 max(x,y) 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1355521/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
No max(x,y) function in Access
提问by DGM
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.
VBA for Access 缺少一个简单的Max(x,y)函数来查找两个或多个值的数学最大值。我习惯于在来自其他语言（如 perl/php/ruby/python 等）的基本 API 中已经有这样的函数。
I know it can be done: IIf(x > y, x,y). Are there any other solutions available?
我知道可以做到：IIf(x > y, x,y). 有没有其他可用的解决方案？
采纳答案by David-W-Fenton
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"):
如何在 Access 中实现返回一组数字的最大值/最小值的函数？这是我使用的代码（通过与 IIf 类比命名为“iMax”，即“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.
至于为什么 Access 不会实现它，在我看来，这不是一件很常见的事情。它也不是很“数据库”。您已经获得了在跨域和多组行中查找最大值/最小值所需的所有函数。它也不是很难实现，或者在需要时仅将其编码为一次性比较。
Maybe the above will help somebody.
也许以上会帮助某人。
回答by ashleedawg
Calling Excel VBA Functions from MS Access VBA
从 MS Access VBA 调用 Excel VBA 函数
If you add a referenceto Excel (Tools→ References→ Microsoft Excel x.xx Object Library) then you can use WorksheetFunctionto call most Excel worksheet functions, including MAX(which can also be used on arrays).
如果添加对 Excel ( Tools→ References→ Microsoft Excel x.xx Object Library)的引用，则可以使用它WorksheetFunction来调用大多数 Excel 工作表函数，包括MAX（也可以用于数组）。
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).
第一个调用需要一秒钟的时间来响应（对我来说实际上是 1.1 秒），但随后的调用要合理得多（对我来说每个调用 <0.002 秒）。
Referring to Excel as an object
将 Excel 作为对象引用
If you're using a lot of Excel functions in your procedure, you may be able to improve performance further by using an Applicationobject to refer directly to Excel.
如果您在过程中使用了大量 Excel 函数，则可以通过使用Application对象直接引用 Excel 来进一步提高性能。
For example, this procedure iterates a set of records, repeatedly using Excel's MAXon a Byte Array to determine the "highest" ASCII character of each record.
例如，此过程迭代一组记录，MAX在字节数组上重复使用 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
回答by Stefan
Because they probably thought that you would use DMAX and DMIN or the sql MAX and only working with the database in access?
因为他们可能认为您会使用 DMAX 和 DMIN 或 sql MAX 并且只使用访问中的数据库？
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...
我也很好奇为什么.. 必须创建一个临时表并将表单值添加到表中，然后在表上运行 DMAX 或 MAX 查询以获得结果似乎有点矫枉过正......
回答by Knox
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.
众所周知，我创建了一个小的 projMax() 函数来处理这些问题。并不是说 VBA 可能会得到增强，但以防万一他们确实添加了适当的 Max（和 Min）函数，它不会与我的函数发生冲突。顺便说一句，原始海报建议做 IIF ......这可行，但在我的函数中，我通常会抛出几个 Nz() 以防止空值破坏函数。
回答by CoveGeek
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.
我喜欢 DGM 对 IIF 语句的使用和 David 对 For/Next 循环的使用，因此我将它们组合在一起。
因为访问中的 VBA 没有严格的类型检查，我将使用变量来保留所有数字、整数和小数，并重新键入返回值。
感谢 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
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.
这两个函数的唯一区别是 IIF 语句中 vVal 和 vNumeric 的顺序。
for each 子句使用内部 VBA 逻辑来处理循环和数组边界检查，而“Base 0”从 0 开始数组索引。
回答by The Unknown
Both functions have problems with Null. I think this will be better.
这两个函数都存在 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
回答by Joe D.
You can do Worksheetfunction.max()or worksheetfunction.min()within Access VBA. Hope this helps.
您可以执行Worksheetfunction.max()或worksheetfunction.min()在 Access VBA 中执行。希望这可以帮助。
