59700

Excel VBA project crashing after compiling

Question:

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.

<strong>Main Idea</strong>

<blockquote>

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 320 KB.

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. Application.Wait or 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.

Answer1:

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>:

<blockquote>

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.

Recommend

  • Error when I try to show a SnackBar in a builder
  • Is it possible to parallelize awk writing to multiple files through GNU parallel?
  • How to create read transform using ParDo and DoFn in Apache Beam
  • explode() doesn't separate a string by spaces?
  • How to return only text after a comma in a string
  • Can pandas read a transposed CSV?
  • Download and install button in greyed out in Eclipse for Apache Tomcat v8.0
  • Spark throws java.util.NoSuchElementException: key not found: 67
  • How to “tint” image with css
  • Could not load file or assembly 'System.Net.Http'
  • List - do I pass objects or references?
  • Error mysqli_select_db [closed]
  • How to upload image to server?
  • How to stop the python turtle from drawing
  • How to integrate Struts2 with Thymeleaf?
  • ActivityThread.java Null pointer exception
  • Macbook React Native install
  • C# Create Control From a String Value
  • NextJS auth with an external server
  • iterating through image folder using javascript and adding the result in HTML
  • Specify the _id field using Bulk.IndexMany in ElasticSearch
  • in Gwt, there are 2 different packages (or 2 options) for doing drag n Drop? Which one is better?
  • Magento Layered Navigation block. Move to center
  • What is the diff. between default.properties and project.properties?
  • How to make a dependent dropdown in codeigniter
  • didSelectItemAtIndexPath of UICollectionView inside of a UIScrollView is not getting called
  • How to move to lines with the same indentation in Visual Studio Code
  • Firestore: Version history of documents
  • Implementation of timeout in LDAP
  • Arraylist of strings into one comma separated string
  • Query takes almost two seconds but matches only two rows - why isn't the index helping?
  • How to make Rss News Reader application in android …? [closed]
  • How do I add a mouse over tooltip to an Image using .DrawImage()
  • Google App Engine Datastore: Dealing with eventual consistency
  • How to handle div that is created dynamically in a table
  • ssh remote server login script
  • How to call different template for different category archive page in woocommerce