"User-defined type not defined" error when upgraded to Windows 10 and Excel 2016

[wilkisa]

Many of our users have been getting errors in Excel with macros that worked perfectly fine in all older version of Excel. Now that they are using Windows 10 and Excel 2016, the "user-defined type not defined" error comes up each time it hits a UDF statement. It is as follows:

Function uploadProdXMLDoc(doc as MSXML2.DOMDocument) asMSXML2.DOMDocument60

Dim request as XMLHTTP
Set request =New XMLHTTP
Set uploadProdXMLDoc = Nothing

With request
.Open “POST”, http://myintranet.mycompany.com/myshare/myfolder/myreportUploadAPI.aspx,False
.setRequestHeader “content-type”, “application/xml”
.setRequestHeader “user-agent”, “prod-report-excel-workbook”
.send doc.XMLDebug.Print .responseText
Set uploadProdXMLDoc = NewMSXML2.DOMDocument60
Upload ProdXMLDoc.LoadXML.responseText
End With

Set request = Nothing

End Function

Can someone please help me modify these UDFs so they will work in Excel 2016? I'm sure we need to change some of the statements but don't know what to change. The references turned on for these documents are:
Visual Basic for Applications
Microsoft Excel 16.0 Object Library
OLE Automation
Microsoft Forms 2.0 Object Library
Microsoft Office 16.0 Object Library
Microsoft XML, v6.0

Any guidance you can give will be very gratefully appreciated

 

[John_w]

Try:

    Dim request As XMLHTTP60
    Set request = New XMLHTTP60

this requires the reference to Microsoft XML, v6.0.

 

[wilkisa]

Thank you, John_w. I'll call my user when he arrives today and modify his document. I'll let you know the outcome.

 

[wilkisa]

I modified the code to be XMLHTTP60 in both places as recommended; closed and restarted Excel but it still fails at the very same place. It actually stops at the FUNCTION step and doesn't go beyond that. Do you have other ideas we should explore?

 

[Joe4]

Did you check/select the necessary reference in the Reference Library List on the computer where it is failing (in VB Editor, go to Tools -> References)?

this requires the reference to Microsoft XML, v6.0.

 

[wilkisa]

If you look at my original post, it shows the list of references already established. It shows MICROSOFT XML, v6.0. Isn't that exactly what you and John_w are saying or am I misreading something?

 

[Joe4]

If you look at my original post, it shows the list of references already established. It shows MICROSOFT XML, v6.0. Isn't that exactly what you and John_w are saying or am I misreading something?

My apologies, I missed that.

 

[wilkisa]

No worries, Joe4, at least you are trying to help. I just thought I was the one missing something. This is becoming a huge problem here at my company and no one seems to know how to update these macros for Windows 10 and Office 2016.

 

[Joe4]

One other thing to mention (and it may be nothing) .

In your original code, all your double-quotes are of the curly variety, i.e.

.Open [COLOR=#ff0000]“POST”[/COLOR],

instead of straight up-and-down like this:

.Open [COLOR=#ff0000]"[/COLOR][COLOR=#FF0000]POST"[/COLOR],

Typically, VBA does not like the curly-quotes.

Perhaps your code does not really look like that (with the curly quotes). Perhaps that is just how it was copied and pasted here.
But just wanted to mention it, in case that could be part of the problem.

 

[John_w]

i missed the fact that you have mixed types (DOMDocument and DOMDocument60) in the Function statement. Try changing it to:

Function uploadProdXMLDoc2(doc As MSXML2.DOMDocument60) As MSXML2.DOMDocument60