I have written a function in VBA (access) that should parse some XML and then return the information in a two column Array. Now it seems to be working but the array it is returning seems to be in a different format to what I am used to and since it is to be used as input for another function (which i have not written) it is not desired. The code reads like this (added ratepairs in there for now but that will be taken from somewhere else in the future) :

Public Function YCRatepairs2()

Dim ratepairs1 As String

ratepairs1 = "<Tenors><Tenor name=""1Y SW"" value=""1.25"" /><Tenor name=""2Y SW"" value=""1.25"" /><Tenor name=""3Y SW"" value=""1.25"" /><Tenor name=""4Y SW"" value=""1.25"" /></Tenors>"

Dim xmlDoc As MSXML2.DOMDocument60
Dim xmlNodeList As MSXML2.IXMLDOMNodeList
Dim xmlNode As MSXML2.IXMLDOMNode

Dim myArray() As Variant

Set xmlDoc = New DOMDocument60

If (xmlDoc.loadXML(ratepairs1)) Then

Set xmlNodeList = xmlDoc.selectNodes("//Tenors/Tenor")
ReDim myArray(xmlNodeList.Length) As Variant

Dim cnt As Integer
cnt = 0
For Each xmlNode In xmlNodeList
myArray(cnt) = Array(xmlNode.Attributes(0).nodeValue, xmlNode.Attributes(1).nodeValue)
cnt = cnt + 1

Next

End If

YCRatepairs2 = myArray


End Function


Now what is strange is that when i add another line (msgbox in this case) to the code to view some data in the array I suddenly have to do this differently. Normally I would do this like this:

MsgBox (myArray(0,0))

However this gives me an error message saying "subscript out of range". When I chang it to this: MsgBox (myArray(0)(0)) it does work.

I have no clue as to why this is the case and moreover I need the first notation to work. Any ideas would be more then welcome.

Looks like you are putting a one-dimensional array into every index of a one-dimensional array, here:
This is what you would call nested arrays. myArray is never more than a one-dimensional array. So in your example, myArray(0) contains a one-dimensional array with Array(0)="1Y SW" and Array(1)="1.25".

You should use the following for the Redim:
This assumes that there will only be two attributes used from each node.

You would then use the following in the For loop:
Looks like your first dimension will be one more than the total number of elements in the array though, so you might want to use xmlNodeList.Length-1 instead.

Ax

Thanks Ax, this works really well! I do however run into a new problem which is that the first column of my array is a string while the second should be a Double. But now it sees both as a string. Any ideas on how to fix that as well? Many thanks.

I'm not sure about Access VBA specifically, but in general EVERYTHING in xml is a string. It's up to you to take care of type casting/conversion.