编译后Excel VBA项目崩溃 [英] Excel VBA project crashing after compiling

问题描述

我在一个项目中有一个很大的用户窗体，当它加载到内存中时会引起一些问题。 Userform_Initialize 事件中没有发生任何异常事件（只需填充组合框并设置默认属性）。
几周前，当用户表单还不够大（以KB为单位）时，一切工作都很好。最初，我认为工作簿已损坏，然后继续导出每个用户表单，模块和类，重新导入到新的工作簿中，然后像往常一样编译项目。这没有解决问题。有趣的是，当我在初始化事件的顶部放置 Stop 并逐步执行代码时，一切正常。

I have a large userform in a project that is causing some issues when it is loaded into memory. There isn't anything exotic happening in the Userform_Initialize event (just populating combo boxes and setting default properties). Everything was working just fine a few weeks ago when the userform wasn't as big (measured in KB). Initially, I thought the workbook was corrupted and proceeded to export every userform, module and class, re-import into a new workbook, and subsequently compiling the project as I've always done. This did not fix the issue. Interestingly enough, when I put a Stop at the top of the initialize event, and step through the code, everything works fine.

主要思想

这使我想到问题的可能原因是用户表单非常大，因此将用户表单加载到内存中的过程比典型加载花费的时间更长。本质上，vb编辑器将继续在initialize事件中执行代码，尝试访问可能尚未在内存中的控件。

This got me thinking that the possible cause of the issue is the fact that the userform is very large, thus the process of loading the userform into memory is taking longer than the typical load. Essentially, the vb editor is continuing to execute the code in the initialize event, attempting to access controls that may not be in memory yet.

我进行了一些粗略的分析，以很好地了解所讨论的用户群的规模。用户窗体已导出，然后重新导入到空白工作簿中。没有用户表单的工作簿大约为 30 KB ，而带有用户表单的工作簿则超过 350 KB ，因此我们可以得出结论，用户表单的大小约为 320 KB 。

I have done some crude analysis to get a pretty good idea of just how large the userform in question is. The userform was exported and re-imported into a blank workbook. The workbook without the userform was around 30 KB, and with the userform, the workbook was over 350 KB, so we can conclude that the userform is around 320 KB.

请务必注意，我在但是，在我的项目中，我无法识别出该特定错误，因为它是在initialize事件中发生的（该特定事件内无法进行错误处理[Bovey，Professional Excel Development，第489页]）。



问题：，但有时间延迟（例如 Application.Wait 或睡眠（通过Windows API），是否还有另一种方法来避免崩溃？

It is important to note that I have extensive error handling in my project, however, I'm unable to identify this particular error as it is occurring in the initialize event (Error handling is impossible inside this particular event [Bovey, Professional Excel Development, pg 489]).

Question : With the exception of a time delay (e.g. Application.Wait or Sleep via Windows API), is there another approach to avoid crashing?

UPDATE

事实证明，延迟应用程序也不可靠。我实际上已经删除了整个Initialize事件，但也无济于事。我在原始帖子中忘记提及的一件事是，我在滥用 Debug->>编译VBA项目功能。

推荐答案

处理了一段时间后，我的一位同事简单地注释了一行随机代码（不在 UserForm_Initialize 中，只是在某些随机模块中），保存了文件，然后重新打开它没有任何问题。然后，我们发现问题不在代码中，而是在 Debug->>中。编译VBA项目。在大多数情况下，我使用 Debug->>在我编写代码时，大约每个小时都要编译一次VBA项目。然后，我保存该文件并继续在相同的编译文件上进行开发。说完一切后，我可能会运行 Debug->>在两个星期的时间内，编译VBA项目大约100次。然后，我在此网站上找到了Chip Pearson的评论。 / a>：

After dealing with this for quite some time, a colleague of mine simply commented one random line of code (not in the UserForm_Initialize, just in some random module), saved the file, and reopened it with no issues. We then discovered that the issue was not in the code, but rather with Debug -->> Compile VBA Project. For the most part, I use Debug -->> Compile VBA Project, about once every hour as I'm coding. I then save that file and continue developing on that very same compiled file. When it is all said and done, I probably run Debug -->> Compile VBA Project about 100 times over the course of two weeks. I then found this comment from Chip Pearson on this website:

VBA代码永远不会存储为在
编辑器中键入的纯文本。输入立即转换为平台和
版本无关的字节码，称为OpCodes。这些操作码由编辑器
转换为您在屏幕上看到的文本。
编译项目时，编译器会将这些OpCodes转换为
特定于平台和版本的代码，称为ExCodes。当您运行
代码时，运行时将读取ExCodes，并基于ExCodes代表项目执行实际的机器代码
。整个过程在原理上类似于Java和Java虚拟机的工作原理。

VBA code is never stored as the plain text that you type in to the editor. Input is immediately converted to platform- and version-independent byte codes called OpCodes. These OpCodes are converted by the editor to the text you see on the screen. When you compile the project, the compiler translates these OpCodes to platform- and version-specific codes called ExCodes. When you run the code, the runtime reads the ExCodes and executes actual machine code on behalf of the project based on the ExCodes. This whole process is similar in principle to how Java and the Java Virtual Machine work.

如果要将所有VBA代码导出到文本文件，然后删除
的所有模块，然后将文本文件中的代码重新导入
到VBA（这正是Rob Bovey的Code Cleaner所做的工作），您将
看到文件减少尺寸。这是因为ExCode已清除
且尚未重新创建。然后，如果您编译项目，则
的文件大小将增加，因为现在它会将ExCode和
一起存储到OpCode中。

If you were to export all your VBA code to text files and then remove all the modules and then re-import the code from the text files back into VBA (which is exactly what Rob Bovey's Code Cleaner does), you'll see a decrease in file size. This is because the ExCodes were purged and have not yet been recreated. Then, if you compile the project, the file size will increase because now it stores the ExCodes in addition to the OpCodes.

永远不需要编译代码。 VBA会在必要时自动执行
。但是，编译命令还执行
语法检查，这是它唯一的实际用途。

You really never need to compile the code. VBA will automatically do it when necessary. However, the Compile command also does syntax checking, which is its only real practical purpose.

这是来自Rob Bovey自己在此处找到了（您还将在该网站上找到Rob Bovey的Code Cleaner）：

And this is from Rob Bovey himself found here (You will also find Rob Bovey's Code Cleaner at that website):

在创建VBA程序的过程中，文件中会堆积很多垃圾代码。如果您不定期清理文件，您将开始遇到由这种额外负担引起的奇怪问题。清理项目包括将其所有VBComponent的内容导出到文本文件，删除组件，然后从文本文件导入这些组件。

During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents to text files, deleting the components and then importing the components back from the text files.

然后，我按照上面在原始问题中所做的那样做。我导出了所有模块，将它们重新导入到新的excel工作簿中，添加了相关的库，并且DID NOT（与我之前一样）运行 Debug->编译VBA项目。此后我再也没有任何问题。

I then did just as I did above in the original question. I exported all modules, re-imported them into a fresh excel workbook, added the relevant libraries, and DID NOT (as I was before) run Debug -->> Compile VBA Project. I haven't had any issues since.

这篇关于编译后Excel VBA项目崩溃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！