Excel VBA 编译会引发“未定义用户定义的类型”错误，但不会转到有问题的代码行

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

提问by Tom 'Blue' Piddock

Symptoms

症状

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

这是编译Excel VBA 项目时的一种症状。出现以下错误：

User-defined type not defined

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 Striginstead of As String. However, this particular error is only popping up during the Debug > Compile VBAProjectmenu option and when the error message box pops up it does not highlight the line of code that the error is occurring in.

这是一个“用户定义的类型未定义”错误，我以前见过它有一些简单的问题，例如命名某些东西As Strig而不是As String. 但是，此特定错误仅在Debug > Compile VBAProject菜单选项期间弹出，并且当错误消息框弹出时，它不会突出显示发生错误的代码行。

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.

经过大量研究，我发现此错误可能与缺少引用有关，因此我排除了这一点，因为我已包含所有需要的引用和 Toolbox 对象。

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

为了确保我没有遗漏任何明显遗漏的Dim语句，我已添加Option Explicit到所有代码页（包括表单）中以确保没有遗漏任何内容。运行编译时仍然显示错误。

There is also this known bugthat states the issue has been known to happen because of the VB6projects using binary compatibility:

还有一个已知的错误表明该问题是由于使用二进制兼容性的VB6项目而发生的：

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.

关闭二进制兼容性并编译项目。Visual Basic 将突出显示包含未定义的用户定义类型的代码行。解决问题后，可以重新打开二进制兼容性。

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

我通过这个问答找到了这篇文章，但是，我在标准 Excel VBA编辑器中找不到这个选项。

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!

有没有办法关闭 Excel VBA 项目中的二进制兼容性？如果人们无法调试到需要更改的内容，他们如何找到这行有问题的代码？任何帮助都会很可爱！

Thank you in advance.

先感谢您。

Edit:I have found the offending line of code and so my particular issue is solvedThe 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?

编辑：我找到了有问题的代码行，所以我的特定问题解决了删除该特定行后问题仍然存在 - 它是其代码中引用的表单上的拼写错误的控件名称。这仍然不能解决您将如何找到这个有问题的代码的特定问题。当这个错误发生时，我们是否能够找到一种好的方法来找到有问题的代码，以便将来的其他人可以避免这种痛苦？

回答by jng

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)

我的情况是：我有一个同事的 .xlsm。1）我打开它并单击按钮：它工作正常。2）我保存文件，关闭excel，再次打开文件：现在它不再工作了。结论是：代码没问题但是excel不能正确管理引用。（我尝试删除重新添加引用但没有成功）

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

所以解决方案是将每个引用的对象声明为Variant并使用CreateObject("Foo.Bar")而不是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")

回答by AndASM

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.

要么您会找到罪犯，要么您将拥有一本神奇地没有问题的新工作簿。后者是由于工作簿文件中的某种隐藏损坏，可能是在二进制 vbproject 部分。

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

欢迎来到没有调试器或其他有用工具的调试世界来为您完成繁重的工作！

回答by Cameron Bradley

Just letting you all know I had this problem too. Rather than the code, the issue lay with what macro a button was calling. (It had been calling the 'createroutes.createroutes' macro, but I had renamed the 'createroutes' module to 'routes'.) Therefore the problem was fixed by pointing the button to the correct location.

只是让大家知道我也有这个问题。问题在于按钮调用了什么宏，而不是代码。（它一直在调用“createroutes.createroutes”宏，但我已将“createroutes”模块重命名为“routes”。）因此，通过将按钮指向正确的位置来解决问题。

回答by user3803315

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.

我遇到了完全相同的问题（当我尝试在用户窗体上实现接口时似乎总是发生。从这里下载并安装代码清理器。这是一个免费软件实用程序，它在很多情况下都救了我。打开你的 VBA 项目，运行该"Clean Code..."选项。请务必检查的“备份项目”和/或运行清洁之前“导出所有代码模块”到安全地点。据我了解，这个工具的出口，然后再进口的所有模块和类，消除了潜入代码中的编译器错误。对我来说就像一个魅力！祝你好运。

回答by Rob Bishop

Had a similiar experience, but it was because I had renamed an enum in one of my classes. I exported and re-imported the Classes that had referred to the old enum and the error message disappeared. This suggests it is a caching issue in the VBA environment.

有类似的经历，但那是因为我在我的一个课程中重命名了一个枚举。我导出并重新导入了引用旧枚举的类，错误消息消失了。这表明这是 VBA 环境中的缓存问题。

回答by C Perkins

I was able to fix the error by

我能够通过以下方式修复错误

1. Completely closing Access
2. Renaming the database file
3. Opening the renamed database file in Access.
4. Accepted various security warnings and prompts.
   • Not only did I choose to Enable Macros, but also accepted to make the renamed database a Trusted Document.
   • The previous file had also been marked as a Trusted Document.
5. Successfully compile the VBA project without error, no changes to code.
6. After the successful compile, I was able to close Access again, rename it back to the original filename. I had to reply to the same security prompts, but once I opened the VBA project it still compiled without error.

1. 完全关闭访问
2. 重命名数据库文件
3. 在 Access 中打开重命名的数据库文件。
4. 接受各种安全警告和提示。
   • 我不仅选择启用宏，而且还接受将重命名的数据库设为可信文档。
   • 之前的文件也被标记为可信文档。
5. 成功编译 VBA 项目，没有错误，代码没有变化。
6. 成功编译后，我可以再次关闭 Access，将其重命名为原始文件名。我不得不回复相同的安全提示，但是一旦我打开 VBA 项目，它仍然编译没有错误。

A little history of this case and observations:

这个案例的一点历史和观察：

• I'm posting this answer because my observed symptoms were a little different than others and/or my solution seems unique.
• At least during part of the time I experienced the error, my VBA window was showing two extra, "mysterious" projects. Regrettably I did not record the names before I resolved the error. One was something like ACXTOOLS. The modules inside could not be opened.
• I think the original problem was indeed due to bad code since I had made major changes to a form before attempting to update its module code. But even after fixing the code the error persisted. I knew the code worked, because the form would load and no errors. As the original post states, the “User-defined type not defined” error would appear but it would not go to any offending line of code.
• Prior to finding this error, I ensured all necessary references were added. I compacted and repaired the database more than once. I closed down Access and reopened the file numerous times between various fix attempts. I removed the suspected offending form, but still got the error. I tried other various steps suggested here and on other forums, but nothing fix the problem.
• I stumbled upon this fix when I made a backup copy for attempting drastic measures, like deleting one form/module at a time. But upon opening the backup copy, the problem did not reoccur.

• 我发布此答案是因为我观察到的症状与其他症状略有不同和/或我的解决方案似乎很独特。
• 至少在我遇到错误的部分时间里，我的 VBA 窗口显示了两个额外的“神秘”项目。遗憾的是我在解决错误之前没有记录名称。一种是类似于 ACXTOOLS 的东西。里面的模块打不开。
• 我认为最初的问题确实是由于错误的代码造成的，因为我在尝试更新其模块代码之前对表单进行了重大更改。但即使在修复代码后，错误仍然存​​在。我知道代码有效，因为表单会加载并且没有错误。正如原始帖子所述，会出现“未定义用户定义的类型”错误，但不会转到任何有问题的代码行。
• 在发现此错误之前，我确保添加了所有必要的引用。我不止一次地压缩和修复了数据库。在各种修复尝试之间，我关闭了 Access 并多次重新打开该文件。我删除了可疑的违规表格，但仍然出现错误。我尝试了此处和其他论坛上建议的其他各种步骤，但都无法解决问题。
• 当我制作备份副本以尝试采取严厉措施（例如一次删除一个表单/模块）时，我偶然发现了此修复程序。但是打开备份副本后，问题没有再次出现。

回答by RexBarker

For the Scripting.Dictionary type, you can either use late binding (as already pointed out ) with:

对于 Scripting.Dictionary 类型，您可以使用后期绑定（正如已经指出的那样）：

Dim Dict as Object
Set Dict = CreateObject("Scripting.Dictionary")

Which works, but you don't get the code auto completion. Or you use early binding, but you need to make sure that VBA can find the Scripting.Dictionary type by adding the reference to the Microsoft Scripting Library via VBA-->Tools-->References--> "Microsoft Scripting Runtime". Then you can use:

哪个有效，但你没有得到代码自动完成。或者您使用早期绑定，但您需要通过VBA-->工具-->引用-->“Microsoft Scripting Runtime”添加对Microsoft Scripting Library的引用，以确保VBA可以找到Scripting.Dictionary类型。然后你可以使用：

Dim Dict as Scripting.Dictionary
Set Dict = New Scripting.Dictionary

... and auto completion will work.

...并且自动完成将起作用。

回答by Nathalii.

Possible solution, you are trying to work with Powerpoint via Excel VBA and you didn't activate Powerpoint Object Library first.

可能的解决方案，您正在尝试通过 Excel VBA 使用 Powerpoint，并且您没有先激活 Powerpoint 对象库。

To do this, in the VBA editor top menu select Tools, References, then scroll down to click the library called Microsoft Powerpoint xx.x Object Library. Office 2007 is library 12, each Office version has a different library. FYI, I've experienced some odd errors and file corruption when I activate the 2007 library but someone tries to open and run this macro using Excel 2003. The old version of Excel doesn't recognize the newer library, which seems to cause problems.

为此，在 VBA 编辑器的顶部菜单中选择工具、引用，然后向下滚动以单击名为 Microsoft Powerpoint xx.x 对象库的库。Office 2007 是库 12，每个 Office 版本都有不同的库。仅供参考，我在激活 2007 库时遇到了一些奇怪的错误和文件损坏，但有人尝试使用 Excel 2003 打开并运行此宏。旧版本的 Excel 无法识别较新的库，这似乎会导致问题。

回答by Schalton

I know this is old, but I had a similar problem and found a fix:

我知道这很旧，但我遇到了类似的问题并找到了解决方法：

I had the same issue with a module I ported from Excel into Access, in an unrelated UDF I was dimming 'As Range' but ranges don't exist in Access. You may be using a variable type without having the proper reference library turned on.

我从 Excel 移植到 Access 的模块遇到了同样的问题，在一个不相关的 UDF 中，我将“作为范围”调暗，但 Access 中不存在范围。您可能正在使用变量类型而没有打开正确的参考库。

If you have any non-standard dims google them and see if you're missing the reference to that library under tools.

如果您有任何非标准的暗淡，请使用谷歌搜索它们，看看您是否在工具下缺少对该库的引用。

-E

-E

回答by Mark

For future reference -

供日后参考 -

I had this issue with this piece of code in Microsoft Access with the debugger highlighting the line with the comment:

我在 Microsoft Access 中的这段代码遇到了这个问题，调试器突出显示了带有注释的行：

Option Compare Database
Option Explicit

Dim strSQL As String
Dim rstrSQL As String
Dim strTempPass As String


Private Sub btnForgotPassword_Click()
On Error GoTo ErrorHandler

Dim oApp As Outlook.Application '<---------------------------------Offending line
Dim oMail As MailItem
Set oApp = CreateObject("Outlook.application") 'this is the "instance" of Outlook
Set oMail = oApp.CreateItem(olMailItem) 'this is the actual "email"

I had to select references that were previously unselected. They were

我必须选择以前未选择的参考文献。他们是

Microsoft Outlook 15.0 Object Library
Microsoft Outlook View Control

Microsoft Outlook 15.0 对象库
Microsoft Outlook 视图控件