在Initialize事件中大型用户模式不可预测地崩溃 [英] Large userform crashing unpredictably on the Initialize event

问题描述

我在项目中有一个大的userform，当它被加载到内存时导致一些问题。在 Userform_Initialize 事件中没有任何异常事件（只是填充组合框和设置默认属性）。
一切都在几个星期前很好，当userform不是那么大。最初，我认为工作簿已损坏，并继续导出每个用户窗体，模块和类，并重新导入到一个新的工作簿。这没有解决问题。有趣的是，当我在初始化事件的顶部放置一个 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. Initially, I thought the workbook was corrupted and proceeded to export every userform, module, and class and re-import into a new workbook. 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编辑器正在继续执行初始化事件中的代码，尝试访问可能不在内存中的控件。

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.

我做了一些粗略的分析，以得到一个很好的想法，有多大的userform有问题。用户窗体已导出并重新导入到空白工作簿中。没有用户格式的工作簿在 30 KB 周围，并且对于userform，工作簿超过 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.

重要的是要注意我有大量的错误处理我的项目，但是，我无法确定这个特定的错误，因为它发生在初始化事件（错误处理是不可能的在这个特定的事件[Bovey，Professional Excel Development，pg 489]）。



问题：除了时间延迟（例如 Application.Wait 或 Sleep 通过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

事实证明，延迟应用程序也无法可靠地工作。我实际上删除了整个初始化事件，也没有可用。我忘记在我的原始帖子中提到的一件事是，我是滥用 Debug - >>编译VBA项目功能。请参阅下面的答案。

UPDATE
It turns out that delaying the application didn't work reliably either. I have actually removed the entire Initialize event to no avail as well. One thing that I forgot to mention in my original post, was that I was abusing the Debug -->> Compile VBA Project feature. See my answer below.

推荐答案

在处理了这段时间后，我的一个同事简单地评论了一行随机的代码（不是在 UserForm_Initialize ，只是在一些随机模块），保存文件，并重新打开它没有问题。然后我们发现问题不在代码中，而是用 Debug - >>编译VBA项目。在大多数情况下，我使用 Debug - >>编译VBA项目，大约每小时一次，因为我编码。然后我保存该文件，并继续开发同样的编译文件。当所有的说和做，我可能运行 Debug - >>在两个星期内编译VBA项目大约100次。然后我从Chip Pearson在这个网站上发现这个评论：

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。这些OpCode由编辑器转换为您在屏幕上看到的文本
。当
编译项目时，编译器将这些OpCode翻译为
平台和版本特定的代码，称为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的代码清理器），你会
看到文件减少尺寸。这是因为ExCodes已清除
，但尚未重新创建。然后，如果你编译项目，
文件的大小将增加，因为现在它存储ExCodes另外
到OpCodes。

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.

Bovey自己发现了这里（您也可以在该网站找到Rob Bovey的代码清理工具）：

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.

这篇关于在Initialize事件中大型用户模式不可预测地崩溃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！