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.
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.</blockquote>
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
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]).
<strong>Question :</strong> With the exception of a time delay (e.g.
Sleep via Windows API), is there another approach to avoid crashing?
<br /><strong>UPDATE</strong><br />
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.
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 <a href="http://www.pcreview.co.uk/threads/what-does-compile-vbaproject-do.4009781/" rel="nofollow">this website</a>:
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.</blockquote>
And this is from Rob Bovey himself found <a href="http://www.appspro.com/Utilities/CodeCleaner.htm" rel="nofollow">here</a> (You will also find Rob Bovey's Code Cleaner at that website):<blockquote>
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.</blockquote>
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.