Application-defined or object-defined error in Excel VBA

Question

I'm getting said error in using VBA in Excel on the following code:

Private Sub XMLGen(mapRangeA, mapRangeB, ticketSize, mapping)
    Dim fieldOneArr As Variant
    Dim fieldTwoArr As Variant
    Dim row As Long
    Dim column As Long
    Dim infoCol As Long
    Dim endInfo As Long
    Dim objDom As DOMDocument
    Dim objNode As IXMLDOMNode
    Dim objXMLRootelement As IXMLDOMElement
    Dim objXMLelement As IXMLDOMElement
    Dim objXMLattr As IXMLDOMAttribute
    Set ws = Worksheets("StockData")
    Dim wsName As String

    Set objDom = New DOMDocument

    If ticketSize = 8 Then
        wsName = "A7Tickets"
    ElseIf ticketSize = 16 Then
        wsName = "A8Tickets"
    Else
        wsName = "A5Tickets"
    End If

    Set ps = Worksheets(wsName)

   'create processing instruction
   Set objNode = objDom.createProcessingInstruction("xml", "version='1.0' encoding='UTF-8'")
   objDom.appendChild objNode

   'create root element
   Set objXMLRootelement = objDom.createElement("fields")
   objDom.appendChild objXMLRootelement

   'create Attribute to the Field Element and set value
   Set objXMLattr = objDom.createAttribute("xmlns:xfdf")
   objXMLattr.NodeValue = "http://ns.adobe.com/xfdf-transition/"
   objXMLRootelement.setAttributeNode objXMLattr

   infoCol = 1
   fieldOneArr = Worksheets(mapping).range(mapRangeA)
   fieldTwoArr = Worksheets(mapping).range(mapRangeB)
   For row = 1 To UBound(fieldOneArr, 1)
        For column = 1 To UBound(fieldOneArr, 2)
            'create Heading element
            Set objXMLelement = objDom.createElement(fieldOneArr(row, column))
            objXMLRootelement.appendChild objXMLelement

            'create Attribute to the Heading Element and set value
            Set objXMLattr = objDom.createAttribute("xfdf:original")
            objXMLattr.NodeValue = (fieldTwoArr(row, column))
            objXMLelement.setAttributeNode objXMLattr
            objXMLelement.Text = ps.Cells(row, infoCol)
            infoCol = infoCol + 1
            endInfo = endInfo + 1
            If endInfo = 4 Then
                infoCol = 1
            End If
        Next column
    Next row

    'save XML data to a file
    If ticketSize = 2 Then
        objDom.Save ("C:\ExportTestA5.xml")
        MsgBox "A5 XML created"
    ElseIf ticketSize = 8 Then
        objDom.Save ("C:\ExportTestA7.xml")
        MsgBox "A7 XML created"
    Else
        objDom.Save ("C:\ExportTestA8.xml")
        MsgBox "A8 XML created"
    End If

End Sub

When I hit debug it points to this line:

fieldOneArr = Worksheets(mapping).range(mapRangeA)

I know that .Range is supposed to be upper case but it keeps on setting it to lower case automatically whenever I correct it.

This code is meant to create an XML file and then write the details from the chosen worksheet (based on the ticketSize variable) into the correct XML fields. Hence I have a mapping worksheet from which I write the field and attribute names, and then write in the info from the correct ticket size worksheet into the text property of the element.

You haven't defined the type of mapRangeA in your function, which would help in isolating the error. We need to know what you're supplying to the function, as the error will be generated if the parameter of range is invalid. — grahamj42
– grahamj42, Commented Mar 16, 2013 at 13:35
Oh, that helped. In my Sub which creates and fills the mapRange variables (of type String) one of my If statements wasn't assigning a value to mapRangeA or mapRangeB. Thanks! — user2151763
– user2151763, Commented Mar 16, 2013 at 13:41

grahamj42 · Accepted Answer · 2013-03-16 13:51:21Z

2

You should define the types of your function parameters, in this case mapRangeA As String. Office object methods and properties are often not very helpful with their error messages, so it's better to have a type mismatch error if you have a problem with a parameter.

answered Mar 16, 2013 at 13:51

grahamj42

2,7623 gold badges29 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Application-defined or object-defined error in Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related