vba 如何测试是否提供了可选参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1660772/
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
How do I test if optional arguments are supplied or not?
提问by TheFlash
How do I test if optional arguments are supplied or not? -- in VB6 / VBA
如何测试是否提供了可选参数？-- 在 VB6/VBA 中
Function func (Optional ByRef arg As Variant = Nothing)
If arg Is Nothing Then <----- run-time error 424 "object required"
MsgBox "NOT SENT"
End If
End Function
回答by Konrad Rudolph
Use IsMissing:
使用IsMissing：
If IsMissing(arg) Then
MsgBox "Parameter arg not passed"
End If
However, if I remember correctly, this doesn't work when giving a default for the argument, and in any case it makes using the default argument rather redundant.
但是，如果我没记错的话，这在为参数提供默认值时不起作用，并且在任何情况下都会使使用默认参数变得多余。
回答by Florian Fankhauser
You can use the IsMissing() Function. But this one only works with the Variant datatype.
您可以使用 IsMissing() 函数。但是这个只适用于 Variant 数据类型。
Sub func(Optional s As Variant)
If IsMissing(s) Then
' ...
End If
End Sub
回答by OSUZorba
If you are using a string or number variable you can check the value of the variable. For example:
如果您使用的是字符串或数字变量，您可以检查变量的值。例如：
Function func (Optional Str as String, Optional Num as Integer)
If Str = "" Then
MsgBox "NOT SENT"
End If
If Num = 0 Then
MsgBox "NOT SENT"
End If
End Function
This allows you to use non-variant variables.
这允许您使用非变体变量。
回答by user7238479
You can use something like:
您可以使用以下内容：
function func(optional vNum as integer:=&HFFFF) '&HFFFF value that is NEVER set on vNum
If vNum = &HFFFF Then
MsgBox "NOT SENT"
End If
End Function
回答by Pontus Gagge
If IsMissing(arg) Then ...
如果 IsMissing(arg) 那么 ...
回答by ashleedawg
With a variant I would use the NZfunction:
对于一个变体，我将使用NZ函数：
Function func (Optional ByRef arg As Variant = Nothing)
If nz ( arg, 0 ) = 0 Then
MsgBox "NOT SENT"
End If
End Function
It can be used with other data types too, just keep in mind that Zero counts as neither Null nor Zero-Length, so nz(0,"")still returns 0.
它也可以与其他数据类型一起使用，请记住零既不是空也不是零长度，因此nz(0,"")仍然返回 0。
回答by RLE
"IsMissing"...Figured there would have to be a way. Thanks all!
“IsMissing”......认为必须有一种方法。谢谢大家！
SQL has a function, In(), where you can pass multiple arguments to see if the target value is in the list. I've always liked that as a solution, so here's my take on that, hope it helps:
SQL 有一个函数 In()，您可以在其中传递多个参数以查看目标值是否在列表中。我一直喜欢将其作为解决方案，所以这是我的看法，希望对您有所帮助：
Public Function IsIn(ByVal TestVal, ByVal VersusVal1, _
Optional ByVal VersusVal2, Optional ByVal VersusVal3, _
Optional ByVal VersusVal4, Optional ByVal VersusVal5, _
Optional ByVal VersusVal6, Optional ByVal VersusVal7, _
Optional ByVal VersusVal8, Optional ByVal VersusVal9, _
Optional ByVal VersusVal10, Optional ByVal VersusVal11, _
Optional ByVal VersusVal12, Optional ByVal VersusVal13, _
Optional ByVal VersusVal14, Optional ByVal VersusVal15, _
Optional ByVal VersusVal16, Optional ByVal VersusVal17, _
Optional ByVal VersusVal18, Optional ByVal VersusVal19, _
Optional ByVal VersusVal20) As Boolean
Dim CheckVals(1 To 20) as Variant
VersusVals(1) = VersusVal1
VersusVals(2) = VersusVal2
VersusVals(3) = VersusVal3
VersusVals(4) = VersusVal4
VersusVals(5) = VersusVal5
VersusVals(6) = VersusVal6
VersusVals(7) = VersusVal7
VersusVals(8) = VersusVal8
VersusVals(9) = VersusVal9
VersusVals(10) = VersusVal10
VersusVals(11) = VersusVal11
VersusVals(12) = VersusVal12
VersusVals(13) = VersusVal13
VersusVals(14) = VersusVal14
VersusVals(15) = VersusVal15
VersusVals(16) = VersusVal16
VersusVals(17) = VersusVal17
VersusVals(18) = VersusVal18
VersusVals(19) = VersusVal19
VersusVals(20) = VersusVal20
On Error Goto 0
IsIn = False
For x = 1 To 20
If Not IsMissing(VersusVals(x)) Then
If TestVal = VersusVals(x) Then
IsIn = True
Exit For
End If
End If
Next x
End Function
So, that's obviously why I needed "IsMissing"; doesn't work without it.
所以，这显然就是我需要“IsMissing”的原因；没有它就行不通。
回答by Selkie
Most of these refer to the variant type, or test if a value is blank.
其中大部分是指变体类型，或测试值是否为空。
However, sometimes you want to check if a range, workbook, worksheet, or other type of object is not passed, without checking things like sheetnames.
但是，有时您想检查区域、工作簿、工作表或其他类型的对象是否未通过，而不检查工作表名称之类的内容。
In that case:
在这种情况下：
DesiredRange is Nothing
Returns a boolean. For example:
返回一个布尔值。例如：
If DestinationRange Is Nothing Then
MsgBox "Need a destination range when importing data"
Else
'We're happy
End If
