如何在VBA中创建一个IsMissingOrEmptyString来处理可选参数？

If an optional argument is a variant IsMissing below works, if it is a string, IsMissing doesn't. How to create IsMissingOrEmptyString to cope with both ?

如果可选参数是下面的变体IsMissing,则如果它是字符串,则IsMissing不会。如何创建IsMissingOrEmptyString来应对这两种情况?

Public Sub test(Optional varArg As Variant)

    m_Flag = false
    If IsMissing(varArg) Then
        m_Flag = true
    End If

 End Sub   


Public Sub test(Optional varArg As String)

    m_Flag = false
    If varArg = "" Then
        m_Flag = true
    End If

 End Sub*

2 个解决方案

#1

IsMissing only works with the Variant datatype, because other datatypes are automatically initialised (assigned a default value) when declared.

IsMissing仅适用于Variant数据类型,因为其他数据类型在声明时会自动初始化(分配默认值)。

In the case of a string variable, the default value is vbNullString, and the fastest way to test for this is to use the lenB function...

对于字符串变量,默认值为vbNullString,测试此方法的最快方法是使用lenB函数...

Public Sub test(Optional varArg as String)
  m_Flag = (LenB(varArg) = 0)
End Sub

The above will set m_Flag to true if varArg = "".

如果varArg =“”,上面将m_Flag设置为true。

Note that declaring varArg as a string variable means that there is no way to distinguish between the case where an empty string is passed to the procedure, and the case where the argument is omitted in the procedure call.

请注意,将varArg声明为字符串变量意味着无法区分将空字符串传递给过程的情况,以及过程调用中省略参数的情况。

#2

If you change your functions's body to this, the result will be the same whether the missing optional parameter is declared as Variant or String.

如果将函数体改为this,则无论将缺少的可选参数声明为Variant还是String,结果都是相同的。

m_Flag = false
If Len(varArg & vbNullString) = 0 Then
    m_Flag = true
End If

So then ...

那么......

Public Sub test(Optional varArg As Variant)

... concatenating an empty string to Null produces an empty string. (Len = 0)

...将空字符串连接到Null会产生一个空字符串。 (Len = 0)

And ...

Public Sub test(Optional varArg As String)

... concatenating 2 empty strings yields an empty string. (still Len = 0)

...连接2个空字符串会产生一个空字符串。 (仍然是Len = 0)

So the outcome would be the same either way.

所以结果将是相同的两种方式。

However, depending on your needs, that may not be adequate. Notice for example, with varArg as String, there is no way to distinguish whether the user didn't supply a value for the parameter or actually submitted a null string as the parameter.

但是,根据您的需要,这可能不够。例如,如果将varArg作为String,则无法区分用户是否未提供参数值或实际提交的空字符串作为参数。

#1