How to create a IsMissingOrEmptyString in VBA to cope with optional argument?

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 ?

Public Sub test(Optional varArg As Variant)

    m_Flag = false
 开发者_JS百科   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*   

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

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...

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

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

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.

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.

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)

And ...

Public Sub test(Optional varArg As String)

... concatenating 2 empty strings yields an empty string. (still 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.