Why is my VBA function returning 0?

Question

Consider the following function in VBA (note that it's been edited based on the answers given so now it's working - see the edit history for the original version):

Function DisplayNode(ByRef Nodes)

    Dim xNode As Object
    For Each xNode In Nodes

        If xNode.nodeName = "LastTradePriceOnly" Then
            DisplayNode = xNode.Text
            Exit Function
        End If
        If xNode.HasChildNodes Then
            DisplayNode = DisplayNode (xNode.ChildNodes)
        End If

   Next xNode

End Function

This function parses an XML response and returns the value of one of the nodes, LastTradePriceOnly.

The Nodes argument is a MSXML.DOMDocument object. When I use msgBox to print the value of xNode.text the value I am expecting is output. However, when I call this function from another function, 0 is returned.

Any thoughts on why I might be returning 0?

Answer 1

You forgot to actually return the value from your recursive call:

DisplayNode = DisplayNode (xNode.ChildNodes)

Been there :)

Answer 2

Alains's answer gets at the root of your problem but doesn't go all the way.

Perhaps you need to recall that a line like DisplayNode = xNode.Text just tells VBA what to return upon function exit, but doesn't actually exit the function. VBA will happily let you assign and reassign the return value, and will just return whatever you told it to last.

Also, if you never assign a value to the function, VBA will assume you wanted to return the default value of whatever the type of the function is. In your case, your function is of type Variant, and the default value of Variant is Empty. You might be seeing this as zero, because VBA implicitly converts Empty to zero in all kinds of situations

With that in mind, consider what happens in your example if any of these are true:

1) your Nodes parameter doesn't have anything in it (i.e. it's an empty array or Collection or whatever it is in MSXML)

2) there are some nodes, but none of them meet either of the tests in your loop

3) there are some nodes, and the last node satisfies (1) or (2) above

You should be able to see that your function will return Empty, even if it found one or more "LastTradePriceOnly" nodes on it's way through your tree of nodes. And then you would see that as zero when you display it in a message box.

It sounds like you really want a more specific routine - maybe something that finds a specific node in a tree and raises an error if it can't do so. I guess it depends on your data, but this explanation should address your immediate question about why you could be seeing zero in your message box.