Why is my VBA function returning 0?

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 ret开发者_StackOverflowurns 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?

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

DisplayNode = DisplayNode (xNode.ChildNodes)

Been there :)

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.

Based on your code, you should change this:

Function DisplayNode(ByRef Nodes)

to this:

Function DisplayNode(Nodes as MSXML2.DOMDocument) as String 

ByRef happens by default in VBA so you don't have to include it unless you want to. Also, not declaring variables or functions defaults them to Variants. Doing this is generally considered a bad coding practice plus you don't get the advantage of IntelliSense.

You might also want to change your declaration of xNode. I haven't worked with DOMDocument before, but if there's more than one different type of object, then the FOR EACH statement will loop through them all; looks like you may only want to loop through one kind of object.

Finally, could the property you want to use be nodeValue and not Text?

When I look in the object browser, the definition for nodeValue is value stored in the node.

However, text is text content of the node and subtree.