vba - Excel VBA项目编译后崩溃

Question

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

主要思想

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 附近.

重要的是要注意我的项目中有大量的错误处理，但是，我无法识别这个特定的错误，因为它发生在初始化事件中(在这个特定的事件中不可能进行错误处理 [Bovey，专业 Excel 开发，第 489 页])。

问题:除了时间延迟(例如 Application.Wait 或 Sleep 通过 Windows API)，还有其他方法可以避免崩溃吗？

更新
事实证明，延迟应用程序也不能可靠地工作。我实际上也删除了整个 Initialize 事件也无济于事。我在原始帖子中忘记提及的一件事是我滥用了 Debug -->> Compile VBA Project特征。请看下面我的回答。

Answer 1

在处理了很长一段时间后，我的一位同事简单地注释了一行代码(不在 UserForm_Initialize 中，只是在某个随机模块中)，保存了文件，然后毫无问题地重新打开它。然后我们发现问题不在于代码，而在于 Debug -->> Compile VBA Project .在大多数情况下，我使用 Debug -->> Compile VBA Project ，大约每小时一次，因为我正在编码。然后我保存该文件并继续在同一个编译文件上进行开发。当一切都说完了，我可能会运行 Debug -->> Compile VBA Project在两个星期的过程中大约 100 次。然后我在 this website 上找到了来自 Chip Pearson 的评论。 :

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.

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.

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 自己发现的 here (您还可以在该网站上找到 Rob Bovey 的 Code Cleaner):

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 工作簿中，添加了相关库，并且没有(像以前一样)运行 Debug -->> Compile VBA Project .从那以后我没有任何问题。