Results 1 to 4 of 4

Thread: Array problem

  1. #1
    Join Date
    Oct 2003

    Red face Unanswered: Array problem

    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


    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.
    Last edited by Kabuki_jo; 11-03-09 at 12:24. Reason: forgot something
    Nobody dies a virgin because life screws us all!

  2. #2
    Join Date
    May 2009
    Looks like you are putting a one-dimensional array into every index of a one-dimensional array, here:
    myArray(cnt) = Array(xmlNode.Attributes(0).nodeValue, xmlNode.Attributes(1).nodeValue)
    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:
    ReDim myArray(xmlNodeList.Length, 1) As Variant
    This assumes that there will only be two attributes used from each node.

    You would then use the following in the For loop:
    myArray(cnt,0) = xmlNode.Attributes(0).nodeValue
    myArray(cnt,1) = xmlNode.Attributes(1).nodeValue
    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.


  3. #3
    Join Date
    Oct 2003
    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.
    Nobody dies a virgin because life screws us all!

  4. #4
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts