changing a for each loop into for loop

Question

I have the following code in VBA

Dim userBeanList As MSXML2.IXMLDOMNodeList
Dim userbean As MSXML2.IXMLDOMNode
Dim beanChild As MSXML2.IXMLDOMNode

XMLDOC.Load ("https://www.catch.api")

r = 4
Set userBeanList = XMLDOC.SelectNodes("/response/responseBody/responseList/item[recordType='TPI']")
For Each userbean In userBeanList
    For Each beanChild In userbean.ChildNodes
    If beanChild.nodeName = "catch" Then GoTo NextIteration

       Sheets("Sheet2").Cells(r, 1) = beanChild.nodeName
         Sheets("Sheet2").Cells(r, 2) = beanChild.Text

  r = r + 1
NextIteration:
    Next beanChild
Next userbean

At the moment i am looping through all of the nodes and then in this case ignoring the node called "catch" and then going into the next iteration as i dont require that node or node value. Rather than doing this how would i change my loop to one where i can just go directly to the nodes of interest and therefore not having to skip by iterations which just seems inefficient?

/////after help from parfait

Dim userBeanList As MSXML2.IXMLDOMNodeList
Dim userbean As MSXML2.IXMLDOMNode
Dim beanChild As MSXML2.IXMLDOMNode

XMLDOC.Load ("http://www.catch.api")

r = 4

Set userBeanList = XMLDOC.SelectNodes("/response/responseBody/responseList/item[recordType='TPI']/*[not(local-name)='catch']")
For Each userbean In userBeanList

       Sheets("Sheet2").Cells(r, 1) = userbean.nodeName

         Sheets("Sheet2").Cells(r, 2) = userbean.Text

         r = r + 1

Next userbean

http://stackoverflow.com/questions/24188773/xpath-to-select-all-and-exclude-child-and-its-children — Tim Williams, Mar 03 '16 at 22:56
It might be a little more efficient to say it beanChild.nodeName <> "catch" then do stuff — justkrys, Mar 03 '16 at 22:57

score 1 · Accepted Answer · answered Mar 04 '16 at 22:49

1

Consider excluding it from the xpath expression. Below specifies any child with a name() or local-name() not equal to 'catch'. This even avoids the inner For Each loop on child nodes.

Set userBeanList = XMLDOC.SelectNodes("/response/responseBody/responseList" _
                                     & "/item[recordType='TPI']/*[not(local-name)='catch']")

Alternatively, with self

Set userBeanList = XMLDOC.SelectNodes("/response/responseBody/responseList" _
                                     & "/item[recordType='TPI']/*[not(self::catch)]")

answered Mar 04 '16 at 22:49

Parfait

104,375
17
94
125

This looks really interesting, how would I change the set if I had an or , for example 'catch' or 'reprive' – Ingram Mar 04 '16 at 23:23
1

Simply add the `or` condition: `[not(self::catch or self::reprive)]` – Parfait Mar 05 '16 at 00:24

changing a for each loop into for loop

1 Answers1