Null 值为零的 VBA 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23368257/
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
VBA Code for Null value to Zero
提问by user3574211
How do I modify these functions so that Null values are returned as Zero?
如何修改这些函数以便 Null 值返回为零？
Function MinOfList(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMin As Variant 'Smallest value found so far.
varMin = Null 'Initialize to null
For i = LBound(varValues) To UBound(varValues)
If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
If varMin <= varValues(i) Then
'do nothing
Else
varMin = varValues(i)
End If
End If
Next
MinOfList = varMin
End Function
Function MaxOfList(ParamArray varValues()) As Variant
Dim i As Integer 'Loop controller.
Dim varMax As Variant 'Largest value found so far.
varMax = Null 'Initialize to null
For i = LBound(varValues) To UBound(varValues)
If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
If varMax >= varValues(i) Then
'do nothing
Else
varMax = varValues(i)
End If
End If
Next
MaxOfList = varMax
End Function
回答by Elias
Per Brad's comment, this solution will only work in Microsoft Access.
根据 Brad 的评论，此解决方案仅适用于 Microsoft Access。
A cheap way out is to use the nz function
一种廉价的方法是使用 nz 函数
NZ(VariableName, 0)
End of the MinOfList Function: MinOfList = NZ(varMin, 0)
MinOfList 函数结束： MinOfList = NZ(varMin, 0)
End of the MaxOfList Function: MaxOfList = NZ(varMax, 0)
MaxOfList 函数结束： MaxOfList = NZ(varMax, 0)
Here's a quick Nz implementation to mimic NZ's functionality:
这是一个模拟 NZ 功能的快速 Nz 实现：
Public Function Nz( Value As Variant, ValueIfNull As Variant ) As Variant
Nz = IIf(IsNull(Value), ValueIfNull, Value)
End Function
回答by Dan Wagner
You can examine the varMinand varMaxobjects with an Ifstatement. Here's how to check in the MinOfListfunction:
您可以使用语句检查varMin和varMax对象If。以下是检查MinOfList函数的方法：
If varMin = Null Then
MinOfList = 0
Else
MinOfList = varMin
End If
