Declaring early bound MSXML object throws an error in VBA

Question

I am trying to import xml data into excel.. So the first line of the code is

Dim XMLDOC As MSXML2.DOMDocument

and this gives an error "user defined type not defined"

In addition to what [Scott](http://stackoverflow.com/a/11245915/190829) suggested, you could `Dim ... As Object` and avoid the early binding issue. — JimmyPena, Jul 05 '12 at 18:05

score 51 · Answer 1 · answered Jun 28 '12 at 13:36

51

Inside the VBE, Go to Tools -> References, then Select Microsoft XML, v6.0 (or whatever your latest is. This will give you access to the XML Object Library.

Updated with fancy pic!

enter image description here

answered Jun 28 '12 at 13:36

Scott Holtzman

27,099
5
37
72

1

Thank you! This was driving me mad. And the fancy pic seals the deal. – RossC Sep 02 '13 at 08:05
damn still using windows xp :| – user734028 Mar 05 '14 at 20:33
7

As a side note, if you are on Windows 8, you may need to reference Microsoft XML v3 because v6 does not expose DOMDocument class. See thread http://social.technet.microsoft.com/Forums/en-US/f9180e5c-7c2f-44be-8415-d0d2b76e3f7f/microsoft-xml-v60-msxml6dll-and-vba – Marcel Gosselin Oct 03 '14 at 15:38
8

As a side note to the side note from @marcel-gosselin, you can still use v6 if you `dim` your variable as `MSXML2.DOMDocument60` (note the 60 at the end) – BlazingFrog Nov 13 '14 at 03:21
3

Nice find @MarcelGosselin. Also solves my problem on Windows Server 2012. – joehanna Jun 12 '15 at 05:14
wait - no freehand red circles? ;P – SierraOscar Mar 24 '16 at 19:26

score 38 · Answer 2 · edited Aug 07 '19 at 16:50

38

I had DOMDocument defined which needed Microsoft XML, v3.0 but I had Microsoft XML, v6.0 selected in references which caused the below error

"user defined type not defined".

The solution

The solution was to either change DOMDocument to DOMDocument60 (60 uses ver 6.0) or use the Microsoft XML, v3.0 reference with DomDocument.

Just a quick note, if anyone is using a different version, such as Microsoft XML, v4.0, then DOMDocument40 should be used. This is because the number at the end of the DOMDocument is specific to the version of the library being used.

edited Aug 07 '19 at 16:50

Dan

7,286
6
49
114

answered Apr 01 '16 at 11:19

Joshua Duxbury

4,892
4
32
51

2

I found that anytime users had to reboot excel, if `DOMDocument` is used in Windows10, the MS XML, v3.0 selected would go back to v6.0. The better route is to use `DOMDocument60` IMHO. – CarloC Sep 08 '17 at 17:28
Version 6.0 is the latest version, 3.0 the last stable version (loaded by default if no version number indicated); vers. 4.0 definitively cannot be recommended anymore in current applications. – T.M. Aug 07 '19 at 18:07

score 4 · Answer 3 · answered Sep 26 '17 at 15:15

I work with a VBA Excel Macro that someone else wrote and I was tasked with fixing it after recently upgrading from Windows 7 / Office 2010 to Windows 10 / Office 2016. I started to receive the same "user defined type not defined" compile error. My previous install also had MS XML v6.0 but apparently you have to specifically point to this version in your code on Windows 10 and/or Office 2016 (I wasn't able to confirm which upgrade caused the issue). I was able to resolve the issue by doing a Find/Replace on the following:

"DOMDocument" to "MSXML2.DOMDocument60"
"XMLHTTP" to "MSXML2.XMLHTTP60"

score 3 · Answer 4 · answered Jun 08 '18 at 22:30

3

I am using Microsoft Windows 10 & Office 2016.

Using Microsoft XML 6.0 does not fix the problem.

Selecting Microsoft XML 3.0 fixed the compilation error

answered Jun 08 '18 at 22:30

Abdelhameed Mahmoud

2,118
2
22
17

I had a unique situation where my program correctly parsed the XML from one URL and failed when extracting from another (completely different) URL when using version 6. Reversing to version 3 made it work with both URLs. I changed all instances of `DOMDocument60` to `DOMDocument30`. I closed and reopened Excel and did not have the issue described above where the reference reverts to version 6. (Using W10 and Office 2016). – MBB70 Mar 13 '20 at 14:11

score 0 · Answer 5 · answered May 15 '20 at 10:19

0

I had the 3rd and 6th versions installed, and the project uses the 4th one. I installed the 4th version from https://www.microsoft.com/en-us/download/details.aspx?id=15697 and this solved the problem.

answered May 15 '20 at 10:19

yradov

1
1

Declaring early bound MSXML object throws an error in VBA

5 Answers5