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

Question

Symptoms

This is a symptom specifically when compiling an Excel VBA project. The following error occurs:

User-defined type not defined

However, the code that produces this error is not highlighted by the compiler and so I cannot identify the issue.

What I already know and have tried

This is a "User-defined type not defined" error that I have seen before with simple issues such as naming something As Strig instead of As String. However, this particular error is only popping up during the Debug > Compile VBAProject menu option and when the error message box pops up it does not highlight the line of code that the error is occurring in.

After a lot of research I have found that this bug can be related to missing references and I have ruled this out as I have included all needed references and Toolbox objects.

To ensure I wasn't missing any obvious missing Dim statements I have added Option Explicit to all code pages (forms included) to make sure nothing was missing. The error still shows when running a compile.

There is also this known bug that states the issue has been known to happen because of the VB6 projects using binary compatibility:

Turn off Binary Compatibility and compile the project. Visual Basic will highlight the line of code that contains the User Defined Type that is not defined. After resolving the problem, Binary Compatibility can be turned back on.

I found this article via this Question and Answer, however, I cannot find this option in the standard Excel VBA editor.

Help save mine and others' sanity!

I know from Google searches and other questions that I am not the only one who has had this issue.

I have tried going through the code manually but there are simply too many lines to feasibly do so.

Is there a way of turning off Binary Compatibility in Excel VBA projects? How do people find this offending line of code if they can't debug to what they need to change? Any help would be lovely!

Thank you in advance.

Edit: I have found the offending line of code and so my particular issue is solved The problem is still here after removing that particular line - it was a misspelt control name on a form being referenced in its code. This still does not solve the particular issue of how you would go about finding this offending code was the issue. Are we able to find a good way of finding the offending code when this bug happens so others in the future can avoid this agony?

Answer 1

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.DOMDocument
Set objXML = New MSXML2.DOMDocument

Replaced by:

Dim objXML As Variant
Set objXML = CreateObject("MSXML2.DOMDocument")

Answer 2

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.

Answer 3

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!