VBA If <object> Is Nothing

Question

I want to test an object to see if it doesn't exist. If it does not exist, I just want to have a MsgBox show up (or write Error in cell A1 or something). Banana does not exist in this XML.

<?xml version="1.0"?>
<catalog>
<book id="Adventure">
   <author>Gambardella, Matthew</author>
   <title>XML Developer's Guide</title>
   <price>44.95</price>
</book>
<book id="Adventure">
   <author>Ralls, Kim</author>
   <title>Midnight Rain</title>
   <price>5.95</price>
</book>
<book id="Adventure">
   <author>Boal, John</author>
   <title>Mist</title>
   <price>15.95</price>
</book>
<book id="Mystery">
   <author>Ralls, Kim</author>
   <title>Some Mystery Book</title>
   <price>9.95</price>
</book>
</catalog>

The test code:

Sub mySub()

Dim XMLFile As Variant
Dim Author As Object
Dim athr As String, BookType As String, Title As String, StoreLocation As String
Dim AuthorArray() As String, BookTypeArray() As String, TitleArray() As String, StoreLocationArray() As String
Dim i As Long, x As Long, j As Long, pn As Object, loc As Object, arr, ln As String, loc2 As Object

Dim mainWorkBook As Workbook
Dim n As IXMLDOMNode
Set mainWorkBook = ActiveWorkbook
Set XMLFile = CreateObject("Microsoft.XMLDOM")
XMLFile.Load ("C:\BooksOriginal.xml")

x = 1
j = 0


Set Author = XMLFile.SelectNodes("/catalog/book/banana")

If Author Is Nothing Then
    MsgBox ("Not Found")
    Range("A1").Value = "Not found"
End If

If Not Author Is Nothing Then
    For i = 0 To (Author.Length - 1)

    athr = Author(i).Text

    If athr = "Ralls, Kim" Then

        Set pn = Author(i).ParentNode
        BookType = pn.getAttribute("id")
        Title = pn.getElementsByTagName("title").Item(0).nodeTypedValue

        AddValue AuthorArray, athr
        AddValue BookTypeArray, BookType
        AddValue TitleArray, Title
        AddValue StoreLocationArray, StoreLocation

        j = j + 1
        x = x + 1
    End If
Next
    Range("A3").Resize(j, 1).Value = WorksheetFunction.Transpose(AuthorArray)
    Range("B3").Resize(j, 1).Value = WorksheetFunction.Transpose(BookTypeArray)
End If

End Sub

'Utility method - resize an array as needed, and add a new value
Sub AddValue(arr, v)
    Dim i As Long
    i = -1
    On Error Resume Next
    i = UBound(arr) + 1
    On Error GoTo 0
    If i = -1 Then i = 0
    ReDim Preserve arr(0 To i)
    arr(i) = v
End Sub

Why does this block not do anything? I feel like it's being completely overlooked at by VBA. I even tried putting an End in the If statement.

If Author Is Nothing Then
    MsgBox ("Not Found")
    Range("A1").Value = "Not found"
    End
End If

Also, the error is also thrown at the print range line.. which is in the If Not Author Is Nothing statement. Very strange.

Answer 1

The reason your loop is still executing is simply that If Author Is Nothing evaluates as true. The call to XMLFile.SelectNodes returns an IXMLDOMNodeList, which is an enumerable container. In fact, the reason that it can be used with For Each syntax depends on this. In general, any enumeration returned by a function will give you an enumerable with no items in it rather than a null object. The For Each syntax is equivalent to doing this:

Do While Author.NextNode()
    '...
Loop

...or...

For i = 0 To (Author.Length - 1)
    '...
Next i

For Each just has the benefit of being more readable.

The error you get actually isn't related to the question you're asking, and correcting the check on the return value of XMLFile.SelectNodes("/catalog/book/banana") won't solve the error if you don't get any results. The error lies in trying to use your arrays after the loop if they aren't instantiated (although the added End would have solved that).

When you exit the loop and get here...

Range("A3").Resize(j, 1).Value = WorksheetFunction.Transpose(AuthorArray)
Range("B3").Resize(j, 1).Value = WorksheetFunction.Transpose(BookTypeArray)

...your AuthorArray and BookTypeArray have only been initialized if you've been through the loop, because you are relying on the ReDim Preserve in the Sub AddValue to initialize them. This has 2 solutions. You can either put an Exit Sub in your test of the return value:

If Author.Length = 0 Then
    MsgBox ("Not Found")
    Range("A1").Value = "Not found"
    Exit Sub
End If

Or you can initialize the arrays at the start of the function.

AuthorArray = Split(vbNullString)
BookTypeArray = Split(vbNullString)

This also has the added benefit of allowing you to skip all of the hoops in your array resizing to determine if they have been initialized. Split(vbNullString) will return an array with a UBound of -1 (MyVariantArray = Array() will do the same for arrays of Variant). That allows you to rewrite Sub AddValue like this:

Sub AddValue(arr, v)
    ReDim Preserve arr(UBound(arr) + 1)
    arr(UBound(arr)) = v
End Sub

Finally, I'd take @SOofXWLS's suggestion and @barrowc's suggestions and use explicit object types since you are late binding. That way your IntelliSense will show auto-complete lists at very least. If you don't know what types of objects are returned, just hit F2 for the Object Browser and check:

If you don't know where to even start with a new object model, you can also use this quick and dirty trick...

Dim XMLFile As Object
Set XMLFile = CreateObject("Microsoft.XMLDOM")
Debug.Print TypeName(XMLFile)   'DOMDocument

... and then...

Dim XMLFile As DOMDocument