vba 定义可以带空参数的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20662226/
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
Define function that can take null parameter
提问by Kai
I'm was recently trying to redefine Access's Nz(Value, [ValueIfNull])function in Excel, because I find it pretty useful yet it's absent in Excel (as I found to my disappointment when moving a few useful functions over). Access's Nz function checks Value- if this value is null, it returns ValueIfNull(otherwise it returns Value). In Access it's useful for checking the value of input boxes (amongst several other things):
我最近试图Nz(Value, [ValueIfNull])在 Excel 中重新定义 Access 的函数，因为我发现它非常有用，但它在 Excel 中却没有（因为我在移动一些有用的函数时发现我很失望）。Access 的 Nz 函数检查Value- 如果此值为 null，则返回ValueIfNull（否则返回Value）。在 Access 中，它可用于检查输入框的值（以及其他几项）：
If Nz(myTextBox.Value, "") = "" Then
MsgBox "You need to enter something!"
End If
Rolling my own Nzfunction didn't seem difficult:
滚动我自己的Nz函数似乎并不困难：
Public Function Nz(value As Variant, Optional valueIfNull As Variant = "") As Variant
If IsNull(value) Then
Nz = valueIfNull
Else
Nz = value
End If
End Function
But as soon as I try to call it with anything that's actually null, Excel complains about it on the calling line (Run-time error '91': Object variable or With block not set, which I understand to be roughly equivilant to a NullReferenceExceptionin other languages), before even getting to the Nz function body. For example Nz(someObj.Value, "")will only work if someObj.Valueisn't null (rendering the function entirely moot).
但是，一旦我尝试使用任何实际为 null 的内容调用它，Excel 就会在调用行（Run-time error '91': Object variable or With block not set，我理解它与NullReferenceException其他语言中的a 大致等效）上抱怨它，甚至在到达 Nz 函数体之前。例如，Nz(someObj.Value, "")只有在someObj.Value不为空时才有效（渲染函数完全没有实际意义）。
Am I missing some detail of VBA here? Coming from languages like VB.NET, it seems very confusing - I understand object references to be simply addresses to an actual object residing in memory, and so passing around the reference (not the object) shouldn't cause issue (until you try to actually do something with the non-existant object, of course). For eg:
我在这里错过了 VBA 的一些细节吗？来自 VB.NET 之类的语言，这似乎非常令人困惑 - 我理解对象引用只是指向驻留在内存中的实际对象的地址，因此传递引用（而不是对象）不应导致问题（直到您尝试当然，实际上对不存在的对象做一些事情）。例如：
Dim myObj As SomeObject
SomeMethod(myObj) 'the call itself is fine
Public Sub SomeMethod(SomeObject obj)
myObj.DoSomething() 'but *here* it would crash
End Sub
How can you create subs and functions in VBA that will accept a null parameter?
如何在 VBA 中创建接受空参数的子函数和函数？
回答by
see thisand thatif anything is still unclear and try
Sub Main()
Dim obj As Range
Debug.Print Nz(obj)
Dim v As Variant
v = Null
Debug.Print Nz(v)
End Sub
Public Function Nz(value As Variant, Optional valueIfNull As Variant = "") As Variant
' deal with an object data type, vbObject = 9
If VarType(value) = vbObject Then
If value Is Nothing Then
Nz = valueIfNull
Else
Nz = value
End If
' deal with variant set to null, vbNull is a Variant set to null
ElseIf VarType(value) = vbNull Then
If IsNull(value) Then
Nz = valueIfNull
Else
Nz = value
End If
End If
End Function
