VBA – Get Substring Between 2 Substrings – Locate value of Nth XML

Getting N-th string between two substrings might sound a bad idea, until you do not need it. And with VBA, you better have something prepared.

The N-th value in XML.

So, imagine the following XML (from this article here):

Your task is to get the first <FirstName> and the second <FirstName>. As this is VBA, the libraries are a scarce resource, thus you have to code it yourself. Like this:

As you see, the repetition parameter is the one, on which we give the N-th wanted value. E.g., in the example above, we get 1889, if we ask for the second time the substring <ClubEstablishedOn> is found, by writing 2 as repetition parameter. The optional parameter, ending the XML is </ , but it might be changed, if needed.

This is how the function looks like:

That’s all, folks. No explanation of the code. Just added it to the VBA_personal boilerplate. Enjoy!

Tagged with: , , , , , , , ,