Excel VBA编译时抛出 "未定义的用户定义类型 "的错误，但没有转到违规的代码行。

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.

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 occuring 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.

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 mispelt control name on a form being referenced in it's 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?

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!