Compile throws a "User-defined type not defined" error but does not go to the offending line of code

My solution is not good news but at least it should work.

My case is: I have a .xlsm from a coworker. 1) I open it and click the button: it works fine. 2) I save the file, close excel, open the file again: now it doesn't work anymore. The conclusion is: the code is OK but excel can't manage references correctly. (I've tried removing the re-adding the references without any success)

So the solution is to declare every referenced object as Variant and use CreateObject("Foo.Bar") instead of New Foo.Bar.

For example:

Dim objXML As MSXML2.DOMDocumentSet objXML = New MSXML2.DOMDocument

Replaced by:

Dim objXML As VariantSet objXML = CreateObject("MSXML2.DOMDocument")

I had exactly the same problem (always seems to occur when I try to implement a Interface onto a userform. Download and install Code Cleaner from here. This is a freeware utility that has saved me on numerous occasions. With your VBA project open, run the "Clean Code..." option. Make sure you check the "backup project" and/or "export all code modules" to safe locations before running the clean. As far as I understand it, this utility exports and then re-imports all modules and classes, which eliminates compiler errors that have crept into the code. Worked like a charm for me! Good luck.

Since it sounds like you've tried many different potentional solutions, you'll probably have to do this the long methodical way now.

Create a new blank workbook. Then piece by piece copy your old workbook into it. Add a reference, write a little bit of code to test it. Ensure it compiles, ensure it runs. Add a sub or function, again, write a little test sub to run it, also ensure it compiles. Repeat this process slowly adding and testing everything.

You can speed this up a bit by first trying larger chunks, then when you find one that triggers the problem, remove it and break it into smaller peices for testing.

Either you will find the offender, or you'll have a new workbook that magically does not have the problem. The latter would be due to some sort of hidden corruption in the workbook file, probably in the binary vbproject part.

Welcome to the world of debugging without debuggers or other helpful tools to do the heavy lifting for you!