Excel VBA Compile会抛出一个“用户定义的类型未定义”的错误，但不会去到出错的代码行

Symptoms

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

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

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

这是一个“用户定义的类型未定义”的错误，我以前在一些简单的问题上见过，比如将一些东西命名为Strig而不是String。但是，这个特殊的错误只在调试> 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.

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

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.

为了确保没有遗漏任何明显的Dim语句，我在所有代码页(包括表单)中添加了显式选项，以确保没有遗漏任何内容。运行编译时仍然会出现错误。

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

还有一个已知的bug指出，由于使用二进制兼容性的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 VBA editor.

我通过这个问题和答案找到了这篇文章，但是我在标准的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 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?

编辑:我找到了违规的代码行，因此我的特殊问题得到了解决，在删除了特定的代码行之后，问题仍然存在——它是表单上被引用的一个错误拼写的控件名。这仍然不能解决您如何找到这个违规代码的问题。当这个错误发生时，我们是否能够找到一种好的方法来查找出错的代码，以便将来的其他人能够避免这种痛苦?

17 个解决方案

#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)

我的情况是:我有一个同事的。xlsm。我打开它，点击按钮:它工作得很好。2)我保存文件，关闭excel，再次打开文件:现在它不再工作了。结论是:代码可以，但是excel不能正确管理引用。(我试过删除重新添加的引用，但没有成功)

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

因此，解决方案是将每个引用的对象声明为变量，并使用CreateObject(“Foo.Bar”)而不是新的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")

#2

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!

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

#3

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.

我遇到了完全相同的问题(当我试图在userform上实现接口时，似乎总是会出现这种情况。从这里下载并安装代码清理程序。这是一个免费软件工具，在很多情况下拯救了我。打开VBA项目后，运行“Clean Code…”选项。确保在运行clean之前检查“备份项目”和/或“导出所有代码模块”到安全位置。据我所知，该实用程序导出并重新导入所有模块和类，从而消除了代码中出现的编译错误。对我来说是一种魅力!祝你好运。

#4

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”模块重命名为“route”。因此问题是通过将按钮指向正确的位置来解决的。

#5

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.

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

#6

I had this problem with an ordinary VB6 program. It turned out that I had omitted a class definition, not a user-defined type. Apparently VB saw something like "Thing.name" and assumed Thing was a UDT. Yes, it's a serious VB6 bug, but you could hardly expect Microsoft to support something they sold sixteen years ago. So what versions of the various products involved are you using? This is only interesting if it occurs with a product that MS supports.

我在一个普通的VB6程序中遇到了这个问题。结果是我省略了类定义，而不是用户定义的类型。显然，VB看到了类似的东西，名字，假设是UDT。是的，这是一个严重的VB6缺陷，但你很难指望微软会支持他们16年前出售的东西。所以你使用的是不同版本的产品?只有当它发生在MS支持的产品中时，才会有趣。

#7

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中，我将“作为Range”调暗，但在Access中不存在Range。您可能在使用变量类型时没有打开适当的引用库。

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

如果您有任何非标准dims谷歌，请查看是否在tools下丢失了对该库的引用。

-E

- e

#8

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视图控件。

#9

I was able to fix the error by

我能修正错误

1. Completely closing Access
2. 完全关闭访问
3. Renaming the database file
4. 重命名数据库文件
5. Opening the renamed database file in Access.
6. 在访问中打开重命名的数据库文件。
7. 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.
   • 之前的文件也被标记为受信任的文档。
8. 接受各种安全警告和提示。我不仅选择启用宏，而且还接受将重命名的数据库作为受信任的文档。之前的文件也被标记为受信任的文档。
9. Successfully compile the VBA project without error, no changes to code.
10. 成功地编译VBA项目，没有错误，没有修改代码。
11. 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.
12. 编译成功后，我可以再次关闭访问权限，将其重命名为原始文件名。我不得不回复相同的安全提示，但是一旦我打开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.
• 至少在我经历错误的部分时间里，我的VBA窗口显示了两个额外的“神秘”项目。遗憾的是，我在解决错误之前没有记录姓名。一个是ACXTOOLS。里面的模块无法打开。
• 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.
• 当我为尝试一些极端的措施(比如一次删除一个表单/模块)做备份时，偶然发现了这个修复。但是在打开备份副本时，问题不会再次发生。

#10

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

脚本。字典类型，你可以使用晚装订(如已指出)与:

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能够找到脚本。通过VBA——>工具——>引用——>“Microsoft脚本运行时”向Microsoft脚本库添加引用来进行字典类型的输入。然后您可以使用:

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

... and auto completion will work.

…自动完成也可以。

#11

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版本都有一个不同的库。FYI，当我激活2007图书馆时，我经历了一些奇怪的错误和文件损坏，但是有人试图使用Excel 2003打开和运行这个宏。旧版本的Excel不认可新的库，这似乎会造成问题。

#12

Late Binding

后期绑定

This error can occur due to a missing reference. For example when changing from early binding to late binding, by eliminating the reference, some code may remain that references data types specific the the dropped reference.

这个错误可能是由于缺少引用而导致的。例如，当从早期绑定更改为后期绑定时，通过消除引用，一些代码可以保留引用特定于已删除引用的数据类型的引用。

Try including the reference to see if the problem disappears.

试着包括参考，看看问题是否消失了。

Maybe the error is not a compiler error but a linker error, so the specific line is unknown. Shame on Microsoft!

可能错误不是编译器错误，而是链接器错误，所以具体的行是未知的。在微软的耻辱!

#13

After years I have discovered one, if not the, answer to the Microsoft bug of the 'User-defined type not defined' error in Excel. I'm running Excel 2010 for Windows.

多年之后，我发现了一个，如果不是，那就是“用户定义类型的Microsoft bug”，在Excel中没有定义错误。我正在为Windows运行Excel 2010。

If you have a UDF named for example 'xyz()', then if you invoke a non-existent entity beginning with that name followed by a period followed by other chars -- e.g., if you try to invoke non-existent range name 'xyz.abc', the stupid app throws that wrong msg., after which it returns you to your sheet.

如果您有一个名为“xyz()”的UDF，那么如果您调用一个不存在的实体，以该名称开头，后面跟着其他字符，例如，如果您试图调用不存在的范围名称“xyz”。这个愚蠢的应用程序扔错了味精。，然后它返回你的工作表。

In my case it was especially unnerving, because I have UDFs named with just one letter, e.g. x(), y(), etc., and I also have range names that include periods--'x.a', 'c.d', etc. Every time I, say, misspelled a range name--for example, 'x.h', the 'User-defined …' error was thrown simply because a UDF named 'x()' existed somewhere in my project.

在我的例子中，这尤其令人不安，因为我用一个字母命名了UDFs，例如x()、y()等，而且我还有包含句号的范围名称——“x”。“,”c。d'等等。每次我拼错一个范围的名字，比如，'x 'h'，“用户定义的…”错误仅仅是因为在我的项目中存在一个名为“x()”的UDF。

It took several hrs. to diagnose. Suggestions above to incrementally remove code from your project, or conversely strip all code and incrementally add it back in, were on the right track but they didn't follow thru. It has nothing to do with code per se; it has only to do with the name of the first line of code in each proc, namely the Sub MyProc or Function MyProc line naming the proc. It was when I commented out one of my 1-letter-named UDFs in a completely unrelated part of the project that the bugged error msg. went away, and from there, after another hr. or so, I was able to generalize the rule as stated.

花了几个小时才完成。诊断。上面关于逐步从项目中删除代码的建议，或者相反地删除所有代码并逐步将其添加回项目中的建议，都是正确的，但是它们没有遵循thru。它本身与代码无关;它只有与第一行代码的名字在每个proc,即子MyProc或函数MyProc行命名proc。当我注释掉了我的一个1-letter-named udf在一个完全无关的项目的一部分,窃听错误味精。离开，从那里，在另一个人力资源部。或者是这样，我能够概括规则。

Perhaps the bug also occurs with punctuation characters other than period ('.') But there aren't very many non-alpha chars permitted in a range name; underline ('_') is allowed, but using it in the manner described doesn't seem to throw the bug.

也许除了句号('.')之外，标点符号也会出现错误，但在范围名称中不允许有太多非字符字符字符;下划线('_')是允许的，但是以描述的方式使用它似乎不会引起错误。

Jim Luedke

吉姆Luedke

#14

I had the same error yesterday: I had two classes, cProgress and cProgressEx, in my project, one of which was no longer used, and when I removed cProgress class I was given this same compilation error.

昨天我犯了同样的错误:在我的项目中，我有两个类，cProgress和cProgressEx，其中一个不再被使用，当我删除了cProgress类时，我得到了同样的编译错误。

I managed to fix the error as follows:

我设法修正了以下错误:

• Exported all modules, forms and classes to the hard drive
• 将所有模块、表单和类导出到硬盘驱动器
• Removed everything from the project
• 从项目中删除所有内容
• Saved the project
• 保存该项目
• Reimported all modules, forms and classes, removed cProgress class, and compiled.
• 重新导入所有模块、表单和类，删除cProgress类并编译。
• The error disappeared.
• 错误消失了。

#15

An different problem with the same symptom: I had a class implemented which was not defined. It was wrapped with a #if that I thought should have not allowed the compiler to see it, but not so. Remove the comment out the Implements statement and all is well. I assume importing the definition would also work...

同样症状的另一个问题是:我实现了一个未定义的类。如果我认为它不应该让编译器看到它，那么它就被一个#包括起来了，但事实并非如此。删除实现语句中的注释，一切正常。我认为导入定义也可以……

#16

I had the same issue, if you enable Microsoft Scripting Runtime you should be good. You can do so under tools > references > and then check the box for Microsoft Scripting Runtime. This should solve your issue.

我也有同样的问题，如果你启用了Microsoft脚本运行时，你应该是很好的。您可以在>引用>的工具下这样做，然后检查Microsoft脚本运行时的复选框。这应该能解决你的问题。

#17

A bit late, and not a complete solution either, but for everyone who gets hit by this error without any obvious reason (having all the references defined, etc.) This thread put me on the correct track though. The issue seems to originate from some caching related bug in MS Office VBA Editor.

有点晚了，而且也不是一个完整的解决方案，但是对于每个在没有任何明显原因(定义了所有引用等等)的情况下被这个错误击中的人来说，这个线程使我走上了正确的道路。这个问题似乎源自微软Office VBA编辑器中一些与缓存相关的bug。

After making some changes to a project including about 40 forms with code modules plus 40 classes and some global modules in MS Access 2016 the compilation failed.

在MS Access 2016中，对一个包含40个包含代码模块和40个类和一些全局模块的表单进行了一些修改后，编译失败。

Commenting out code was obviously not an option, nor did exporting and re-importing all the 80+ files seem reasonable. Concentrating on what had recently been changed, my suspicions focused on removal of one class module.

注释代码显然不是一个选项，导出和重新导入所有80+文件也不合理。专注于最近发生的变化，我的怀疑集中在删除一个类模块。

Having no better ideas I re-cereated an empty class module with the same name that had previously been removed. And voliá the error was gone! It was even possible to remove the unused class module again without the error reappearing, until any changes were saved to a form module which previously had contained a WithEvents declaration involving the now removed class.

由于没有更好的想法，我重新处理了一个空类模块，该模块的名称与之前删除的名称相同。沃里亚错误消失了!甚至可以在不出现错误的情况下再次删除未使用的类模块，直到将任何更改保存到一个表单模块中，该模块以前包含了包含现在已删除的类的WithEvents声明。

Not completely sure if WithEvents declaration really is what triggers the error even after the declaration has been removed. And no clues how to actually find out (without having information about the development history) which Form might be the culprit...

不完全确定WithEvents声明是否真的是在声明被删除之后触发错误的原因。而且没有任何线索表明(没有关于发展历史的信息)哪种形式可能是罪魁祸首……

But what finally solved the issue was:

但最终解决问题的是:

1. In VBE - Copy all code from Form code module
2. 在VBE中，从表单代码模块中复制所有代码
3. In Access open Form in Design view and set Has Module property to No
4. 在设计视图中访问开放表单，并将模块属性设置为No。
5. Save the project (this removes any code associated with Form)
6. 保存项目(这将删除与表单相关的任何代码)
7. (Not sure if need to close and re-open Access, but I did it)
8. (不确定是否需要关闭和重新打开访问，但我做到了)
9. Open Form in Design view and set Has Module property back to Yes
10. 打开窗体在设计视图和设置有模块属性回到Yes
11. In VBE paste back the copied out module code
12. 在VBE中粘贴复制的模块代码
13. Save
14. 保存