Why is my VBA function returning 0?

Question

Consider the following function in VBA (note that it has been edited based on the answers given so that it now works - see the changelog 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 the XML response and returns the value of one of the nodes, LastTradePriceOnly .

The Nodes argument is an MSXML.DOMDocument object. When I use msgBox to print the value of xNode.text , the value that I expect is displayed. However, when I call this function from another function, 0 is returned.

Any thoughts on why I can return 0?

Answer 1

The Alanian answer is at the root of your problem, but does not go completely.

Perhaps you need to remember that a line of type DisplayNode = xNode.Text simply tells VBA what to return after the function exits, but actually does not exit the function. VBA will happily allow you to assign and reassign the return value and simply return everything you told him to last.

In addition, if you never assign a value to a function, VBA assumes that you want to return the default value regardless of the type of function. In your case, your function is of type Variant , and the default value of Variant is Empty . You can see this as zero because VBA implicitly converts Empty to zero in all situations.

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

1) your Nodes parameter has nothing in it (i.e. it is an empty array or collection, or something else in MSXML)

2) there are some nodes, but none of them answer any 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 one or more LastTradePriceOnly nodes are found on it through your node tree. And then you will see it as zero when you display it in the message box.

It sounds like you really need a more specific subroutine - maybe something that finds a particular node in the tree and causes an error if it cannot do this. I think it depends on your data, but in this explanation your closest question should be considered as to why you can see zero in your message box.

Answer 2

You forgot to return the value from your recursive call:

DisplayNode = DisplayNode (xNode.ChildNodes)

Was there:)

Answer 3

Based on your code, you should change this:

 Function DisplayNode(ByRef Nodes) 

:

 Function DisplayNode(Nodes as MSXML2.DOMDocument) as String 

ByRef is used by default in VBA, so you do not need to enable it if you do not want it. In addition, without declaring variables or functions, by default they correspond to variants. This is usually considered bad coding practice, plus you do not get the benefits of IntelliSense.

You can also change the xNode declaration. I didn’t work with DOMDocument before, but if there are more than one type of object, then the FOR EACH statement will go through all of them; it looks like you can only request one object.

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

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

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