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

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
647
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:

HTML:
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
 
Last edited:

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,066
Try:
Code:
    Dim request As XMLHTTP60
    Set request = New XMLHTTP60
this requires the reference to Microsoft XML, v6.0.
 

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
647
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

Well-known Member
Joined
Apr 7, 2002
Messages
647
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,799
Office Version
365
Platform
Windows
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.
 
Last edited:

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
647
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,799
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Apr 7, 2002
Messages
647
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,799
Office Version
365
Platform
Windows
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.
Code:
.Open [COLOR=#ff0000]“POST”[/COLOR],
instead of straight up-and-down like this:
Code:
.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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,066
i missed the fact that you have mixed types (DOMDocument and DOMDocument60) in the Function statement. Try changing it to:

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

Forum statistics

Threads
1,085,844
Messages
5,386,303
Members
401,992
Latest member
CleverHopper

Some videos you may like

This Week's Hot Topics

Top