Testing if a string is null

Question

I am pretty new in VBA and I have not yet got used to the syntax completely, so I'm sorry if my question sounds stupid.

I am working with RequisitePro40 and VBA 7.0 in Word 2010. In one of my modules I have the following loop and If conditions:

Dim rqRequirements As ReqPro40.Requirements
Dim rqRequirement As ReqPro40.Requirement
Const eAttrValueLookup_Label = 4
Dim a As Integer
...

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

    If rqRequirement.AttrValue("MyAttreName", eAttrValueLookup_Label).text <> Null Then
        a = 1
    End If

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

 Next

In each iteration of the loop, both a = 1 and a = 2 are executed!!

Based on This, the equality and inequality operators are "=" and "<>". Therefore I would expect that either a = 1 or a = 2 execute for a string. Is there something wrong with my syntax? Or is it a ReqPro related Problem?

I also tried using "Is" and "IsNot" operators but they result in Compiler error: Type mismatch

Can Someone help me with this?

Update: The actual goal is to see if the

rqRequirement.AttrValue("MyAttreName", eAttrValueLookup_Label).text

is Null or not. I added the second if to show the problem that the statement is somehow not working the way I expect it to work.

Replacing "Null" to "vbNullString" did not make any changes.

I also tried the IsNull function as @Slai suggested. the result is pretty much the same:

    If IsNull(rqRequirement.AttrValue(att, eAttrValueLookup_Label).text) Then
        a = 3
    End If

    If Not IsNull(rqRequirement.AttrValue(att, eAttrValueLookup_Label).text) Then
        a = 4
    End If

Both statements a = 3 and a = 4 are true and executed.

Impale

How have you declared a? As a string? – Phrenology

And on which line is the error? – Phrenology

@Phrenology There is no error. But apparently both conditions text = Null and text <> Null are true for the same text. Which does not seem logical to me and I can't see why it happens – Impale

there is IsNull Function for that msdn.microsoft.com/en-us/vba/language-reference-vba/articles/… – Intoxicated

1) what's the type of the object returned by rqRequirement.AttrValue("MyAttreName", eAttrValueLookup_Label).text? (you may look at ReqPro40 object model documentation for that) 2) What's your actual aim? (I can't understand the If-Then structure in your code) – Iey

With VBA you wouldn't normally test a String for "Null". You'd test whether it's "empty" or not and the most effiicient way to do so is: Len(stringVariable) > 0 If the strVariable = "" is also possible, but not as efficient in execution. – Craunch

@Iey The actual goal is to see if the text is Null on not. I added the second if to show the problem that the statement is somehow not working the way I expect it to work. – Impale

According to this SO-question .text could be the problem. The answer suggests using .value instead. – Tse

@IGV it may well be that the underlying thing (Requirement) has a Value property that returns Null, but the Q you link to is specifically about what an EXCEL RANGE can return and is not directly applicable, here. – Craunch

Answer 1

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.

Craunch

Isn't s = " " same as vbNullString test? – Phrenology

The test against vbNullString is preferred in fact. While vbNullString = "" it is better to use the built-in name than to use the empty quotes. – Teens

I thought so which is why I put it :-( – Phrenology

Thanks - added it to the list :-) – Craunch

vbNullString is not the same as "". The difference may be important. – Satin

@Satin You are correct and that's why I didn't include it, originally. It works with this specific test - how to determine if a string is "empty". But yes, it could be an important difference in other situations. Just not when dealing with a known string (for example a string variable in VBA), as we appear to be in this question (the .text property). The InputBox (topic of the thread to which you link) is a control and can have a Null state, in contrast to a String. – Craunch

Answer 2

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.

Teens

Answer 3

1) I think you can use vbNullString to test for empty string. Otherwise use "Null" if actual string value.

2) Ensure a is declared as long

If rqRequirement.AttrValue("MyAttreName", eAttrValueLookup_Label).text <> vbNullString Then

     a = 1

End If

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

Phrenology

The if Structure is meant to be like that! I just want to check if the string is Null. and of course I won't need all 3. I just added them to show the problem that my string is at the same time both null and not null. – Impale

Answer 4

I landed here looking for an answer to "VBA: How to test if a string is Null"

while this answer may not apply to this particular users situation, it does apply to the subject question.

Dim s As String, s2 As String
s = ""
s2 = vbNullString
Debug.Print StrPtr(s) = 0, StrPtr(s2) = 0

which returns

False   True

because vbNullString is a C style NULL pointer for working with COM objects, and so its memory address when returned by the undocumented StrPtr function will always be 0

Chem

Answer 5

To ensure mutual exclusivity, ask the question only once.

a = IIf(rqRequirement.AttrValue("MyAttreName", eAttrValueLookup_Label).text = vbNullString , 2, 1)

You can also use an If-Then-Else construct, particularly if you have other actions you want to perform at the same time.

The above code example assumes the ~.text call is correct.

Phaeton