Testing if a string is null

VBA doesn't support testing whether a string is "Null". VBA isn't like a .NET language or JavaScript (for example). The basic variable types all have a default value, a String is of zero length ("") from the moment the variable is declared - it has no uninstantiated state. You can also test for vbNullString.

If you test

Dim s as String
Debug.Print s = Null, s <> Null, s = "", s = "a", IsNull(s), s = vbNullString

The return is

Null  Null  True  False  False  True

So if you're trying to test whether anything has been assigned to a String variable the only things you can do are:

Debug.Print Len(s), s = "", Len(s) = 0, s = vbNullString

Which returns

0  True  True True

Note that the slowest of these possibilities is s = "", even though it seems the simplest to remember.


As others have noted, you want to test against the null version of a string, vbNullString, and not against Null specifically. In addition to this, you also need to make sure your object isn't null itself. For example:

Dim rqRequirements As ReqPro40.Requirements
Dim rqRequirement As ReqPro40.Requirement
Const eAttrValueLookup_Label = 4
Dim a As Long ' Avoid Integer since it has a strong habit of causing overflow errors.
...

For Each vReqKey In rqRequirements
    Set rqRequirement = rqRequirements(vReqKey)

    If Not rqRequirement Is Nothing Then
        If rqRequirement.AttrValue("MyAttreName", eAttrValueLookup_Label).text <> vbNullString Then
            a = 1
        End If

        If rqRequirement.AttrValue("MyAttreName", eAttrValueLookup_Label).text = vbNullString Then
             a = 2
        End If
    End If
 Next

Now, I haven't worked with this specific object type before, but I am fairly certain that AttrValue("MyAttreName", eAttrValueLookup_Label) is returning some kind of object. If this is the case, then the below pattern would be preferred:

    Dim rqRequirements As ReqPro40.Requirements
    Dim rqRequirement As ReqPro40.Requirement
    Const eAttrValueLookup_Label = 4
    Dim a As Long ' Avoid Integer since it has a strong habit of causing overflow errors.
    ...

    For Each vReqKey In rqRequirements
        Set rqRequirement = rqRequirements(vReqKey)

        If Not rqRequirement Is Nothing Then
            Dim Attribute as Object ' Or whatever type it should be
            Set Attribute = rq.Requirement.AttrValue("MyAttreName", eAttrValueLookup)
            If Not Attribute is Nothing Then
                If Attribute.text <> Null Then
                    a = 1
                End If

                If Attribute.text = Null Then
                     a = 2
                End If
            End If
        End If
     Next

In this way, we are only ever calling upon the text property of the Attribute if we have actually set the Attribute. This avoids 424 errors down the line.

Finally, if you want to figure out what is happening in the code that is causing both if's to run, do something like this:

Debug.Print "Attribute Text: ", Attribute.Text

This will allow you to see what your code is seeing. You can consider using breakpoints as well.